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

The SQL Server plan cache can be a good tool to use in identifying current performance issues and in looking for new ways to improve performance.

In this second part of a three-part article series, we’ll look at how to examine the plan cache to quickly identify plans that need tuning, including:

• Plans that have missing indexes
• Plans that are performing key lookups
• Plans with implicit conversion warnings

When you fix these plans, and often the fix is quite simple, you can improve overall performance.

Find and fix plans with missing indexes

You can examine the plans currently inside your plan cache and look for ones that have missing index hints (Figure-1 is an example from SolarWinds Database Performance Analyzer).

 

Figure 1. Using SolarWinds Database Performance Analyzer to examine plans inside plan cache.

 

To find all current plans that have missing indexes, use the following script:

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

 

Be careful, though. Adding indexes can negatively impact any deletes, updates, and inserts. Test thoroughly to make certain that adding an index won’t create new problems.

Find and fix plans with implicit conversion warning

Plans with implicit conversion warnings likely indicate a mismatch in the datatype used in the query with the datatype defined in the database. A very common example of this type of mismatch is use of an integer value in the query for a column defined as VARCHAR or NVARCHAR.

To find the plans that have implicit warnings run this code:

 

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

 

If you detect plans with implicit conversion warnings you can:

• Change the column datatype
• Change the code

From experience, I recommend trying to change the code before changing the column datatype.

 

Find and fix plans with clustered index seeks and key lookups

You can also examine the plan cache to find plans with clustered index seeks and key lookups, which are good targets for tuning.

For example, use the following code to return a row for every operator inside of every plan in the cache. If you don’t want all the operators, you can modify this code to filter as needed.

NOTE

It might take a few minutes to run this on a large plan cache.

 

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
cp.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 cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY query_plan.nodes('//RelOp') rel(operators)

I often focus just on key lookups at this stage, because they can be easily fixed by adjusting indexes.

See Jonathan Kehayias’ excellent example of this at: http://www.sqlskills.com/blogs/jonathan/finding-key-lookups-inside-the-plan-cache/.

 

More opportunities in the plan cache: single-use plans and plans that need tuning

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