Systems Engineering and RDBMS

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
name,
type,
(single_pages_kb + multi_pages_kb) AS cache,
entries_count as cnt
FROM sys.dm_os_memory_cache_counters
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:

sql_plans.jpg

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:

SELECT
cache_plan.objtype,
cache_plan.size_in_bytes,
cache_plan.cacheobjtype,
cache_plan.usecounts,
sql_text.text
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”

  1. […] in some of our previous blog posts to demonstrate some code – you can read about those examples here and here. In this post, we will cover what this operator really does. The APPLY operator allows one […]

  2. […] Procedure Cache in SQL Server 2005 […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: