index fragmentation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » index fragmentation

index fragmentation

Rate Topic

Display Mode

Topic Options

Author

Message

klineandking

Posted Monday, November 09, 2009 1:09 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 2:21 PM
Points: 116, Visits: 160

I would like to know the percentage at which you would have to defragment your indexes?

Post #816083

GSquared

Posted Monday, November 09, 2009 1:37 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

Per Books Online:

Fragmentation alone is not a sufficient reason to reorganize or rebuild an index. The main effect of fragmentation is that it slows down page read-ahead throughput during index scans. This causes slower response times. If the query workload on a fragmented table or index does not involve scans, because the workload is primarily singleton lookups, removing fragmentation may have no effect.

I recommend doing an online search on the subject. There are a number of good, informative articles on the subject.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #816094

Paul White

Posted Tuesday, November 10, 2009 2:30 AM

SSChampion

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989, Visits: 10,529

Nice quote G². There is a lot of over-excitement surrounding fragmentation. Maybe it's because people 'know' that it is important to keep disk files defragmented?

The situation in SQL Server is quite different. Sure, if the majority of your queries involve big (range) scans on covering or clustered indexes, which invoke SQL Server's read-ahead mechanism, then yes, fragmentation can be important (much less so if your I/O system is RAM- or SSD- based!)

Read-ahead is only invoked on large scans where the data pages required are not already in cache. So, for many systems which either do mostly repetitive single-row operations, or which have enough buffer pool to keep the vast majority of the working set of data in memory, defragmenting offers little benefit, other than perhaps making the DBA feel better.

Paul

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #816361

GSquared

Posted Tuesday, November 10, 2009 6:47 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

I have an automatic defragmentation routine for indexes that checks number of pages (> 1000), fragmentation level, and check sys.dm_db_index_usage_stats (taking uptime into account). If the index has very few scans, it won't automatically defrag it. Will add it to an automated weekly report to let the DBA know that it may need to be manually defragged, or added to an override list that forces a defrag regardless of use.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #816453

TheSQLGuru

Posted Wednesday, November 11, 2009 8:07 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 3:08 PM
Points: 3,574, Visits: 5,111

klineandking (11/9/2009)

I would like to know the percentage at which you would have to defragment your indexes?

The percentage at which you have an issue with performance related to index fragmentation?? BigGrin

This really is one of those "it depends" scenarios.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail

Post #817188

Kathy Cowens

Posted Friday, November 13, 2009 7:55 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 7:47 AM
Points: 31, Visits: 244

Hi,
See the following: http://www.sqlmag.com/Article/ArticleID/96059/sql_server_96059.html

This is a useful tool for those of us that need the comfort level Smile

Happy Weekend!

Post #818516

NickBalaam

Posted Friday, November 13, 2009 8:28 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 7:27 AM
Points: 342, Visits: 788

The microsoft recommendation is

<= 5% - Do nothing
>5 and <= 30% - Reorganize (defrag)
>30% - Rebuild

But as you can see from the all the posts....it depends! And is worth remebering that fragmentation in OLTP is not as costly as fragmentation in OLAP. You can use the following sql to identify fragmentation on a given tables index'

SELECT
a.index_id,
name,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(' [database name] '),OBJECT_ID(' [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;

Post #818546