Get all SQL Statements with an table scan in cached query plans

IImagine that one day you come to work and you have time to look for the overall quality of the execution of your queries; I know, like myself, you probably never had such a day at work.

This script, even though it is looking for table scan's can easily be updated to look for anything in execution plans. One could have it look for looping over large rows (typical for symptom of skewed statistics) or other less performing methods.

What does this script do

It queries the system tables of the server and looks for the query execution plans that in it's XML have, in this case a table scann

The script goes over all executions on a given server.

  • Parameters
  • The Script
Parameters in the script

The Script does not use parameters, unfortunately it's all hard coded.
To download the script click here.

Important to know

One needs to compare the statement with the objectives of the statement and find possible root causes. For a table scan, this might be that the compiler does not find an index, or that the index is disabled.