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

“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. Ola Hallengren’s maintenance scripts are fantastic.

Buy a third party index maintenance program. When something is difficult or impossible with the native SQL Server tools, vendors step in and build tools to make it easier.

Another Silent Killer

All of these solutions have their pros and cons – and sometimes, the best answer is to just not do defragmentation at all.

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.

Related

Brent Ozar Unlimited 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.

Thanks, glad you liked it! Well, you've got maintenance plans, or you can roll your own index defrag script. There's a lot of scripts out there, but I haven't used any of the SQL 2000 ones. I'd start by checking out the SQL Server Central script library – they've got a ton of index maintenance scripts:

thank u for ur fast answer,but to dont disturb u i will c the linkes and tutorials while iam realy run out of time,but if i will be late and if u dont mind i will need ur help to guid me,and will be very thankfull,its nice to c ppl like to help othr ppl stil out there 🙂 thank uuuuuuuuuu

sory for anoying but its first time for me on sql server always was on oracle but new company so:/ can just tell me steps 1 ,2,3,4….. and i will look at coz i feel messsssssssss or give me specific linkes coz i c many on ur website and i get confused fel like i need all of tht 🙂 iam realy sory but i wil lose my job like that or at lest wil lost some of my reputation :/