sp_updatestats vs update statistics full scan mssql7

[kodaksmile]

Just a quick question - In sql 7 is there a difference between running sp_updatestats and a stored procedure that loops through all tables in the database and runs update statistics full scan?

Thanks

 

[ptheriault]

Right from msn.com

sp_updatestats effectively executes UPDATE STATISTICS, by specifying the ALL keyword, against all user-defined and internal tables in the database. sp_updatestats displays messages that indicate its progress. When the update is completed, it reports that statistics have been updated for all tables. Statistics on disabled nonclustered indexes are also updated by sp_updatestats. sp_updatestats ignores tables with a disabled clustered index

As you can see there is no reason to run a procedure that will loop through. Also if you specify with full scan it could take a long time to run depending on the size of your database.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[kodaksmile]

I inherited an sp written by someone else that does the loop through and specifies the full scan. I just want to make sure I don't degrade performance of the db by switching over to the supplied sp_updatestats. Does anyone know what sample rate is used by sp_updatestats in sql 7?

 

[ptheriault]

It depends on the size of the table. The default behavior is to perform a sample scan on the target table or indexed view. SQL Server automatically computes the required sample size.


- Paul
- If at first you don't succeed, find out if the loser gets anything.