Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
buzzi
Starting Member
48 Posts |
 Posted - 2009-06-01 : 16:13:24
|
| Hello All,I am trying to understand why the REBUILD INDEX is not removing the "Extent Scan Fragmentation" after i ran the rebuild index. I tried to run this multiple times with different FILLFACTOR options but the % is not changing, The table i am trying to rebuild has a clustered index & 3 non clustered indexes. The clustered index is on a primary key which is an INDENTITY column and i don't have multiple data files, its 1 .mdf file. DBCC SHOWCONTIG scanning 'mytable' table...Table: 'mytable' (1199395392); index ID: 1, database ID: 1TABLE level scan performed.- Pages Scanned................................: 16171- Extents Scanned..............................: 2023- Extent Switches..............................: 2022- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 99.95% [2022:2023]- Logical Scan Fragmentation ..................: 0.02%- Extent Scan Fragmentation ...................: 68.46%- Avg. Bytes Free per Page.....................: 1338.7- Avg. Page Density (full).....................: 83.46%DBCC SHOWCONTIG scanning 'mytable' table...Table: 'mytable' (1199395392); index ID: 9, database ID: 1LEAF level scan performed.- Pages Scanned................................: 574- Extents Scanned..............................: 73- Extent Switches..............................: 72- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 98.63% [72:73]- Logical Scan Fragmentation ..................: 0.17%- Extent Scan Fragmentation ...................: 28.77%- Avg. Bytes Free per Page.....................: 1616.6- Avg. Page Density (full).....................: 80.03%DBCC SHOWCONTIG scanning 'mytable' table...Table: 'mytable' (1199395392); index ID: 11, database ID: 1LEAF level scan performed.- Pages Scanned................................: 2047- Extents Scanned..............................: 256- Extent Switches..............................: 255- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 100.00% [256:256]- Logical Scan Fragmentation ..................: 0.05%- Extent Scan Fragmentation ...................: 70.31%- Avg. Bytes Free per Page.....................: 1539.4- Avg. Page Density (full).....................: 80.98%DBCC SHOWCONTIG scanning 'mytable' table...Table: 'mytable' (1199395392); index ID: 12, database ID: 1LEAF level scan performed.- Pages Scanned................................: 275- Extents Scanned..............................: 35- Extent Switches..............................: 34- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 100.00% [35:35]- Logical Scan Fragmentation ..................: 0.36%- Extent Scan Fragmentation ...................: 91.43%- Avg. Bytes Free per Page.....................: 1627.8- Avg. Page Density (full).....................: 79.89%DBCC execution completed. If DBCC printed error messages, contact your system administrator.RESULTS OF DBCC CHECKALLOCThe total number of extents = 208844, used pages = 1665030, and reserved pages = 1670616 in this database. (number of mixed extents = 349, mixed pages = 2656) in this database.CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'mydatabase'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.Any help is greatly appreciated.Thank you all |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-06-02 : 10:46:43
|
| The Extent Scan Fragmentation is likely due to the fact that you have other objects in the database. The table takes up just over 2,000 extents, and SQL Server can not find a large enough contiguous place to put that table. I would not worry about it, because even if you did have space, I doubt SQL server would try to place the table in that space. Also, the table is a bit small for reindexing. If you tested your queries before and after the rebuild, I doubt you would see a big difference. For most systems today, rebuilding indexes does not give a difference noticeable by a human until the table gets up around 10,000 pages. |
 |
|
|
buzzi
Starting Member
48 Posts |
Posted - 2009-06-02 : 12:06:19
|
| Thanks for the reply, the table does have more than 10,000 pages, this is output of the table after i ran DBCC CHECKTABLE There are 161704 rows in 16171 pages for object "mytable".DBCC execution completed. If DBCC printed error messages, contact your system administrator.any other clues?? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-06-02 : 12:40:48
|
| Reason is: Index is small or you must have multiple Files in your filegroup when index belongs. In these case,the number you get is not true. |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-06-02 : 13:15:09
|
| Bah! I was hung up on looking a the number of extents, instead of the pages. Sorry bout that. |
|
|
|
buzzi
Starting Member
48 Posts |
Posted - 2009-06-02 : 14:59:56
|
| No problem!sodeep,i am not having multiple data files, i just have 1 primary file.Thanks, |
|
|
|
|
|
|
|
|
Extent Fragmentation in sql 2005