五餅二魚工作室

最近在協助客戶處理一些效能的問題，想要利用 SQL Trace 去做側錄，再來針對一些特別時段和特定作業來做系統的調整，因此如同一些網路上相關的文章所介紹的方法一樣，先利用 SQL Profiler 設定好追蹤屬性。

 

接下來再利用「匯出」→「指令碼追蹤定義」→「對於 SQL Server 2005 – SQL11」，將設定匯出成為 SQL Trace 的設定指令。

 

接下來再針對匯出檔案修改檔案 Size , 檔案路徑和名稱 , 以及資料檔產生的規則等設定後就可以使用了。但在這次所遇到的案例中，因此會有些異常狀況發生，導致 SQL Server 或者是作業系統重新啟動，造成所捨定的 SQL Trace 就中斷了，因此為了解決這樣的問題，我們先把原本的設定指令做一點加工。首先我們在處理段的前後加上 「CREATE PROCEDURE AutoTrace AS BEGIN」，中間我們稍微調整一下，讓檔案名稱可以隨的不同啟動時間而指定不同的檔案名稱。

 

並且把最後面本來接在 finish: 後面的 GO 給換成「END」，也就是說把整個設定處理變成一個 Stored Procedure 來使用。

 

接下來我們透過一個 SQL Server 所提供的系統預存程序 「sp_procoption」來設定，指定剛剛所建立的預存程序 AutoTrace 會在啟動的時候自動執行

EXEC sp_procoption AutoTrace, 'startup' , 'on'

 

如果要查看有哪些預存程序是有設定啟動時自動執行的話，則可以使用已下指令查詢，可以看到 AutoTrace 這個預存程序已經設定為啟動時自動執行。

SELECT name, type_desc, is_auto_executed, create_date, modify_date FROM sys.procedures

 

如果要關閉的話，則可以透過使用系統預存程序 「sp_procoption」，將最後一個參數改為「off」，或者是說更暴力一點，直接將預存程序給刪除，這樣 SQL 啟動的時候就不會自動執行了。

EXEC sp_procoption AutoTrace, 'startup' , 'off'

 

因此如果有需要設定長時間的 SQL Trace 的朋友，可以參考這樣的方式來做設定，當然可能衍生的問題就是會有檔案很多的狀況，針對這樣的狀況，一般我是指定設定檔案目錄為壓縮，這樣會占用比較小的儲存空間，以上是個人一點使用上的經驗，提供給大家參考看看囉。