SQL Server CPU 問題

我們繼承了一些 SQL Server 2014 數據庫，它們在 Azure IaaS 上執行。我們收到有關性能的投訴並試圖控制它。我們開始查看 CPU 性能，並執行以下測試（所有測試都在數據庫機器本身上執行，即不通過網路）：

SET NOCOUNT ON
DECLARE @Loops INT SET @Loops = 1 
DECLARE @RESULT INT

WHILE @Loops <= 2000000 
BEGIN
   IF COALESCE('123', '456') = '456'  
           SET @RESULT = 1 
   SET @Loops = @Loops + 1   
END

GO

在生產機器上，16 個虛擬處理器（20% 到 30% 繁忙），我們得到以下結果：

Completes in 3 minutes

在測試機器上，4 個虛擬處理器（也大約 20% 到 30% 忙）我們得到以下資訊：

Completes in 1 second

我嘗試在生產機器上執行一些基準測試，但它們沒有顯示任何問題，並且始終顯示生產機器 CPU 至少是測試機器的兩倍。

是否有一些可能配置錯誤的 SQL Server 設置？知道如何解決此問題嗎？

其他基礎設施資訊：

• 兩個數據庫都有

  • “最大並行度”設置為 1
  • “並行性的成本門檻值”設置為 5
  • Azure VM 儲存是高級 SSD (P30)

• 生產（慢）是 Standard_D14_v2

• 測試（快速）是 Standard_D12_v2

• 數據、臨時和日誌是分開的

• 數據跨 3 個條帶化 P20 或 P30 條帶化

• 4 條 P20 或 P30 上的溫度

• 登錄單P20或P30

請注意，“問題”數據庫始終在可用性組上執行，我們想知道這是否可能是這裡的一個因素。

好的，我們已經深入了解了。有一個擴展事件，DYNPERF_AX_CONTEXTINFO，似乎是“動態性能分析器”的一部分。禁用該事件以及此人工測試案例在生產數據庫上的性能現在類似於測試數據庫。供參考，問題事件如下：

CREATE EVENT SESSION [DYNPERF_AX_CONTEXTINFO] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1)
   ACTION(sqlserver.session_id)
   WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%select @CONTEXT_INFO =%') AND NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%model%')))
ADD TARGET package0.event_file(SET filename=N'C:\SQLTrace\DYNPERF_AX_CONTEXTINFO.xel',max_file_size=(50),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

看起來 sql_statement_completed 事件正在循環內觸發。同樣有趣的是，似乎是“like_i_sql_unicode_string”評估導致了問題，而不是事件記錄本身。

與其首先關注硬體，甚至關注可用性組，不如了解這個“查詢”（測試案例）正在等待什麼。

為此，您可以執行 Paul Randal 的腳本，用於在一段時間內擷取等待統計資訊，或者sp_BlitzFirst從開源的 First Responder’s Kit 中執行。或者您可以sp_WhoIsActive定期輪詢並查看測試查詢在每個樣本中等待的內容。

由於我是 2016 年的，我將使用sys.dm_exec_session_wait_stats它，因為它超級容易做，而且現在是晚上 11:30，我很累。

我在 SSMS 中針對我的一個 AG 框（在不到 2 秒的時間內完成）執行了您的測試查詢，獲取了會話 ID，然後執行了這個：

SELECT *
FROM sys.dm_exec_session_wait_stats
WHERE session_id = @spid;

結果是這樣的：

我從一個跳轉框執行了這個，所以這解釋了少量的ASYNC_NETWORK_IO. 另一個主要的等待是SOS_SCHEDULER_YIELD有道理的 - 這是一個 CPU 密集型任務，會定期關閉調度程序（或者可能錯過它的時間，這是一個 VM，還有其他工作正在進行，包括 SSAS、AG 的東西、生產工作量、預定工作等）。在 2000 毫秒的查詢期間，它只有 141 毫秒。

我在我的本地實例上執行了相同的測試，除了寫這個答案之外幾乎沒有做任何事情（查詢在 1 秒內完成）：

根本沒有重要的等待。

您的基準測試的等待統計數據應該能說明您的情況。

---

我們可以給出的任何答案都可能只是沒有等待資訊的推測（可能在“慢速伺服器”上存在您不知道的其他 CPU 負載，或者由於 AG 或其他查詢，伺服器正在經歷執行緒耗盡，或者是“毒藥等待”，如THREADPOOLorRESOURCE_SEMAPHORE等​​）。

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