Posted in SQL Server, T-SQL Tuesday

T-SQL Tuesday #131 – It’s like this…

It’s another T-SQL Tuesday, that monthly blog party. It’s kind of like a Halloween party but instead of costumes and candy, we write blog posts about a topic related to SQL Server.

Thanks to Rob Volk (b|t)for hosting this month’s T-SQL Tuesday. Rob has tasked us with using an analogy for explaining something in SQL Server.

I don’t know if I have a favorite analogy for what I do. But let’s see if this one works:

Pretend I have to run errands around town. I need to go to the grocery store, hardware store, and return something at the department store.

I’m also making a list as to what I need from each store. At the grocery store, I need to pick up a couple of the basics: eggs, a loaf of bread, half & half for my coffee and some cheddar cheese. At the hardware store, I need to pick up some nails to hang some pictures. In addition, I have to return a shirt that I bought online that is the wrong size.

The first thing I need to do is figure out which stores I need to go to and which order. So I open up Google map and determine which stores are closest to each other and what order it makes sense to go to each one.

Now that I know what I need to do, I run my errands and get everything done fairly efficiently. It worked great. As these are some fairly common errands, I now have my plan of attack for the next time I need to do these.

Over the next couple of months, I notice this works well for the most part. Sometimes my list changes slightly – I need to get mozzarella instead of cheddar or I am exchanging the shirt that didn’t fit for the size that does instead of just returning it, but it doesn’t seem to make a difference so the route I’ve come up with works well.

But other times, I notice things don’t quite work as smoothly. When I run my errands on the weekends, it seems like everyone else has the same idea so I’m having to wait in line a lot more. And why is checkout lane 4 always closed? Or if I need to cook for a holiday or special occasion, it takes me a lot longer to get everything at the grocery store since I need a lot more ingredients.

And then sometimes random things happen. Remember that time I went into the department store to return a dress and walked out with an Instant Pot? And then another time, there was car accident so it took me longer to get from the hardware store to the grocery store; traffic was at a standstill.

Some of these things I can figure out before I leave the house, like when my lists for each store are drastically different than usual, so I can decide on a new route before I go. Other times, I just don’t have that information or something else unexpected comes along and I’m stuck with the plan I have and can’t do anything about it because the problem has nothing to do with the route I’ve mapped out.

So have you figured out the analogy yet? It’s SQL Server execution plans.

Just as I’m planning out where I need to go, SQL Server figures out the good enough plan for getting the data it needs. Sometimes it works well when you have differences – like needed mozzarella instead of cheddar – but then it doesn’t when you suddenly need something completely different, like returning a shirt but buying a household appliance instead. If you have loads that require different sizes, like shopping for a holiday meals, SQL Server can figure out that it may want to use different indexes if they’re available so it may find a more efficient plan to use.

But you also have to remember that at times, queries aren’t slow because of a bad execution plan but other things like heavier server workloads at given times, like running errands on the weekend, or someone else’s query blocking you, like getting stuck in traffic because of a car accident.

These are all things you have to pay attention to when writing and troubleshooting a query using the execution plan.

Now if only my actual errand list could be made more efficient….