Press "Enter" to skip to content

Category: Indexing

Thoughts on Automatic Index Compaction

Published 2026-10-26 by Kevin Feasel

John Deardurff shares some thoughts:

Anyone who has managed SQL Server for a while probably recognizes the traditional 5% reorganize or 30% rebuild approach. But those Index Fragmentation Thresholds were completely made up. Primarily as they were simple values to baseline that made it easy to automate. But today we need to ask whether fragmentation percentage is really the best measurement for deciding when an index needs maintenance.

As storage has changed, Microsoft guidance around index maintenance has changed. The question should no longer be; How fragmented is my index? A better question is: How efficiently are the pages being used, and how much work is needed to access my data?

Click through for a demo of what automatic index compaction can do in Azure SQL Database. The two important notes I have are as follows. First, as John points out, automatic index compaction does not update statistics, so you’d still need to take care of that. Second, I don’t think there’s ever a good use case for REORGANIZE.

1 Comment

Entry Points and Graph Neighborhoods with DiskANN

Published 2026-10-21 by Kevin Feasel

Mala Mahadevan tries something out:

I was experimenting with vector search in SQL Server 2025 using approximately 3.8 million Hacker News articles that that I used for my talk on SQL Server-based vector search feature at EightKB. I generated two sets of embeddings from the same articles: one stored as FP32 (32-bit floating point) and the other as FP16 (16-bit floating point). Both sets contained the same articles and used DiskANN vector indexes.I used a query as below to find articles similar to a selected example.

What Mala found was that FP16 queries could sometimes take longer and require more reads than FP32 queries, which doesn’t make sense on its face. Click through to see two experiments she devised to come up with the answer.

Leave a Comment

Auto-Indexing in SQL Server

Published 2026-10-21 by Kevin Feasel

Brent Ozar unveils a secret:

It’s super simple: the installation file, AutoIndex.sql, creates an Agent job and a series of stored procedures including auto_create_index and auto_drop_index. They watch the index usage and missing index DMVs, log their findings to tables, and trend the data over time in order to figure out which indexes to create, and which ones to prune back because they’re only slowing you down.

And it’s even free!

I enjoy these posts, where Brent goes back to ideas people had 20+ years ago. It really shows how creative the SQL Server development team was back in the day.

Leave a Comment

Full-Text Indexing and Power BI Semantic Model Refresh Times

Published 2026-10-20 by Kevin Feasel

Chris Webb runs a test:

The September release of Power BI saw the introduction of a powerful new feature: full text indexing, which makes searching and filtering on text columns much faster and enables the use of the new TextContains() and TextSimilarity() DAX functions. You need to enable it on a per-column basis and when you do that you have the option to rebuild the full-text index for those columns when you do a Full refresh or when you do a refresh of the type Indexes. So the big question is: how long does it take to build a full-text index on a column and what impact will it have on your overall refresh times?

Read on for that answer in a reasonably-sized dataset containing street addresses.

Leave a Comment

The Trade-offs of Indexing

Published 2026-10-14 by Kevin Feasel

Rob Farley thinks about costs and benefits:

“How do you weigh the risk vs the reward of your database design decisions?” asks my old friend Pat Wright. And one of the examples he gives is around indexes. I like indexes, so let’s talk about some of the choices when you’re thinking about adding a new index. And for the sake of simplicity, a new nonclustered rowstore index.

A new index might make an expensive query be much faster. Users might feel like the application has become much snappier. But a new index is not free.

Read on for Rob’s thoughts.

Leave a Comment

DiskANN Vector Index and Search Now GA

Published 2026-09-30 by Kevin Feasel

Pooja Kamath makes an announcement:

Today, we are announcing the general availability of DiskANN Vector Index & Search across Azure SQL Database, Azure SQL Managed Instance with the always-up-to-date update policy, and SQL database in Microsoft Fabric.

DiskANN brings scalable approximate nearest-neighbor search directly to the SQL engine. Developers can store vectors alongside relational data and combine vector similarity with the filters, joins, security policies, and transactional data their applications already rely on.

It does support INSERT, UPDATE, and DELETE, and they say there’s asynchronous index maintenance. If that works the way it should, it gets rid of one of the biggest pain points of using DiskANN-based vector indexes in the SQL Server universe today.

Comments closed

Page Splits and Fill Factor

Published 2026-09-24 by Kevin Feasel

Brent Ozar differentiates page splits:

You’re looking at page split numbers in a monitoring tool or Perfmon, and you’ve heard that page splits are bad, so you’re lowering fill factor, expecting your page splits to go down.

You’re monitoring the wrong number.

Jeff Moden has a good comment in there as well that what Brent’s saying is often true, but there can be edge cases. Though that’s part of the point: it’s an edge case, not a primary case. And I like Erik Darling’s comment on the post as well, as it’s often fretting about the color of the furniture when the house is burning down.

Comments closed

NOLOCK Hurts, Even with Indexes

Published 2026-09-17 by Kevin Feasel

Brent Ozar proves a point:

NOLOCK is bad and you probably shouldn’t use it, but every time I mention that publicly, the pushback just keeps coming. I don’t know why people so firmly believe that their situation couldn’t possibly be affected by bad/random data from NOLOCK.

Today’s misconception comes from a LinkedIn commenter telling me it’s safe to use if you’re doing index seeks. Hoo boy. 

Click through for the proof.

Comments closed

Tracking Online Index Rebuild Progress

Published 2026-09-15 by Kevin Feasel

Rich Benner doesn’t take 0% for an answer:

Ever had a situation where you’re rebuilding a large index but when you check sp_whoisactive, you see 0% complete … and you know the system is just lying to you?

We recently had a scenario where we were rebuilding a large index for a customer over a long weekend. We were monitoring closely, as this has caused production issues in the past. After 2.5 hours, we did not see any progress on the rebuild.

Read on to see what you can use to estimate the progress.

Comments closed