T-SQL Query Plan Analysis Prelude

Have you ever found yourself pulling your own hair with both hands, desperate to understand why that stubborn T-SQL query runs so desperately slow? Were you intrigued by the cool if complex visuals of its query plan? Did you try to turn its knowledge into insight but couldn’t figure out a Hash Match from a Nested Loop?

Query Plan Example

If so, this series might be for you.

There comes a time in every project where some queries start running as fast as a snail tied to a heavy anvil. Why does this happen? Why can’t those queries be more like a cheetah on a double espresso? Well, there can be a myriad of reasons why this happens. So which reasons tend to be the most common? If you’ve been doing this stuff for a while, you might have seen issues such as network communications getting clogged up with big queries, low memory on the server, dubious database design or a worrying lack of coffee during development activities.

Slow Query

I think the last one is especially important, at least for a java junkie such as yours truly. The number of times I’ve found myself writing dubious T-SQL code is inversely proportional to having had that morning brew in the same day.

Now while I doubt it will ever be able to help with coffee deprivation issues, a query plan can be a valuable source of insight for troubleshooting performance gone astray in our problematic query. Of course, a query plan is but one of multiple source of insight we have available. We might need to evaluate each one of those sources in order to understand how to fix a specific wobble.

It is therefore important to know both what we can and can’t do with a query plan.

During this series, we’ll be exploring a number of questions, such as:

  • What can query plans do for us? And what can’t they?
  • How can we capture and look at query plans?
  • How can we interpret those query plans and make sense of them?
  • What common performance-devouring patterns can we look out for?

I hope this series will be as useful to you as it will be fun for me.

So how about you? Have you been adventuring in query plans for long? What have been your greatest head-scratchers? And what would like to understand better?


Next Pumpkin: How Can Query Plans Help Us?
Pumpkin Index: Learn / T-SQL Query Plan Analysis


Photo by Liz West is licensed under CC BY 2.0

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s