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;