Indexing

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 December 29, 2021

Foreign Keys

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

Erik Darling

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…

Foreign Keys in SQL Server (video)

Last Updated December 29, 2021

Foreign Keys

Learn the basics of Foreign Keys in SQL Server: trust, join elimination, cascading deletes, and indexing child keys. Limited on time? Check out the Table of Contents below. Table of Contents: 03:46 – Foreign keys change performance 04:12 – Let’s say we have no foreign key 04:41 – This query has a join 05:05 –…

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

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

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

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

Making index changes in SQL Server is tricky: you immediately want to know if the new index helped your performance and if it’s being used, but SQL Server execution plans and their related statistics can be are insanely confusing. It can be useful to run sp_recompile after you create an index, but not necessarily for the reason you might…

Using Triggers to Automatically Add Indexes

Last Updated February 13, 2017

Bad Idea Jeans, Indexing, SQL Server

Let’s say you’ve got a vendor application that creates tables, loads them, queries them, and drops them all the time. You can’t change the code. The tables involved desperately need an index in order to go faster, and they’re your biggest performance bottleneck. Enter DDL triggers. Regular DML triggers fire when you insert/update/delete rows in a…

Video: Test Your Index Design Skills (with poll results)

Last Updated February 13, 2017

You know how to design indexes, but you’re not sure how good your skills really are. In this quiz-packed session you’ll get a chance to test your skills! Kendra Little will walk you through a set of index design challenges. You’ll have time to answer each problem on your own, then find out whether the…

Why Index Fragmentation and Bad Statistics Aren’t Always the Problem (Video)

Last Updated February 13, 2017

Index Maintenance, SQL Server

Do you rely on index rebuilds to make queries run faster? Or do you always feel like statistics are “bad” and are the cause of your query problems? You’re not alone– it’s easy to fall into the trap of always blaming fragmentation or statistics. Learn why these two tools aren’t the answer to every problem…

How to Add Nonclustered Indexes to Clustered Columnstore Indexes

Last Updated February 13, 2017

Bad Idea Jeans, Columnstore Indexes, Indexing, SQL Server

SQL Server 2012 introduced nonclustered columnstore indexes, but I never saw them used in the wild simply because once created, they made the underlying table read-only. Not a lot of folks like read-only tables. (Bad news, by the way – that limitation hasn’t disappeared in 2014.) SQL Server 2014 brings clustered columnstore indexes, and they’re…

UPDATE STATISTICS: the Secret IO Explosion

Last Updated February 13, 2017

Index Maintenance, SQL Server, Statistics

I first knew something was up when I looked at the job history for a simple maintenance plan. It had two steps: Rebuild all the indexes in the database – this took 10 minutes each night. Update statistics – this took 2-3 hours each night. What was going on? Statistics in SQL Server are small,…

Keep it Constrained

Last Updated February 13, 2017