SQLServer 2014 增量统计信息
来源:互联网 发布:java依赖注入 编辑:程序博客网 时间:2024/06/05 03:48
CREATE STATISTICS 和相关统计信息语句现在允许通过使用 INCREMENTAL 选项创建按分区的统计信息。相关语句允许或报告增量统计信息。受影响的语法包括 UPDATE STATISTICS、sp_createstats、CREATE INDEX、ALTER INDEX、ALTER DATABASE SET 选项、DATABASEPROPERTYEX、sys.databases 和 sys.stats。有关详细信息,请参阅CREATE STATISTICS (Transact-SQL)。
SQLServer 2014 允许创建分区级别的增量统计信息,即在分区中,允许对指定的分区进行统计信息更新,避免了全表统计信息的更新
过去版本对统计信息的更新:
UPDATE STATISTICS dbo.SalesOrderDetail (IX_DBOSalesOrderID) WITH FULLSCANGOUPDATE STATISTICS dbo.SalesOrderDetail (IX_DBOSalesOrderID) WITH SAMPLE 50 PERCENTGOUPDATE STATISTICS dbo.SalesOrderDetail (IX_DBOSalesOrderID) WITH RESAMPLEGO
以下统计信息类型,不支持增量统计信息:
> 使用未与基表的分区对齐的索引创建的统计信息。
> 对 AlwaysOn 可读辅助数据库创建的统计信息。
> 对只读数据库创建的统计信息。
> 对筛选的索引创建的统计信息。
> 对视图创建的统计信息。
> 对内部表创建的统计信息。
> 使用空间索引或 XML 索引创建的统计信息。
测试增量统计信息的使用方法:
USE AdventureWorks2014GOSELECT 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]<60000GO
--创建聚集索引(同时创建了索引统计信息)并分区CREATE CLUSTERED INDEX [IX_DBOSalesOrderID] ON [dbo].[SalesOrderDetail]([SalesOrderID]) ON [PS_INT]([SalesOrderID])GOALTER 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])GOALTER 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 HISTOGRAMGO
最后一行信息不超过60000,为一个分区内的边界,即上面看到第3分区中的最大数值。
现在再往其他分区插入数据:
--添加其他分区数据SET IDENTITY_INSERT dbo.SalesOrderDetail ONGOINSERT 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,ModifiedDateFROM [Sales].[SalesOrderDetail] WHERE [SalesOrderID]>=60000GOSET IDENTITY_INSERT dbo.SalesOrderDetail OFFGO
第4分区和第5分区已经有数据了,但是按上面查看统计信息直方图,仍然没有变化,统计信息并没有更新。
当更新 1~3分区统计信息时,直方图也没有变化(多了边界值)。
UPDATE STATISTICS dbo.SalesOrderDetail (IX_DBOSalesOrderID) WITH RESAMPLE ON PARTITIONS(1 TO 3)GODBCC SHOW_STATISTICS('dbo.SalesOrderDetail', IX_DBOSalesOrderID) WITH HISTOGRAMGO
当逐个更新分区的统计信息时,该统计信息的总体分布也更新了,这样的更新就不需要全表扫描更新统计信息了!
UPDATE STATISTICS dbo.SalesOrderDetail (IX_DBOSalesOrderID) WITH RESAMPLE ON PARTITIONS(4)GODBCC SHOW_STATISTICS('dbo.SalesOrderDetail', IX_DBOSalesOrderID) WITH HISTOGRAMGOUPDATE STATISTICS dbo.SalesOrderDetail (IX_DBOSalesOrderID) WITH RESAMPLE ON PARTITIONS(5)GODBCC SHOW_STATISTICS('dbo.SalesOrderDetail', IX_DBOSalesOrderID) WITH HISTOGRAMGO
上图只是最后一步统计信息的直方图,四五分区更新后,统计信息为完整的表统计了。所以增量分区统计信息,可以进行分区级别统计信息的更新。若表数据较多,全表扫描将比较慢,此栗子并未测试时间。不过使用确实麻烦点,要求创建的时候必须指定参数 STATISTICS_INCREMENTAL 。
参考:
SQL Server 统计信息理解(总结)
CREATE STATISTICS (Transact-SQL)
UPDATE STATISTICS (Transact-SQL)
SQL SERVER – What is Incremental Statistics?
Improving Partition Maintenance with Incremental Statistics
- SQLServer 2014 增量统计信息
- Trafodion 增量更新统计信息
- SQLServer统计信息理解
- oracle 实例的增量统计信息
- SQLServer语句运行统计信息
- sqlserver 更新表统计信息
- 利用SqlConnection获得SqlServer的统计信息
- SQLSERVER读懂语句运行的统计信息
- SQLSERVER读懂语句运行的统计信息
- SqlServer数据库统计信息的操作
- sqlserver 重置自增量
- 性能优化——统计信息——SQLServer自动更新和自动创建统计信息选项
- 第十二章——SQLServer统计信息(1)——创建和更新统计信息
- 第十二章——SQLServer统计信息(1)——创建和更新统计信息
- sqlserver里的统计信息与维护计划
- SQLServer 创建dtsx包更新统计信息(示例)
- SQLserver提供的统计信息流已损坏
- SQLServer 创建dtsx包更新统计信息(示例)
- 设计模式简介
- Caffe源码解读(五):Caffe最优求解器Solver
- 工厂模式
- html的input框
- 【HTML】HTML中对于文字的修饰
- SQLServer 2014 增量统计信息
- UE4配置Andrid时识别不出安卓设备的解决方法。
- c#----数据库的创建
- openfire安装完成之后总是登不上,忘记用户名密码解决办法
- SyntaxError- Non-ASCII character '-xe8' in file
- JavaScript Set集合的并集,交集,差集
- Hadoop家族学习路线图
- Unity面试编程题
- Android Shape自定义纯色圆角按钮