42

I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is:

Does a re-index update the statistics?

We found discussions online arguing both that it does and that it doesn't.

50

You can keep the following in mind when caring about updating statistics (copied from Rebuilding Indexes vs. Updating Statistics (Benjamin Nevarez)

  1. By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.

  2. By default, the UPDATE STATISTICS statement updates both index and column statistics. Using the COLUMNS option will update column statistics only. Using the INDEX option will update index statistics only.

  3. Rebuilding an index, for example by using ALTER INDEX … REBUILD will also update index statistics with the equivalent of using WITH FULLSCAN unless the table is partitioned, in which case the statistics are only sampled (applies to SQL Server 2012 and later).

  4. Statistics that were manually created using CREATE STATISTICS are not updated by any ALTER INDEX ... REBUILD operation, including ALTER TABLE ... REBUILD. ALTER TABLE ... REBUILD does update statistics for the clustered index, if one is defined on the table being rebuilt.

  5. Reorganizing an index, for example using ALTER INDEX … REORGANIZE does not update any statistics.

The short answer is that you need to use UPDATE STATISTICS to update column statistics and that an index rebuild will update only index statistics. You can force an update to all statistics on a table, including index-stats and manually created stats, with the UPDATE STATISTICS (tablename) WITH FULLSCAN; syntax.

The following code illustrates the rules encapsulated above:

First, we'll create a table with a couple of columns, and a clustered index:

USE tempdb;

IF OBJECT_ID(N'dbo.SomeTable', N'U') IS NOT NULL
DROP TABLE dbo.SomeTable;

CREATE TABLE dbo.SomeTable
(
    rn int NOT NULL IDENTITY(1,1)
        CONSTRAINT pk
        PRIMARY KEY NONCLUSTERED
    , i int NOT NULL INDEX i 
    , d sysname NOT NULL
) ON [PRIMARY] WITH (DATA_COMPRESSION = NONE);

CREATE UNIQUE CLUSTERED INDEX cx ON dbo.SomeTable (i, d);

CREATE STATISTICS d ON dbo.SomeTable (d) WITH FULLSCAN;

INSERT INTO dbo.SomeTable (d, i)
SELECT c1.name, c1.id
FROM sys.syscolumns c1;

This query shows the date when each stats object was last updated:

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

The results show no updates have yet taken place, which is correct since we just created the table:

╔═══════════════╦═══════════╦═══════════╗
║  ObjectName   ║ StatsName ║ StatsDate ║
╠═══════════════╬═══════════╬═══════════╣
║ dbo.SomeTable ║ cx        ║ NULL      ║
║ dbo.SomeTable ║ i         ║ NULL      ║
║ dbo.SomeTable ║ pk        ║ NULL      ║
║ dbo.SomeTable ║ d         ║ NULL      ║
╚═══════════════╩═══════════╩═══════════╝

Let's rebuild the entire table, and see if that updates stats:

ALTER TABLE dbo.SomeTable REBUILD;

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';
╔═══════════════╦═══════════╦═════════════════════════╗
║  ObjectName   ║ StatsName ║        StatsDate        ║
╠═══════════════╬═══════════╬═════════════════════════╣
║ dbo.SomeTable ║ cx        ║ 2018-09-17 14:09:13.590 ║
║ dbo.SomeTable ║ i         ║ NULL                    ║
║ dbo.SomeTable ║ pk        ║ NULL                    ║
║ dbo.SomeTable ║ d         ║ NULL                    ║
╚═══════════════╩═══════════╩═════════════════════════╝

The results show only the clustered index stats were updated.

Next, we perform a discrete UPDATE STATS operation:

UPDATE STATISTICS dbo.SomeTable(d) WITH FULLSCAN;

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

As you can see, we've just updated the stats on the d column:

╔═══════════════╦═══════════╦═════════════════════════╗
║  ObjectName   ║ StatsName ║        StatsDate        ║
╠═══════════════╬═══════════╬═════════════════════════╣
║ dbo.SomeTable ║ cx        ║ 2018-09-17 14:09:13.590 ║
║ dbo.SomeTable ║ i         ║ NULL                    ║
║ dbo.SomeTable ║ pk        ║ NULL                    ║
║ dbo.SomeTable ║ d         ║ 2018-09-17 14:09:13.597 ║
╚═══════════════╩═══════════╩═════════════════════════╝

Now, we'll update stats on the entire table:

UPDATE STATISTICS dbo.SomeTable WITH FULLSCAN;

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';
╔═══════════════╦═══════════╦═════════════════════════╗
║  ObjectName   ║ StatsName ║        StatsDate        ║
╠═══════════════╬═══════════╬═════════════════════════╣
║ dbo.SomeTable ║ cx        ║ 2018-09-17 14:09:13.600 ║
║ dbo.SomeTable ║ i         ║ 2018-09-17 14:09:13.600 ║
║ dbo.SomeTable ║ pk        ║ 2018-09-17 14:09:13.603 ║
║ dbo.SomeTable ║ d         ║ 2018-09-17 14:09:13.607 ║
╚═══════════════╩═══════════╩═════════════════════════╝

As you can see, the only way to be certain all the stats are updated is to either update each one manually, or to update the entire table with UPDATE STATISTICS (table);.

  • @JeremyWeir - as you can see from the example code I just added to the question above, the only statistics updated are those explicitly updated either through either an ALTER INDEX ... REBUILD or an UPDATE STATISTICS statement. If the table itself is rebuilt, only the clustered index statistics are updated. FYI, a primary key and a clustered index are not necessarily supported by the same index object. – Max Vernon Sep 17 '18 at 19:16
3

The Microsoft Docs page for SQL Server statistics states:

Operations such as rebuilding, defragmenting, or reorganizing an index do not change the distribution of data. Therefore, you do not need to update statistics after performing ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG, or ALTER INDEX REORGANIZE operations. The Query Optimizer updates statistics when you rebuild an index on a table or view with ALTER INDEX REBUILD or DBCC DBREINDEX, however this statistics update is a byproduct of re-creating the index. The Query Optimizer does not update statistics after DBCC INDEXDEFRAG or ALTER INDEX REORGANIZE operations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.