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:
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.
Procedure Cache Bloating issues – I
Posted by decipherinfosys on December 4, 2008
We had covered in one of our posts before how the usage of non parameterized adhoc SQLs in an application can create performance issues by bloating the procedure cache and lamented the fact that in SQL Server there is no parameter setting to help take control of the cache (unlike Oracle which does provide you a lot of control). You can access those posts here:
- Procedure Cache in SQL Server 2005
- Bind Variables/Parameterized Queries in SQL Server
- 64 bit vs 32 bit – covers the memory advantages.
So, if you are new to a project and/or you do not know the current application well enough, how can you easily tell whether the applications hitting your production system are running into this issue of procedure cache bloating because of in-efficient code? Use this query to get that information:
SELECT
OBJTYPE AS PLAN_TYPE,
COUNT(*) AS PLAN_NUMBERS,
(SUM(CAST(SIZE_IN_BYTES AS BIGINT))/1024)/1024 AS SIZE_MB,
AVG(USECOUNTS) AS USE_COUNT
FROM SYS.DM_EXEC_CACHED_PLANS
GROUP BY OBJTYPE
PLAN_TYPE PLAN_NUMBERS SIZE_MB USE_COUNT
-------------------- --------------- -------------------- -------------
UsrTab 15 0 20
Prepared 8319 891 9
View 694 60 13
Adhoc 28794 1307 6
Check 18 0 17
Trigger 1 0 8
Proc 162 78 134
(7 row(s) affected)
If you see above, you will see that in the PLAN_TYPE of “Adhoc”, the number of plans are huge and they also are taking up the most memory. Their use counts are very low as well. This is a clear indication of the issue that the system is facing. How to fix it? Besides fixing the application to write good parameterized code, you can also looking into the setting the “Forced Parameterization” option in SQL Server 2005. In SQL Server 2008, there is another instance level parameter “Optimize for Adhoc Workloads” which we will cover in Part II of this post.
So, is there any way to stop the bleeding without clearing up the entire cache? There is a way in SQL Server 2005. One can use the following command to clear out the adhoc and prepared plan types but still keep the Proc plan type intact in the cache.
DBCC FREESYSTEMCACHE(‘SQL Plans’)
PLAN_TYPE PLAN_NUMBERS SIZE_MB USE_COUNT
-------------------- --------------- -------------------- -------------
UsrTab 15 0 20
View 694 60 13
Adhoc 1 0 1
Check 18 0 17
Trigger 1 0 8
Proc 162 78 134
(6 row(s) affected)
Post the execution of the command, you can see from the output from above, the selective removal of the two enteries in the Procedure Cache. Procedure cache consists of different cache stores and it is possible to selectively remove some of those from the cache. You can read more about the different cache stores and the meta data queries to understand the plan cache behavior at this post on MSDN or this post on sqlteam.com.
Now, once the immediate bleeding has been stopped by running the command, what else can you do – we had mentioned the Forced Parameterization option above. You can set it at the database level by using the “ALTER DATABASE” command or via the GUI as well (search the BOL for Forced Parameterization and you will get the steps to do so). This forces the parameterization for the values in the adhoc SQL queries submitted by the applications. Only under certain scenarios like this one it is advisable not to use parameterization but otherwise in all the OLTP based applications, one should strive to have parameterized queries – the benefits are listed in one of the posts the link of which is given above. This is useful in those scenarios when you are asked to manage a vendor application and do not have much control over the application code – this option as well as plan guides are your best options in those scenarios.
In the next post, we will cover the new SQL Server 2008 parameter which kinda/sorta lets you have some more control on the procedure cache.
