APPLYing Your SQL Knowledge With Cached Plans And XPath – TSQL Tuesday #17
Posted by Josh | Posted in SQL Server | Posted on 12-04-2011
Tags: plan cache, SQL Server, XML, XPath
4
It’s the second Tuesday of the month, which means it’s time for T-SQL Tuesday again! This month is hosted by Matt Velic, a fellow former accidental DBA who I had the pleasure of meeting at SQL Saturday in Philadelphia this year. The topic is the APPLY operator, and I can think of no better way to illustrate its use than with XPath and some of the absolutely awesome DMV’s available for interrogating the plan cache.
One of the big challenges I’ve faced as a DBA is proactively finding and fixing bad code before it reaches a critical stage and causes issues. One way I’ve found to effectively do this is to monitor and interrogate the plan cache on a server, looking for problem children and areas of concern across several dimensions. Fortunately for us, execution plans are stored as XML, which means we can use XPath to seek out specific elements which could indicate a poor query.
For example, here’s a query that will look at the top 10 most expensive query plans in terms of average logical IOs:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT TOP 10 DB_NAME(CAST(qpa.value AS INT)) DBName, qs.total_logical_reads / qs.execution_count avg_logical_reads, SUBSTRING(st.text, CASE WHEN qs.statement_start_offset IN (0,NULL) THEN 1 ELSE qs.statement_start_offset/2 + 1 END, CASE WHEN qs.statement_end_offset IN (0,-1,NULL) THEN LEN(st.text) ELSE qs.statement_end_offset/2 END - CASE WHEN qs.statement_start_offset IN (0, NULL) THEN 1 ELSE qs.statement_start_offset/2 +1 END ) query_text, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) qpa WHERE qpa.attribute = 'dbid' AND qpa.value > 4 ORDER BY qs.total_logical_reads / qs.execution_count DESC |
Now this information in and of itself is fairly useful, but using a little more APPLY magic and some XPATH, we can get even more information. Let’s say, for instance, we’d like to pull out all clustered index scans. In XML execution plans, every one of these little nasties is represented by a “@PhysicalOp="Clustered Index Scan". Easy enough to find using the nodes() XPATH function, at which point we can also gather some useful numbers such as estimated IO and CPU cost.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), TopIOQuery AS ( SELECT TOP 10 DB_NAME(CAST(qpa.value AS INT)) DBName, qs.total_logical_reads / qs.execution_count avg_logical_reads, SUBSTRING(st.text, CASE WHEN qs.statement_start_offset IN (0,NULL) THEN 1 ELSE qs.statement_start_offset/2 + 1 END, CASE WHEN qs.statement_end_offset IN (0,-1,NULL) THEN LEN(st.text) ELSE qs.statement_end_offset/2 END - CASE WHEN qs.statement_start_offset IN (0, NULL) THEN 1 ELSE qs.statement_start_offset/2 +1 END ) query_text, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) qpa WHERE qpa.attribute = 'dbid' AND qpa.value > 4 ORDER BY qs.total_logical_reads / qs.execution_count DESC ) SELECT t.DBName, t.avg_logical_reads, CAST('<q> ' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(t.query_text,' OR ',' OR ' + CHAR(10)),' AND ',' AND ' + CHAR(10)),'&','&amp;'),'>','&gt;'),'<','&lt;') + ' </q>' AS XML) Query, t.query_text, RelOp.Col.value('(./@PhysicalOp)[1]','VARCHAR(200)') Operation, ISNULL(RelOp.Col.value('(.//Object[1]/@Schema)[1]','SYSNAME'),'tempdb.') + '.' + RelOp.Col.value('(.//Object[1]/@Table)[1]','SYSNAME') TableName, RelOp.Col.value('(./@EstimateRows)[1]','FLOAT') EstimatedRows, RelOp.Col.value('(./@EstimateCPU)[1]','FLOAT') EstimatedCPU, RelOp.Col.value('(./@EstimateIO)[1]','FLOAT') EstimatedIO, RelOp.Col.value('(./@EstimatedTotalSubtreeCost)[1]','FLOAT') EstimatedCost, RelOp.Col.query('./OutputList') OutputList, CAST('<p> ' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RelOp.Col.value('(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]','VARCHAR(MAX)'),' OR ',' OR ' + CHAR(10)),' AND ',' AND ' + CHAR(10)),'&','&amp;'),'>','&gt;'),'<','&lt;') + ' </p>' AS XML) Predicate, RelOp.Col.query('.') RelOpXML, t.query_plan QueryPlan FROM TopIOQuery t CROSS APPLY t.query_plan.nodes('//RelOp[@PhysicalOp="Clustered Index Scan"]') RelOp(col) WHERE RelOp.col.value('(.//Object/@Schema)[1]','SYSNAME') <> '[sys]' ORDER BY avg_logical_reads DESC,query_text,EstimatedIO DESC |
You could modify this query to return just about set of information you like. Execution plans are a veritable gold mine of information. If you’re liking what you see, I’d highly recommend you read the excellent series “Can You Dig It” by Jason Strate (blog | twitter).
One final note: I would be careful about running these on a live instance during busy hours, they can be quite resource hungry. Happy plan querying!
