| Author |
Topic |
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
 Posted - 2008-05-21 : 00:30:36
|
| Dear All,i've used the DBCC showcontig command against my table table103but i dont know how to analyze the results of fragmentation levels. please give me some explanations or some good links.....the results are:DBCC SHOWCONTIG scanning 'TABLE103' table...Table: 'TABLE103' (1899257921); index ID: 1, database ID: 10TABLE level scan performed.- Pages Scanned................................: 20- Extents Scanned..............................: 13- Extent Switches..............................: 18- Avg. Pages per Extent........................: 1.5- Scan Density [Best Count:Actual Count].......: 15.79% [3:19]- Logical Scan Fragmentation ..................: 90.00%- Extent Scan Fragmentation ...................: 92.31%- Avg. Bytes Free per Page.....................: 3281.4- Avg. Page Density (full).....................: 59.46%VinodEven you learn 1%, Learn it with 100% confidence. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-05-21 : 02:53:36
|
quote:
Originally posted by sunsanvin
- Logical Scan Fragmentation ..................: 90.00%- Extent Scan Fragmentation ...................: 92.31%
These are two of the key lines. Logical fragmentation is a measure of what fraction of pages are out of order. Extent is much the same just with extents (8 page groups)Generally, a logical fragmentation >30% means that the table should be reindexed.Your table is small (20 pages) so you won't be seeing much impact from the fragemntation and it isn't that much of a worry. For larger pages (1000 pages + ) is is a big issue to have high fragmentation.--Gail Shaw |
 |
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2008-05-21 : 05:37:34
|
| Great Explanation Gail Shaw.....thank you very muchVinodEven you learn 1%, Learn it with 100% confidence. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-21 : 05:56:29
|
I believe Scan Density is the third key line.Should be a value of least 90%.And Gilas #1 and #2 should be taken into account if there are multiple files.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-05-21 : 06:09:21
|
| i like the Avg. Page Density. this shows how full your pages are which signals posibility of page splits._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-21 : 06:11:44
|
The "Avg. Bytes Free per Page" with value 3281.4 indicates that this table's record size is greater than 3281 bytes.Which in turn indicates that you only can store 2 records per page.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2008-05-21 : 07:22:17
|
quote:
Originally posted by Peso
The "Avg. Bytes Free per Page" with value 3281.4 indicates that this table's record size is greater than 3281 bytes.Which in turn indicates that you only can store 2 records per page.
E 12°55'05.25"N 56°04'39.16"
the table has 49 columns. is that the reason for this?VinodEven you learn 1%, Learn it with 100% confidence. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-05-21 : 07:34:36
|
| 49 bigish columns (varchar, nvarchar, char, nchar,...) I assume.But, yes, 49 columns is a contributing factor.--Gail Shaw |
|
|
|
Zack
Starting Member
26 Posts |
Posted - 2008-05-21 : 08:10:10
|
| Isn't Extend Swtiches an important value as well? It shows the number of times DBCC switched between pages while running the command. It would seem the fewer switches the better. sunsanvin: How about rebuilding the indexes on that table and then posting the results of DBCC again so we can see the difference rebuilding the index made. |
|
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2008-05-22 : 00:44:21
|
| Actually there is only one index on that table. that is clustered index and that column is a newid varchar(50).VinodEven you learn 1%, Learn it with 100% confidence. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-05-22 : 02:42:09
|
quote:
that column is a newid varchar(50).
That's gonna cause very rapid fragmentation.Stupid question - why store the newID in a varchar rather than a uniqueindentifier?--Gail Shaw |
|
|
|
|
dbcc showcontig results...analyze please