Updatign status after index creation in SQLS ERVER 2008

Question

Hello,
When a new index is added to an existing table with rows in it, is it always required to execute the following to update the stats (or) will Create Index will inturn also updatethe stats?

USE DBName;
GO
EXEC sp_updatestats

Would this gather stats for all the tables in database DBName?
Thanks!

Answer 1

Hello

The SQL Engine will take care of the statistics if the "Auto Update Statistics" setting is set to True for the database in where you create the index

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

Yes, the Auto Update Stats in my database is TRUE.

Thanks for the suggestion!

Answer 3

At the time of creating a new index, one set of statistics is created automatically with FULLSCAN for the index key columns (but not the INCLUDE columns).

The Books Online article about SP_UPDATESTATS says - "Runs UPDATE STATISTICS against all user-defined and internal tables in the current database". This means we can't control which indexes or statistics would the sp_updatestats update. It would decide as per its internal algorithm. We can only give the desired sampling rate as a parameter for this stored procedure.

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | (http://aalamrangi.wordpress.com)

Answer 4

Could you please refer the link given below, may it help you.

http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables/

Answer 5

The downside of auto update statistics is that it can kick off at the wrong time, impeding performance.

Alternative is statistics update as a scheduled job nightly:

http://www.sqlusa.com/articles/query-optimization/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012