Finding Non-SARGable queries

I was in Rob Farley's sessions at SQLBits when he was talking about: Understanding SARGability (to make your queries run faster)

It got me thinking....

Is there a way I can find out which SQL queries are doing table or index scans?

So I put some proof of concept code (POC) together.

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)

WHERE

CONVERT(varchar(max), query_plan) like '<tablescan%'

OR

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.

References:

http://msmvps.com/blogs/robfarley/archive/2010/01/22/sargable-functions-in-sql-server.aspx

http://sqlbits.com/Agenda/event7/Understanding_SARGability__to_make_your_queries_run_faster_/default.aspx