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 |
|
sureshot
Yak Posting Veteran
72 Posts |
 Posted - 2008-01-25 : 13:50:03
|
| Table A5.6GB80 million rowsInitial Page Space Used = 85.7% (sys.dm_db_index_physical_stats - avg_page_space_used_in_percent)Index and data size are almost unchanged after an ONLINE rebuild of the clustered index. Page count actually goes up by 800. No fill factor or pad index settings. Page space used = 99.2%During an OFFLINE rebuild the page count drops by ~20%. Page space used = 99.8%.How can the pages be 99.2% used yet the number of pages hasn't gone done from the ONLINE rebuild?I'm getting the feeling a shortcut was taken somewhere. Does an ONLINE rebuild only reorganize leaf level index pages but not any of the upper levels? Essentially it puts the pages in order but doesn't do any structure consolidation like OFFLINE?It would seem that you're missing out on some of the benefits of defragmenting if your data is still spread across more pages and you don't get as many rows per read or it takes more reads to go down your tree to get to your data. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-26 : 00:16:34
|
| Does table have clustered index? What's table freg level before and after rebuild index? I did it on my table, index size dropped from 145gb to 97gb. |
 |
|
|
sureshot
Yak Posting Veteran
72 Posts |
Posted - 2008-02-24 : 11:14:26
|
| I apologize for not seeing this earlier. My e-mail forwarding broke and I did not receive the notifications.Yes, this was the clustered index.**Pre Index Rebuild**Page Count: 733,960Frag Count: 311,508Avg Frag %: 30.9%Avg Frag Size in Pages: 2.39Page Space Used %: 85.7%**Post Index Rebuild ONLINE**Page Count: 734,778Frag Count: 91,220Avg Frag %: 0.17%Avg Frag Size in Pages: 8.17Page Space Used %: 99.2%(restored the db to pre-rebuild state from backup to try offline)**Post Index Rebuild OFFLINE**Page Count: 589,966Page Space Used %: 99.8% |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-25 : 14:45:29
|
| Did you change fill factor? |
|
|
|
sureshot
Yak Posting Veteran
72 Posts |
Posted - 2008-02-26 : 09:44:16
|
| No. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-26 : 21:44:01
|
| Did you rebuild indexes or reorg indexes? They are different. |
|
|
|
sureshot
Yak Posting Veteran
72 Posts |
Posted - 2008-02-27 : 08:37:46
|
| online index rebuild (sql 2005) |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-27 : 22:37:03
|
| I don't see that in my db on sql2k5 sp2 servers. How did you do that by the way? |
|
|
|
sureshot
Yak Posting Veteran
72 Posts |
Posted - 2008-03-17 : 15:38:13
|
| ALTER INDEX ... REBUILD with (online = on) |
|
|
|
|
|
|
Online index rebuild - same page counts after?