Indexing

You can’t do everything with filtered indexes in SQL Server. For instance, you can’t create the following index: Transact-SQL 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…

Testing ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY in SQL Server 2014

Last Updated August 15, 2024

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 IndexOptimize Maintenance Script

Last Updated April 14, 2023

Index Maintenance, Ola Hallengren’s Database Maintenance Scripts

138

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

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

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

Jeremiah Peschka

SQL Server has this fancy feature called constraints. Database constraints are used to restrict the domain (the range of allowed values) of a given attribute. That’s just a funny way of saying: through a set of carefully crafted rules, we control the shape of our universe. Our Test Table We’re going to be testing with…

Filtered Indexes vs. Table Partitioning

Last Updated February 13, 2017

Jes Schultz

Indexing, Partitioning, SQL Server

It was a dark and stormy… Oh, wrong story. It was actually a warm, sunny afternoon in Charlotte, NC. I was presenting “Index Methods You’re Not Using” at PASS Summit. In this talk, I discussed how indexed views, filtered indexes, and compressed indexes can improve your query performance by reducing I/O. From stage right, an…

What You Can (and Can’t) Do With Indexed Views

Last Updated May 9, 2019

Jes Schultz

Views are logical objects in SQL Server databases that present you with a “virtual table”. Views are typically created for one of three reasons: security, simplification, or aggregation. Security: we create views so that a user can read specific columns out of certain tables, but not all the data. Simplification: sometimes, it’s easier to write…

Filtered Indexes and Dynamic SQL

Last Updated February 13, 2017

Jeremiah Peschka

Brent Ozar Unlimited Team

I’ve been told that an attendee at the PASS Summit pre-conference event asked about using dynamic SQL to get around some of the problems with filtered indexes. I’m not entirely sure what the question was, but it did give me the opportunity to play around with filtered indexes and write some simple demo code to…

What You Can (and Can’t) Do With Filtered Indexes

Last Updated January 21, 2019

Filters aren’t just for instagram photos. When you write a query, you (usually) don’t want to return all the rows from the table(s) involved – so you add a WHERE clause to the statement. This ensures that fewer rows are returned to the client – but doesn’t reduce the amount of I/O done to get…

Index Hints: Helpful or Harmful?

Last Updated February 13, 2017

Jes Schultz

Let me ask you a question: do you want to boss the SQL Server query optimizer around? If you answered no: good. You’re willing to let the query optimizer do its job, which is to find the least expensive way to run a query, as quickly as possible. If you answered yes: you’re a brave…

How Much is Offline During an Index Rebuild?

Last Updated February 16, 2017

Index Maintenance

Kendra with her Not-A-SAN This question came up in the pre-show chat for our weekly webcast: when you rebuild a nonclustered index offline, does it impact only the nonclustered index? Or does it impact the entire table? Showing is more fun than telling! Let’s take a look. First I restore AdventureWorks to slow storage Sometimes…

Why Index Fragmentation Doesn’t Matter [Video]

Last Updated February 13, 2017