sql-server – 增量更新后统计信息消失

发布时间：2020-12-26 13:08:39 所属栏目：MsSql教程来源：网络整理

导读：副标题#e# 我们有一个使用增量统计信息的大型分区SQL Server数据库.所有索引都按分区对齐.当我们尝试通过分区在线重建分区时,所有统计信息在重建索引后都会消失. 下面是使用AdventureWorks2014数据库在SQL Server 2014中复制问题的脚本. --Example against

使用您的示例,以下是事物的外观：

set statistics time on;

update statistics dbo.TransactionHistory(IDX_ProductId)
with fullscan;

--SQL Server Execution Times:
--  CPU time = 94 ms,elapsed time = 131 ms.


update statistics dbo.TransactionHistory(IDX_ProductId)
with resample on partitions(2);

 --SQL Server Execution Times:
 --  CPU time = 0 ms,elapsed time = 5 ms.

drop index IDX_ProductId On dbo.TransactionHistory;

CREATE NONCLUSTERED INDEX IDX_ProductId ON dbo.TransactionHistory (ProductId) 
  WITH (DATA_COMPRESSION = ROW)  
  ON [PRIMARY]

update statistics dbo.TransactionHistory(IDX_ProductId)
with fullscan;

 --SQL Server Execution Times:
 --  CPU time = 76 ms,elapsed time = 66 ms.

有关增量统计信息的完整扫描统计信息更新时间为131毫秒.非分区对齐统计信息的全扫描统计信息更新时间为66毫秒.由于merging the individual statistics pages back into the main histogram产生的开销,非对齐统计信息的速度较慢.但是,使用分区对齐的统计对象,我们可以更新一个分区并在5毫秒内将其合并回主直??方图blob.因此,此时具有增量统计的管理员将面临决策.他们可以通过仅更新传统上需要更新的分区来减少他们的整体统计维护时间,或者他们可以尝试更高的采样率,以便他们可能在与之前维护时间帧相同的时间段内获得更多的采样行.前者允许在维护窗口中呼吸空间,后者可能会将非常大的表上的统计信息推送到查询根据更准确的统计数据获得更好计划的位置.这不是保证,您的里程可能会有所不同.

读者可以看到66 ms在这个表上不是一个痛苦的统计更新时间,所以我尝试在stackexchange数据集上设置测试.在我下载的最近转储中有6,418,608个帖子(不包括StackOverflow帖子和2012年的所有帖子 – 我的数据错误).

我已经通过[CreationDate]对数据进行了分区,因为…演示了.

以下是一些非常标准方案的一些时间(100％ – 索引重建,默认 – 统计自动更新或没有指定采样率的UPDATE STATISTICS：

>使用Fullscan创建非增量统计：CPU时间= 23500毫秒,已用时间= 22521毫秒.
>使用Fullscan创建增量统计：CPU时间= 20406 ms,已用时间= 15413 ms.
>使用默认采样率更新非增量统计：CPU时间= 406 ms,已用时间= 408 ms.
>使用默认采样率更新增量统计：CPU时间= 453 ms,已用时间= 507 ms.

假设我们比这些默认情况更复杂,并且已经确定10％的采样率是应该为我们提供所需计划的最低速率,同时将维护时间保持在合理的时间范围内.

>使用10％样本更新非增量统计：CPU时间= 2344 ms,已用时间= 2441 ms.
>使用10％样本更新增量统计：CPU时间= 2344毫秒,已用时间= 2388毫秒.

到目前为止,增量统计数据没有明显的好处.但是,如果我们利用未记录的sys.dm_db_stats_properties_internal()DMV(下面),您可以深入了解您可能想要更新的分区.假设我们对分区3中的数据进行了更改,我们希望确保统计信息对于传入查询是新鲜的.以下是我们的选择：

>默认情况下更新非增量(也是自动统计更新的默认行为)：408 ms.
>以10％更新非增量：2441 ms.
>更新增量统计,分区3使用重采样(10％ – 我们定义的采样率)：CPU时间= 63 ms,经过时间= 63 ms.

这是我们需要做出决定的地方.我们取得了63毫秒的胜利.基于分区的统计更新,还是我们的采样率更高？假设我们愿意在增量统计中将采样的初始命中率设为50％：

>以50％更新增量统计：已用时间= 16840毫秒.
>使用重新采样更新增量统计,分区3(50％ – 我们的新更新时间)：已用时间= 295毫秒.

我们能够抽取更多的数据,也许设置优化器来更好地猜测我们的数据(尽管它还没有使用分区级统计数据),而且我们能够更快地完成这项工作增量统计.

不过要弄清楚最后一件有趣的事情.同步统计更新怎么样？即使自动停止启动,50％的采样率是否仍然保留？

我从分区3中删除了数据并在CreationDate上运行了一个查询并检查了然后检查了下面相同查询的费率.保留了50％的采样率.

因此,长话短说：增量统计可以是一个有用的工具,具有适当的思考和初始设置工作.但是,您必须知道您尝试解决的问题,然后您需要适当地解决它.如果您的基数估计值不佳,您可能会获得更好的战略抽样率和一些投资干预计划.但是,由于使用的直方图是单个合并的统计信息页而不是分区级信息,因此您只获得了一小部分好处.如果您在维护窗口感到痛苦,那么增量统计可能对您有所帮助,但可能需要您设置高接触维护干预流程.无论如何,请记住requirements for incremental statistics：

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

希望这可以帮助

select 
    sysdatetime(),leading_column = index_col(quotename(sh.name)+'.'+quotename(t.name),parition_number = isnull(sp.partition_number,modification_counter = coalesce(sp.modification_counter,n1.modification_counter) 
from sys.stats s 
join sys.tables t 
    on s.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
left join sys.indexes i 
    on s.object_id = i.object_id
        and s.name = i.name
cross apply sys.dm_db_stats_properties_internal(s.object_id,s.stats_id) sp
outer apply sys.dm_db_stats_properties_internal(s.object_id,s.stats_id) n1
where n1.node_id = 1
    and (
            (is_incremental = 0)
               or
            (is_incremental = 1 and sp.partition_number is not null)
         )
    and t.name = 'Posts'
    and s.name like 'st_posts%'
order by s.stats_id,isnull(sp.partition_number,1)

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

3/3

首页