Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

index fragmentation Expand / Collapse
Author
Message
Posted Monday, November 9, 2009 1:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Monday, November 9, 2009 1:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Posted Tuesday, November 10, 2009 2:30 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:48 PM
Points: 9,923, Visits: 11,169
Nice quote G2. 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
Posted Tuesday, November 10, 2009 6:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Posted Wednesday, November 11, 2009 8:07 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:58 PM
Points: 4,359, Visits: 6,195
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??

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
Posted Friday, November 13, 2009 7:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 22, 2014 11:50 AM
Points: 31, Visits: 267
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

Happy Weekend!

Post #818516
Posted Friday, November 13, 2009 8:28 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 9:47 AM
Points: 357, Visits: 914
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
Posted Friday, November 13, 2009 1:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 2:21 PM
Points: 116, Visits: 160
Thanks guys i really appreciate all your info on this
Post #818879
Posted Friday, November 13, 2009 1:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 2:21 PM
Points: 116, Visits: 160
Thanks really appreaciate all your help
Post #818882
Posted Friday, November 13, 2009 2:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 2:21 PM
Points: 116, Visits: 160
thank you so much i appreciate it
Post #818888
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse