SQL Server – Performance tuning – DBCC FLUSHPROCINDB – Flush procedures of a particular database from cache


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)

image

References

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!

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s