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 cacheSELECT 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.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!