Finding Non- SARGable queries

SARGable means Search ARGument Able and relates to the ability to search through an index for a value. Now this might impact your queries in a huge way. This will tell you whether your query is performing a table scan or a column/index scan.

Given below is an crude attempt to get all the queries and then differentiate between them on the basis of scan they perform

 SELECT top 10 text ,query_plan ,*
FROM sys.dm_exec_query_stats
 CROSS APPLY sys.dm_exec_query_plan(sys.dm_exec_query_stats.plan_handle)
 CROSS APPLY sys.dm_exec_sql_text (sql_handle)
CONVERT(varchar(max), query_plan) like '<tablescan%'
 CONVERT(varchar(max), query_plan) like '<IndexScan%'
ORDER BY total_elapsed_time desc;

Its not a very sophisticated query (it only took a few minutes to write and yes, I agree, I should shred the xml instead of using a convert and like) but it shows the concept of querying the execution plans to get details of queries that has run (rather than the best way of getting the information). It takes a while to run so don’t go running it in LIVE, and might bring back some false positives – so please be careful with the results.

About the author


Leave a Reply

Your email address will not be published. Required fields are marked *