deepakee
asked on
UPDATE Statistics
I've just truncated a very big Transaction log file of a database and I now want to manually run UPDATE STATISTICS on all the tables of the database to improve performance
1) Is it OK to manually run UPDATE STATISTICS if the database itself has AUTO UPDATE/AUTO CREATE STATISTICS on/selected in the Properties/Options of the database? Or does AUTO UPDATE need to be disabled when I manually run UPDATE STATISTICS?
2) Can I run it with users using the database? Is it better to wait till no user is logged in?
3) Do I need to take a backup of the database before updating the statistics? or is it safe for data and not backup is required?
Thank you
Deepak
1) Is it OK to manually run UPDATE STATISTICS if the database itself has AUTO UPDATE/AUTO CREATE STATISTICS on/selected in the Properties/Options of the database? Or does AUTO UPDATE need to be disabled when I manually run UPDATE STATISTICS?
2) Can I run it with users using the database? Is it better to wait till no user is logged in?
3) Do I need to take a backup of the database before updating the statistics? or is it safe for data and not backup is required?
Thank you
Deepak
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your replies.
One more question. If I want to update statistics for all the tables of a database, is there a quicker way than manually run UPDATE STATISITCS table_name for each table? Or UPDATE STATISITCS table_name for each table is the only way?
One more question. If I want to update statistics for all the tables of a database, is there a quicker way than manually run UPDATE STATISITCS table_name for each table? Or UPDATE STATISITCS table_name for each table is the only way?
ASKER
Is EXEC SP_UPDATESTATS safe for data and no backup is also required before running it?
it is safe for data, as it only reads the data from the tables (part of it), it only updates the statistics records for the tables.
You should not need a backup before running it, while I usually run the backup AFTER the statistics run
You should not need a backup before running it, while I usually run the backup AFTER the statistics run
Yes, you can start a manual update.
We have a job set to run at midnight to update statistics every night. We can have many users so I avoid running it during the day. It may slow down the system.
It is safe for data. No backup required.