| Author |
Topic |
|
readysetstop
Posting Yak Master
123 Posts |
 Posted - 2007-06-20 : 14:28:33
|
| I'm having a kind of debate (the friendly kind) with my lead DBA about the virtues of updating stats vs. reindexing. He seems to think that if we run an update statistics just before a reindex, that the reindex will not only run faster, but somehow increase query performance. From what I understand, reindexing updates all the stats anyway, so I don't see what effect this would have, if any. Can anyone either correct me, or back me up on this?Thanks.-D.____________________________________________________________________________________"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-20 : 16:39:11
|
| You are correct. Just compare time takes to reindexing vs update stats and reindexing, you'll see the difference especially on big table. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-21 : 03:14:45
|
| Move the debate to DEFRAG + UpdateStats v. REINDEX, that should liven it up!Kristen |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-21 : 11:35:01
|
quote:
Originally posted by Kristen
Move the debate to DEFRAG + UpdateStats v. REINDEX, that should liven it up!Kristen
 Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-21 : 11:35:41
|
Or may be rebbot the server before the DEFRAG  Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-21 : 11:43:06
|
| Is that faster than rebooting the server After Defrag? Decisions, Decisions ... |
|
|
|
readysetstop
Posting Yak Master
123 Posts |
Posted - 2007-06-21 : 11:51:47
|
I think I'll leave the reboot part out. Some of these boxes might not come back up.  Seriously, though. I was thinking about a defrag every other week, statistic updates daily. Reindex as needed. Sound about right?____________________________________________________________________________________"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-21 : 11:58:11
|
| Do a CREATE INDEX with DROP_EXISTING. Do an update stats when you cannot reindex. Otherwise, your create index will update the stats.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-21 : 11:59:51
|
| "defrag every other week, statistic updates daily. Reindex as needed"We reindex small tables, defrag bigger ones. We only process tables that have "sufficient" fragmentation, we do that every night (spreads the load).We update stats daily.Kristen |
|
|
|
readysetstop
Posting Yak Master
123 Posts |
Posted - 2007-06-22 : 14:45:37
|
| So what would you consider a 'big' table, then? Is it just a function of rowcount, or does the number of indexes or columns matter? Or is this something I'm just going to have to test per server? (All 250 of them...)____________________________________________________________________________________"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-25 : 08:38:42
|
| I have no idea whether this is an "ideal" cutoff point, but for whatever reason when we wrote the routine its the one we are using [on SQL 2000]Based on the output of SELECT @strSQL = 'DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, if CountPages < 10000 then we use REINDEX.(@tableidchar === sysobjects.id)Kristen |
|
|
|
|
Update Statistics vs. Reindex.