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

December 10, 2013

Jeremiah Peschka

constraints, database design

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

November 26, 2013

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

November 19, 2013

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

November 13, 2013

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

November 12, 2013

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?

October 22, 2013

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?

October 9, 2013

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]

September 23, 2013

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

September 5, 2013

Index Maintenance, SQL Server

106

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

July 30, 2013

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

July 25, 2013

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!)

June 14, 2013

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

May 8, 2013

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

April 30, 2013

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…

Disabling vs. Dropping Indexes

February 28, 2013

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

January 31, 2013

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

January 28, 2013