Missing-index advice, filtered indexes, columnstore, and over-indexing.
62 associated posts59 primary posts
In this 76-minute session, I'll show you how to use the latest features of sp_BlitzIndex to rapidly improve performance on an existing database. I'll show you how to figure out quickly if you've got too many indexes or not enough, and then demonstrate how to get advice from AI on how to consolidate them in seconds.
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,…
SQL Server 2025 and .NET 10 bring several new improvements to storing JSON natively in the database and querying it quickly. On the SQL Server 2025 side, the two big ones are the new native JSON indexes and the new JSON_CONTAINS function. Let's see their improvements in action. On the .NET 10 side, EF 10…
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:
I'm in the Detroit airport today, en route home after a client gig. I've got some time to kill, so let's go through your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=-yd59N2bWzQ
On a nice sunny Saturday, I took the 911 around the Las Vegas canyons, and stopped to answer your top-voted questions from https://pollgab.com/room/brento:
https://www.youtube.com/watch?v=ja90e234rIo
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-6ae5293320819639713835/]
Last week, we had an abrupt intro, and this week, we have an abrupt ending when my camera overheats, hahaha:
https://www.youtube.com/watch?v=pQOk2N3ljCc
I got an interesting request for consulting, and I'm going to paraphrase it: We were using Azure SQL DB with automatic index tuning enabled for months. Things were going great, but... we just deployed a new version of our code. Our deployment tool made the database schema match our source control, which... dropped the indexes…
Pinal Dave recently ignited a storm of controversy when he quizzed readers about which one of these would be faster on AdventureWorks2019:
[crayon-6ae5293320ce1322654218/]
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.
Beep beep! Here's a speed round of Office Hours where I rip through a dozen questions in under ten minutes. Want to see your own questions answered? Post 'em and upvote the ones you like at https://pollgab.com/room/brento.
https://youtu.be/qt2mmS0RY5I
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.
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'
Picking up right where we left off yesterday, we continue down through the not-so-highly-upvoted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=saIwheS6_Mg
I hang out on my balcony in San Diego with a gin & tonic and a bunch of your highly upvoted questions from https://pollgab.com/room/brento.
https://youtu.be/kAzUupChX_Y
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…
sp_BlitzIndex analyzes the design & health of your clustered & nonclustered rowstore indexes and gives you suggestions on how to get higher performance without changing your queries.
If you run it with no parameters, you get advice on the indexes in the current database:
This morning, Greg Gonzalez (who I respect) posted about visualizing the tipping point with Plan Explorer (a product I respect), and he wrote: The tipping point is the threshold at which a query plan will "tip" from seeking a non-covering nonclustered index to scanning the clustered index or heap. The basic formula is: A clustered…
When someone asks you, "Is this database read-intensive or write-intensive?" you probably look at sys.dm_io_virtual_file_stats to measure the file activity - but that isn't really correct. After all, your users run a lot of queries all day that are simply satisfied from the buffer pool. SQL Server caches your data so it doesn't have to hit the disks, but ... those are reads, right? (Write? Get it?)
