Update Statistics

Question

How to update statistics on entire DATABASE ?

Answer 1

Hello Vijay,

Instead of updating stats for entire database I would suggest you to choose tables which are heavily updated and inserted or deleted .Updating stats for whole DB might cause job to run long and some blocking issues

You can run below command for particular database.

execute sp_updatestats

http://msdn.microsoft.com/en-us/library/ms173804.aspx

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 2

EXEC sp_updatestats

Please refer the below link:http://technet.microsoft.com/en-us/library/ms187348.aspx

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 3

IN addition you can see this link to update statistics on all databases:

Execute UPDATE STATISTICS for all SQL Server Databases

---

The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


My blog

Answer 4

Here is an other script from MS TechNet Script Center: Update out-dated statistics

Or you can use a maintenance plan with a Update Statistics Task (Maintenance Plan)

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 5

Hi Vijay,

You can refer this Link : http://sqlserverplanet.com/dba/update-statistics

Hope it will help

---

Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you.

Answer 6

The script we use on many SQL Server boxes for index and statistics maintenance is written by Ola Hallengren and can be found at http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html.

---

Regards, Dean Savović

Answer 7

Note that my testing show that sp_updatestats do not update statistics for the whole database. Rather, it uses the same "if more than 20% of data has been modified" as the database auto update statistics setting does. I.e., my experience is that this procedure is not useful.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 8

Note that my testing show that sp_updatestats do not update statistics for the whole database. Rather, it uses the same "if more than 20% of data has been modified" as the database auto update statistics setting does. I.e., my experience is that this procedure is not useful.

---

Tibor Karaszi, SQL Server MVP | web | blog

Hello Tibor,

Agre with you but what exactly you mean by ''if more than 20% of data has been modified" sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.

Thanks

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 9

"thus avoiding unnecessary updates of statistics on unchanged rows"

But there's a huge difference between "unchanged" and 20% or more modified. Say you have 10,000,000 rows. You have had to do 2,000,000 modifications for the column(s) in order for sp_updatestats to update the statistics. That is a lot, IMO. I generally use Ola's IndexOptimize and specify it to update if > 0 rows has been modified. :-)

---

Tibor Karaszi, SQL Server MVP | web | blog