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 |
|
Kristen
Test
22859 Posts |
 Posted - 2007-10-21 : 13:07:11
|
| I#ve been doing disaster recovery on a web box that died today.So I thought I'd do some "downtime" maintenance on the DB serverI ran a BDREINDEX on all tables, all indexes. (I know this is the 2000 way, but I assume its as good as the proper 2005 way??).5 minutes on a 10GB database. Not bad!I checked the DEFRAG and UPDATE STATS processes that run overnight.They are basically defragging only tables with SHOWCONTIG indicating fragmentation. And then doing an UPDATE STATISTICS WITH FULLSCAN on all tablesThat is taking an average of 30 minutes ...Is DBREINDEX the equivalent of an UPDATE STATISTICS WITH FULLSCAN, or is it in some way a smaller-sample version?I'm wondering why I don't just lock the DB and do a REINDEX of everything in 5 minutes ...Kristen |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-21 : 14:50:16
|
| i think it's a fullscan but use DBCC SHOW_STATISTICS to be sure._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-21 : 15:14:26
|
| "DBCC SHOW_STATISTICS"Didn't know that. Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-21 : 15:32:19
|
| OK, tried that. Lots of loverly output. How do I tell the difference between an ordinary DBREINDEX and a FULLSCAN one ??Thanks |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-21 : 17:00:48
|
| Dbcc dbreindex takes update stats option from whatever used in update statistics statement. |
|
|
|
|
|
|
Defrag / Reindex