作者是 Brian Moran，電子郵件地址為 savvy@sqlmag.com

問：我嘗試使用 SQL Server 事件探查器為我的一個客戶最佳化一個繁忙的 SQL Server 資料庫。這個 8GB 的資料庫執行在一臺 CPU 利用率為 70% 到 80% 的 8 CPU 伺服器上。該伺服器很快變得沒有響應，我必須重新啟動伺服器。Microsoft Developer Network (MSDN) 支援人員告訴我，事件探查器會給繁忙的 SQL Server 增加沉重的負擔。為了最佳化資料庫，我需要檢視查詢生成的 SQL 文字和讀數。如何從一臺繁忙的、多處理器的 SQL Server 上收集此資訊而又不會使伺服器忙過頭兒呢？
[@more@]答：我將事件探查器視為 SQL Server 最佳化儲存中最重要的工具，並幾乎每天建設性地使用它。在過去幾年中，我已在 100 多個客戶站點使用了事件探查器，包括每秒鐘執行數萬件事務的伺服器。只要小心，我從未遇到過無法使用事件探查器的情況。但像任何強大的工具一樣，事件探查器也可能引發問題。

首先，要了解事件探查器只是一個 GUI 前端，它呼叫一系列統稱為 SQL 跟蹤的功能和過程。在大多數情況下，與直接呼叫 SQL 跟蹤過程相比，使用事件探查器捕獲系統資訊代價更高。要確保您不錯過繁忙伺服器上的任何事件，您需要從事件探查器 GUI 中選擇伺服器過程跟蹤資料選項。不過，此選項啟動兩個單獨的跟蹤來捕獲同一組事件。SQL Server 將一個事件流傳送到事件探查器 GUI，將另一個事件流傳送到伺服器上的本地檔案。您擔心執行跟蹤產生的影響嗎？如果擔心，您肯定不希望執行兩個跟蹤！

另一個事件探查器選項使您能夠直接寫入 SQL Server 表。但是，如果您在乎效能，則不要使用此選項。SQL Server 會將事件探查器捕獲的每一個事件寫入表中，還會將每一個事件記錄在事務日誌中，從而給繁忙的伺服器增加了巨大的負擔。您應該將事件資料捕獲到本地檔案中，然後使用 fn_trace_gettable() 函式將跟蹤資料載入到表中以供分析。或者，您可以將跟蹤資料寫入網路驅動器，但是我通常能夠將跟蹤資料寫入本地驅動器而不造成其他 I/O 瓶頸。

考慮到效能影響，我很少在繁忙的生產伺服器上執行事件探查器，而是使用一系列直接呼叫 SQL 跟蹤過程的自定義過程來停止、啟動和控制跟蹤。在此解答中，我沒有足夠的篇幅來解釋我的自定義過程。不過，學習如何編寫您自己的過程來控制跟蹤是相當簡單的。您可以使用事件探查器來了解跟蹤是如何執行的。首先，啟動一個事件探查器例項以刪除阻止事件探查器捕獲活動的預設篩選器。然後，啟動另一個事件探查器例項並建立您希望手動執行的跟蹤。第一個事件探查器例項將捕獲第二個例項用於執行您的跟蹤的過程，並且您將擁有一個用於建立您自己的跟蹤過程的良好模型。您還可以直接從事件探查器使用“指令碼跟蹤”選項透過指令碼來管理正在執行的跟蹤。

無論您是透過事件探查器執行跟蹤，還是透過直接呼叫 SQL 跟蹤執行跟蹤，幾乎都不會降低效能，除非它們增長得太大、太快。我見過每秒鐘增長 20MB 的跟蹤檔案，它很可能會影響效能。不過，我經常在極繁忙的伺服器上執行跟蹤，但效能卻沒有顯著地降低，這是因為我將跟蹤檔案的增長率保持在可管理的級別上。您需要透過實驗來判定對於您的特定硬體來說什麼情況下跟蹤會增長得太大、太快。當我在繁忙的伺服器上執行跟蹤時，我總是指定一個最大檔案大小。如果某個跟蹤的增長失控，其大小超過了我設定的最大檔案大小限制，則該跟蹤將停止。以我的經驗，我發現 50MB 是安全的最大大小。如果一個跟蹤的代價很大，會降低效能，那麼它將在幾秒鐘之內達到 50MB 的限制。適合您的具體情況的最大大小取決於您的硬體和事務卷。

另一個控制跟蹤檔案的大小和降低跟蹤誘發效能問題的可能性的方法是：只包含您需要的事件和資料列。確定事件列和資料列的適當搭配是一個複雜的話題。有關選擇最符合您的需要的事件列和資料列的更多資訊，請參閱 Kalen Delaney 於 2001 年 5 月發表的“Tracking Down Event Clues”（跟蹤事件線索，InstantDoc ID 20159）和我在 2003 年 4 月發表的文章“Working with Trace Filters”（使用跟蹤篩選器，InstantDoc ID 38040）。您還可以透過指定您要捕獲的事務的最小 CPU 持續時間來控制跟蹤大小。您的大多數事務將很快，執行時間不超過 20 毫秒，因此將最小時間篩選器設定為 20 毫秒可阻止大量向跟蹤檔案寫入的資料，但不會丟失大量重要事件。

關於管理跟蹤檔案大小，我的最後一點建議是密切注意使用者定義的函式 (UDF)，這些函式會建立大量跟蹤資料。如果您有一個 SELECT 語句使用 UDF 返回 10,000 行，並且該 UDF 包含 10 個語句，您就會生成 100,000 個跟蹤事件。如果 10 個人同時執行該 SELECT 語句，該 UDF 就會生成 100 萬個跟蹤事件。通常，一旦您確定特定 UDF 不是導致效能問題的原因，您將需要包括跟蹤篩選器以消除 UDF 活動。

此 SQL Server 提示由“SQL Server Magazine”（SQL Server 雜誌）提供，該雜誌是建立一流應用程式的智慧指南。

在繁忙 SQL Server 上執行事件探查器

相關文章