Using System Tables to Analyse CI Scans for Specific Table

I had a situation this week, where I wanted to see what queries were causing CI scans and table scans. Now this sounds fairly straight forward. You can find CI Scans by looking in sys.dm_db_index_usage_stats, and query text can be found by using sys.dm_exec_sql_text. But how to join the 2 together, as there is no plan handle in the index dmvs?

Well, this is the solution I came up with. I'm sure there is a better way of doing it, and if you know of one, then please feel free to leave a comment. (Using XQuery against a table holding the query plans and then using a CTE to find the query text does not count as a better way!!!) ;-)

Basically, what I am doing in the query below, is using sys.dm_exec_query_stats as a base to find the query handle and plan handle, and then using CROSS APPLY to run the sys.dm_exec_query_plan and sys.dm_exec_sql_text functions, to retrieve the actual plan and text, respectively.

I am then simply dumping the query and it's associated plan into a temp table and running a simple query to pull out the results I'm after. You can simply tinker with the WHERE clause to get a whole variety of useful information.

I have converted the XML to NVARCHAR, not because I am scared of XQuery (Please see the XML category of my BLOG if you don't believe me!!) :-) It's simply because the XML is schema bound to a very complex schema, that is a bit of a nightmare to work with, as I required nodes from multiple namespaces, although I will try this method and post about it at a later date.

Anyway, I digress, here is the query...

...I hope you find this a useful edition to your script library.

SELECT [text], cast(query_plan AS NVARCHAR(MAX)) AS qplan INTO ##qplans
FROM sys.dm_exec_query_stats qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)
      CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)

SELECT *
FROM ##qplans
WHERE [text] like '%PUT TABLE NAME HERE%'
     AND [qplan] like '%clustered index scan%'

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom