SQL Fascination

Some time back, Pinal Dave published a blog entry with an example of an XML script that used XQuery to examine the query cache – the script in itself is a very useful example of using XQuery against the query plans, but doesn’t quite hit the mark in terms of being an invaluable performance tuning script since it provides an information overload and doesn’t help locate those annoying query problems.

Using it as inspiration however, you might find this useful when tracking down dodgy queries. A number of key items have been added:

• The database the scan has occurred in.
• The schema the scan has occured in.
• The table name the scan has been performed on.

Understandably, very useful fields to additionally expose, since these allow filtering of the results to exclude tables that are of no interest due to their size (small dimension / lookup tables for example.)

WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
CachedPlans
(DatabaseName,SchemaName,ObjectName,PhysicalOperator, LogicalOperator, QueryText,QueryPlan, CacheObjectType, ObjectType)
AS
(
SELECT
Coalesce(RelOp.op.value(N'TableScan[1]/Object[1]/@Database', N'varchar(50)') , 
RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Database', N'varchar(50)') ,
RelOp.op.value(N'IndexScan[1]/Object[1]/@Database', N'varchar(50)') ,
'Unknown'
)
as DatabaseName,
Coalesce(
RelOp.op.value(N'TableScan[1]/Object[1]/@Schema', N'varchar(50)') ,
RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Schema', N'varchar(50)') ,
RelOp.op.value(N'IndexScan[1]/Object[1]/@Schema', N'varchar(50)') ,
'Unknown'
)
as SchemaName,
Coalesce(
RelOp.op.value(N'TableScan[1]/Object[1]/@Table', N'varchar(50)') ,
RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Table', N'varchar(50)') ,
RelOp.op.value(N'IndexScan[1]/Object[1]/@Table', N'varchar(50)') ,
'Unknown'
)
as ObjectName,
RelOp.op.value(N'@PhysicalOp', N'varchar(50)') as PhysicalOperator,
RelOp.op.value(N'@LogicalOp', N'varchar(50)') as LogicalOperator,
st.text as QueryText,
qp.query_plan as QueryPlan,
cp.cacheobjtype as CacheObjectType,
cp.objtype as ObjectType
FROM
sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)
)
SELECT
DatabaseName,SchemaName,ObjectName,PhysicalOperator
, LogicalOperator, QueryText,CacheObjectType, ObjectType, queryplan
FROM
CachedPlans
WHERE
CacheObjectType = N'Compiled Plan'
and
(
PhysicalOperator = 'Clustered Index Scan' or PhysicalOperator = 'Table Scan' or
PhysicalOperator = 'Index Scan')

The final alteration is the limitation of the results to only those query plans that include scans, although you could use this to target hash matches or other potentially expensive operations that indicate there is a query plan / indexing opportunity to investigate.

Using this script makes it far easier to run through the query cache and can easily be further modified to include a link to the sys.dm_exec_query_stats via the plan_handle so you could also pull the execution count for the queries with the scans to further prioritize performance tuning work.