use of update statistics?

Question

What is the use of update statistics?When we are using update statistics?

---

Answer 1

Statistics are generated automatically and used by the query optimizer when determining query plans.  SQL Server has database property options to enable statistics and modify the rate by which they are updated.

Statistics overview:
http://msdn.microsoft.com/en-us/library/ms190397.aspx

You can run UPDATE STATISTICS to update the auto-generated statistics, which is used to force a statistics update if the stats are stale. 

Update Statistics:
http://msdn.microsoft.com/en-us/library/ms187348.aspx

Technical explanation of Statistics:
http://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/canrsquot-sql-server-update-stats-fly-141183

Thanks,
Sam Lester (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 2

When we are using sp_updatestatistics

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 3

Hi g2sk1986,

You can use UPDATE STATISTICS or the stored procedure sp_updatestats when you want to improve query performance. For more details, please refer to this article.

Let’s assume that we run UPDATE query affecting 100 million rows. The UPDATE was estimated to take DAYS not HOURS to complete, then we can use UPDATE STATISTICS to make this update run faster. For more details, please refer to this blog.

Also using UPDATE STATISTICS for index would make SQL Server know whether the index is a very good and valid index to use. Please see this thread for your reference.

---

Best Regards, Ray Chen

Answer 4

Hi g2sk1986,

As i know

Whenever we write any query ,the query optimizer will prepare a execution plan and work according to that execution plan and while creating execution plan it has to consider the indexes and constraints, now here UPDATE STATISTICS comes into play(UPDATE STATISTICS contains the information about index structure) so execution plan will create according to update statistics data. so to get the better performance  updating the statistics are necessary.

 when we use update statistics??

Frankly

there is no "exact" answer to this :( , you should do it as per your perticular environment i do update statistics whenever i do indexing on my database :).