SQL Server 2016升级迁移过程中性能问题诊断案例

日常运行的批量更新作业,平日是5分钟之内结束,今天出现超过30分钟没结束的情况,实际运行3个小时以上,应用程序超时报错。

数据库版本:SQL Server 2016企业版

问题SQL:

declare @batch integer,
        @min    integer,
        @max   integer,
        @count integer
 
select  @min = 1,
    @count = count(*),
    @batch = 5000,
    @max = 5000
FROM dbo.MarketingRecipientEvents

Update MarketingRecipient
SET DateTrackedURLClicked = mre.EventDate
FROM MarketingRecipient mr
INNER JOIN dbo.MarketingRecipientEvents mre on mr.CustomerID = mre.ExternalRecipientID
WHERE mr.MarketingScheduleID = 364
AND mre.EventType in ('CLICKED')
AND mr.DateTrackedURLClicked IS NULL
AND mre.MarketingRecipientEventsID between @min and @max

问题表上MarketingRecipient上有两个相同覆盖列索引,正常情况下使用出问题的时候使用

ix_MarketingRecipient_MarketingScheduleID_CustomerID 进行Nested Loop 连接,出问题的时候使用

IX_MarketingRecipient_MarketingScheduleID_DateMessageBounced进行Nested Loop连接

CREATE NONCLUSTERED INDEX [ix_MarketingRecipient_MarketingScheduleID_CustomerID] ON [dbo].[MarketingRecipient]
(
[MarketingScheduleID] ASC,
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [DATA]
GO

CREATE NONCLUSTERED INDEX [IX_MarketingRecipient_MarketingScheduleID_DateMessageBounced] ON [dbo].[MarketingRecipient]
(
[MarketingScheduleID] ASC
)
INCLUDE (  [CustomerID],
[DateMessageBounced],
[DateMessageSent],
[DateEmailOpened],
[DateEmailBlocked],
[MarketingRecipientID],
[DateTrackedURLClicked])

问题执行计划:

查看执行计划对应的统计信息:

SELECT  sp.stats_id
,object_name(s.object_id) object_name
,object_schema_name(s.object_id) schema_name
,name
,filter_definition
,last_updated
,rows
,rows_sampled
,rows_sampled*100/rows as [percent]
,steps
,unfiltered_rows
,modification_counter
,sp.persisted_sample_percent
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE 1=1
and modification_counter < rows/5 + 500 and modification_counter > sqrt(rows*1000)
and object_schema_name(s.object_id) = 'dbo' and rows > 500
and object_name(s.object_id)= 'MarketingRecipient';

发现modification_counter列的值不低:

目前数据库刚迁移到2016版本,但是compatibility_level依旧是120并且没有启用trace 2371,所以,自动更新统计信息的逻辑是:

更新行>500+表行数*20% = 500+ 36891356*20%=7378771,  目前行数5718611不满足条件,所以统计信息不完善导致执行计划异常,优化器并没有捕捉到

如果使用新的自动更新统计信息逻辑:

更新行>√表行数*1000=√36891356*1000=192071, 5718611满足条件,验证一下:不改代码的情况下给SQL加plan guide:

OPTION (QUERYTRACEON 2371)

发现统计信息在编译期间自动更新,并且生成正确的执行计划。

本例是数据库从2014企业版到2016企业版升级过程中间状态造成的性能问题,生产环境如果要启用compability_level=130,

在2016环境下建议以下步骤

  1. 先启用querystore建立基线,收集足够的统计数据。
  2. 更改compability_level=130
  3. 对比基线数据,捕获变更的计划进行针对性的query plan force操作,固定到之前的基线。


在没有升级到compability_level=130的情况下,建议建立DBA更新统计信息的job,每晚根据新的算法更新过期的统计信息。

也可以利用https://github.com/olahallengren/sql-server-maintenance-solution

中的IndexOptimize.sql 进行统计信息维护,利用新的参数:@StatisticsModificationLevel来设置更改行数占表总行数百分比。

发表评论

电子邮件地址不会被公开。 必填项已用*标注