SQL Server 2008 Update Statistics and Index Rebuild

SQL Server 2008 Update Statistics and Index Rebuild:

Whenever a Index is rebuild the statistics are updated but not for the non indexed Statistics on the table. This means if a Index rebuild occurs the Statistics are updated for the indexes which is a known fact. But if we have many other statistics which are not on Indexes then they are not updated by this Index Rebuild.

Let us do a quick Demo below:

Let us create a Demo table with a Index and a non index statistics created on it.


SET NOCOUNT ON

DECLARE @RowID INT
SET @RowID = 0
if exists(select name from sys.tables where name = 'DemoTable')
begin
drop table DemoTable;
end
CREATE TABLE DemoTable(
ID INT,
NonIndexedData CHAR(2000)
CONSTRAINT PK_DemoTable PRIMARY KEY CLUSTERED (ID))

WHILE @RowID <> 15000
BEGIN
SET @RowID = @RowID + 1
INSERT INTO DemoTable(ID, NonIndexedData)
SELECT @RowID, 'This is Row ' + CONVERT(VARCHAR(5), @RowID)
END

--Now let's see what the statistics look like for the Primary Key

DBCC SHOW_STATISTICS ('DemoTable', PK_DemoTable) WITH STAT_HEADER

--Above query just gives NULLS.
--Now lets rebuild the index then we will update the statistics

--Let's rebuild the index to start with
WAITFOR DELAY '00:01';

ALTER INDEX PK_DemoTable ON DemoTable REBUILD

--Now lets check our statistics again

DBCC SHOW_STATISTICS ('DemoTable', PK_DemoTable) WITH STAT_HEADER

--As we can see from above results rebuilding the index also updated the statistics.

--But what about the statistics that are NOT part of an index?

--Let's create a non-indexed statistic
WAITFOR DELAY '0:01';

CREATE STATISTICS stats_NonIndexedData ON DemoTable(NonIndexedData)

--Now let's look at what that statistic looks like

DBCC SHOW_STATISTICS ('DemoTable', stats_NonIndexedData) WITH STAT_HEADER

--OK looks good… now let's rebuild that index again
WAITFOR DELAY '00:01';

ALTER INDEX PK_DemoTable ON DemoTable REBUILD

--and let's look at the statistic again

DBCC SHOW_STATISTICS ('DemoTable', stats_NonIndexedData) WITH STAT_HEADER
DBCC SHOW_STATISTICS ('DemoTable', PK_DemoTable) WITH STAT_HEADER

--From above it is proved the Statistics are not updated for this Non Index Data related Statistics.

WAITFOR DELAY '00:01';

--Let us now use sp_updateStats to update statistics and see

EXEC sp_updatestats

--lets check if statistics are updated for non indexed data and indexed data

DBCC SHOW_STATISTICS ('DemoTable', stats_NonIndexedData) WITH STAT_HEADER
DBCC SHOW_STATISTICS ('DemoTable', PK_DemoTable) WITH STAT_HEADER

--Now Let us try update Statistics command which will force a update statistics

UPDATE STATISTICS [dbo].[DemoTable]
WITH FULLSCAN

--Check the statistics after update Statistics
DBCC SHOW_STATISTICS ('DemoTable', stats_NonIndexedData) WITH STAT_HEADER
DBCC SHOW_STATISTICS ('DemoTable', PK_DemoTable) WITH STAT_HEADER
go
drop table DemoTable;
go

From the above demo it known that if the database has very less indexes then index rebuild will take much less time than a update statistics as update statistics is updating the statistics for all the statistics including the index statistics.

Also, we can say if database contains most of the indexes and statistics based on them then doing both update statistics and index rebuild will duplicate the work.

Leave a Reply

Your email address will not be published. Required fields are marked *