SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Updating statistics after rebuilding index
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anoop_mig25
Starting Member

India
12 Posts

Posted - 03/19/2013 :  15:52:48  Show Profile  Reply with Quote
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

USA
15635 Posts

Posted - 03/19/2013 :  16:55:35  Show Profile  Visit robvolk's Homepage  Reply with Quote
Rebuilding an index automatically updates statistics, so there's no need to run it after.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
768 Posts

Posted - 03/20/2013 :  14:02:56  Show Profile  Reply with Quote
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

USA
5071 Posts

Posted - 03/20/2013 :  15:37:30  Show Profile  Visit russell's Homepage  Reply with Quote
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.


Edited by - russell on 03/20/2013 15:37:58
Go to Top of Page

anoop_mig25
Starting Member

India
12 Posts

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

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1906 Posts

Posted - 03/21/2013 :  02:52:57  Show Profile  Visit jackv's Homepage  Reply with Quote
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

USA
5071 Posts

Posted - 03/21/2013 :  10:20:15  Show Profile  Visit russell's Homepage  Reply with Quote
Yes it does.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000