Index Maintenance

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…

I bet you’re worried about your index fragmentation. If your database was a phone book, it would look like this one. And I bet you’re wasting your time, missing the real point. What is Index Fragmentation and How It Happens Let’s take a step back for a second and pretend that your database is a…

Last week, I blogged about the basics of SQL Server index fragmentation: why it happens, how to fix it, and how often people are fixing it.  I left you with a cliffhanger: it seemed that the frequency of defrag jobs didn’t appear to affect fragmentation levels: Databases with no index defragmentation were an average of…

We asked the community for index fragmentation information for data mining purposes, and the results are in – and interesting. Over the next couple of weeks, I’ll talk about some of the results we found in the field. What Is SQL Server Fragmentation? SQL Server stores data in pages.  To make things easy, think of…