如何解決 SQL Server 應用程式的效能問題

本文的發布號曾為 CHS224587本頁概要

SQL 事件探查器

監視對象

在正常負載下運行應用程式

在正常負載下運行應用程式

解釋結果

概要

本文分步介紹了如何解決 SQL Server 的效能問題。解決效能問題需要使用一系列步驟隔離並確定應用程式運行緩慢的原因。可能的原因包括：

•	阻塞。
•	系統資源爭用。
•	應用程式設計問題。
•	執行時間較長的一組特殊的查詢或預存程序。

本文介紹了如何確定效能問題的來源。本文還引用了 Microsoft 知識庫中的其他文章，這些文章包含特定效能問題的詳細資料，可用於其他問題的疑難解答。

回到頂端

SQL 事件探查器

在解決 SQL Server 7.0 或更高版本的應用程式效能問題時，SQL 事件探查器是一種非常有效工具。SQL 事件探查器可以使您輕鬆地捕獲在正常負載下伺服器上發生的所有事件，並提供有關這些事件的資訊。將 SQL 事件探查器與 Microsoft Windows NT 效能監控器和一些簡單的查詢配合使用來確定是否發生阻塞，可為您提供解決大多數效能問題所必需的資訊。

回到頂端

監視對象

1. 設定 SQL 事件探查器以捕獲跟蹤。為此，請按照下列步驟操作：

a.	開啟 SQL 事件探查器。
b.	在“工具”菜單上，單擊“選項”。
c.	確保選中了所有事件類別和所有資料列選項。
d.	單擊“確定”。
e.	建立一個新的跟蹤。
f.	在[檔案] 功能表上，指向“建立”，然後單擊“跟蹤”。
g.	在“常規”選項卡上，指定跟蹤名稱和捕獲資料的檔案。
h.	在“事件”選項卡上，將下列事件類型添加到跟蹤： 標題 要添加的事件 說明 Cursors CursorPrepare 此事件表示已經使用 ODBC、OLEDB 或 DB-Library 在 SQL 陳述式上準備了游標。 Error and Warning Missing Column Statistics 此事件表示可能對“最佳化器”有用的列統計資訊不可用。文本列顯示丟失了統計資訊的多個列。此事件連同 Misc:Auto-UpdateStats 事件表示已觸發自動建立統計資訊選項。 Misc. Attention 此事件表示用戶端發送了一個注意訊號。 Misc. Auto-UpdateStats 此事件表示已觸發自動更新統計資訊選項。 Misc. Exec Prepared SQL 此事件表示 ODBC、OLE DB 或 DB-Library 執行了一個（或多個）以前準備的 Transact-SQL 陳述式。 Misc. Execution Plan 此事件表示執行了 Transact-SQL 陳述式的計劃樹。 Misc. Prepare SQL 此事件表示 ODBC、OLE DB 或 DB-Library 應用程式準備了要使用的一個（或多個）Transact-SQL 陳述式。 Misc. Unprepare SQL 此事件表示 ODBC、OLE DB 或 DB-Library 應用程式未準備要使用的一個（或多個）Transact-SQL 陳述式。 Sessions Connect 此事件表示已建立一個新的串連。 Sessions Disconnect 此事件表示用戶端已中斷連線。 Sessions Existing Connection 此事件表示啟動 SQL 事件探查器跟蹤時存在一個串連。 Stored Procedures SP:Completed 此事件表示預存程序已完成執行。 Stored Procedures SP:Recompile 此事件表示預存程序在執行期間被重新編譯。 Stored Procedures SP:Starting 此事件表示預存程序已經開始執行。 Stored Procedures SP:StmtCompleted 此事件表示預存程序中的語句已完成執行。 TSQL: SQL:BatchCompleted 此事件表示 Transact-SQL 批處理已完成。文本列顯示已執行的語句。 TSQL: SQL:StmtCompleted 此事件表示 Transact-SQL 陳述式已完成。文本列顯示已執行的語句。 TSQL: RPC:Completed 此事件表示遠端程序呼叫 (RPC) 已完成。
i.	如果應用程式收到逾時錯誤、停止回應（掛起）或遇到使有問題的語句永遠不能結束啟動並執行其他事件，那麼還應包括下列事件： TSQL: SQL:BatchStarting 此事件表示 Transact-SQL 批處理的開始。文本列顯示正在執行的語句。 TSQL: SQL:StmtStarting 此事件表示 Transact-SQL 陳述式的開始。文本列顯示正在執行的語句。 TSQL: RPC:Starting 此事件表示遠端程序呼叫 (RPC) 的開始。 Stored Procedures SP:StmtStarting 此事件表示預存程序中的語句正在開始執行。 這將協助您確保可以看見逾時發生時正在執行的語句
j.	在資料列選項卡上，確保包含下面的列： 對於 SQL Server 2000 開始時間 結束時間 LoginSid SPID 事件類別 文本資料 整數資料 位元據 期間 CPU 讀取 寫入 應用程式名稱 NT 使用者名稱 DB 使用者名稱 對於 SQL Server 7.0 開始時間 結束時間 串連 ID SPID 事件類別 文本 整數資料 位元據 期間 CPU 讀取 寫入 應用程式名稱 NT 使用者名稱 SQL 使用者名稱

有關使用 SQL 事件探查器的資訊，請參閱 SQL Server 7.0 和 SQL Server 2000 聯機叢書。

2. 使用效能監控器捕獲 Windows NT 和 SQL Server 計數器。為此，請按照下列步驟操作：

a.	啟動 Windows NT 效能監控器。
b.	在視圖菜單上，單擊日誌。
c.	在選項菜單上，單擊日誌。
d.	指定檔案名稱和位置以記錄效能計數器。您可以適當地調整更新時間間隔。
e.	在編輯菜單上，單擊添加到日誌。
f.	添加所有對象。Windows NT 和 SQL Server 物件。
g.	要開機記錄，請在選項菜單上單擊日誌，然後單擊開機記錄按鈕。

有關其他資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

150934 (http://support.microsoft.com/kb/150934/) 如何為 NT 疑難解答建立效能監控器日誌

3. 檢查阻塞。

要查看是否發生阻塞，請運行 sp_who 系統預存程序：

exec sp_who

此輸出將包含 blk 列。查看輸出是否有表示阻塞發生的任何非零項。在出現效能下降的全部時間範圍內定期執行該過程。

注意：運行 sp_who 系統預存程序將檢查是否存在阻塞。通常情況下，要完整地解決阻塞問題，這些資訊還不夠。 有關其他資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

251004 (http://support.microsoft.com/kb/251004/) INF：如何監視 SQL Server 7.0 阻塞

回到頂端

在正常負載下運行應用程式

理想情況下，最好在相同時間範圍內捕獲 SQL 事件探查器、效能監控器和阻塞輸出。該時間範圍必須包括應用程式的效能由好到差的時間範圍。綜合考慮該資訊將能夠協助您清楚地瞭解效能下降的位置。

解釋結果

1.	檢查阻塞。 如果 sp_who 輸出中的 blk 列非零，則表示系統上發生阻塞。如果進程相互阻塞，那麼被阻塞進程的執行時間可能會更長。 有關其他資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章： 224453 (http://support.microsoft.com/kb/224453/) INF：瞭解和解決 SQL Server 7.0 或 2000 阻塞問題
2.	檢查 SQL 事件探查器的輸出。 在解決效能問題時有效地查看 SQL 事件探查器的資料是非常有用的。最重要的是，要意識到您不必查看捕獲的所有內容，而應當有選擇地查看。SQL 事件探查器提供了可以協助您高效地查看已捕獲資料的功能。在屬性選項卡上（單擊檔案菜單上的屬性），SQL 事件探查器允許您通過刪除資料列或事件、按資料列分組（排序）以及應用篩選器來限制顯示的資料。您可以檢索整個跟蹤或只是特定值的特定列（在編輯菜單上單擊尋找）。還可以將 SQL 事件探查器的資料儲存到 SQL Server 表中（在檔案菜單上，指向另存新檔，然後單擊跟蹤表），然後對它運行 SQL 查詢。 注意，應只在以前已儲存的追蹤檔案上執行篩選。如果在某個活動跟蹤上執行這些步驟，由於已經啟動跟蹤，將會有丟失已捕獲資料的危險。首先將活動跟蹤儲存至某個檔案或表（在檔案菜單上，單擊另存新檔），然後在繼續執行前重新開啟此跟蹤（在檔案菜單上，單擊開啟）。在處理已儲存的追蹤檔案時，篩選操作不會永久地刪除被篩選出來的資料，它只是不顯示這些資料。您可以根據需要添加和刪除事件及資料列來協助進行檢索。 在 SQL 事件探查器追蹤檔案中檢查效能情形時，首先要確定伺服器上發生不同類型事件的位置。 按事件類別對跟蹤進行分組： a. 在檔案菜單上，單擊屬性。 b. 在資料列選項卡上，使用向上按鈕移動組標題下面的事件類別，並使用向下按鈕刪除組標題下面的所有其他列。 c. 單擊確定。 按事件類別的列進行分組可顯示 SQL Server 上正在發生什麼類型的事件以及發生頻率。在此列中檢索下列事件： SP:RECOMPILE 此事件表示預存程序在執行期間被重新編譯。多個重新編譯事件表示 SQL Server 在查詢編譯上（而不是在查詢執行上）花費了資源。 有關解決預存程序重新編譯問題的其他資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章： 243586 (http://support.microsoft.com/kb/243586/) 預存程序重新編譯的疑難解答 Attention 注意訊號表示用戶端取消了查詢。通常，這是由於下面兩個原因之一所至： 使用者明確地取消了查詢或結了束應用程式。 - 或 - 超出了查詢逾時。 如果顯示注意訊號，則可能表示某些查詢正在緩慢運行。 有關其他資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章： 243589 (http://support.microsoft.com/kb/243589/) 如何解決 SQL Server 7.0 或更高版本上查詢低效能的問題 要協助確定收到注意訊號的查詢，請修訂此跟蹤使其不按任何資料列分組，然後篩選出那些收到訊號的系統進程 ID (SPID)（在篩選器選項卡上，設定 SPID = x）。在注意訊號最前面的 SQL:StmtStarting、SQL:BatchStarting 或 SP:StmtStarting 事件是收到逾時或取消的查詢。您可以在事件類別列中搜尋 Attention 事件，以便容易地找到此事件（在編輯菜單上，單擊尋找）。 PREPARE SQL 和 EXEC PREPARED SQL Prepare SQL 事件表示 ODBC、OLE DB 或 DB-Library 應用程式準備了要使用的一個（或多個）Transact-SQL 陳述式。Exec Prepared SQL 事件則表示應用程式利用了現有的已準備的語句來執行命令。 比較這兩種事件出現的次數。理想情況下，應用程式必須一次準備一個 SQL 陳述式並多次執行此語句。這將在每次執行語句時節約最佳化器編譯新計劃的成本。因此，Exec Prepared SQL 事件的數量應大大超過 Prepare SQL 事件的數量。如果 Prepare SQL 事件的數量幾乎等於 Exec Prepared SQL 事件的數量，則可能表示應用程式沒有很好地利用準備/執行模式。最好不要準備將只執行一次的語句。有關準備 SQL 陳述式的更多資訊，請參見 SQL Server 7.0 聯機叢書中的 “準備 SQL 陳述式”主題。 如果 Exec Prepared SQL 事件的數量沒有比 Prepare SQL 事件的數量多出 3 到 5 倍，則應用程式可能沒有有效地利用準備/執行模式。 有關其他資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章： 243588 (http://support.microsoft.com/kb/243588/) 特殊查詢效能問題的疑難解答 在 SQL Server 2000 中，將消除每個準備/執行中的過多往返次數，因此，3 到 5 的比率並不非常嚴格。但是，要嘗試並多次重複使用準備好的計劃，它仍然是個適用的規則。 Missing Column Statistics此事件表示最佳化器用於產生更好的查詢計劃的統計資訊不可用。這表示查詢在所涉及的至少一個表上沒有可用的索引。除了沒有可用的索引外，SQL Server 甚至沒有關於列所涉及的統計資料，因而無法作出明確的查詢計劃決定。結果是所產生的查詢計劃可能不是最佳的。如果看到這些事件，請查看所產生的查詢和執行計畫，並參見下面的 Microsoft 知識庫文章，以獲得改進查詢效能需要採取的步驟： 243589 (http://support.microsoft.com/kb/243589/) 如何解決 SQL Server 7.0 或更高版本上查詢低效能的問題 查看 Missing Column Statistics 事件時，請首先重點檢查那些與長時間啟動並執行查詢相關聯的事件。有些事件可能由 SQL Server 通過 autostats 自動產生並解決，可能不需要使用者幹預。因此，最好的策略是首先重點檢查期間較長的查詢（如下文所示），並注意是否有關聯的 Missing Column Statistics 事件。 如果沒有看到這些事件類別的執行個體，下一步則要確定時間花在什麼地方了。 按期間對跟蹤輸出進行分組： a. 在檔案菜單上，單擊屬性。 在資料列選項卡上，使用向上按鈕移動組標題下面的期間，並使用向下按鈕刪除組標題下面的所有其他列。 c. 在事件選項卡上，將除 TSQL 和 Stored Procedures 以外的所有組刪除。 d. 單擊確定。 根據期間進行分組，您可以很容易地看到哪些 SQL 陳述式、批處理或過程的運行效率最低。非常重要的是，不僅要查看出現問題的時間，而且要獲得效能良好時的時間基準以便進行比較。您可以根據啟動時間進行篩選，以便在效能良好時將跟蹤分為多個部分，在效能降低時將跟蹤當作一個單獨的部分。尋找效能良好時最長期間的查詢。這些很可能就是問題的根源。如果整個系統的效能下降，甚至是好的查詢也可能顯示很長的期間，這是因為它們正在等候系統資源。 如果只有少量的查詢具有較長的期間，請參閱下面的 Microsoft 知識庫文章： 243589 (http://support.microsoft.com/kb/243589/) 如何解決 SQL Server 7.0 或更高版本上查詢低效能的問題 如果查看到個別查詢期間較短，但數量較多，而且效能監控器輸出中的 SQL Compilations/sec 計數器（將在下文說明）很高，請參閱下面的 Microsoft 知識庫文章： 243588 (http://support.microsoft.com/kb/243588/) 如何特殊查詢效能問題的疑難解答 檢查其餘的資料列： 通過查看跟蹤資料中的其他資料列，可以進一步深入瞭解效能問題的本質。下面是要考慮的一些事項： 如果 CPU 使用率很高，請按 CPU 進行分組以便確定哪些查詢使用 CPU 的時間最長。在文本列中查詢“hash”或“merge”，以便找到哪個查詢執行計畫正在使用這些聯結類型。這些聯結類型對 CPU 和記憶體的佔用量比巢狀迴圈聯結的要大，而後者通常為 IO 密集的。 如果磁碟 IO 是瓶頸，請按讀取和寫入分組。查看應用程式名稱、NT 使用者名稱和 SQL 使用者名稱欄位可以協助隔離長時間執行查詢的來源。 例外狀況事件的整數資料列將顯示返回給用戶端的任何錯誤。通過在 SQL Server 7.0 聯機叢書中搜尋這些編號，可以找到這些錯誤資訊的內容。 串連 ID 欄位可以協助確保您正在查看指定用戶端的同一會話。而 SPID 無法保證這一點，因為使用者可能已經中斷連線，並且新的使用者已經串連並收到相同的 SPID。 根據具體情況，這些欄位的好處可能有所不同，但如果上文中明確提到的欄位沒有提供答案，則應當對這些欄位進行檢查。
3.	檢查效能監控器輸出。 效能監控器將顯示整個系統的瓶頸。它可能會顯示 SQL Server 和應用程式都正常運行，但電腦可能會效能下降、缺乏記憶體或其他資源。或者，某些計數器可能會顯示應用程式和 SQL Server 在執行方式上存在的問題。請至少檢查下列計數器：

•	對象：Process 計數器：Processor 執行個體：SQL Server
•	對象：Processor 計數器：%Processor Time 執行個體：檢查每個處理器執行個體
•	對象：Physical Disk 計數器：Avg.Disk Queue Length 執行個體：檢查每個物理磁碟執行個體
•	對象：SQL Server:SQL Statistics 計數器：SQL Compilations/sec

尋找效能在某一時間範圍內從好變差的變化趨勢：什麼首先增加？是電腦 CPU 被束縛，還是 DISK IO 被束縛？這些資訊連同上文中的事件探查器輸出，將協助您縮小問題的範圍。較高的 CPU 使用率問題可能表示存在大量的預存程序重新編譯、特殊查詢編譯、或雜湊聯結及合并聯結被密集使用。必須按照上文所引用的文章來確定正確的操作過程。較長的磁碟隊列長度可能表示需要更多的系統記憶體或更好的磁碟子系統。