Aaron Bertrand

10 For 10 : My 10 Favorite SQLPerformance Posts Over 10 Years

SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Free Download

Featured Author

Paul White is an independent SQL Server consultant specializing in performance tuning, execution plans, and the query optimizer.

Paul’s Posts

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

Partitioning on a Budget

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.