Does rebuild index update statistics?

I recently did a talk to a group of SQL users.  Quite a few facts ended up surprising the audience.   I thought I’d share a few and wanted to start with index rebuild.

If someone asks you the question “Does rebuild index update statistics?”,  you probably will say “of course”.  You may be surprised to know that index rebuild doesn’t update all statistics. 

when you use alter index rebuild, only statistics associated with that index will be updated.   In order to illustrate this better, let’s draw a table

	Index Stats	non-index stats
ALTER INDEX REORG	NO	NO
ALTER INDEX <index_name>  REBUILD	yes but only for stats associated with  that index	NO
ALTER INDEX ALL  REBUILD	yes, stats for all indexes will be updated	NO
DBREINDEX (old syntax)	YES	YES

Note that non-index stats means the statistics associated with a column/columns that are automatically created or manually created.

As you can see from above, don’t assume all of your statistics get updated just because you have a maintenance plan to rebuild index.   Sometimes, non-index statistics are very critical as well.  Manual updating statistics may be necessary because our current trigger threshold is high for large tables (20% in most cases as in KB “Statistical maintenance functionality (autostats) in SQL Server” ) though trace flag 2371 can help (in blog).

 

Demo

Here is a demo that alter index doesn’t update all stats.

first use the following script to setup

if object_id ('t') is not null
    drop table t
go
create table t(c1 int, c2 as c1 & 1)
go

create index t1_indx1 on t(c1 )
go
set nocount on
declare @i int
set @i = 0
while @i < 1000
begin
insert into t (c1) values (@i)
set @i = @i + 1
end
go

update statistics t with fullscan
go

go
--this will create a stats on c2
select count(*) from t where c2 =1

go

Because  I ran update statistics, the follow query will show that the t1_indx1 and _WA_Sys_00000002_162F4418 have the same value for laste_updated

SELECT
    obj.name, stat.name, stat.stats_id, last_updated
FROM sys.objects AS obj
JOIN sys.stats stat ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
where obj.name = 't'

 

Now, I ran alter index all rebuild.

-- alter all indexes
alter index all on t rebuild
--re-organize won't update even stats of the index
--alter index all on t reorganize

 

Then I ran the following query.  Note that  the last_updated for t1_indx1 has newer time stamp than _WA_Sys_00000002_162F4418 because _WA_Sys_00000002_162F4418 never got updated by alter index command.

--run theh following and note that the stats created by auto stats didn't get updated by rebuild
--only the stats from the index got updated

SELECT
    obj.name, stat.name, stat.stats_id, last_updated
FROM sys.objects AS obj
JOIN sys.stats stat ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
where obj.name = 't'

 

Past blog related to statistics: Case of using filtered statistics

 

Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support

Comments

• Anonymous
  March 07, 2015
  Good Demo Jack !!!

• Anonymous
  March 09, 2015
  "though trace flag 2370 can help" - that's a typo, I guess, should be 2371

• Anonymous
  March 10, 2015
  Thank you "SomewhereSomehow" for pointing out the typo on trace flag 2371.  I corrected it

• Anonymous
  April 03, 2015
  Thank you. Very much needed.

• Anonymous
  August 16, 2016
  In a previous blog post (https://blogs.msdn.microsoft.com/psssql/2013/03/19/sql-server-2012-partitioned-table-statistics-update-behavior-change-when-rebuilding-index/) you metioned that an index rebuild on a partitioned table just leads to statistics being sampled, but no more full scan as before. Does this hold for SQL Server 2014 and 2016, and for DBREINDEX?

• Anonymous
  September 24, 2017
  So what's the possible resolution? Should we run Update Stats on tables immediately after any bulk insert/update/ or indexes rebuilds? Will that be a good idea Or will it be a burden on batch /job time-window?

• Anonymous
  November 19, 2018
  Great article Jack, thanks for info