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.)
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 Microsoft says fragmentation matters – a lot. They 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. 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.
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.
Continue to Part 2: My Study on SQL Server Index Fragmentation
Want to learn more? We’ve got video training explaining it! In our free 90 minute video series How to Think Like the SQL Server Engine, you’ll learn:
- The differences between clustered and nonclustered indexes
- How (and when) to make a covering index
- The basics of execution plans
- What determines sargability
- How SQL Server estimates query memory requirements
- What parameter sniffing means, and why it’s not always helpful
After I saw the results of your query on my production database, I immediately implemented Michelle's script. It's made a world of difference in our database performance.
That's great! I'm a big believer in defrags, and her script is good stuff.
I've checked out Michelle's blog, but she's using SQL 2005 and I'm still stuck in the dark ages of 2000. What can I do about index fragmentation?
Excellent article as usual Brent.
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:
Excellent Article As always Brent!
Brent , great article .Does changing the fill factor of the index improves If a table has lots of inserts and is frequently fregmented.
Asit – thanks! It totally depends – in some situations that can help a lot. You’ll need to test in your environment though.
Great article, thanks.
One question I have is does rebuilding indexes guarantee that index fragmentation is eliminated on all indexes?
I ran rebuild indexes on all tables in a database and compared the fragmentation percentage before and after this operation.
The rebuild was accomplished via the SQL Server Maintenance Plan Wizard.
On some indexes the fragmentation level was reduced but not eliminated, and on others it was unchanged.
Any idea as to why this may be the case?
David – often that happens on very small objects, like tables with only a few hundred pages.
Brent – thanks. Really useful information, much appreciated.
my sql server 2000 is very slow and user complaning and my OS is server 2003 standard ,please if any help or steps plzzzzzzzzzzzzzzzz will be great and very thankful
Hi! Sure, click on First Aid at the top of the site, and we’ve got plenty of resources for performance tuning including video tutorials and blog posts.
If you’d like urgent hands-on help, shoot us an email by clicking Contact at the top of the page and we can set up a consulting engagement.
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
Sure, you bet – that’s how our consulting engagements work. We’re able to jump in and get started improving performance fast.
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 :/
Ouch – unfortunately no, we’re booked today and I can’t walk you through a subject this complex quickly.