Index Maintenance

How Much is Offline During an Index Rebuild?

Last Updated February 16, 2017

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

Kendra Little

Index Maintenance, SQL Server

118

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…

5 Things About Fillfactor

Last Updated February 13, 2017

Kendra Little

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…

Stop Worrying About SQL Server Fragmentation

Last Updated March 5, 2017

Brent Ozar

Index Maintenance, SQL Server

139

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…

Index Fragmentation Findings: Part 2, Size Matters

Last Updated July 13, 2017

Brent Ozar

Index Maintenance, SQL Server

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…

Index Fragmentation Findings: Part 1, The Basics

Last Updated July 13, 2017

Brent Ozar Unlimited Team

Index Maintenance, SQL Server

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…