SQL Server Plan Cache: The Junk Drawer for Your Queries

We all have them. Those drawers in our house that contain a jumbled mess of items accumulated over the years. These items don't have any specific place to be stored, so they end up as part of a catchall that many people call the "junk drawer." For example, the junk drawer in my house contains items used frequently (e.g., four pairs of scissors), less frequently (e.g., slightly broken cheese slicer), and almost never (e.g., corncob holders). There are also items that are used less frequently because they're meant for emergencies, such as flashlights and Hello Kitty Band-Aids.

Related: Do You Have Hidden Cache Problems?

There's an area of memory called the plan cache or procedure cache inside of SQL Server that's dedicated to storing execution plans. I often refer to the plan cache as the "junk drawer" for SQL Server because it contains plans that are used frequently, less frequently, and almost never (or just once).

Related: Fine-Tuning Plan Reuse

The biggest difference between a junk drawer and the plan cache is that the plan cache doesn't store any actual junk. The execution plans in the plan cache serve an important role. When a query is run, SQL Server first checks the plan cache to see if an existing plan already exists. If it finds one, it doesn't need to spend time compiling a new plan. Thus, the execution plans in the plan cache make the engine more efficient and typically lead to better performance. However, just as you need to occasionally clean your junk drawer, you need to periodically examine your plan cache to see which plans are still good, which plans need fixing, and which plans can be thrown away.

What's in Your Plan Cache?

Your plan cache stores details about all the SQL statements that have been executed over time. Besides examining its contents to investigate performance problems (i.e., reacting to a situation), you need to take a more proactive stance by periodically looking for information that can tell you about the health of your SQL Server instance. For example, the plan cache can tell you about the:

Knowing about and dealing with these types of plans can help keep your system operating at peak efficiency. Let's take a closer look at how to find them.

How to Find Single-Use Plans in Your Plan Cache

As I mentioned previously, single-use plans are a lot like those corncob holders in the junk drawer. Because they're not being used, they're just taking up space.

By itself, having a plan stored for a statement that was executed only one time isn't an issue. A problem arises when you have many single-use plans filling your plan cache. This is known as plan cache bloat. As these plans fill your plan cache, SQL Server will have less space to store other plans. This results in degraded performance, which typically manifests as an increase in CPU consumption. The increase in CPU consumption is due to the database engine building new execution plans because SQL Server wasn't able to find and reuse an existing plan.

To minimize plan cache bloat, SQL Server 2008 introduced the configuration option optimize for ad-hoc workloads. This option tells SQL Server to not store the full plan the first time a query is executed. Only a plan stub is created, taking up less space inside the plan cache. When the plan is used a second time, the fully compiled plan is stored. The optimize for ad-hoc workloads option isn't enabled by default. There's little to no downside to enabling it on your servers.

However, the optimize for ad-hoc workloads option shouldn't be seen as a fix for a system that's suffering from plan cache bloating. It only delays the inevitable. At some point, you'll need to find and remove the single-use plans, either by manually removing them from the plan cache or by modifying the application that's issuing the queries. There will be times when neither option is viable, in which case you might need to increase the amount of memory available for SQL Server.

You can use the query in Listing 1 to find the plans that have been used only once. This query looks for objects of type Compiled Plan. It doesn't look for objects of type Compiled Plan Stub because the amount of memory consumed by all these stubs is typically fairly small. Plus, they're among the first objects to be removed from the plan cache by SQL Server when memory pressure exists.

SELECT text, cp.objtype, cp.size_in_bytesFROM sys.dm_exec_cached_plans AS cpCROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) stWHERE cp.cacheobjtype = N'Compiled Plan'AND cp.objtype IN(N'Adhoc', N'Prepared')AND cp.usecounts = 1ORDER BY cp.size_in_bytes DESCOPTION (RECOMPILE);

The query in Listing 1 returns the text and size of each single-use plan. To get an idea of how much of the plan cache is allocated to these single-use plans, you can compare the total amount of memory used by the single-use plans to the size of the entire plan cache. Kimberly Tripp provides a query to accomplish this task in her blog "Plan cache and optimizing for adhoc workloads."

If you see more than 50 percent of your plan cache being taken up by single-use plans, I recommend that you enable the optimize for ad-hoc workloads option (assuming that you haven't already done so). In addition, if you have more than 2GB of memory being used for single-use plans, I suggest that you also implement some corrective measures. However, that 2GB line in the sand depends on the entire amount of RAM available for SQL Server.

If you'd like to clear your plan cache periodically of single-use plans, you can use the scripts that Tripp presents in "Plan cache, adhoc workloads and clearing the single-use plan cache bloat." Although these scripts were written for SQL Server 2008 and SQL Server 2005, they also work for SQL Server 2012 and SQL Server 2008 R2.

How to Find Plans That Need Tuning

Many DBAs are interested in knowing whether the plan cache includes any plans that need tuning. To find such plans, you can look for plans that have missing indexes, implicit conversion warnings, Key Lookup operators, or Clustered Index Seek operators.

You can quickly find the plans that have missing indexes by using a script like that in Listing 2. However, before adding indexes to your tables, you need to thoroughly test them to make sure that they don't negatively affect any delete, update, and insert operations.

;WITH XMLNAMESPACES(DEFAULTN'http://schemas.microsoft.com/sqlserver/2004/07/showplan')SELECT dec.usecounts, dec.refcounts, dec.objtype  , dec.cacheobjtype, des.dbid, des.text,deq.query_planFROM sys.dm_exec_cached_plans AS decCROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS desCROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deqWHEREdeq.query_plan.exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0ORDER BY dec.usecounts DESC

To look for plans that have implicit conversion warnings, you can use the script in Listing 3. Implicit conversion warnings indicate a mismatch between the data type used in the query and the data type defined in the database. The most common mismatch is using an integer value in a query that's run against a column defined as VARCHAR or NVARCHAR in the database.

;WITH XMLNAMESPACES(DEFAULTN'http://schemas.microsoft.com/sqlserver/2004/07/showplan')SELECTcp.query_hash, cp.query_plan_hash,ConvertIssue =  operators.value('@ConvertIssue','nvarchar(250)'),Expression =  operators.value('@Expression','nvarchar(250)'),  qp.query_planFROM sys.dm_exec_query_stats cpCROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qpCROSS APPLY query_plan.nodes('//Warnings/PlanAffectingConvert')  rel(operators)

If you find that your plans have a lot of implicit conversion warnings, you need to take one of two corrective steps: Change the column data type or change your code. I recommend that you try changing the code before you start changing the column data type.

To look for plans that have Key Lookup or Clustered Index Seek operators, you can use the script in Listing 4. It returns a row for every operator inside of every plan in your cache. Note that this script might take a few minutes to run on a system with a large plan cache.

;WITH XMLNAMESPACES(DEFAULTN'http://schemas.microsoft.com/sqlserver/2004/07/showplan')SELECTcp.query_hash, cp.query_plan_hash,PhysicalOperator =  operators.value('@PhysicalOp','nvarchar(50)'),LogicalOp = operators.value('@LogicalOp','nvarchar(50)'),AvgRowSize = operators.value('@AvgRowSize','nvarchar(50)'),EstimateCPU =  operators.value('@EstimateCPU','nvarchar(50)'),EstimateIO = operators.value('@EstimateIO','nvarchar(50)'),EstimateRebinds =  operators.value('@EstimateRebinds','nvarchar(50)'),EstimateRewinds =  operators.value('@EstimateRewinds','nvarchar(50)'),EstimateRows =  operators.value('@EstimateRows','nvarchar(50)'),Parallel = operators.value('@Parallel','nvarchar(50)'),NodeId = operators.value('@NodeId','nvarchar(50)'),EstimatedTotalSubtreeCost =  operators.value('@EstimatedTotalSubtreeCost','nvarchar(50)')FROM sys.dm_exec_query_stats cpCROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qpCROSS APPLY query_plan.nodes('//RelOp') rel(operators)

Although the script in Listing 4 returns all plan operators, you could modify it to filter the operators as needed. Jonathan Kehayias provides a good example of code that returns only Key Lookup operators in his blog post "Finding Key Lookups inside the Plan Cache." When examining plans, I like to concentrate on Key Lookup operators because they can be easily fixed by adjusting indexes.

Just like you can use those tiny eyeglass repair kit screwdrivers in your junk drawer to fix a slightly broken cheese slicer, the scripts that I just discussed can be used to fix plans that are slightly broken or need tuning. For example, they can be used to fix queries that are missing an index.

How to Find Plans That Are Similar

Having execution plans that are similar is like having four pairs of scissors in a junk drawer. You really need only one pair of scissors. The rest are just taking up space.

To help find plans that are similar and just taking up space, Microsoft introduced the query hash in SQL Server 2008. The query hash lets you group queries that are similar in function but differ in literal values. Knowing about the query hash is important because it gives you the opportunity to further maximize plan reuse.

For example, consider the following two queries that differ only by the literal value being searched for:

SELECT P.FirstName, P.LastNameFROM Person.Person AS PWHERE P.FirstName = 'Amanda'GOSELECT P.FirstName, P.LastNameFROM Person.Person AS PWHERE P.FirstName = 'Logan'GO

Each query generates its own plan, but the queries are logically equivalent. You can see this by using the code in Listing 5.

SELECT st.text, qs.query_hashFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) stWHERE st.text = 'SELECT P.FirstName, P.LastNameFROM Person.Person AS PWHERE P.FirstName = ''Amanda'''OR st.text = 'SELECT P.FirstName, P.LastNameFROM Person.Person AS PWHERE P.FirstName = ''Logan'' 'GO

As Figure 1 shows, there are two SQL statements, but they have the same query hash.

Figure 1: Examining the Query Hashes to See Whether the Queries Are Logically Equivalent

By grouping queries by their query hashes, you can find out how many queries in the plan cache are logically equivalent. To do this, you can use the code in Listing 6. As you can see in Figure 2, the code returns a list of the query hashes.

Figure 2: Listing the Query Hashes and the Associated SQL Text

The Count column shows how many times each query hash occurs in the plan cache. For example, the first row reveals that seven queries have the query hash of 0x0BF736956D4C0960. The Text column includes the text for all the queries associated with the query hash. For example, in the first row, the Text column will include the text from all seven queries that have the hash value of 0x0BF736956D4C0960.

SELECT COUNT(*) AS [Count], query_stats.query_hash,  query_stats.statement_text AS [Text]FROM  (SELECT QS.*,  SUBSTRING(ST.text,(QS.statement_start_offset/2) + 1,  ((CASE statement_end_offset    WHEN -1 THEN DATALENGTH(ST.text)    ELSE QS.statement_end_offset END  - QS.statement_start_offset)/2) + 1) AS statement_text    FROM sys.dm_exec_query_stats AS QS    CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST)    AS query_statsGROUP BY query_stats.query_hash, query_stats.statement_textORDER BY 1 DESC

As this example shows, this information lets you see the frequency with which some SQL statements are being executed over time. If you see that you have many statements with the same query hash, you should consider creating one parameterized statement to be used instead. This will allow the engine to store and reuse one plan instead of many plans. Storing more plans means that fewer plans need to be compiled, which improves performance.

If you can't change the code to create one parameterized query, you might consider using plan guides in an effort to maximize plan reuse. You can read more about the use of plan guides in "Specify Query Parameterization Behavior by Using Plan Guides."

A Wealth of Information Is Available

The plan cache has a wealth of information pertaining to the overall health of your SQL Server instance. By examining the plan cache, you can find out if your system is being bogged down by single-use plans, if you have many queries that need tuning, or if you have many queries that are logically equivalent.

For more information about plan caches, you should read Greg Low's excellent white paper "Plan Caching in SQL Server 2008." It explains how plans are cached, how you can maximize plan reuse, and how SQL Server manages the memory allocated to the plan cache.

It's important that you take the time to examine what's inside your plan cache in an effort to be proactive. Over time, it'll become second nature for you to think about examining the plan cache in response to performance questions. And it wouldn't hurt to clean out your junk drawer once in a while, either.

**************************************

Special thanks to Tim Chapman, a Premier Field Engineer (PFE) at Microsoft, for his help with some of the queries in this article.