Can you explain what all this means? Want to know how you determine whether a table needs to be defragged or not. . .

Question

Post reply

Can you explain what all this means? Want to know how you determine whether a table needs to be defragged or not. . .

KTG

Ten Centuries

Points: 1065
More actions
August 15, 2008 at 10:12 am

#211249

I ran
DBCC SHOWCONTIG (' ) with fast,ALL_INDEXES and got:
TABLE level scan performed.
- Pages Scanned................................: 46385
- Extents Scanned..............................: 5807
- Extent Switches..............................: 5806
- Scan Density [Best Count:Actual Count].......: 99.86% [5799:5807]
- Extent Scan Fragmentation ...................: 5.58%
DBCC SHOWCONTIG scanning ' ' table...
Table: ' ' (914102297); index ID: 2, database ID: 12
LEAF level scan performed.
- Pages Scanned................................: 3823
- Extent Switches..............................: 531
- Scan Density [Best Count:Actual Count].......: 89.85% [478:532]
- Logical Scan Fragmentation ..................: 1.88%
DBCC SHOWCONTIG scanning ' ' table...
Table: ' ' (914102297); index ID: 35, database ID: 12
LEAF level scan performed.
- Pages Scanned................................: 6039
- Extent Switches..............................: 4062
- Scan Density [Best Count:Actual Count].......: 18.58% [755:4063]
- Logical Scan Fragmentation ..................: 62.38%
DBCC SHOWCONTIG scanning ' ' table...
Table: ' ' (914102297); index ID: 36, database ID: 12
LEAF level scan performed.
- Pages Scanned................................: 4934
- Extent Switches..............................: 3870
- Scan Density [Best Count:Actual Count].......: 15.94% [617:3871]
- Logical Scan Fragmentation ..................: 74.50%
DBCC SHOWCONTIG scanning ' ' table...
Table: ' ' (914102297); index ID: 37, database ID: 12
LEAF level scan performed.
- Pages Scanned................................: 5653
- Extent Switches..............................: 5178
- Scan Density [Best Count:Actual Count].......: 13.65% [707:5179]
- Logical Scan Fragmentation ..................: 89.88%
What do you look for in determining whether a table needs to be defraged or not?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Mostly, just the logical scan fragmentation %

Above 30%, rebuild the index. Between around 15 and 30, reorganise.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

KTG Ten Centuries Points: 1065 More actions · Answer 2

What do you mean by reorganizing? All I know is defragging. . . :unsure:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

ALTER INDEX ... REBUILD

or

ALTER INDEX ... REORGANIZE

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

KTG Ten Centuries Points: 1065 More actions · Answer 4

How do I determine the index name that it is given the index ID for. . . I have multiple indexes on this table and dont know which one it is refering too.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Select name, index_id from sys.indexes where object_name(object_id) = < Table name >

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass