Welcome to - oh, right - yet another SQL Server blog.
After twenty years working with SQL Server, I finally decided to write about it. This is well worn ground, so why do it?
A step-by-step guide to logical trees
When I first started learning about the deeper detail of the optimizer I found some great examples of logical trees in action. Taking an applied approach using some real world examples made it easy to quickly appreciate specific optimizer behaviours.
What I didn't find though was any step-by-step guide - explaining the basic operators and gradually building up from there. It's an approach I'd seen Itzik Ben-Gan very successfully pull off in his excellent T-SQL Fundamentals. Far from it being a dry reference book, it includes many significant details that even an experienced developer may not be aware of.
So, with that as inspiration, I set about tackling logical trees.
Avoiding "cut and paste" knowledge or repros
I couldn’t have done any of this without having a starting point given by other writers. Nonetheless I’ve set out to independently confirm the findings in the source material - writing my own repros or even (painfully) disassembling SQL Server code.
Doing that has not only cemented my own understanding, but also highlighted occasions where my findings have differed from the original articles. (The most likely explanation for these differences is simply the version of SQL Server - much of the prior work was written some years ago, before the release of SQL Server 2019 that I used for my testing.)
There have of course been times - more than a few - where I've drawn a blank in my own investigation, and have reached out. Authorities and community members such as Conor Cunningham (Microsoft), Paul White and i-one have been kind enough to answer some of my questions, for which I'm very grateful.
Showcasing optimizer magic
There are some truly impressive features in the optimizer, some of which are relatively unknown but can be tapped for huge potential - whether it be straightforward performance gains, or even facilitating faster development and easier code maintenance.
I’ll be posting some stand alone demos of these. Hopefully they will shine a light on some common misunderstandings - and give you ammunition to challenge a colleague or manager who has maybe depended on received wisdom rather than solid testing.
As well as the writers mentioned above, I’d like to give a tip ‘o the hat to these fine folk…
Kalen Delaney has been shining a light on SQL Server internals for over 20 years. Respect.
Itzik Ben-Gan writes brilliant books that have taught me something new about T-SQL coding in every one of them.
Paul Randal knows storage functionality like nobody else. Hey, he wrote big chunks of DBCC. Say no more.
Aaron Bertrand has, amongst many things, lots of great thinking on coding techniques.
Brent Ozar is simply relentless in sharing knowledge about essentials such as index design.
Kendra Little in her pieces about partitioning, demonstrates that proper testing can blow away common misconceptions.
Erin Stellato covers a variety of topics, but in particular she's the "go to" person for Query Store.
Erik Darling is forever active on dba.stackexchange, with help for allcomers.
Redgate have been kind enough to provide software in support of this blog. Their tools combine class leading functionality with elegant user interfaces that are a pleasure to use. Thanks in particular to Leanne Hope for arranging the software - you can follow her on Twitter @Leanne_Redgate