Why you shouldn’t run sp_updatestats after you rebuild your indexes

Don’t run sp_updatestats after rebuilding your indexes. The sp_updatestats will use the default sample and you could end up with less accurate stats post sp_updatestats than you had after your index rebuild. Only update statistics with fullscan post index rebuild, or better yet, only update your column statistics post index rebuild.

Current statistics =

Table Statistic ID Statistic Last Updated rows rows_sampled unfiltered_rows Modifications
tbl 1 tbl_Hash 2015-06-26 20:21:36.5930000 1318318 476423 1318318 0
tbl 2 tbl_DBID 2015-06-26 20:21:37.0930000 1318318 486032 1318318 0
tbl 3 tbl_AppName 2015-06-26 20:21:37.5770000 1318318 486032 1318318 0
tbl 4 tbl_Login 2015-06-26 20:21:38.0600000 1318318 486032 1318318 0
tbl 5 tbl_Time 2015-06-26 20:21:38.5330000 1318318 486032 1318318 0
tbl 6 _WA_Sys_00000011_341F99B2 2015-06-26 20:21:38.9370000 1318318 486032 1318318 4

After running sp_updatestats.  Note the rows_sampled changed.

Table Statistic ID Statistic Last Updated rows rows_sampled unfiltered_rows Modifications
tbl 1 tbl_Hash 2015-06-26 20:48:21.1600000 1718318 553940 1718318 0
tbl 2 tbl_DBID 2015-06-26 20:48:21.6830000 1718318 536499 1718318 0
tbl 3 tbl_AppName 2015-06-26 20:48:22.2030000 1718318 536499 1718318 0
tbl 4 tbl_Login 2015-06-26 20:48:22.7170000 1718318 536499 1718318 0
tbl 5 tbl_Time 2015-06-26 20:46:32.4200000 1718318 536499 1718318 0
tbl 6 _WA_Sys_00000011_341F99B2 2015-06-26 20:48:23.1500000 1718318 536499 1718318 0

Rebuild all indexes
EXEC sp_MSforeachtable @command1=”SET QUOTED_IDENTIFIER ON;ALTER INDEX ALL ON ? REBUILD;”

Note how the rows_sampled is all the rows

 Table Statistic ID Statistic Last Updated rows rows_sampled unfiltered_rows Modifications
tbl 1 tbl_Hash 2015-06-26 20:51:06.1900000 1718318 1718318 1718318 0
tbl 2 tbl_DBID 2015-06-26 20:51:08.5700000 1718318 1718318 1718318 0
tbl 3 tbl_AppName 2015-06-26 20:51:10.9400000 1718318 1718318 1718318 0
tbl 4 tbl_Login 2015-06-26 20:51:13.3430000 1718318 1718318 1718318 0
tbl 5 tbl_Time 2015-06-26 20:51:15.7100000 1718318 1718318 1718318 0
tbl 6 _WA_Sys_00000011_341F99B2 2015-06-26 20:48:23.1500000 1718318 536499 1718318 0

added 10,000 rows and ran sp_updatestats.  Note how we sampled less than all the rows

Table Statistic ID Statistic Last Updated rows rows_sampled unfiltered_rows Modifications
tbl 1 tbl_Hash 2015-06-26 20:53:39.3270000 1728318 479590 1728318 0
tbl 2 tbl_DBID 2015-06-26 20:53:39.8230000 1728318 520146 1728318 0
tbl 3 tbl_AppName 2015-06-26 20:53:40.3230000 1728318 520146 1728318 0
tbl 4 tbl_Login 2015-06-26 20:53:40.8200000 1728318 520146 1728318 0
tbl 5 tbl_Time 2015-06-26 20:53:41.3100000 1728318 520146 1728318 0
tbl 6 _WA_Sys_00000011_341F99B2 2015-06-26 20:53:41.7470000 1728318 520146 1728318 0

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s