Posts Tagged ‘MSSQL Server’

As you may know, SQL Server will create a query plan for each batch it executes. The plan is then stored in plan cache for future re-use. However if your application uses lots of ad-hoc queries (like S3D filters for e.g.), you will end up with a lot of cached plans that are used only once and are just lying idle, taking space and wasting memory.

So how do you check the usage of such plans? You can do it two ways:

  • Run this query against your database
SELECT Count(*) Total, SUM(size_in_bytes)/1048576 MBs
FROM sys.dm_exec_cached_plans 
WHERE cacheobjtype LIKE 'Compiled Plan%' 
And objtype = 'Adhoc' And usecounts=1

  • Run a scan of your database server using SCA (System & Configuration Analyzer) 5.6.5 or later. The AdHoc counters are shown under MSSQL Info node as shown below:


So now you know the adhoc plan usage but how do you limit it’s growth? Luckily SQL Server 2008 has added a configuration option, “optimize for ad hoc workloads”, that can be used to improve the efficiency of the plan cache. With this options set to 1, the SQL engine will store a small compiled plan stub in the plan cache when a batch is compiled the first time, instead of a full compiled plan.

If you use SCA, you can see the current settings for this option under MSSQL Config Options node:


Alternatively you can run sp_configure to show advanced options:


Since it’s an advanced option, you need to reconfigure it with override. So to enable it, you would need to set the run_value to 1.


In some cases, like mentioned here , you may need to periodically monitor the cache and clear it manually using the command, DBCC FREESYSTEMCACHE(‘SQL Plans’), or create an automated SQL server job to do that.

To help with an automated solution, SCA now includes a job to clear the Adhoc cache:


The job is then invoked as per the schedule and shows up in the Job Manager view:


The job is only invoked if the cache size is greater than 500 MBs



Hopefully this gives you an idea on how to manage your “cache plans”…till next time.


Read Full Post »