Press "Enter" to skip to content

Category: Indexing

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

The State of JSON Indexing in SQL Server 2025

Published 2026-08-21 by Kevin Feasel

Greg Low shares some thoughts:

SQL Server 2025 finally gives developers a native JSON data type and, with it, a purpose-built way to index JSON documents with the new CREATE JSON INDEX statement. Before this, indexing JSON meant exposing individual properties through computed columns and building standard indexes on top.

It’s a major step toward closing the gap with databases like PostgreSQL, long praised for its JSON and JSONB support. However, as a preview feature, JSON indexing comes with real constraints DBAs and developers should understand before adopting it.

This guide has everything you need to know about JSON indexing in SQL Server 2025: what it is, how it works, and current limitations.

Click through to learn more.

Comments closed

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.

Comments closed

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.

Comments closed

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