AlwaysOn 輔助節點上的查詢很慢

我在 AlwaysOn 群集（啟用同步送出和讀取意圖）中有兩台相同的伺服器（相同的硬體、cpu、記憶體等），兩台伺服器都執行 SQL Server 2016 SP1 (13.0.4001.0)。對於具有相同查詢計劃和統計資訊的某些查詢，查詢在輔助副本上執行需要超過 10 秒，而在主副本上完成時間不到 2 秒。我已經驗證了統計資訊和查詢計劃是完全相同的，並測試了在這些伺服器上沒有其他負載的情況下執行查詢。無法找到輔助副本上查詢速度慢的原因。

從主副本查詢和計劃

https://www.brentozar.com/pastetheplan/?id=S1O5Yxscb

從輔助副本查詢和計劃

https://www.brentozar.com/pastetheplan/?id=SJdz5ljq-

但是，當我在輔助伺服器上的查詢中給出 maxdop = 8 提示時，它會在 2 秒內完成。

此外，當我在故障轉移時切換角色時，查詢會在 2 秒內在新的主伺服器上執行，而在輔助伺服器上又需要大約 10 秒。因此，這似乎不是硬體問題，因為查詢在被指定為 AG 集群上的主伺服器的伺服器上執行速度很快，而在輔助節點上執行速度很慢。

1. 請檢查輔助節點上的性能指標（CPU%、記憶體、緩衝區）。它是一個報告伺服器。AlwaysOn 可以選擇將所有讀取查詢發送到輔助節點。

2. AlwaysOn 利用快照隔離。您能否驗證 tempdb 是否被大量使用？請驗證 tempdb 和其他數據庫的寫入/讀取延遲。這是一種檢查方法：從數據庫日誌中查詢

DECLARE @Sample TABLE (
 DBName varchar(128) 
,NumberOfReads bigint
,NumberOfWrites bigint)

INSERT INTO @Sample 
SELECT name AS 'DBName'
     ,SUM(num_of_reads) AS 'NumberOfRead'
     ,SUM(num_of_writes) AS 'NumberOfWrites' 
FROM sys.dm_io_virtual_file_stats(NULL, NULL) I
 INNER JOIN sys.databases D  
     ON I.database_id = d.database_id
GROUP BY name 

WAITFOR DELAY '00:05:00.000';

SELECT FirstSample.DBName
     ,(SecondSample.NumberOfReads - FirstSample.NumberOfReads) AS 'Number of Reads'
     ,(SecondSample.NumberOfWrites - FirstSample.NumberOfWrites) AS 'Number of Writes'
FROM 
(SELECT * FROM @Sample) FirstSample
INNER JOIN
(SELECT name AS 'DBName'
     ,SUM(num_of_reads) AS 'NumberOfReads'
     ,SUM(num_of_writes) AS 'NumberOfWrites' 
FROM sys.dm_io_virtual_file_stats(NULL, NULL) I
 INNER JOIN sys.databases D  
     ON I.database_id = d.database_id
GROUP BY name) AS SecondSample
ON FirstSample.DBName = SecondSample.DBName
ORDER BY 'Number of Reads' DESC;

3. 您是否嘗試在輔助節點上進行簡單的備份/恢復，刪除 AlwaysOn，並驗證查詢是否仍然很慢？

4）我知道您在查詢中沒有 NOLOCK 提示。您可以嘗試在主副本上重建索引，看看會發生什麼？在 SQL Server Always On 輔助副本上執行查詢的時間要長得多。

“強制執行快照隔離時，會忽略 NOLOCK 提示。主副本和輔助副本之間的執行持續時間存在差異，因為在強制執行快照隔離的只讀輔助副本上忽略 NOLOCK 提示，但在執行快照的主副本上忽略 NOLOCK 提示預設情況下不強制執行隔離。這會導致對聚集索引的掃描在輔助副本上強制執行鍵順序。在主副本上，NOLOCK 提示優先並且確實會影響行為。當聚集索引高度碎片化時，強制執行只讀輔助副本上的掃描鍵順序導致 SQL Server 發出單頁讀取。但在主副本上，SQL Server 執行分配單元掃描以讀取每個 IO 請求的多個頁面。

5. 你能驗證設置是什麼嗎？按照上面 Kin 的建議查詢Maxdop和記憶體分配，這是另一個好主意

-- Find MaxDop
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'max degree of parallelism'
GO 

-- Change MaxDop settings replace to 8
EXEC dbo.sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE;
GO  
-- Find Memory allocation on instance
SELECT [name] AS [Name]
,[configuration_id] AS [Number]
,[minimum] AS [Minimum]
,[maximum] AS [Maximum]
,[is_dynamic] AS [Dynamic]
,[is_advanced] AS [Advanced]
,[value] AS [ConfigValue]
,[value_in_use] AS [RunValue]
,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN ('Min server memory (MB)', 'Max server memory (MB)')

你檢查過磁碟嗎？如果塊大小不同，所需的 IO 數量也會不同。即，如果一側有 64kb（一個範圍）塊大小而另一側有 4kb（預設）塊大小，則讀取相同數量的頁面將需要 16 倍的 IO。

此外，如果磁碟或索引的碎片不同，則會導致相同索引、統計資訊和計劃的性能差異。檢查伺服器和數據庫是否具有相同的維護配置。

引用自：https://dba.stackexchange.com/questions/186135