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 affect 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 series 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).
