Alter Index with STATISTICS_NORECOMPUTE

Question

Alter Index with STATISTICS_NORECOMPUTE

chileu17

Hall of Fame

Points: 3093
More actions
October 16, 2008 at 2:26 pm

#194593

Hi everyone I have a doubt and I am not sure how to procede. I have migrated a db from sql 7.0 to sql 2k5. So I have started rebuiliding/reorganizing the indexes that needed to (Indexes fragmented).
The thing is that there is this option 'STATISTICS_NORECOMPUTE' and after reading for a while, and trying to get all my ideas together I can say that it makes the server not to update my tables' statistics. But I need the statistics re-done, so I shouldn't use this sentence right? and use 'UPDATE STATISTICS ' ???????' Or what is the execution order in which I have to process? Thanks :hehe:

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

If you do an ALTER INDEX REBUILD, by default the server will update the statistics on its own. If you did ALTER INDEX REBUILD WITH (STATISTICS_NORECOMPUTE = ON) then you are correct, it wouldn't update statistics. Since you said this database was a previous version of SQL Server, you should just do the index rebuilds and let it update the statistics.

If you're later doing maintenance and do ALTER INDEX REORGANIZE, then you might want to do UPDATE STATISTICS afterward because a REORGANIZE doesn't automatically do it for you likle REBUILD does.

chileu17 Hall of Fame Points: 3093 More actions · Answer 2

Thanks for the quick reply, but I have another doubt, do the statistics I 'migrated' from sql 7.0 work the same as sql 2k5?Or should I rebuild all the indexes for all my tables? Thanks again.

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 3

You should just go through and update all the statistics, everywhere. Statistics are pretty different from 7 to 2000 and even more so from 2000 to 2005.

You'll want to look at each index to see if it needs to be rebuilt. If they're not fragmented, leave them alone.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

chileu17 Hall of Fame Points: 3093 More actions · Answer 4

Grant Fritchey (10/17/2008)
You should just go through and update all the statistics, everywhere. Statistics are pretty different from 7 to 2000 and even more so from 2000 to 2005.
You'll want to look at each index to see if it needs to be rebuilt. If they're not fragmented, leave them alone.

Thanks for the reply I had rebuilt all the indexes that needed to, and updated statistics on the rest, but to get that accomplished I had to run the same query for every table, is there a command or sp that would update the statistics for all tables at once?

Thanks again for your help guys.

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 5

UPDATE STATISTICS is the best command for working with statistics since it has the most flexibility. However, for batch work, sp_updatestats can do all the stats on a database.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning