jump to navigation

Massive and Rapid Index Fragmentation in SQL Server 2005 September 11, 2009

Posted by Sean Welsh in sql server 2005.

At work I have an issue in one of my databases that is worth a blog post. The nature of the table is that it contains about 10 million records. One of the fields is related to MSISDNs (International Mobile Phone numbers). The traffic (inserts) is quite high. Typically several hundred records every five minutes during prime time (midday to 8pm).

In production we are using SQL Server Standard Edition. I issued this query to identify the problem indexes:

— index defrag detection
use <db_name>;
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(‘<db_name>’), OBJECT_ID(N'<db_name>.dbo.<table_name>’), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

The recordset indicated that idx_msisdn was 99% fragmented.

I ran this statement to rebuild the index which took about 90 secs.

— alter index rebuild
alter index idx_msisdn on <table_name> rebuild;

As this is a high traffic table and we are running standard this had to be done “undercover of the night” to avoid timing out too many app users while the index rebuild runs. (It causes a cfquery timeout in our ColdFusion app tier as rebuilding an index in SQL Server 2005 Standard Edition takes a full-table lock…)

I Googled around as you do and found various motherhood posts on why regular defragging of your databases is a good thing. However, having virtuously risen at 0500 to defrag the db without inconveniencing too many users. I was dismayed to learn that by 1000 when I went back to the office, that particular idx_msisdn was back to 99% fragmentation!

I hauled the Infrastructure Manager to my workstation and we ran the index rebuild again (in broad daylight). We checked that the index was defragged which it was. Then about a minute later we checked again by running the index defrag query. The percentage of defragging was increasing by 1% a minute… After 5 mins we were up to 5%. I went and had my lunch hour and sure enough when I got back the fragmentation level was at 60%!

Ouch… Basically we would have to rebuild our indexes every hour to keep up. Not sustainable…

The solution however was relatively easy. By default SQL Server 2005 will use fillfactor = 0 when rebuilding an index. If you dig into Books Online you will learn that 0 = 100 as far as SQL Server is concerned. Amazing but true…

If you are indexing a field that is likely to have a lot of “inserts in the middle of an indexed sequence” you need to specify a lower fillfactor.

SQL Server organizes indexes into “pages” which are 8K by default.

If I am indexing these MSISDNs


(imagine 8k worth)

and I have not set fillfactor in my index rebuild statement. By default the index pages will “split” everytime someone inserts a number “in between” the sequence when the index was built. This splitting of “pages” is what is measured by the index fragmentation percentage.

So I decided to specify a fillfactor of 50 for this particular index. This means that SQL Server is reserving 50% of the space for “in between” additions to the index (e.g. 614000000052). As there are lots of MSISDNs and I get hundreds every five minutes, this seems reasonable to me. Though it might be excessive.

So far idx_msisdn is remaining nicely defragged.



No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: