Index Fragmentation Findings: Part 1, The Basics

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 them as being literally pages of paper.  Let’s say you’re the database, and I’m going to give you data to store.  You’ve got a pile of blank paper and a pen, and your job is to keep track of the names of the customers I say out loud.

I want my imaginary table to be stored with a clustered key on Last Name, First Name.

I start talking, and I say things like this:

• Denny Cherry
• Steve Ballmer
• Bill Gates
• Donald Farmer
• Jason Massie
• Tom LaRock
• Tim Ford

Notice that I’m not going in alphabetical order – I’m just yelling them out as people buy my product.  People don’t buy it in alphabetical order.  (Looks like Denny is an early adopter – he’s probably the kind of guy who runs Windows 7 on his desktop already.)

Fragmented Pages

You’re faced with two challenges:

• How much blank space do you leave on each page in order to accommodate new people who will have last names between, say, Farmer and Ford?
• When you run out of space on a page, how do you quickly rewrite that page to break up the data and leave more room?

There’s a lot of database concepts here like fill factor and page splits, but the problem we’re focusing on here today is fragmentation: what happens when these pages of paper are scattered all over the place with varying amounts of free space on them.

It gets more complicated with wider tables, tables that store more data than just first and last name.  The wider they are, the more likely they are to have complicated indexes – so you’ve got lots of pieces of paper to manage, duplicate copies of your data.

After a couple of hours of me yelling out names, two things are going to happen. First, you’re going to quit, and second, I’m going to have to figure out how to clean up the mess to improve performance.

Does It Really Matter?

Are we just being anal when we want our pages nice, clean and neat?  Do we really need to keep our cubicle clean?  (I’ve seen some of your workplaces, and no, pizza crumbs don’t go away by themselves.)

It turns out fragmentation matters – a lot.  Microsoft put out a whitepaper on SQL Server 2000 Index Defragmentation Best Practices, and it included performance statistics on the impact of fragmentation.  Here’s a quote:

“The workload performance increase realized in the small-scale environment ranged from 60 percent at the low level of fragmentation to more than 460 percent at the highest level of fragmentation. The workload performance increased realized for the large-scale environment ranged from 13 percent at the low fragmentation level to 40 percent at the medium fragmentation level.”

Index fragmentation slowed their systems down between 13% to 460%.  Ouch.

How Do We Fix SQL Server Fragmentation?

The solution is to regularly defragment or rebuild our indexes. As with anything else in SQL Server, there’s three ways to do that:

• Use the maintenance plan wizard in SQL Server Management Studio. Microsoft includes a really simple wizard to automate this process, but it has a killer weakness.  It rebuilds every index, every time, regardless of the fragmentation level.  On databases under 10gb with long maintenance windows every night or weekend, this makes sense.  The larger the database grows and the smaller the maintenance window shrinks, this is no longer an option.  This is compounded by the fact that SQL Server Standard Edition can’t rebuild indexes online – it holds locks on the table while it works, locking out your users.
• Write your own T-SQL scripts to do more custom logic. Using T-SQL commands, you can check first to see which indexes are fragmented, and only rebuild the ones that match your thresholds for reindexing or rebuilding.  Michelle Ufford aka SQLFool wrote an excellent index defrag script for SQL Server 2005/2008, and it’s available on SQLServerPedia’s code library.  She’s also doing a free webcast about index defragmentation on March 12.
• Buy a third party index maintenance program. When something is difficult or impossible with the native SQL Server tools, vendors like Quest step in and build tools to make it easier.  Quest Capacity Manager adds even more functionality with things like reporting and maintenance windows: you can pick maintenance window times for each server, and Capacity Manager will automatically defrag as many indexes as possible during that time window and bail out before your production users return.

Another Silent Killer

All of these solutions have their pros and cons, and sadly, many DBAs just don’t take any action at all.  Last year, I wrote a tech brief for Quest called Best Practices in Index Maintenance – Fighting the Silent Performance Killer, and I called it that because so many DBAs just don’t know how much performance they’re leaving on the table.  The cheesiness didn’t stop with the title, either – here’s the opening paragraph:

“Right now in your data center, a vicious, unforgiving villain is slowly draining the performance from your database servers. It strikes without warning and doesn’t leave a trace in event logs or messages. This killer is fragmentation: data that belongs together logically is not stored together physically.“

I’m revisiting the topic now because in these tight budget times, we’re not getting the money we need to improve server performance, and we have to figure out how to get more value out of the hardware we already have.

So How Many DBAs Defragment Their Databases?

Before we start, I’d start by saying these results were only gathered from volunteers willing to send in their data. My hunch, based off the DBAs I spoke with, is that results are probably worse than this survey appears. When DBAs emailed me their results, and I asked if they were currently doing any index defrag maintenance, they said things like:

• “Ummm…no. Just learning about it.”
• “No, but I’ve been meaning to.”

So my guess, and this is just an unscientific guess, is that DBAs who aren’t doing index defragmentation were less likely to send in their index fragmentation statistics. Because of that, I won’t cover the percentages of who’s doing it and who’s not, because I don’t think it’s that accurate.

How Often Do They Defrag Indexes?

Answers were daily, weekly, monthly and not at all, which leads to the next question: how much of a difference does it make?  Let’s look at the average fragmentation percentages for all objects in the database:

• Databases with no index defragmentation were an average of 5% fragmented
• Monthly – 17% fragmented
• Weekly – 3% fragmented
• Daily – 6% fragmented

It’s like a looking at the results of a developer writing SQL after three Red Bulls – it doesn’t make any sense, and you just want to wad it up and throw it away.

But that’s where data mining comes in.  If we start analyzing the data and including more attributes about what we’re analyzing, it turns out it all makes perfect sense.

Continue to Part 2: My Study on SQL Server Index Fragmentation

Brent Ozar PLF Team

Brent Ozar PLF is a boutique consulting firm focused on understanding your environment and strategy. We partner with you to objectively identify pain points and develop solutions that align to your business goals. Your experience comes first; we share our knowledge and expertise to help you.

Twitter - Facebook - More Posts