This is a month of milestones. I was awarded my 25th MVP award on July 5th, which I believe ties me with Tibor for the longest-tenured MVP. SQL Server 2012 went out of all forms of official support about two weeks ago, on July 12th. And this site turns 10 years old today – the first post was on July 24th, 2012. I thought it would be fun to look back and comment on some of my contributions over that decade.
2012
Best approaches for running totals – updated for SQL Server 2012
In the very first post on this site, I compared the performance of long-standing running totals methods with the new windowing functions added in SQL Server 2012. In almost all cases, the new approach worked better – but there’s always a catch, right?
2013
Break large delete operations into chunks
I wrote this 2013 post as a response to seeing many people try to delete millions of rows from very large tables, and face blocking that crippled their applications. Nearly 10 years later I still reference it frequently in comments or answers on Stack Overflow.
2014
Readable Secondaries on a Budget
When Availability Groups were first introduced, they were only available in Enterprise Edition. Customers who didn’t have unlimited cap-ex funds were clamoring for a way to scale out reads. In this 2014 post, I detailed a way to do this with good old log shipping.
2015
In a similar vein to my favorite post from 2014, I described a method to simulate partitioning – another feature that was Enterprise-only at the time – using filtered indexes.
2016
A Big Deal : SQL Server 2016 Service Pack 1
Toward the end of the year, I was able to spread the news that a SQL Server 2016 service pack would make most Enterprise-only features available in all editions. I detailed all the changes and explained why this was such a huge win for non-Enterprise customers.
2017
One way to get an index seek for a leading %wildcard
Leading wildcard searches are notoriously inefficient because they require inspecting every single value. One way to get a seek is to have an indexed computed column using REVERSE. In this post from 2017, I explored the use of a trigram as a similar strategy.
2018
What to do (or not do) about top wait stats
Usually, wait stats associated with an individual query don’t help you solve the problem, because the wait is actually caused by some other process. This post from 2018 took a look at the top 10 wait types observed by the sum of SQL Sentry customers at the time and, for each one, asked the question, “Can you solve this problem with just the wait type and the query that experienced it?”
2019
Filtered Indexes and Forced Parameterization (redux)
In this 2019 post, I explained a trivial workaround to a frustrating limitation with filtered indexes. This was not my first post complaining about filtered indexes (other posts are linked in the opening paragraph).
2020
Please stop using this UPSERT anti-pattern
In 2020, I talked about an all-too-common pattern: checking if a row exists, if so, update, otherwise, insert. This pattern is a shortcut to deadlocks and, in this post, I explain a slightly better approach (using graphics I’m quite proud of).
2021
Refreshing Tables With Less Interruption Using Partition Switching
Last year, I wrote about a way to use partition switching to refresh lookup data with minimal or zero user disruption. I explained the new ABORT_AFTER_WAIT option and demonstrated the tradeoffs involved.
2022 (BONUS!)
Serializing Deletes From Clustered Columnstore Indexes
Well, 2022 isn’t over yet but, so far, my favorite post has been this one about reducing the impact of deleting data from a table with a clustered columnstore index.
