Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Updating statistics after rebuilding index

Author  Topic 

anoop_mig25
Starting Member

20 Posts

Posted - 2013-03-19 : 15:52:48
Hi friends what is the best way of updating an statistics after rebuilding index . Is it using UPDATE STATISTICS command or use sp_updatestats. please suggest

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-03-19 : 16:55:35
Rebuilding an index automatically updates statistics, so there's no need to run it after.
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2013-03-20 : 14:02:56
quote:
Originally posted by robvolk

Rebuilding an index automatically updates statistics, so there's no need to run it after.



Yes and No...;)

Rebuilding an index will update the index statistics on that table, but will not update any column statistics. To get the column statistics updated, there are several options:

1) Use sp_updatestats with @resample parameter. This will update any statistics that need to be updated using the last sampling rate defined. If an index statistic is selected it will be rebuilt at 100% - all others would be rebuilt based on the previous sampling rate used when that statistic was rebuilt.

2) Maintenance Plan Task - Update Statistics. Using this task, make sure you select to update column statistics only and all column statistics for all selected tables will be updated.

3) Issue your own UPDATE STATISTICS command with the COLUMN parameter supplied.

4) Other methods...

Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-20 : 15:37:30
Index statistics are updated, column statistics no. In the OP's case, Rob's answer is correct.
BOL
quote:
When you execute ALTER INDEX ALL … on a table, only the statistics associates with indexes are updated. Automatic or manual statistics created on the table (instead of an index) are not updated.


When updating statistics manually, I use UPDATE STATISTICS. sp_updatestats simply executes UPDATE STATISTICS with the ALL keyword passed in. Also, sp_updatestats executes against all user tables while UPDATE STATISTICS is for a specific table.

Go to Top of Page

anoop_mig25
Starting Member

20 Posts

Posted - 2013-03-20 : 23:25:12
Thanks robvolk,jeffw8713, and russell for your replies .
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-03-21 : 02:52:57
Russell - for sp_updatets does also have the RESAMPLE option , which updates the stats based on the latest sample rate

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-21 : 10:20:15
Yes it does.
Go to Top of Page
   

- Advertisement -