After putting up a primer post about statistics last week, my mind has now jumped to another facet of performance: cached plans.

Cached plan objects, whether they are for procedures, ad-hoc statements, views, etc., can have both a negative and positive effect on system performance. There are some general guidelines for when you will want to consider freeing the cache in order to negate when the cache plan is having a negative effect on system performance:

  • Whenever you make database schema changes to widely used objects
  • Large loads of data or heavy transactions higher than average
  • If the database servers up ad-hoc reports
  • You are getting ready to do performance testing

The SQL statement to clean up the cache is: dbcc freeproccache [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ].

On the three optional parameters you can use with this statement:

  • Plan_handle is the varbinary(64) identifier for the in memory plan. This identifier is constant only while this plan remains in system cache. It can be found in the sys.dm_exec_sql_text, sys.dm_exec_query_plan or sys.dm_exec_plan_attributes DMVs

 

  • SQL_handle is also a varbinary(64) identifier. It refers to the batch or stored procedure that a query is a part of in the system. It can be found in the sys.dm_exec_query_stats, sys.dm_exec_requests, sys.dm_exec_cursors, sys.dm_exec_xml_handles, sys.dm_exec_query_memory_grants or sys.dm_exec_connections DMVs  

       

  • Pool_name is the name of a Resource Governor resource pool and is a sysname. You can find more detail in the sys.dm_resource_governor_resource_pools DMV.

       

For heavy performance testing, or for a server that is really bogged down, the dbcc DROPCLEANBUFFERS statement is a way to clean out the cold buffer cache without restarting the server. It is recommended though, to run a CHECKPOINT command; this command forces SQL to write all dirty pages to disk.

After this has completed, then run the DROPCLEANBUFFERS command against the system to clean out the system buffer. Note that for pure reporting systems with no writes back to the database, this is not a required step.