Press "Enter" to skip to content

Category: Indexing

Index Design Guide Updates

Kendra Little has a guide for us:

We’ve recently updated the SQL Server and Azure SQL index architecture and design guide. This article is an in-depth guide to indexing in databases using the SQL Server engine, including SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics.

Our recent update adds a table to categorize the types of indexes discussed in the article, clarifies B-trees vs B+ trees, and describes how row locators (aka “secret columns”) are used in nonclustered indexes.

Read on for more information and do check out the guide.

Comments closed

Filtered Indexes and Functions

Eitan Blumin looks at filtered indexes:

In fact, absolutely no functions of any kind can be used within the WHERE clause of a filtered index. Not even schema-bound user-defined scalar functions.

Unfortunately, as stated in the Microsoft Docs page about Filtered Indexes, the WHERE clause of a filtered index can only support simple comparison operators.

Well, it’s not entirely true, as you CAN actually use some functions, but on two conditions:

Read the whole thing. Eitan lays out one limitation of filtered indexes and provides a couple of potential workarounds.

Comments closed

Choosing between Duplicate Indexes

David Alcock wants to know what choice you make when all choices lead to the same conclusion:

As there is an index that has the LastName and FirstName columns the optimiser has opted for an index seek operator using the IX_Person_LastName_FirstName_MiddleName index, and if I look into the Plan XML I can see that it’s using a trivial plan: StatementOptmLevel=”TRIVIAL”.
This basically means there’s one obvious way to return the query results so the optimiser has avoided the cost of going through full optimisation and has elected to use this plan straightaway.
So what happens if I create an identical copy of that particular index, in fact let’s create five indexes that are exactly the same:

There’s a Mass Effect 3 joke in my intro line. But read on for the answer and also check out Barney Lawrence’s comment for a bit more elucidation.

Comments closed

Wide Non-Clustered Indexes as Kinda-Sorta Clustered Indexes

Grant Fritchey gets our hopes up:

Everyone knows that you only get a single clustered index, right? Wouldn’t it be great though if you could have two clustered indexes?

Well, you can. Sort of. Let’s talk about it.

Click through to see what Grant means. This is a thing that I’ve done occasionally, though much more often, I’ve ripped it out because Database Tuning Advisor suggested it and a credulous user took DTA at its word.

Comments closed

Preconceived Notions with Filtered Indexes

Aaron Bertrand has learned a thing or two about filtered indexes:

Confession time. For filtered indexes, I have long held the following impressions:

1. That the definition of the filter had to exactly match the predicate in the query.

2. That col IN (1, 2) was not supported in a filter definition, since it’s the same as col = 1 OR col = 2, which is definitely not supported.

If I were to take a wild guess, I’d think impression 1 was probably influenced by the extreme limitations filtered indexes have with parameterized queries. Anyhow, read the whole thing and learn why both of these are wrong.

Comments closed

Hypothetical Indexes in SQL Server

Eitan Blumin explains what hypothetical indexes are and why they might be useful:

Using Hypothetical Indexes, you can generate an estimated execution plan for a given query, that would essentially assume the existence of a “hypothetical” index as if it actually exists as a real index. Compare that estimated execution plan to its counterpart without the hypothetical index, and you’ll be able to determine whether creating this index for real is worth the time and effort.

Hypothetical Indexes are actually nothing new in SQL Server. It existed since SQL Server version 2005. However, its use is still not widespread to this day. Most likely because it’s not very easy to use and the relevant commands are undocumented.

Click through to see how to use them and an important warning if you try it in production.

Comments closed

Indexing and Window Functions

I continue a series on window functions in SQL Server:

If you’ve been around the block with window functions, you’ve probably heard of the POC indexing strategy: Partition by, Order by, Covering. In other words, with a query, focus on the columns in the PARTITION BY clause (in order!), then the ORDER BY clause (again, in order!), and finally other columns in the SELECT clause to make the index covering (not in order! though it doesn’t hurt!).

But do read on to understand why this is not sufficient.

Comments closed

Why the Optimizer Doesn’t Look at Buffer Pool Data

Paul Randal has an explanation for us:

SQL Server has a cost-based optimizer that uses knowledge about the various tables involved in a query to produce what it decides is the most optimal plan in the time available to it during compilation. This knowledge includes whatever indexes exist and their sizes and whatever column statistics exist. Part of what goes into finding the optimal query plan is trying to minimize the number of physical reads needed during plan execution.

One thing I’ve been asked a few times is why the optimizer doesn’t consider what’s in the SQL Server buffer pool when compiling a query plan, as surely that could make a query execute faster. In this post, I’ll explain why.

This is an interesting post because it explains why the developers of the database engine would purposefully ignore something that could make things faster, but at a potentially devastating cost.

Comments closed