SQLServer 2014 增量统计信息

2023-02-20 00:00:00 创建更新分区统计信息直方图

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 。

本文来源https://www.modb.pro/db/52953

相关文章