Performing Fast Searches of Query Plans

Performing Fast Searches of Query Plans

I've made blog posts in the past on using the exist() method to search for query plans with specific attributes (e.g., Find Cached Query Plans By Index Name). Another good aspect of the exist() method is that it is a much faster way to search for a specific string inside of a query plan than trying to roll your own search by converting to text.

I decided to write this post after reading a great post by Grant Fritchey (blog|@ScaryDBA) called Querying Information from the Plan Cache, Simplified. In this post, Grant compares rolling your own search of the XML vs. extensive parsing of the XML vs. doing a text search of text plan from sys.dm_exec_text_query_plan. SQL Server is not an exceptional performer when it comes to extensive XML parsing. Likewise, converting the query plan to text and then searching it using string functions like LIKE is not a great solution either. Grant was able to get really good performance by doing the LIKE search on the text query plan from sys.dm_exec_text_query_plan.

However, you can actually do very quick string searches within XML using the exist() method. Grant was able to get his query that performs extensive XML parsng down from 30 seconds to 8 seconds using sys.dm_exec_text_query_plan. Now to be perfectly clear, Grant's query runs much faster on my laptop than it does on whatever machine he was using. Likely, my cache is much smaller right now due to less activity. His text solution runs in 3 seconds on my laptop. My solution using the exist() method runs in 2 seconds. It's only slightly faster than the text solution.

Is the speed difference enough to make it worth the extra effort of figuring out how to write the exist() clause? Maybe not. But if you do it enough, that part isn't so painful. It was actually very easy to write the exist() clause for my solution. Give Grant's post a read to see his versions of the queries. Here is my solution using the exist() clause:

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sqlx) 
SELECT DB_NAME(deqp.dbid),
    SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,
    (CASE deqs.statement_end_offset
        WHEN -1 THEN DATALENGTH(dest.text)
        ELSE deqs.statement_end_offset
    END - deqs.statement_start_offset) / 2 + 1) AS StatementText,
    deqs.statement_end_offset,
    deqs.statement_start_offset,
    deqp.query_plan,
    deqs.execution_count,
    deqs.total_elapsed_time,
    deqs.total_logical_reads,
    deqs.total_logical_writes
FROM    sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE deqp.query_plan.exist('//sqlx:StmtSimple[@StatementOptmEarlyAbortReason="TimeOut"]') = 1;