描述等候統計資料

  • 8 分鐘

監視伺服器效能的一個整體方式是評估伺服器正在等候的資源。 等候統計資料很複雜，而且 SQL Server 會使用數百種等候類型進行檢測，以監視每個執行中的執行緒，並記錄執行緒正在等候的內容。

偵測 SQL Server 效能問題並對其進行疑難排解，需要瞭解等候統計資料的運作方式，以及資料庫引擎在處理要求時如何使用這些資料。

等候統計資料運作方式的螢幕擷取畫面。

等候統計資料分為三種等候類型：資源等候、佇列等候和外部等候。

  • 資源等候發生於 SQL Server 中的背景工作執行緒要求存取執行緒目前正在使用的資源時。 資源等候的範例包括鎖定、閂鎖和磁碟 I/O 等候。
  • 佇列等候發生於背景工作執行緒進入閒置狀態並正在等候指派工作時。 佇列等候的範例包括死結監視和已刪除的記錄清除。
  • 外部等候發生於 SQL Server 正在等候外部處理序 (例如連結的伺服器查詢) 完成時。 外部等候的範例包括與傳回大型結果集至用戶端應用程式相關的網路等候。

您可以檢查 sys.dm_os_wait_stats 系統檢視來探索執行執行緒所遇到的所有等候，以及 Azure SQL Database 的 sys.dm_db_wait_stats。 sys.dm_exec_session_wait_stats 系統檢視會列出使用中的等候工作階段。

這些系統檢視可讓 DBA 取得伺服器效能的概觀，並立即識別設定或硬體問題。 此資料會從執行個體啟動時開始保存，但可視需要清除資料以識別變更。

等候統計資料是以伺服器總等候時間百分比進行評估。

前 10 個依百分比等候的螢幕擷取畫面。

sys.dm_os_wait_stats 中的這項查詢結果會顯示等候類型、等候時間百分比彙總 ([等候百分比] 資料行)，以及每種等候類型的平均等候時間 (秒)。

在此案例中，伺服器具有 Always On 可用性群組，如 REDO_THREAD_PENDING_WORK 和 PARALLEL_REDO_TRAN_TURN 等候類型所示。 CXPACKET 和 SOS_SCHEDULER_YIELD 等候的百分比相對較高，表示此伺服器有一些 CPU 壓力。

由於 DMV 會提供自上次啟動 SQL Server 以來累積最高時間的等候類型清單，因此定期收集和儲存等候統計資料資料，可協助您瞭解效能問題並將這些問題與其他資料庫事件相互關聯。

由於 DMV 會提供自上次啟動 SQL Server 以來累積最高時間的等候類型清單，因此定期收集和儲存等候統計資料資料，可協助您瞭解效能問題並將這些問題與其他資料庫事件相互關聯。

SQL Server有數種等候類型可用，但其中一些很常見。

  • RESOURCE_SEMAPHORE - 這種等候類型表示查詢正在等候記憶體變成可用，而且可能表示某些查詢獲授與過多的記憶體。 如果查詢執行時間很長或甚至發生逾時，通常會觀察到這個問題。 這些等候類型可能是由於過期的統計資料、遺漏索引和過多的並行查詢所造成。

  • LCK_M_X - 這種等候類型的頻繁出現可能表示封鎖問題，您可以透過下列方式解決：變更 READ COMMITTED SNAPSHOT 隔離等級、在索引中進行變更以減少交易時間，或可能透過 T-SQL 程式碼中更好的交易管理。

  • PAGEIOLATCH_SH - 這種等候類型可能表示索引有問題 (或缺少有用的索引)，其中 SQL Server 掃描的資料太多。 或者，如果等候計數很低，但等候時間很高，則可能表示儲存體效能問題。 您可以藉由分析 sys.dm_os_wait_stats 系統檢視中的 waiting_tasks_count 和 wait_time_ms 資料行來觀察這項行為，以計算指定等候類型的平均等候時間。

  • SOS_SCHEDULER_YIELD - 這種等候類型可能表示高 CPU 使用率 (與大量大型掃描或遺漏索引相互關聯)，而且通常會有大量的 CXPACKET 等候。

  • CXPACKET - 如果這種等候類型很高，則可能表示設定不正確。 在 SQL Server 2019 之前，平行處理原則的最大程度預設設定是針對查詢使用所有可用的 CPU。 此外，平行處理原則的成本閾值設定預設為 5，這可能會導致平行執行的查詢很少，而可能會限制輸送量。 降低 MAXDOP 和增加平行處理原則的成本閾值可以減少這種等候類型，但 CXPACKET 等候類型也可能表示高 CPU 使用率，這通常會透過索引微調解決。

  • PAGEIOLATCH_UP - 資料頁 2:1:1 上的這種等候類型可能表示分頁可用空間 (PFS) 資料頁上的 TempDB 爭用。 每個資料檔案會有一個 64 MB 資料的 PFS 頁面。 這項等候通常是由於只有一個 TempDB 檔案所造成，因為在 SQL Server 2016 之前，預設行為是針對 TempDB 使用一個資料檔案。 最佳做法是在每個 CPU 核心使用一個檔案，最多八個檔案。 另請務必確定 TempDB 資料檔案大小相同，並有相同的自動成長設定，以確保平均使用這些檔案。 SQL Server 2016 和更高版本可控制 TempDB 資料檔案的成長，以確保其以一致且同時的方式成長。

除了上述的 DMV 之外，查詢存放區還會追蹤與指定查詢相關聯的等候。 不過，查詢存放區追蹤的等候資料不會以 DMV 中資料的相同資料粒度進行追蹤，但可提供查詢正在等候內容的不錯概觀。