Press "Enter" to skip to content

Category: Indexing

When Columnstore Indexes are Not the Answer

Published 2026-08-12 by Kevin Feasel

Mehdi Ghapanvari explains that columnstore indexes should not be a default:

A SQL Server columnstore index does not improve performance when a query fetches many columns. This is an important factor to consider when choosing between columnar and row-based data storage. In this article, I will set up a demo to show this point.

Yes, it’s obvious if you know how columnstore indexes work. But if you’re new to the topic, it’s a good primer on why we don’t use these things everywhere. But in their wheelhouse, they’re incredibly powerful.

Leave a Comment

How OR Predicates Affect Indexes

Published 2026-08-05 by Kevin Feasel

Dualcore DBA adds a clause:

We’ve created an index on both of the columns in the WHERE clause both of which are also in the SELECT list. As a reminder, non-clustered indexes implicitly include the clustered index key in the included columns even if we have not explicitly specified it and so with this in mind, our index fully covers our query. This index should be good for an index seek right? Let’s execute our query again:

This solution isn’t the only way to SQL Server to use a specific pair of indexes—you can also use the UNION operator to replace one OR, for example. And that usually resolves the issue without needing index hints.

Leave a Comment

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.

Comments closed

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.

Comments closed

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.

Comments closed

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.

Comments closed

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