Performance tuning & analysis during new development has it’s own unique challenge – on one hand as database developers, we need a clean procedure cache and on the other hand, we do not want to impact the performance of other databases on the same instance.

To start from a cold procedure cache, we know that we can use the DBCC FREEPROCCACHE. However, this would completely clear out the procedure cache – impacting others who may be working on the same instance, but on other databases. We can supply the plan/query handle or pool name to reduce the impact of the DBCC FREEPROCCACHE, but because the plan handle changes each time the plan is regenerated, a lot more manual steps become involved in the process.

To restrict the clearing of the procedure cache to the current database only, we can use the DBCC FLUSHPROCINDB command. The DBCC FLUSHPROCINDB command only accepts one parameter – the database ID.

For example, the following command will flush the procedures in cache for the AdventureWorks2008R2 database on my SQL Server instance.

/*
!!!!WARNING!!!!
This script is provided AS-IS and without warranty.
The author, Nakul Vachhrajani; the website, BeyondRelational.com
and Microsoft Corproation are not responsible for any damage caused
by misuse of this script
*/

--Fetch the DB_ID for the required database
USE AdventureWorks2008R2
GO
SELECT DB_ID() AS AdventureWorks2008R2DBID

--Clear out the procedure cache for the AdventureWorks2008R2 database ONLY
DBCC FLUSHPROCINDB (5)

References

• Plan caching in SQL Server 2008 – http://msdn.microsoft.com/en-us/library/ee343986(SQL.100).aspx

NOTE: To the best of my knowledge, this is an undocumented DBCC command. Please use it with utmost caution. If you find the official Books On Line documentation, please let me know and I will update the post to reflect the same.

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!