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来设置更改行数占表总行数百分比。