| Author |
Topic |
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
 Posted - 2002-11-08 : 14:27:33
|
| I'm using DBCC DBREINDEX.- Pages Scanned................................: 35- Extents Scanned..............................: 5- Extent Switches..............................: 4- Avg. Pages per Extent........................: 7.0- Scan Density [Best Count:Actual Count].......: 100.00% [5:5]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 20.00%- Avg. Bytes Free per Page.....................: 1755.5- Avg. Page Density (full).....................: 78.31%I noticed that the EXTENT SCAN is still at 20%. Is this because I used a fill factor of 80 or is there something I'm missing with SQL2k.DanielSQL Server DBA |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-11-08 : 16:58:37
|
| Do you have a clustered index on the table or is it a heap ? |
 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-11-08 : 17:02:21
|
quote:
Do you have a clustered index on the table or is it a heap ?
1 clusted index...DanielSQL Server DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-11-08 : 18:36:49
|
| You're running DBCC SHOWCONTIG. Right? Just keeping you honest.From BOL (maybe you already checked this out):Logical Scan Fragmentation and, to a lesser extent, Extent Scan Fragmentation values give the best indication of a table's fragmentation level. Both these values should be as close to zero as possible (although a value from 0% through 10% may be acceptable). It should be noted that the Extent Scan Fragmentation value will be high if the index spans multiple files. Both methods of reducing fragmentation can be used to reduce these values.So I think that you are okay here. |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-11-08 : 19:24:23
|
quote:
You're running DBCC SHOWCONTIG. Right? Just keeping you honest.
Yes. I am using DBCC SHOWCONTIG to show the results after I use DBCC DBREINDEX. So my post was honest when I said I am using DBREINDEX, the showcontig was just an extra.My point was: If I rebuild the index why does the EXTENT stay fragmented?I do agree that I'm ok cause the logical scan is 0 and the scan density is really whats suposed to count for performance... but I cant help but thinking that there is just a little more I tuning I can do to make my queries move from super-fast to light-speed. DanielSQL Server DBA |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-11-09 : 11:06:46
|
| I think that the low number of extents tends to over exagerate this number, since you only have 5 extents allocated to the table then only one of the is physically out od order. What do you get fromdbcc extentinfo('databasename','tablename')HTHJasper Smith |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-11-10 : 13:46:06
|
quote:
I think that the low number of extents tends to over exagerate this number, since you only have 5 extents allocated to the table then only one of the is physically out od order. What do you get fromdbcc extentinfo('databasename','tablename')HTHJasper Smith
dbcc extentinfo('databasename','tablename') results:file_id page_id pg_alloc ext_size obj_id index_id pfs_bytes----------- ----------- ----------- ----------- ----------- ----------- ------------------1 232 8 8 549576996 1 0x40404040404040401 264 8 8 549576996 1 0x40404040404040401 272 8 8 549576996 1 0x40404040404040401 280 8 8 549576996 1 0x40404040404040401 288 7 8 549576996 1 0x4040404040404000(5 row(s) affected)DanielSQL Server DBA |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-11-10 : 15:00:51
|
| You can see from the output that the last 4 extents are physically contiguous (the first page of the extent - which is the meaning of the page_id column - increments in values of 8 as there are 8 pages per extent) and only the first allocated extent is physically out of order (extent scan fragmentation). Not sure why it can't allocate contiguous extents for the table though. Have you tried a dbreindex with 100% fill factor followed by one with 80%. To be honest I doubt it will have any impact whatsoever because it's such a small table.HTHJasper Smith |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-11-11 : 09:38:44
|
quote:
You can see from the output that the last 4 extents are physically contiguous (the first page of the extent - which is the meaning of the page_id column - increments in values of 8 as there are 8 pages per extent) and only the first allocated extent is physically out of order (extent scan fragmentation). Not sure why it can't allocate contiguous extents for the table though. Have you tried a dbreindex with 100% fill factor followed by one with 80%. To be honest I doubt it will have any impact whatsoever because it's such a small table.HTHJasper Smith
Yeah this is a pretty small table... I have a table with 10591 pages that doesnt have this problem so I'm not understanding why one with only 5 pages cant defrag itself... Ok. So I changed it to 100% and the fragmentation grew to 25%. I ran dbreindex 3 more times and it went to 0%. I then changed it back to 80% and the fragmentation went back to 20%. From there I ran dbreindex 2 more times and its at 0%.- Pages Scanned................................: 38- Extents Scanned..............................: 5- Extent Switches..............................: 4- Avg. Pages per Extent........................: 7.6- Scan Density [Best Count:Actual Count].......: 100.00% [5:5]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 0.00%- Avg. Bytes Free per Page.....................: 1598.7- Avg. Page Density (full).....................: 80.25%Thanks.DanielSQL Server DBA |
|
|
|
|
Extent Scan Fragmentation