Press "Enter" to skip to content

Category: Indexing

Using Filtered Indexes in SQL Server

Stephen Planck explains the value (and limitations) of filtered indexes:

When a table holds millions of rows yet most queries touch only a small, well-defined subset, a traditional non-clustered index feels like using a searchlight to find something sitting under a desk lamp: the index still stores an entry for every row even though the workload rarely needs most of them. SQL Server’s answer is the filtered index—introduced in SQL Server 2008 and still under-used today—allowing you to index just the rows that match a predicate you supply in a WHERE clause.

I always thought I would get more value out of filtered indexes. Instead, they’re very useful for specific circumstances, but not the all-purpose performance dynamos you’d want them to be, particularly because of the limitations.

Leave a Comment

JSON Indexes in SQL Server 2025 CTP 2.0

Daniel Hutmacher gives it a try:

Starting today, the public preview of SQL Server 2025 is available to download!

One really interesting new feature that got my attention was the addition of JSON indexes. I’m a big fan of everything that makes working with JSON easier, since JSON blobs are so much easier to work with than table variables when you’re moving data from point A to point B. This is especially true when you’re working with complex, relational data.

Daniel lays out some of the limitations of JSON index creation and also some of the performance gains you might see from it. This will be most helpful in data engineering scenarios, shredding JSON from various services, but the normalization purist in me says that if you’re shredding JSON enough to need indexes, it’s probably time to normalize that data.

1 Comment

Thoughts on Index Rebuilds

Kevin Hill shares some thoughts:

Here’s the truth: if you’re doing this daily on indexes smaller than 10,000 pages, you might be chewing up CPU, bloating your logs, and annoying your users  for zero gain.

Let’s fix that.

I disagree with Kevin on index reorganization, in that I would never perform index reorgs—there’s no there there. Even so, I firmly agree with the thrust of Kevin’s argument and believe that, in the majority of cases, companies with DBAs (or people who have stumbled through maintenance plans before) are maintaining indexes too much rather than not enough.

Leave a Comment

Hash Indexes in MySQL

Lukas Vileikis explains how hash indexes work in MySQL:

In MySQL, hash indexes are indexes that are used in queries that use the equality operators like = or <=> (which is the MySQL NULL safe equality operator, equivalent to the SQL Standard IS NOT DISTINCT FROM). Hash indexes are not used in other situations, so they can be useful for things like random or generated PRIMARY KEY values where you are looking up single rows and not needing to order rows or searching for a range of values.

Just like in SQL Server hash indexes only work for in-memory tables, and some of the mechanisms look very similar.

Comments closed

SQL Server Performance Office Hours Episode 10

Erik Darling has some doozies today:

Many of our Tables have 15 plus guid s from other tables, but not declared as Foreign Key. Plus Foreigne Keys. When I add a Index on for every Foreign Key plus Indexes for declared foreign keys we end up with tables with 30 plus indexes for fks only. We don’t have write performance issues. would you index every fk for every possible join?

Click through for the answer to this and four other questions.

Comments closed

Checking Index Utilization in Oracle

David Fitzjarrell wants to see which indexes are in use:

Database performance is one of the biggest areas for DBAs to address, measured primarily in time. Users usually complain when queries “take too long” and immediately run to the DBA team to register their disappointment. One avenue used to address such concerns is the creation and maintenance of indexes, and as any DBA with any experience can attest, simply throwing indexes at a table isn’t necessarily the smartest play; a major concern is if the index is actually being utilized. Oracle, in its wisdom, has provided tools to monitor these objects in the form of system views. Let’s look at what is available and how to get the most benefit out of them.

Click through for some scripts and information on how to tell whether an index is in use or not.

Comments closed

A Primer on Indexing in SQL Server

Kevin Hill goes back to basics:

“What Is an Index?”
I get this question a lot, especially from developers and sysadmins who’ve been handed a SQL Server and told, “Keep it running fast.” No pressure.

Given that the percentage of people in this industry who have actually seen a phone book continually decreases, it’s for the best that Kevin didn’t use that worn-out example.

If you’re new to indexing in SQL Server, this is a good way to jump-start your knowledge. Then, you spend a while learning the exceptions to the rules and the foibles in existing systems.

Comments closed

Vector Indexing in PostgreSQL

Hans-Jürgen Schönig builds some indexes on vector data:

In the previous post we have imported a fairly large data set containing Wikipedia data, which we downloaded using pgai. However, importing all this data is not enough because we also need to keep an eye on efficiency. Therefore, it is important to understand that indexing is the key to success.

Click through for an overview of what options are available for indexing vectors in PostgreSQL, as well as the trade-offs and disk space requirements.

Comments closed

Indexing for PostgreSQL in pgNow

Ryan Booz continues a series on pgNow:

In that first article, I shared how pgNow can be a lifesaver when you need immediate performance insights, highlighting features like query tuning and current activity monitoring. The tool’s ability to take periodic snapshots of query activity and spotlight active sessions has already been a significant help for early users.

Today, I wanted to look at another area of information that pgNow can help you explore during times of performance degradation or even as part of a regular database maintenance and hygiene: the Indexing tab.

Click through to see what’s in the feature and to get a free copy of the preview for pgNow.

Comments closed

B-Tree Indexes in PostgreSQL vs SQL Server

Lukas Fittl compares and contrasts:

When it comes to optimizing query performance, indexing is one of the most powerful tools available to database engineers. Both PostgreSQL and Microsoft SQL Server (or Azure SQL) use B-Tree indexes as their default indexing structure, but the way each system implements, maintains, and uses those indexes varies in subtle but important ways.

In this blog post, we explore key areas where PostgreSQL and SQL Server diverge: how their B-Tree indexes implementations behave under the hood and how they store and access data on disk. We’ll also benchmark the impact of deduplication of values on index size in each database system.

I love this kind of post because you hear that SQL Server has indexes and PostgreSQL has indexes (or Oracle has indexes or whatever), and thus, all of your index building knowledge in one applies to the other…right?

One thing that changes the article a bit is that the author doesn’t use page-level compression on indexes in SQL Server. I’d expect the results to change a fair amount, even if the SQL Server non-clustered indexes still ended up larger in the end than PostgreSQL indexes.

Comments closed