針對整個 SQL Server 或資料庫應用程式似乎執行緩慢的問題進行疑難排解

適用於:SQL Server

Summary

當所有針對 SQL Server 實例或特定資料庫應用程式的查詢都變慢時,原因通常不在任何單一查詢本身。 本文協助資料庫管理員與應用程式開發者,依序處理九層:應用程式、作業系統(OS)、網路、CPU、I/O、記憶體、封鎖、排程器,以及追蹤開銷,來排查 SQL Server 效能緩慢的問題。 每個步驟都會說明應注意的症狀,並提供詳細的故障排除資訊。 遵循這些步驟能幫助你找出造成變慢的層次,避免在問題在其他地方時調整單一查詢。

步驟 1:排除讓 SQL Server 看起來緩慢的應用程式問題

檢查應用層。 從應用程式取得查詢、在 SQL Server 實例上手動執行查詢,並查看其執行方式。 以這種方式測試數個查詢。 如果 SQL Server 實例上的查詢速度較快,問題可能出在應用程式層或應用程式伺服器層。

附註

請注意 資料庫應用程式和 SSMS 之間的查詢效能差異。

如果應用程式在不同伺服器上執行,請檢查該應用程式伺服器的效能(請參見 步驟 2:排除導致 SQL Server 變慢的作業系統問題 以進行疑難排解)。 您可能需要與應用程式開發小組連絡,以檢查應用程式是否有任何問題。

步驟 2:排除導致 SQL Server 變慢的作業系統問題

檢查 SQL Server 執行所在的作業系統是否回應緩慢。 例如,滑鼠移動速度緩慢,視窗不會回應很長一段時間、伺服器的遠端桌面存取速度很慢,或連線到伺服器上的共用速度很慢。

其他服務或應用程式可能會造成此問題。 請使用 效能監視器(Perfmon)來排除故障。 欲了解更多資訊,請參閱使用 效能監視器 進行故障排除。

如需其他OS效能問題,請參閱 Windows Server 效能疑難解答檔。

常見問題包括:

其他應用程式、作業系統或系統上運行的驅動程式都可能導致此問題。

要排除此問題,請使用工作管理員、效能監視器 或 Resource Monitor 來找出問題所在。 如需詳細資訊,請參閱 高 CPU 使用量疑難解答指引。

步驟 3:排除應用程式與 SQL Server 之間的網路問題

問題可能位於網路層中,導致應用程式與 SQL Server 之間的通訊速度緩慢。 使用下列方法來針對此問題進行疑難解答:

  • 這個問題的一個症狀可能是 SQL Server 端的 ASYNC_NETWORK_IO 等待。 如需詳細資訊,請參閱 對因 ASYNC_NETWORK_IO 等候類型而導致的慢速查詢進行疑難排解。

  • 請與您的網路管理員合作,檢查網路問題(防火牆、路由等等)。

  • 收集網路追蹤,檢查是否有網路重置和重傳事件。 有關追蹤工具的資訊,請參見持續發生的 SQL Server 網路連線問題。 如需疑難解答構想,請參閱 間歇性或週期性網路問題。

  • 啟用 Perfmon 計數器,以檢查網路介面層級 (NIC) 的網路效能。 不應有任何被捨棄的封包和錯誤封包。 檢查網路介面頻寬:

    • 網路介面\已接收且已捨棄的封包
    • 網路介面\封包收到錯誤
    • 網路介面\輸出時已捨棄的封包
    • 網路介面\封包輸出錯誤
    • 網路介面\每秒總位元組數
    • 網路介面\目前頻寬

步驟 4:針對 SQL Server 中的高 CPU 使用量進行疑難解答

如果系統上執行 CPU 密集型查詢,可能會導致其他查詢無法取得足夠的 CPU 資源。 不過,較常見的情況是,來自查詢的高 CPU 使用量可能表示查詢需要最佳化。 要排查高 CPU 使用率問題,請依照「在 SQL Server 中排除高 CPU 使用率問題」中的步驟進行。

步驟 5:針對造成 SQL Server 緩慢的過多 I/O 進行疑難解答

SQL Server 工作負載整體速度緩慢的另一個常見原因是 I/O 問題。 I/O 速度緩慢可能會影響系統上大部分或所有查詢。 使用下列方法來針對問題進行疑難解答:

  • 檢查硬體問題:

    • SAN 設定錯誤(交換器、纜線、HBA、儲存裝置)。
    • 超過 I/O 容量(整個 SAN 網路不平衡,不只是後端儲存體,請檢查所有共用 SAN 的伺服器 I/O 輸送量)。
    • 驅動程式或韌體問題或更新。
  • 檢查是否有不理想的 SQL Server 查詢,導致大量 I/O,並以 I/O 要求使磁碟區飽和。

    • 找出導致大量邏輯讀取(或寫入)的查詢,並調整這些查詢以減少磁碟 I/O。 使用 適當的索引 是第一步。
    • 使統計資料保持更新,因為它們為查詢最佳化工具提供足夠的資訊來選擇最佳計劃。
    • 重新設計查詢,有時數據表可能有助於改善 I/O。
  • 篩選驅動程式:如果文件系統篩選驅動程序處理大量 I/O 流量,SQL Server I/O 回應可能會受到嚴重影響。

    • 將資料夾排除在防毒掃描之外,並由軟體廠商修正過濾驅動程式問題,以防止影響 I/O 效能。
  • 其他應用:同一台機器上的另一個與 SQL Server 搭配運作的應用程式可能會因過多的讀寫請求而導致 I/O 路徑飽和。 這種情況可能會讓 I/O 子系統超出容量限制,導致 SQL Server 的 I/O 變慢。 識別應用程式並微調應用程式,或將其移至別處以消除它對 I/O 堆疊的影響。

    此問題也可能是由在其他電腦上執行、但與這部 SQL Server 電腦共用相同 SAN 的應用程式所造成。 請與您的 SAN 系統管理員合作,以平衡 I/O 流量(請參閱 檢查硬體問題)。

如需 SQL Server I/O 相關問題的詳細疑難解答,請參閱 對於因 I/O 問題導致的 SQL Server 效能緩慢進行疑難排解。

步驟 6:排除導致 SQL Server 緩慢的記憶體問題

整體或 SQL Server 內部的記憶體不足可能會導致查詢等候記憶體授與 () 或編譯記憶體時RESOURCE_SEMAPHORERESOURCE_SEMAPHORE_QUERY_COMPILE速度緩慢。 欲了解更多關於記憶體授與等待的資訊,請參閱 「故障排除因 SQL Server 中的記憶體授與所導致的效能緩慢或記憶體不足問題」。

使用下列方法來針對問題進行疑難解答:

  • 使用 Perfmon 計數器檢查作業系統層級的外部記憶:

    • 記憶體\可用 MB
    • Process(*)\Working Set (所有實例)
    • Process(*)\Private Bytes (所有實例)
  • 針對內部記憶體壓力,請使用 SQL Server 查詢 sys.dm_os_memory_clerks,或使用 DBCC MEMORYSTATUS。

  • 檢查 SQL Server 錯誤記錄檔是否有 701 錯誤。

如需詳細的疑難解答步驟,請參閱 SQL Server 中記憶體不足或低記憶體問題的疑難排解。

步驟七:排除會使所有查詢等待的阻擋問題

取得鎖定可保護資料庫系統中的資源。 如果一個會話長時間持有鎖,而其他會話等待這些鎖,這就是阻塞的情況。

像 SQL Server 這類資料庫系統經常會發生短暫封鎖。 但長時間封鎖,尤其是大多數或全部查詢等待鎖定時,可能會導致整個伺服器被視為無反應。

使用下列步驟來針對問題進行疑難解答:

  1. 透過查看 sys.dm_exec_requests DMV 輸出中的 blocking_session_id 欄位或 sp_who2 儲存程序輸出中的 BlkBy 欄位來識別頭部封鎖工作階段。

  2. 找出主要封鎖鏈正在執行的查詢(長時間持有鎖定的查詢)。

    如果主要封鎖工作階段中沒有查詢正在執行,則可能因應用程式問題而存在遭到遺棄的交易作業。

  3. 重新設計或調整頭部阻塞查詢以更快執行,或減少交易內的查詢數目。

  4. 檢視查詢中使用的交易隔離並調整。

如需封鎖情況的詳細疑難排解,請參閱 瞭解並解決 SQL Server 封鎖問題。

步驟 8:針對排程器問題進行疑難排解(未讓出、死結排程器、未讓出 IOCP 接聽程式、資源監視器)

SQL Server 會使用合作式排程機制(排程器),讓作業系統可對其執行緒在 CPU 上進行排程。 如果 SQL 排程器有問題,SQL Server 線程可能會停止處理查詢、登入、註銷等等。 因此,視受影響的排程器數量而定,SQL Server 可能看似部分或完全沒有回應。

排程器問題可能源自於各式各樣的問題,包括產品錯誤、外部和篩選驅動程式,以及硬體問題。 請依照以下步驟排除這些問題。

在 SQL Server 錯誤日誌中識別排程器錯誤

檢查您的 SQL Server 錯誤記錄檔中是否有下列錯誤,時間點為回報 SQL Server 無回應時:

  • ***********************************************
    *
    * BEGIN STACK DUMP:
    * 03/10/22 21:16:35 spid 22548
    *
    * Non-yielding Scheduler
    *
    ***********************************************
    
  • **********************************************
    *
    * BEGIN STACK DUMP:
    * 03/25/22 08:50:29 spid 355
    *
    * Deadlocked Schedulers
    *
    * ********************************************
    
  • * *******************************************************************************
    *
    * BEGIN STACK DUMP:
    * 09/07/22 23:01:04 spid 0
    *
    * Non-yielding IOCP Listener
    *
    * *******************************************************************************
    
  • * ********************************************
    *
    * BEGIN STACK DUMP:
    * 07/25/22 11:44:21 spid 2013
    *
    * Non-yielding Resource Monitor
    *
    * ********************************************
    

解決排程錯誤

如果你發現了前一節描述的錯誤,請依照以下步驟操作:

  1. 請確認你使用的是哪個 SQL Server 的累積更新(CU)版本。 檢查在您目前的 CU 之後發行的 CU 中,是否有任何已修正的問題。 如需 SQL Server 修正,請參閱 目前支援之 SQL Server 版本可取得的最新更新。 如需詳細的修正清單,您可以下載此 Excel 檔案。

  2. 更多點子請參考「SQL Server排程與Yielding疑難排解」。

  3. 檢查大量封鎖案例或高平行處理查詢,這些情況可能導致排程器發生死結。 如需詳細資訊,請參閱 The Tao of a Deadlocked Scheduler。

  4. 若為非產生 IOCP 接聽程式,請檢查您的系統是否記憶體不足,且 SQL Server 正在分頁。另一個原因可能是防病毒軟體或入侵預防軟體攔截 I/O API 呼叫,並降低線程活動的速度。 如需詳細資訊,請參閱 IOCP接聽程式是否實際接聽? 以及 載入特定模組或篩選驅動程式時的效能和一致性問題。

  5. 針對資源監視器問題,在某些情況下,您可能不一定關心這個問題。 如需詳細資訊,請參閱 在執行 SQL Server 的伺服器上,資源監視器進入非回應狀態。

  6. 如果這些資源都沒幫助,請向 Microsoft 開支援單,並上傳在 \LOG 子目錄中建立的記憶體傾印檔案以供分析。

步驟 9:尋找資源密集型的 Profiler 或 Extended Events 追蹤

尋找作用中的擴充事件或 SQL Server Profiler 追蹤,特別是對文字資料行進行篩選的項目(資料庫名稱、登入名稱、查詢文字等等)。 可能的話,請停用追蹤,並查看查詢效能是否改善。 根據所選事件,每個執行緒可能會額外消耗 CPU,導致整體速度變慢。

要辨識延伸事件的使用中追蹤,請參見 sys.dm_xe_sessions。 關於 Profiler 追蹤,請參見 sys.traces。

SELECT * FROM sys.dm_xe_sessions
GO
SELECT * FROM sys.traces

附註

SQL Server Profiler 與 SQL Trace 已被棄用。 建議改用 延伸事件 。