Last year I was fortunate to present a session titled ‘Guillotines, Sat-Nav and the Query Optimiser’ at some SQL user groups and a couple of conferences too (thanks again Data Scotland and Data Relay). I chose a session on the query optimiser as the longer that I’ve worked with SQL Server the more I have really come to appreciate how it works.
I’ve decided to turn the session into a few (OK, it was a few at the time but it’s getting a bit more than that!) blog posts which I hope readers will find interesting because to me the query optimiser really is the magic box of SQL Server. We all use it every time we run a query but it’s mainly hidden away doing its thing completely unnoticed and because of that it’s also not that often that we need to have a look at what it’s actually doing.
Declarative and Imperative Languages
For starters it’s important to understand that T-SQL behaves differently to some other languages and that’s because it is what’s known as a declarative language. Languages that I used back in the day when I was more of a developer are what’s known as imperative languages and there’s one fundamental difference between the two types.
To put it bluntly imperative languages do exactly as they’re told. If I run the following code (which I have, many, many times):
10 PRINT "DAVE IS AWESOME" 20 GOTO 10
the code is going to be followed exactly how it is written. There’s nothing to think about, there’s no decisions to be made along the way, it just does exactly what it says on the tin (to steal a catchphrase).
T-SQL and other declarative languages are a bit different. We still give instructions in the form of queries but inside SQL Server it’s up to the query optimiser to decide how our query should be processed, this decision making element is the main different between the two types of language.
Query complexity determines the amount of decisions the optimiser has to make to determine the most optimal way (or more accurately what is probably the most optimal way) of processing our query which takes the form of an execution plan, which is then passed to the execution engine.
Although only one execution plan is passed for execution, during query optimisation the query optimiser assess a number of plans, known as candidate plans before selecting the plan with the lowest cost (this is why it is known as a cost-based optimiser).
Often overlooked is that the optimiser cannot assess every single plan for every query and whilst it has to return the most optimal plan that it can it also to do that in the quickest time (sort of).
Now that we’ve introduced the query optimiser in the next post we’ll take a deeper look into the query optimisation process itself and start to look at the inner workings of the query optimiser.