Press "Enter" to skip to content

Category: Indexing

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.

Leave a Comment

The Pain of Moving Indexes between Filegroups

Published 2026-06-09 by Kevin Feasel

Erik Darling explains a process:

At some point you’re going to want to move some indexes to a new filegroup. Maybe you’re separating data across storage, maybe you’re cleaning up after someone who put everything on PRIMARY and walked away, maybe you’ve got your reasons and they’re none of my business.

Whatever the cause, you’d think this would be a solved problem in a database that’s been around since the Clinton administration.

It is not.

Some days, I’m convinced that the only way to win is not to play at all. Erik explaining how to migrate LOB data across filegroups fits that bill perfectly.

Leave a Comment

Working with Indexed Views

Published 2026-06-05 by Kevin Feasel

Erik Darling talks indexed views:

Anyway. T-SQL Server Management Studio. When most people think about index views, they rightfully think about all the stuff they can’t do with them.

And I sympathize with that because, man, so many times they’ve been like, oh, if only you could do this, if only you could do that, it sure would be nice. And I realize that all the air has gone out of the room as far as making index views more powerful because everyone’s like, well, you could just use batch mode.

Click through for some tips around update operations when dealing with indexed views, as well as a side rant about merge joins.

Leave a Comment

Filtered Indexes in SQL Server

Published 2026-05-29 by Kevin Feasel

Erik Darling has a new video:

Now, you just can’t talk about indexing in SQL Server really without talking about filtered indexes. They are a very, very important thing. Conceptually, they are just not that hard to figure out.

It’s an index with a where clause. It only indexes some of the data. It qualifies for the where clause. I don’t know. Like the benefits of that just seem rather apparent to me.

Benjamin Franklin highly encourages you to watch this video, even though filtered indexes are one of the most frustrating things in SQL Server. There are so many cases where I think they should work, and they actually work in approximately a third of those cases.

Leave a Comment

Automatic Index Compaction in Azure SQL

Published 2026-05-29 by Kevin Feasel

Chad Callihan takes a look at a preview feature:

There isn’t one set way to manage indexes. Maybe you use Ola Hallengren scripts. Maybe it’s something you put together yourself. Either way, there might be a big shift coming for SQL Server database administrators and how index management is handled.

Last month, Microsoft announced Automatic Index Compaction, which is in preview for Azure SQL Database, Azure SQL Managed Instance, and SQL Database in Fabric. Instead of utilizing something like Ola Hallengren scripts or your own homegrown setup to monitor and rebuild indexes, the database engine will continuously run in the background and handle indexes for you, hence the “automatic” in the name.

Read on to see how it works, as well as a note around page density and index fragmentation. But Jeff Moden makes a good point in the comments, so check that out.

Leave a Comment

Storage of Memory-Optimized Columnstore Indexes

Published 2026-05-28 by Kevin Feasel

Hugo Kornelis joins a pair of technologies:

Time for the next part in my series on storage structures. The previous parts covered on-disk rowstore, columnstore indexes, and memory-optimized storage. In this part, I will look at the combination of the latter two: memory-optimized columnstore indexes.

Memory-optimized columnstore indexes were introduced in SQL Server 2016. I’ve seen some slick Microsoft marketing sessions in that time that were big on “real-time operational analytics”. A new trend where analytical processing would no longer be done on a stale copy of the data in a separate data warehouse, but directly on the OLTP database. Reports would always be fully current, there would be no more need for an ETL pipeline, and due to the combination of memory-optimized for OLTP workloads and columnstore for analytical processing, everything would always be fast. In theory.

Yeah, this one kinda fizzled out quickly. It was in line with the HTAP craze from about the same time period. And Hugo shows in this post part of why very few companies ever adopted it.

Leave a Comment

Full-Text Indexes and SQL Server 2025

Published 2026-05-27 by Kevin Feasel

Rich Benner rebuilds indexes:

The Full-Text Engine manages full-text indexes. The engine splits your text columns into individual terms and builds an inverted index, mapping each term back to the rows in which it appears. Unlike a standard B-tree index, the structure lives outside the normal index internals and is maintained asynchronously via a background process called a crawl. Effectively, each word in your text string ends up indexed, rather than the string as a whole. This makes certain types of searches much more efficient (you need to use search terms like CONTAINS() to utilize full text indexes).

If you have to search strings like this then full-text indexes can be very effective. String searching isn’t great in SQL Server, but this is definitely a tool in your belt if you have a requirement that makes it useful.

Because of some changes to the way full-text indexing works in SQL Server 2025, there is a post-upgrade maintenance task you’ll have to perform.

Leave a Comment

Partitioning and Columnstore Indexes

Published 2026-05-15 by Kevin Feasel

Erik Darling puts together a great combination for a very large dataset:

 So, today we’re going to talk about partitioning in columnstore because there are important differences between partitioned columnstore tables and partitioned rowstore tables. One of the sort of superpowers that columnstore has is the ability to use metadata about which row groups have which data in them, and it can skip entire segments that do not contain relevant data.

I agree with Erik’s point that you do need around 500 million or so rows before this capability really shines, but if you do pick the right partition key, you get one of those rare and coveted performance improvements from partitioning.

Comments closed

Filtered Indexes and Computed Columns

Published 2026-04-24 by Kevin Feasel

Greg Low has a public service announcement:

On a client site some years back, I came across a situation (unfortunately too common) where a column in a table was being used for two purposes. It could either hold an integer value or a string. Only about 100 rows out of many millions had the integer value. Some of the client code needed to calculate the maximum value when it was an integer. First step I tried was to add a persisted calculated column like so:

After that, Greg tried to create a filtered index. Read on to see how that worked.

Comments closed