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)
- 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,