我过去一周一直在做MS10775A课程,但培训师无法可靠回答的一个问题是:


重新编制索引会更新统计信息吗?


我们在网上找到了讨论的依据,认为它既可以实现也可以不实现。

评论

值得注意的是,REINDEX确实会更新列统计信息,这是重建索引的副作用-您无需更新统计信息。表中的数据不变。这是相同的数据,只是a)移动了它在旋转盘上的位置(重组页面时),或b)坐在了不同的页面中(对于重新构建)。因此:重新索引确实会更新(某些)统计信息:无需这样做。

#1 楼

在关心更新统计信息时可以记住以下几点(从“重建索引对比更新统计信息”(Benjamin Nevarez复制)中。
默认情况下,UPDATE STATISTICS语句同时更新索引和列统计信息。使用UPDATE STATISTICS WITH FULLSCAN选项将仅更新列统计信息。使用UPDATE STATISTICS选项将仅更新索引统计信息。
例如,通过使用COLUMNS重建索引也将使用与INDEX等效的方式更新索引统计信息,除非对该表进行了分区,在这种情况下,仅对统计信息进行采样(适用于SQL Server 2012及更高版本)。使用ALTER INDEX … REBUILD手动创建的统计信息不会被任何WITH FULLSCAN操作(包括CREATE STATISTICS)更新。如果在正在重建的表上定义了聚集索引,则ALTER INDEX ... REBUILD会更新聚集索引的统计信息。例如,使用ALTER TABLE ... REBUILD重新组织索引不会更新任何统计信息。您可以使用ALTER TABLE ... REBUILD语法强制更新表上的所有统计信息,包括索引统计信息和手动创建的统计信息。

以下代码说明了上述封装的规则:

首先,我们将创建一个包含几列和一个聚簇索引的表:

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;


结果显示尚未进行任何更新,这是从我们刚创建表以来是正确的:

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';


让我们重建整个表,看看是否更新了统计信息:统计信息已更新。

接下来,我们执行一个离散的ALTER INDEX … REORGANIZE操作:

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


您可以看到,我们刚刚更新了UPDATE STATISTICS列上的统计信息:

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                    ║
╚═══════════════╩═══════════╩═════════════════════════╝


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';


可以看到,确定所有统计信息都被更新的唯一方法是手动更新每个统计信息,或使用UPDATE STATISTICS (tablename) WITH FULLSCAN;更新整个表格。

评论


@JeremyWeir-从您刚刚在上面的问题中添加的示例代码中可以看到,更新的唯一统计信息是通过ALTER INDEX ... REBUILD或UPDATE STATISTICS语句显式更新的那些统计信息。如果重新构建表本身,则仅更新聚集索引统计信息。仅供参考,同一索引对象不一定支持主键和聚簇索引。

– Max Vernon♦
18-09-17在19:16

#2 楼

SQL Server统计信息的“ Microsoft文档”页面指出:重建,碎片整理或重组索引之类的操作不会更改数据的分布。因此,执行ALTER INDEX REBUILD,DBCC DBREINDEX,DBCC INDEXDEFRAG或ALTER INDEX REORGANIZE操作后,无需更新统计信息。当您使用ALTER INDEX REBUILD或DBCC DBREINDEX在表或视图上重建索引时,Query Optimizer会更新统计信息,但是此统计信息更新是重新创建索引的副产品。在执行DBCC INDEXDEFRAG或ALTER INDEX REORGANIZE操作之后,查询优化器不会更新统计信息。