• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 522
  • Last Modified:

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

 
0
deepakee
Asked:
deepakee
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
1) yes, you can do this at any time
2) you can run this while users on the database, however if the database is really big, you might prefer to plan it for the evening hours
3) no need to backup first
0
 
ptjcbCommented:
I agree with AngelIII

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.
0
 
deepakeeAuthor Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
deepakeeAuthor Commented:
Is  EXEC SP_UPDATESTATS safe for data and no backup is also required before running it?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now