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';