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:
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
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.