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，每晚根據新的演算法更新過期的統計資訊。

也可以利用

中的  進行統計資訊維護，利用新的引數：@StatisticsModificationLevel來設定更改行數佔表總行數百分比。