Systems Engineering and RDBMS

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:

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.

3 Responses to “Procedure Cache Bloating issues – I”

  1. dbaguy said

    Just a reminder for those people on case sensitive systems be careful in your examples with case, for example
    SYS.DM_EXEC_CACHED_PLANS is really
    sys.dm_exec_cached_plans.

  2. […] adhoc workloads: We have discussed the need of having this parameter before – you can read about it here.  This parameter does help in working around the issue though is not the same as the options […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: