How big is your procedure cache?

Part of SQL Server running queries is that once a query is analyzed, parsed and compiled, that compiled plan is kept in memory so that it can be quickly re-used in the future. If you are using proper parameterized queries, most of your queries will get kept around and re-used saving lots of CPU and memory on SQL Server. If you don’t use parameterized queries, you may be filling up your SQL Server memory with cached plans that may never get used again.  So take a look at how much memory is being taken up by your SQL Plans.

 

-- How big is the procedure cache

SELECT name,

SUM(single_pages_kb + multi_pages_kb) / 1024.0 mbused

FROM   sys.dm_os_memory_clerks

WHERE  name = ‘SQL PLans’

GROUP  BY name;

 

One thing to keep in mind with the procedure cache is that it is not the size that matters, but what you can do with it.

If the amount of memory being used in plans is greater that you expected, or greater than you like, you can use the SQL Server health reports to track down some of the culprits.

For more info, come see my presentation at SQL Saturday on March 17, 2012 in Vancouver BC.