I have a habit of coming up with intriguing titles for sessions before I actually come up with any content. Last year I came up with Guillotines, Sat-Nav and the Query Optimiser and then had the challenge of tying it all together somehow. This months T-SQL Tuesday topic (#131) comes by the way of it's rather fantastic host Rob Volk and it gives me a great opportunity to describe the session because...
Guillotines = execution but what I did find out whilst I was conducting minutes of research in Wikipedia was that the guillotine was put together by a dream team of a doctor and harpsichord maker to provide a much quicker (and more humane) execution compared to a big old axe. In terms of SQL Server that is exactly what the query optimiser is designed to do, give the most optimal execution (and I suppose in a way much more humane too).
The optimiser does this by using all kinds of magic to come up with an execution plan. What's an execution plan I hear you shout? If you haven't shouted it then please do it as loud as possible, you'll feel great. Well, with a bit of imagination it's a bit like Satellite Navigation...which helps massively for the session title.
We don't tell the query optimiser how to do things. We ask it very nicely for the results we want and because we've asked nicely it determines what is probably the best way of doing it in the form of an execution plan (which it shoves over to the execution engine) and most of the time it does a splendid job too.
This plan is just like having a set of directions and basically it will tell the execution engine exactly what to do. It's just like when we ask sat-nav how to get from A to B via C avoiding toll roads (which is always, I'm from Yorkshire) the execution plan contains the instructions for things like how to join this and that table to whatever table etc etc.
There's a whole load of phases and stages and other stuff to explain but for now, execution plan = directions. I'll stick with the theme (because now I'm on a roll) to describe trivial plan optimisation. You wouldn't use sat-nav to give someone directions to the top of your street would you? No. It's too much hassle and also it's blinking obvious. Well the optimiser won't bother with the hassle of full optimisation if there's one straightforward obvious way of executing your query either and will use a trivial plan instead. It saves effort, a bit like my kids when I ask them to do ANYTHING.
Want more analogies? Sure. If you're like me you'll occasionally think that you know better than satellite navigation and take the directions into your own hands. This might work now and again but most of the time it just takes longer. Query hints. This is exactly what is going on here, you're overruling sat-nav and using the B1263 country road because it was faster last month only to get stuck behind a tractor.
One more? Go on then. Speaking of sat-nav; have you ever noticed sat-nav not telling you to use the brand new bypass that cuts right through the middle of the nearest picturesque village? That's because if the maps aren't up to date it doesn't know the road is there. In a much similar way this is why you should keep SQL's statistics up to date, having up to date information helps inform the optimiser to make better decisions when coming up with optimal directions.
Make sense?
OK I'm seriously out of ideas now but as if by incredible timing I have just recently published a rehearsal recording of my guillotines session on my brand spanking new YouTube channel. Here you see checking word order is very important; my brand new spanking channel could have been something rather embarrassing.
Anyway, I've got sidetracked, here's the link to a rehearsal of Guillotines, Sat-Nav and the Query Optimiser if you'd like to know more about guillotines, because you really should, there's even a quiz! Oh and there's some other optimiser stuff too.
Thanks for reading!
