Improve SQL Server Performance by Looking at Plan Cache (Part 1)

The SQL Server plan cache stores details on statements that are being executed over time. Each time a statement executes SQL Server will look inside the plan cache first to see if a plan already exists. If a plan exists SQL Server will use that plan instead of spending time compiling a new plan. This ensures the engine operates efficiently.

The plan cache holds a great deal of information about the overall health of your database instance. You can use the plan cache to investigate current performance issues as well as proactively look for opportunities to improve performance. By examining the plan cache, for example, you might discover:

• there are too many single-use plans
• there are many queries that need tuning
• there are many queries that are logically equivalent

In this first part of a three-part article series, we’ll look at how to discover single-use plans inside the plan cache, how to assess whether the single-use plans are impacting performance, and how to reduce the impact of single-use plans on your database instance.

How to find single-use plans inside your plan cache?

Storing a query plan for a statement that is executed only once, also known as a single-use plan, isn’t a problem by itself. However, storing many single-use plans can fill the plan cache. When the plan cache is filled with many single-use plans (also known as plan cache bloat) and there is no space to store new plans the result can be a degradation of SQL Server performance. This performance hit is often seen as an increase in CPU consumption. This happens because SQL Server couldn’t find and reuse an existing plan, forcing the database engine to build a new plan.

Do you have many single-use plans in the plan cache?

To find out what single-use plans have been stored for your instance issue this statement to get the text and size of the single-use plans in your plan cache:

SELECT text, cp.objtype, cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype IN (N'Adhoc', N'Prepared')
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC 
OPTION (RECOMPILE);

Note that in the previous example, we are looking for objects of the type Compiled Plan. There is also another object called a Compiled Plan Stub.?For more on that, see the “About the optimize for ad hoc workloads configuration option” section below.

Are single-use plans impacting performance?

To find out if single-use plans are causing problems in your instance, you can measure the amount of memory used by single use plans and then compare that to the size of the entire plan cache.

There is no official recommended amount of memory consumption for single-use plans compared to the plan cache as a whole. Any possible answer will, of course, depend on the total amount of RAM available for SQL Server. I recommend that if more than 50% of the plan cache is single-use plans, you should consider enabling ‘optimize for ad-hoc workloads’ option (see the “About the optimize for ad hoc workloads configuration option” section below) if you haven’t already.

In most cases, if you have more than 2GB of memory used for single-use plans, you will likely need to take action as well. Again, this 2GB limit depends on how much RAM you have available for SQL Server itself.

Use the following query to make this comparison (thanks to Kimberly Tripp for this code http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/):

 

SELECT objtype AS [CacheType]
        , count_big(*) AS [Total Plans]
        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
        , avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC

What to do if single-use plans are bloating the plan cache

If you determine that your plan cache is bloated because of single-use plans, and that those single-use plans are impacting performance negatively, you have several options including:

• Manually clearing the plan cache of single-use plans
• Enabling the optimize for ad hoc workloads configuration option to reduce the number of single-use plans stored in the plan cache

If these options aren’t available, or don’t mitigate the problem, you will need to consider adjusting the configuration of the applications issuing the queries that result in single-use plans, and possibly increasing the amount of memory available for SQL Server.

Minimizing plan cache bloat by clearing the plan cache

To periodically clear the plan cache of single-use plans, use these scripts made available from Kimberly Tripp:https://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/. Although these scripts were built for SQL2005 and SQL2008, they still work for SQL2008 R2 and SQL2012.

Minimize plan cache bloat with the optimize for ad hoc workloads configuration option

SQL Server 2008 introduced the ‘optimize for ad-hoc workloads’ configuration option which, when enabled, causes SQL Server to create a plan stub instead of a full plan the first time a statement is executed. If the plan is used a second time, then SQL Server will store the full plan. This option was introduced to help minimize the performance impact of plan cache bloat.

Though this configuration option is not enabled by default, we recommend you enable it, because there is little or no downside to doing so. The amount of memory taken up by the plan stubs is typically small, and the stubs are among the first objects that SQL Server will remove from the plan cache when memory pressure exists.?

It’s important, though, not to consider the?’optimize for ad-hoc workloads’?option as a permanent fix for a system with continuous plan cache bloating. As mentioned earlier, you may also need to consider alterations to the applications issuing the queries that cause single-use plans to be created, or making more memory available to SQL Server.

More opportunities in the plan cache: plans that need tuning and logically equivalent queries

Be sure to read this full article for more information about how you can improve SQL Server performance by identifying plans that need tuning (Part 2) and minimizing queries that are logically equivalent (Part 3).