In T-SQL Query Plan Analysis Prelude we looked at some common reasons why queries can sometimes go on a snail speed contest and that query plans are one of the ways we can know how to transform those carefree molluscs into caffeinated cheetahs.

So how exactly can query plans help us do that? Are they like magic wands that turn over-ripe pumpkins into dubious-expiry carriages and make used teeth disappear in return for equally used pennies?

Well… Hold that thought for a minute.

To see how understanding query plans can dispense some fairy dust on our happy queries to make them fly, we need to go back to T-SQL itself.

T-SQL, The Wise

You may have already heard that T-SQL is a declarative language. But what does this mean?

A declarative language is one in which we declare what data or behaviour we want to see from the target system, and then let the system itself figure out the best way to accomplish it. This contrasts with an imperative language, in which we do specify how the system needs to go about its business.

T-SQL is a declarative language because we use it to declare what data we want to bring back from SQL Server, and what it should look like. However, we do not use T-SQL to tell SQL Server how to go about reading pages from the disk, what algorithms to use when matching data, or how to work the coffee machine.

So why is this important?

SQL Server, The Optimized

Have you noticed the trade-off in using T-SQL? In exchange for only declaring what data we want to see, we forfeit the ability to control how that data is retrieved. But is this really a bad thing?

For any given query, there can be a myriad of ways in which the data can be fetched. Some ways will be better than others. There are countless obscure factors to consider in deciding the best way of doing so, such as in what data pages the data rests, the statistical distribution of keys, what algorithms to use to match the data, and many more. Far too many for a regular human being to consider every single time without being committed to the nearest loony house. Fortunately, SQL Server’s query plan optimizer tends to do an excellent job of making the best of what is available at any given moment. Almost as good as a tax collector, one has to say.

So can’t we have any influence over how data is retrieved?

Well, technically we can. In desperate times, we can call upon the powers of the dark side of the force in the form of hints and query plan guides.

The question, however, is should we really call upon them?.

Telling SQL Server to retrieve data in a certain way indeed appear very attractive in the beginning. However, it ultimately chains a heavy cast iron ball to the query optimizer. Think Beagle Boys in the brig here, while they’re not busy pilfering Scrooge’s money bin. Telling SQL Server how to run its business can make it unable to react to advantageous changes in the database structure or nature of the data. While the dark side can be very seductive, like good disciples of the Force, patience we must have and a better way we must seek, so says Yoda in his gurified grammar.

Naturally, there are times when we’ll have no choice left but to heed to the power of the dark side. For example, we might really need to optimize a query on a vendor database, and neither be allowed to change the database structure nor the query itself. Or when our query is so insanely yet irreducibly complex, the optimizer throws the towel. But these are always exceptions, to be considered when better ways have been exhausted.

And are there better ways?

Yes, young padawan, as Yoda would say. This is where the query plan comes in. All hail the query plan. Donations at the desk, please.

A query plan can tell us three very important things.

• How SQL Server intends to retrieve the data.
• How the data was really retrieved.
• What we can tweak so the query acts less like a snail and more like a cheetah.

It tells us what we can tweak? Really?

Yes, really.

Like a guiding lighthouse in a foggy night at sea, a query plan can bring attention to the more problematic places in our query, allowing us to moor our boat safely in search of a solution. It then tops up our cargo hold with a surprising amount of useful information about the query. We can use this information to gain insight on how to optimize the query or the database itself to transform that window shopping snail into a cheetah on speeds.

Cool! Is This All I Need?

No, not really.

Though very important, query plans are but one of several gadgets in our super-hero utility belt. In the next pumpkin, before we start the serious deeds of valour, we’re going to take a look at what other toys we have available in our quest to rid the world of dubious queries.