When a query is sometimes fast and sometimes slow, there are a lot of ways you can reduce the effects of parameter sniffing. We're always going to have parameter sniffing in SQL Server and Azure SQL DB - it's just the way the product is built - but there are a lot of options to reduce the blast radius.
Sometimes I really hate my job. Forever now, FOREVER, it's been a standard thing where I can say, "When you're measuring storage performance during index and query tuning, you should always use logical reads, not physical reads, because logical reads are repeatable, and physical reads aren't. Physical reads can change based on what's in cache,…
Before SQL Server 2025, if you want to store JSON data in Microsoft SQL Server or Azure SQL DB, and you want fast queries, the easiest way is to:
Store the data in an NVARCHAR(MAX) column (because the native JSON datatype didn't arrive until SQL Server 2025)
Add a computed column for the specific JSON keys we'll want to query quickly
Index those keys
Query it using the JSON_VALUE function
99.9% of you are never gonna need this.
But let's say you need to run queries with leading (not trailing) wildcards, like this search for all the different national versions of the Encabulator, each of which has different prefixes depending on which government it's being sold to:
First things first: pick up that filthy keyboard, take it over to the trash can, turn it upside down, and give it a good, vigorous shake.
Next, go to your biggest SQL Server and run:
You're looking at a wide table with 100-200 columns.
Years ago, it started as a "normal" table with maybe 10-20, but over the years, people kept gradually adding one column after another. Now, this behemoth is causing you problems because:
Accelerated Database Recovery (ADR) is a database-level feature that makes transaction rollbacks nearly instantaneous. Here's how it works.
Without ADR, when you update a row, SQL Server copies the old values into the transaction log and updates the row in-place. If you roll that transaction back, SQL Server has to fetch the old values from the transaction log, then apply them to the row in-place. The more rows you've affected, the longer your transaction will take.
Let's get a little nerdy and look at database internals. Create two tables: one with a million unique values, and one with a million identical values:
[crayon-6a5aae6694937067280884/]
Let's start with the Stack Overflow database (any size will work), drop all the indexes on the Users table, and run a delete:
[crayon-6a5aae6694e09327356235/]
Pinal Dave recently ignited a storm of controversy when he quizzed readers about which one of these would be faster on AdventureWorks2019:
[crayon-6a5aae669509f449365293/]
I laughed so hard when I saw the storm of responses on Twitter. People sure do get passionate about this kind of thing. If you ever wanna witness patience and generosity in action, look at Pinal's responses to this tweet.
What does fragmentation mean? How does it happen? Can you fix it with fill factor? Should you rebuild your indexes to fix it?
At the SQLBits conference, I tried a new way of explaining it using markers and pieces of whiteboard paper as database pages, and doing inserts live on the fly.
Every now and then, a client says, "We want to add indexes to this third party application, but if we do, we'll lose support."
No problem - enter indexed views.
There's a widespread misconception that SQL Server's columnstore indexes are like an index on every column.
I debunk that myth in the first 30 minutes of my Fundamentals of Columnstore class, where I explain that a better way to think of them is that your table is broken up into groups of rows (1M rows or less per group), and in each group, there's an index on every column.
I just shake my head when I see people setting fill factor in an attempt to "fix" fragmentation.
The default fill factor is 100%. That means during an index rebuild, SQL Server packs 100% of your 8KB pages with sweet, juicy, golden brown and delicious data.
The short answer is that if your query orders columns by a mix of ascending and descending order, back to back, then the index usually needs to match that same alternating order. Now, for the long answer. When you create indexes, you can either create them in ascending order - which is the default: [crayon-6a5aae6695e3c695001462/] Or…
Made a New Year's resolution to lose weight this year? Forget taking it off your waistline - take it off your database instead with:
sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2, @SortOrder = 'size'
When you've got a process that uses temp tables, and you want to speed it up, it can be tempting to index the temp table to help work get done more quickly. However, in most cases - not all, but most - that's a bad idea. To explain why, I'm going to take a large…
You've conquered my Fundamentals of Index Tuning and Fundamentals of Columnstore classes, and you're ready to take it to the next level.
It's time for you to join my Mastering Index Tuning class. When I teach it live - the next one is July 19-21 - it's 3 days of lectures and hands-on labs where you learn:
If you've got a regular rowstore table and you need to modify thousands of rows, you can use the fast ordered delete technique to delete rows in batches without hitting the lock escalation threshold. That's great for rowstore indexes, but...columnstore indexes are different.
To demo this technique, I'm going to use the setup from my Fundamentals of Columnstore class:
Last week, I gave you a 1-week, $1 deadline, and 777 of you took me up on it. That's awesome! I love having fun with stuff like this.
Let's do it again! You've got 1 week to learn the Fundamentals of Columnstore for $1, and sales close Saturday. Here's the abstract:
