SQLServer 2014 增量统计信息

SQLServer 2021-04-18

459

CREATE STATISTICS 和相关统计信息语句现在允许通过使用 INCREMENTAL 选项创建按分区的统计信息。相关语句允许或报告增量统计信息。受影响的语法包括 UPDATE STATISTICS、sp_createstats、CREATE INDEX、ALTER INDEX、ALTER DATABASE SET 选项、DATABASEPROPERTYEX、sys.databases 和 sys.stats。

SQLServer 2014 允许创建分区级别的增量统计信息，即在分区中，允许对指定的分区进行统计信息更新，避免了全表统计信息的更新

过去版本对统计信息的更新：

UPDATE STATISTICS dbo.SalesOrderDetail (IX_DBOSalesOrderID) WITH FULLSCAN
GO
UPDATE STATISTICS dbo.SalesOrderDetail (IX_DBOSalesOrderID) WITH SAMPLE 50 PERCENT
GO
UPDATE STATISTICS dbo.SalesOrderDetail (IX_DBOSalesOrderID) WITH RESAMPLE
GO

以下统计信息类型，不支持增量统计信息：

使用未与基表的分区对齐的索引创建的统计信息。
对 AlwaysOn 可读辅助数据库创建的统计信息。
对只读数据库创建的统计信息。
对筛选的索引创建的统计信息。
对视图创建的统计信息。
对内部表创建的统计信息。
使用空间索引或 XML 索引创建的统计信息。

测试增量统计信息的使用方法：

USE AdventureWorks2014
GO
SELECT MIN(SalesOrderID),MAX(SalesOrderID),COUNT(*) FROM [Sales].[SalesOrderDetail]
GO
--创建分区函数
CREATE PARTITION FUNCTION PF_INT (INT) AS RANGE LEFT FOR VALUES
(40000,50000, 60000, 70000)
GO
--创建分区方案
CREATE PARTITION SCHEME PS_INT AS PARTITION PF_INT TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
GO
--临时测试表
SELECT * INTO dbo.SalesOrderDetail FROM [Sales].[SalesOrderDetail] WHERE [SalesOrderID]<60000
GO


--创建聚集索引（同时创建了索引统计信息）并分区
CREATE CLUSTERED INDEX [IX_DBOSalesOrderID]  ON [dbo].[SalesOrderDetail]([SalesOrderID]) ON [PS_INT]([SalesOrderID])
GO
ALTER INDEX [IX_DBOSalesOrderID] ON [dbo].[SalesOrderDetail] REBUILD WITH (STATISTICS_INCREMENTAL = ON);
GO
--注意：以下两种方法不行！
CREATE CLUSTERED INDEX [IX_DBOSalesOrderID]  ON [dbo].[SalesOrderDetail]([SalesOrderID]) 
WITH (STATISTICS_INCREMENTAL = ON)
ON [PS_INT]([SalesOrderID])
GO
ALTER TABLE [dbo].[SalesOrderDetail]
ADD CONSTRAINT [IX_DBOSalesOrderID] PRIMARY KEY CLUSTERED ([SalesOrderID])
WITH (STATISTICS_INCREMENTAL = ON)
ON [PS_INT] ([SalesOrderID])
GO


--查看分区数据情况
SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('dbo.SalesOrderDetail')
GO

图一

当前只有分区2和分区3存储数据。再查看统计信息直方图。

--查看统计信息
DBCC SHOW_STATISTICS('dbo.SalesOrderDetail', IX_DBOSalesOrderID) WITH HISTOGRAM

图二

最后一行信息不超过60000，为一个分区内的边界，即上面看到第3分区中的最大数值。现在再往其他分区插入数据。

--添加其他分区数据
SET IDENTITY_INSERT dbo.SalesOrderDetail ON
GO
INSERT INTO dbo.SalesOrderDetail(SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber
,OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,rowguid,ModifiedDate)
SELECT SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,OrderQty,ProductID
,SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,rowguid,ModifiedDate
FROM [Sales].[SalesOrderDetail] WHERE [SalesOrderID]>=60000
GO
SET IDENTITY_INSERT dbo.SalesOrderDetail OFF
GO

图三

第4分区和第5分区已经有数据了，但是按上面查看统计信息直方图，仍然没有变化，统计信息并没有更新。当更新 1~3分区统计信息时，直方图也没有变化（多了边界值）。

UPDATE STATISTICS dbo.SalesOrderDetail (IX_DBOSalesOrderID) WITH RESAMPLE ON PARTITIONS(1 TO 3)
GO
DBCC SHOW_STATISTICS('dbo.SalesOrderDetail', IX_DBOSalesOrderID) WITH HISTOGRAM
GO

图四

当逐个更新分区的统计信息时，该统计信息的总体分布也更新了，这样的更新就不需要全表扫描更新统计信息了！

UPDATE STATISTICS dbo.SalesOrderDetail (IX_DBOSalesOrderID) WITH RESAMPLE ON PARTITIONS(4)
GO
DBCC SHOW_STATISTICS('dbo.SalesOrderDetail', IX_DBOSalesOrderID) WITH HISTOGRAM
GO
UPDATE STATISTICS dbo.SalesOrderDetail (IX_DBOSalesOrderID) WITH RESAMPLE ON PARTITIONS(5)
GO
DBCC SHOW_STATISTICS('dbo.SalesOrderDetail', IX_DBOSalesOrderID) WITH HISTOGRAM
GO

图五

上图只是最后一步统计信息的直方图，四五分区更新后，统计信息为完整的表统计了。所以增量分区统计信息，可以进行分区级别统计信息的更新。若表数据较多，全表扫描将比较慢，此栗子并未测试时间。不过使用确实麻烦点，要求创建的时候必须指定参数 STATISTICS_INCREMENTAL 。

sql server

文章转载自SQLServer，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQLServer 2014 增量统计信息

评论