Indexing

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…

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: Transact-SQL EXEC sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2, @SortOrder = 'rows'; 12 EXEC sp_BlitzIndex @GetAllDatabases = 1,  @Mode = 2,…

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: Transact-SQL DROP TABLE IF EXISTS dbo.AllTheSame; DROP TABLE IF EXISTS dbo.AllDifferent; CREATE TABLE dbo.AllTheSame (Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, TimeItHappened DATETIME2 INDEX IX_TimeItHappened); CREATE TABLE dbo.AllDifferent (Id INT…

Let’s start with the Stack Overflow database (any size will work), drop all the indexes on the Users table, and run a delete: Transact-SQL SET STATISTICS IO ON; GO BEGIN TRAN DELETE dbo.Users WHERE DisplayName = N'Brent Ozar'; 1234 SET STATISTICS IO ON;GOBEGIN TRANDELETE dbo.Users WHERE DisplayName = N'Brent Ozar'; I’m using SET STATISTICS IO…

Every weekday during the month of October, one of the modules from my Fundamentals classes will be absolutely free to watch. But you gotta watch it that day – you snooze, you lose. This week is Fundamentals of Index Tuning. To follow along with the labs this week & next week, you’ll need SQL Server 2017…

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. Say our vendor app is the Stack Overflow database, and the app constantly queries Users by display name: USE StackOverflow; GO DropIndexes; SET STATISTICS IO…

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: CREATE INDEX…

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’ Or: sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2, @SortOrder = ‘rows’ This produces an inventory of all the indexes sorted by…

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: How to…