Welcome

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.

Supported by

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

About me

Somewhere around twelve years old I managed to scrape enough money together to buy a Sinclair ZX80. After pottering around in BASIC for a few months, I decided to inflict self-harm and learn machine code. That's right, not even having an assembler. And without a disassembler, debugging meant staring at screens of hex dump. Weirdly, I got hooked.

A year or so later, I somehow ended up getting paid for this bedroom geekery. By seventeen I'd written my first technical book - Spectrum Machine Code Made Easy. Most of it was written in a soggy tent on a rained out off-the-leash camping holiday, punctuated by what most groups of teenagers do when left unsupervised - drinking ill advised choices of alcoholic drinks and making woeful attempts at chatting up. Whoever typed up my biro-written copy had work on their hands.

By the time my twenties dawned, I'd plunged into writing games, and (hey Spectrum fans) wound up co-writing Bomb Jack. A quarter of a million copies sold, but with a bank account remaining doggedly in the red. A few games later I was burnt out. There's only so many hex-dumps you can decipher at 3am, near over-dosed on a gallon of cheap coffee.

I took a decade long excursion into being a being a product manager / tech evangelist for some great Macintosh software and hardware. Bizarrely, this included being Pixar's first representative in the UK, long before they were famous.

The coding itch eventually returned. The new fangled web thing led me into data driven websites, and shortly after I thought I'd see what SQL Server was all about. That was version 7 back in the late nineties. I've pretty much stayed with it ever since - barring a couple of years of working on Oracle Exadata for some of the worlds biggest retailers (developing on a 100TB database, with 360 CPU cores and 4 terabytes of RAM at my fingertips. Wowza)

Despite the fun of riding that particular rocket ship, SQL Server has always been my home team. If anyone gets any benefit from this blog, it will be a great reward - some forty years on since I got that ZX80. Oh, and after all that time, I'm now back to staring at deep level debuggers at 3am. Just with better coffee.