Press "Enter" to skip to content

Category: Indexing

A Primer on Indexing in SQL Server

Published 2026-07-29 by Kevin Feasel

Ed Pollack has a guide:

Indexes are supposed to make SQL Server faster – so why do so many databases end up slower, bloated, and harder to maintain when they have more of them? It usually comes down to misapplied indexes rather than missing ones. There may be too many that are too wide, tuned with settings that don’t fit the workload, or built on assumptions that stopped being true years ago.

This guide walks through the most common SQL Server index tuning mistakes seen in production environments, such as over-indexing, oversized INCLUDE lists, unnecessary fill factor settings, misuse of SORT_IN_TEMPDB, over-aggressive index maintenance – and the myth that heaps are a shortcut to speed. Features real examples.

I think this serves as a reasonable overview of the topic. You can certainly get into more nuance on a number of the topics, but this is a good starting point.

Leave a Comment

JSON Index Internals in SQL Server

Published 2026-07-20 by Kevin Feasel

Hugo Kornelis looks into one of the newest types of index available in SQL Server:

It’s time to continue the series on internals of storage structures. I already covered the more standard storage types in the parts about on-disk rowstore, columnstore indexes, memory-optimized storage, and memory-optimized columnstores, and in the last episode of this series I started to cover specialized storage structures by looking at XML indexes.

Microsoft introduced limited JSON support in SQL Server 2016. However, it took until SQL Server 2025 before the native json data type and JSON indexes were introduced. So let’s look at how these work under the cover.

Click through to learn more about how SQL Server stores the contents of a JSON index.

Leave a Comment

Going Non-Clustered Index-Mad

Published 2026-07-17 by Kevin Feasel

Jeff Mlakar is saying the number of NCIs is too darn high!:

I wish I could say I was exaggerating when I say that I’ve investigated query performance involving 20+ NCI on a single table on more than a few occasions. “Why not?” you may think – the more NCI the better, right? Well think on this:

Click through for the reasoning. You know it’s extra-bad when 95% of those indexes have “dta” in the name. Jeff even calls that out in the post.

Leave a Comment

Index Rebuild Completion Percentages in SQL Server

Published 2026-07-16 by Kevin Feasel

Andrea Allred goes searching for the truth:

I truly don’t know when it happened, but over the last while I have noticed that the percentage complete on indexes has disappeared when I run sp_whoisactive. It makes me so sad! I used that functionality often to track how things were progressing in my databases. At first I thought it was a version thing and it would come back, then I wondered if it was only when I use “ONLINE = ON”, but I am seeing it blank more and more. It has left me feeling like I am missing something and today, I finally did the digging to learn how to get that visibility back.

Click through to see how.

Leave a Comment

JSON Index Testing in SQL Server 2025

Published 2026-07-09 by Kevin Feasel

Reitse Eskens gives it a whirl:

In today’s cloud era, Azure SQL is usually first with new functionality, while on-premises SQL Server follows. One of the new things is the JSON data type and accompanying JSON index.

Because I had to learn how JSON works in SQL Server for my DP-800 exam, I decided to see how the JSON index works and when it works. I’ll go into the execution plans, some details, and check out the statistics when a query runs. Just so you know, this is much deeper than the certification requires, so no need to get this all in your head for the exam.

Click through for a rundown of how this feature works on-premises and what you should be on the lookout for.

Comments closed

A Use for Secondary XML Indexes

Published 2026-07-01 by Kevin Feasel

Hugo Kornelis follows up from an earlier post:

Welcome back to my plansplaining blog series, where I dive deep into the details of non-obvious execution plans. This part is also a sort of follow up on my post from two weeks ago, when I wrote about the structure and usage of XML indexes, and had to admit that I had not been able to come up with good use cases for all types of secondary XML index.

That very same day, I received an email from Mikael (Micke) Eriksson, who pointed me to this question and answer on Stack Exchange for Database Administrators. I then modified that example a bit, to come up with an execution plan that I consider interesting enough that I want to describe it here.

Click through for the example and a dive into what the plan does.

Comments closed

Moving Away from Average Fragmentation for Index Maintenance

Published 2026-06-22 by Kevin Feasel

Tim Radney proffers some advice:

As a SQL Server DBA with years of experience tuning production environments, I’ve seen the same patterns repeat: nightly index maintenance jobs running for hours, consuming massive CPU and I/O, only for performance to remain inconsistent or even degrade in some cases. Many of us (myself included, early in my career) relied heavily on avg_fragmentation_in_percent from sys.dm_db_index_physical_stats to decide when to reorganize or rebuild indexes. It’s time we move past that outdated approach and stop just doing what we’ve always done.

One thing I would add on to Tim’s advice is, determine whether that index even needs rebuilt or if you’re burning resources for no practical benefit. If there’s no practical performance benefit from the operation—and with all-flash arrays that are within an order of magnitude of RAM speeds, you might already be close to that point—then the index rebuild is for naught. The index maintenance strategy that we all learned back in the day was to minimize the time we spent waiting for spinning disks to reach relevant data. When random access lookups are approximately the same speed as sequential lookups, position on the disk doesn’t matter all that much.

Comments closed

Storage of XML Indexes in SQL Server

Published 2026-06-19 by Kevin Feasel

Hugo Kornelis continues a series on storage structures:

After covering on-disk rowstore, columnstore indexes, memory-optimized storage, and memory-optimized columnstores, it is now time to turn our attention to storage structures that are used for specific datatypes only. The first “victim” will be the XML index.

When you need to store XML data in a SQL Server database, you can choose between two data types, each with their pros and cons. You can choose to store the XML data as nvarchar(max). This preserves the exact content of the XML as you received it, which might be required for legal reasons. However, any query that cares about the specific content of the XML data will have to resort to very complicated string expressions.

The xml data type shreds the XML and stores the content in an internal format, that allows SQL Server to work with for instance XQuery or XPath expressions. This format also saves space as compared to the nvarchar(max) alternative. However, when you query the data, the content will be the same, but formatting and whitespace might be different.

The latter also allow you to build indexes on top of them, and those indexes are what Hugo covers.

Comments closed

Matching Queries to Indexed Views

Published 2026-06-17 by Kevin Feasel

Erik Darling has a new video:

Erik Darling here with Darling Data, and in today’s video we’re going to continue on the Learn T-SQL voyage that we have started, and I’m going to talk today a little bit about indexed view matching, because SQL Server is, let’s just call it a mature, or an experienced database engine, and is quite capable, at least in Enterprise Edition, Standard Edition, you do not pay the Microsoft Friendship Tax, so you will be taxed performance-wise, but is quite capable of matching base queries to an indexed view where the syntax matches in some way between them. So, usually exactly between them, not in some way, usually pretty close to just about what you would ask for.

Click through for several tips and, as you experience the frustration of consistently trying to make best use of the view’s index, be glad you’re not trying to get filtered views to work.

Comments closed

Bloom Filters with Valkey

Published 2026-06-09 by Kevin Feasel

Jay Miller checks for a record:

A bloom filter is a small, probabilistic data structure designed to answer one question: “Have I seen this item before?” It provides two potential answers: Absolutely Not, and Probably.

You may think that 100% Yes or No would be better but here’s the thing, probably is really fast and you’re really concerned about the Absolutely Not’s taking up unnecessary connections.

The article speaks to Aiven’s implementation of a Bloom filter in Valkey, but does get into some neat details on bloom filters in general. And if you want to go further down that route, Paul White explains how SQL Server uses Bloom filters.

Comments closed