Procedure cache in SQL Server 2005
Posted by decipherinfosys on September 19, 2007
In one of our previous post, we had covered the importance of using bind variables (parameterized queries). In response to that post, one of the readers asked whether it is possible to look at what is available in the memory for the RDBMS. Yes, it is pretty easy to get to that information. In this post, we will cover how to do that in the case of SQL Server 2005 and then will cover Oracle and DB2 LUW in future posts. In the case of SQL Server, memory is used for buffer cache (storing the data) and procedure cache (storing the query plans). The cache is stored as 8KB pages (Oracle has more options on this size). Let’s see how we can find out what is in the procedure cache and how often those plans are getting used.
In SQL Server 2005, there is a DMV that can be used to get this information – the SQL is shown below:
SELECT top 5
(single_pages_kb + multi_pages_kb) AS cache,
entries_count as cnt
ORDER BY cache desc
On our test system, this is the output:
name type cache cnt
------------------------ ---------------------- -------------------- ------
Object Plans CACHESTORE_OBJCP 24352 46
Bound Trees CACHESTORE_PHDR 20648 252
SQL Plans CACHESTORE_SQLCP 19432 292
TokenAndPermUserStore USERSTORE_TOKENPERM 14488 31167
SchemaMgr Store USERSTORE_SCHEMAMGR 10584 0
If you see the output from above, you will see CACHESTORE_OBJCP, CACHESTORE_PHDR and CACHESTORE_SQLCP as the top three cache related enteries. Each has it’s own importance. CACHESTORE_OBJCP represents the compiled plans for stored procedures, triggers and functions, CACHESTORE_SQLCP represents cached SQL statements and batches that are not part of stored procedures/triggers/functions and CACHESTORE_PHDR represents the parsed SQL text. On our test system, we have a few stored procedures that are used by the test harness and there are a lot of dynamic SQL queries that are fired off by the test application that uses an ORM layer. That is why the count for CACHESTORE_OBJCP is 46 and CACHESTORE_SQLCP count is 305.
While I was writing this post, a colleague of mine also pointed out that all this information is also available through the performance monitor. Here is an image that shows you which counter you can use to get that information:
Once you get the counts, the next logical step is to look for the actual queries that are in the system cache. In order to do that, we will make use of two more DMV’s in SQL Server 2005 and will make use of the new “OUTER APPLY” functionality:
FROM sys.dm_exec_cached_plans as cache_plan
outer apply sys.dm_exec_sql_text (cache_plan.plan_handle) as sql_text
ORDER BY cache_plan.usecounts DESC
One can look at the output and see how much space is being occupied by different plans. Since SQL Server does not provide a configuration option to put a cap on the procedure cache, if the application is not using parameterized queries, you will see this cache to be blotted. Hopefully, like Oracle, Microsoft can also provide a configuration option in the future to keep that in check – of course, there is no alternative to a well designed application however, as consultants brought in to tune the environment in production, re-design or fixing the fundamental building blocks of the application is rarely an option that we have.
2 Responses to “Procedure cache in SQL Server 2005”
Sorry, the comment form is closed at this time.