I’ve always been a big fan of analysing the SQL workload to find expensive and long duration queries, the best tool by far that I regularly use is RML utilities http://www.microsoft.com/downloads/en/details.aspx?FamilyId=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en
The toolset is just plain amazing in the wealth of details that it can give. i have yet to be on a performance tuning gig where the results don’t take everyone by surprise when they find that some seemingly trivial part of the workload is actually 200 times more expensive than anything else.
The problem with this is approach is that generating the trace file is quite intrusive and fiddly. We have the new DMV’s but they just don’t present the data in the same way.
While on the SQL Ranger course in 2008 with fellow SQL geek Dirk Gubbels from Belgium (picture below) introduced me to the idea of a “zero touch” performance review using the new dynamic management views and some fancy xpath queries to find poor query plans. I found the idea fascinating as it would also allow for retrospective analysis of the SQL workload without the heavy instrumentation process, but never really got into it.
Well a few years later I actually started to write some code to analyse the query plans. Even though I come from a dot.net background with lots of experience of message queuing and XML data contracts and xml manipulation in dot.net, xpath in sql still eludes me. Its something that I use on a project and then instantly forget to make room for something sexier – a bit like complex regex 😉
Luckily, Bob Beauchemin a fellow MVP and member of the kick ass sqlskill’s team that brings us Paul Randal and Kimberly Tripp has one of the best working examples I could Google of this approach
Bob’s sample (below) has great parameterisation of the XML, allowing for checking for various operators
CREATE PROCEDURE LookForPhysicalOps (@op VARCHAR(30))
AS
SELECT sql.text, qs.EXECUTION_COUNT, qs.*, p.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE query_plan.exist('
declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
') = 1
GO
EXECUTE LookForPhysicalOps 'Clustered Index Scan'
--EXECUTE LookForPhysicalOps 'Hash Match'
--EXECUTE LookForPhysicalOps 'Table Scan'
Another good link with sample queries is below:
and here is one by me to check which queries in the top ten most expensive by logical reads have table scans, clustered index scans or hash matches.
SELECT top 10 sql.text
, qs.EXECUTION_COUNT
, case when query_plan.exist('
declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = "Hash Match"]'
)=1 then 1 else 0 end as hash_match
, case when query_plan.exist('
declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = "Table Scan"]'
)=1 then 1 else 0 end as table_scan
, case when query_plan.exist('
declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = "Clustered Index Scan"]'
)=1 then 1 else 0 end as clustered_index_scan
, qs.*
, p.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
order by [total_logical_reads] desc
Now maybe its on my blog I won’t forget the syntax again!
