Indexing

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

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

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

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

Brent Ozar

Index Maintenance, SQL Server, Videos

Are you still rebuilding all of your indexes every week – or heaven forbid, every night – and still having performance problems? Odds are, you’re not actually making things better – you’re making them worse! I explain why in this video: For more, read my post: Stop Worrying About Index Fragmentation.

Rebuild or Reorganize: SQL Server Index Maintenance

Last Updated February 13, 2017

Index Maintenance, SQL Server

108

Index maintenance compacts a structure *and* changes physical order– both can be important! Editors Note: this post was updated in June 2014 to link to an online index rebuild bug that can cause corruption. Once up on a time, there was a database server with 500GB of data and a heavy read workload of dynamic…

Trivial Optimization and Missing Indexes in SQL Server

Last Updated May 12, 2019

Recently, Jes asked the team an index tuning question: “If a query has an index hint in it, will the optimizer ever suggest a missing index for that query?” I immediately loved the question because I’d never really thought about it before. I typically think of index hints as being a very risky game and…

How to Find ‘Secret Columns’ in Nonclustered Indexes

Last Updated November 16, 2017

Indexing, sp_BlitzIndex

sp_BlitzIndex® just can’t keep a secret. That’s a feature, not a bug. I wrote sp_BlitzIndex® specifically so that it would rat out some things that SQL Server is doing behind the scenes to secretly help you out– because it’s really better if you know about it. What are secret columns? SQL Server needs a unique…

The Index Your Queries will Never Request (A Clustered Index!)

Last Updated April 10, 2017

When it comes to indexes, SQL Server is really helpful. It lets you see what indexes queries are asking for both in execution plans, and missing index dynamic management views (“DMVs”). I like to look at the DMV missing index requests using sp_BlitzIndex®. When you look at missing index requests, it’s always important to remember…

Indexing Wide Keys in SQL Server

Last Updated February 13, 2017

Key length matters in SQL Server indexes. It’s a best practice to keep your index keys as narrow as possible, and SQL Server enforces a maximum key length of 900 bytes on most “normal” clustered and nonclustered indexes. But what happens if you want to optimize the lookup of a wide column? You’re not necessarily…

5 Things About Fillfactor

Last Updated February 13, 2017

Index Maintenance

Are you a page half full, or a page half empty kind of person? I’ll never think “I’ve seen it all” when it comes to SQL Server– there’s just always someone waiting around the corner who’s found a weird new thing to do with it. But there are some things I really wish I could…

What’s Better: Disabling vs. Dropping Indexes?

Last Updated April 2, 2021

In order to improve your applications and your databases, they will need to change over time. The structure of the database changes, the structure of the tables change, the data in the tables change, the application changes, the queries against the data change. Indexes that once helped performance now just bloat your database and cause extra work…

Best Practices + Table Partitioning: Merging Boundary Points

Last Updated February 13, 2017

Partitioning, SQL Server

One of the many best practices for SQL Server’s table partitioning feature is to create “extra” empty partitions around your data. This is explained in SQL Server Books Online in the page on altering a Partition Function: Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before…

SQL Server Table Partitioning Tutorial: Videos and Scripts

Last Updated December 29, 2021