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.
| 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. |
 |
|
|
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... |
|
|
|
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.BOLquote:
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. |
|
|
|
anoop_mig25
Starting Member
20 Posts |
Posted - 2013-03-20 : 23:25:12
|
| Thanks robvolk,jeffw8713, and russell for your replies . |
|
|
|
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 rateJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-03-21 : 10:20:15
|
| Yes it does. |
|
|
|
|
|
|
|
|
Updating statistics after rebuilding index