T-SQL Query Plan Analysis Prelude

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?

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.

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?

Jorge Candeias's Picture

About Jorge Candeias

Jorge helps organizations succeed by building high-performing solutions on the Microsoft tech stack.

London, United Kingdom https://jorgecandeias.github.io