Identifying Query Plans that are not good enough

After doing my usual daily checks I noticed that I had an unread blog entry – Reason for Early Termination of Statement by Grant Fritchey (Blog|Twitter) in my RSS Feeds, Grant’s posts are always very good so I put a few moments aside to take a look through and I’m glad that I did!

If you haven’t read Grant’s post then you really need to for two reasons:

  1. It’s really good
  2. The following will make no sense if you don’t as it’s a directly linked.

Grant has done an excellent job of reminding us all that the optimizer is fallible and on occasions will not find the best plan. What we need is a way to find these plans …….

The first thing you need to do is to open SSMS and connect to the database that you wish to check for procedure plans that aren’t good enough, then we’re ready to go.

Run the following code, preferably in your development environment:

 

If you have some bad plans, you will have an output similar to the one below:

image

Now you’re left with the task of simplifying the queries so the optimizer can do a better job.