Indexing

Indexing, Locking, Blocking, and Isolation Levels, SQL Server

An interesting question came up in our SQL Server Performance Tuning course in Chicago: when creating an indexed view, does it require an exclusive lock on the underlying table or tables? Let’s test it out with a simple indexed view run against a non-production environment. (AKA, a VM on my laptop running SQL Server 2014.)…

Filtered Indexes and IS NOT NULL

Last Updated February 13, 2017

Filtered indexes can be tricky. Just getting your queries to use the filtered index can be a real pain. Parameterization may mean it’s “unsafe” to use the filtered index. The optimizer might want the filtered column in your key or include column list when it doesn’t seem like it should have to be there. The…

Why is This Partitioned Query Slower?

Last Updated June 11, 2019

Execution Plans, Partitioning

It’s tempting to think that table partitioning will improve query performance. After all, it’s an Enterprise Edition feature– it must have a lot of magic, right? Table partitioning does have magic for the right situations. It shines when you want to add a large amount of data to a table or remove a large amount…

Is leading an index with a BIT column always bad?

Last Updated March 12, 2018

“Throughout history, slow queries are the normal condition of man. Indexes which permit this norm to be exceeded — here and there, now and then — are the work of an extremely small minority, frequently despised, often condemned, and almost always opposed by all right-thinking people who don’t think bit columns are selective enough to…

Clustered Index key columns in Nonclustered Indexes

Last Updated March 12, 2018

Columnstore Indexes, Indexing, SQL Server

Clustered indexes are fundamental And I’m not just saying that because Kendra is my spiritual adviser! They are not ~a copy~ of the table, they are the table, ordered by the column(s) you choose as the key. It could be one. It could be a few. It could be a GUID! But that’s for another…

Finding Tables with Nonclustered Primary Keys and no Clustered Index

Last Updated February 13, 2017

i’ve seen this happen Especially if you’ve just inherited a database, or started using a vendor application. This can also be the result of inexperienced developers having free reign over index design. Unless you’re running regular health checks on your indexes with something like our sp_BlitzIndex® tool, you might not catch immediately that you have…

New Cardinality Estimator, New Missing Index Requests

Last Updated February 13, 2017

Brent Ozar

During some testing with SQL Server 2014’s new cardinality estimator, I noticed something fun: the new CE can give you different index recommendations than the old one. I’m using the public Stack Overflow database export, and I’m running this Jon Skeet comparison query from Data.StackExchange.com. (Note that it has something a little tricky at the…

Indexing for GROUP BY

Last Updated March 12, 2018

It’s not glamorous And on your list of things that aren’t going fast enough, it’s probably pretty low. But you can get some pretty dramatic gains from indexes that cover columns you’re performing aggregations on. We’ll take a quick walk down demo lane in a moment, using the Stack Overflow database. Query outta nowhere! Transact-SQL…

Indexing for Windowing Functions

Last Updated March 12, 2018

Indexing, SQL Server, T-SQL

Hooray Windowing Functions They do stuff that used to be hard to do, or took weird self-joins or correlated sub-queries with triangular joins to accomplish. That’s when there’s a standalone inequality predicate, usually for getting a running total. With Windowing Functions, a lot of the code complexity and inefficiency is taken out of the picture,…

Faster queries using narrow indexes and CROSS APPLY

Last Updated February 13, 2017

Jeremiah Peschka

It’s common to try to build the perfect index for a query. This index only uses the exact right columns for the key columns and other, presentational, data is pushed to the INCLUDE section of the index. While you can create a narrow key this way, the entire index row can become wide. A Sample…

Do Foreign Keys Matter for Insert Speed

Last Updated February 13, 2017

Jeremiah Peschka

Indexing

Do you have the key? Do foreign keys hurt the performance of inserts? Okay, we all know that foreign keys do require some work, but the crazy people of the internet have wildly varying guesses as to just how much work is involved. Estimates varied from “barely any” to “as much as 50% slower”. I…

Are Index ‘Included’ Columns in Your Multi-Column Statistics?

Last Updated July 13, 2017

Internals Cat populates the density vector When you create an index in SQL Server with multiple columns, behind the scenes it creates a related multi-column statistic for the index. This statistic gives SQL Server some information about the relationship between the columns that it can use for row estimates when running queries. But what if…

Online Index Creation and Cruel Defaults

Last Updated February 13, 2017

Have you ever been watching queries crawl, feeling sort of helpless because you can’t change them? And even if you could, it’s hitting a new custom column in an ISV table that no one bothered to tell you about. I’ve been there. I’ve been pomade deep there. I’ve also had my Robot tell me: “there’s…

Using “OR” and “IN” with SQL Server’s Filtered Indexes

Last Updated August 22, 2018

You can’t do everything with filtered indexes in SQL Server. For instance, you can’t create the following index: CREATE INDEX IX_Votes_filter ON dbo.Votes (PostId) WHERE (VoteTypeId = 1 OR VoteTypeId = 2); GO 123 CREATE INDEX IX_Votes_filter ON dbo.Votes (PostId) WHERE (VoteTypeId = 1 OR VoteTypeId = 2);GO If you try, you’ll get the error message:…

Testing ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY in SQL Server 2014

Last Updated February 13, 2017

Index Maintenance, Locking, Blocking, and Isolation Levels, SQL Server

One of the blocking scenarios I find most interesting is related to online index rebuilds. Index rebuilds are only mostly online. In order to complete they need a very high level of lock: a schema modification lock (SCH-M). Here’s one way this can become a big problem: An online index rebuild starts against a large index A…

How to Configure Ola Hallengren’s Maintenance Scripts

Last Updated August 9, 2020

Brent Ozar

Index Maintenance, Ola Hallengren's Database Maintenance Scripts

127

If you’re a production database administrator responsible for backups, corruption checking, and index maintenance on SQL Server, try Ola Hallengren’s free database maintenance scripts. They’re better than yours (trust me), and they give you more flexibility than built-in maintenance plans. However, the index maintenance defaults aren’t good for everyone. Here’s how they ship: Transact-SQL CREATE PROCEDURE…

Who’s Allowed to Add Indexes to ISV Apps?

Last Updated February 13, 2017

Brent Ozar

Indexing, ISV and SaaS Database Design, SQL Server

Performance tuning of independent software vendor (ISV) databases is a little tricky. To understand who does what, let’s think through all of the work required with building and hosting a database application: Who adds indexes and tunes queries? Typically the two parties involved – the vendor and the customer – start at opposite ends of…

When Does Index Fragmentation Matter?

Last Updated February 13, 2017

Jeremiah Peschka

Index Maintenance, SQL Server

A while back, we posted about how SQL Server index fragmentation doesn’t matter. That blog post generated a lot of heated discussion and has served to make people aware that fragmentation may not be the source of their woes. Sometimes, though, there are data access patterns where fragmentation really does matter. Perpetual Fragmentation Some data…

Should I Run sp_recompile After I Create An Index?

Last Updated February 13, 2017