熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

OpenSQLTrace:自動跟蹤處理和分析系統

2013-11-15 14:35:58  來源: SQL Server 

  如果您定期運行跟蹤並且保留所有結果以便進行歷史趨勢分析那麼通過跟蹤捕獲的數據的價值將大大增加但是存儲空間很快會成為約束我們的主要生產服務器每小時執行 百萬個事務而持續時間為 分鐘的跟蹤會創建 GB 大小的跟蹤文件我們的系統致力於整理所有數據並且只保存其精華您可以安排任何服務器的特定跟蹤(或者設置循環跟蹤)並且自動加載和處理跟蹤文件正如您上個月所看到的那樣我們的系統從 TSQL 中剔除了不重要的詳細信息從而將事務類型減少到可管理的數量並且生成和保存了開銷最大的事務的報表在經過幾個周的積累之後這樣的報表可以提供對整個服務器或任何特定事務類型進行性能趨勢分析的數據
  
  安裝
  
  您可以將我們的系統安裝在任何已經將服務器連接鏈接到您希望運行跟蹤的所有 SQL Server 的網絡 SQL Server 上因此為了保存跟蹤文件必須可以從被跟蹤的服務器通過網絡對該中心服務器的硬盤驅動器進行訪問中心跟蹤服務器充當所有跟蹤的計劃程序數據處理器循環報表的發布者歷史數據的儲存庫以及 DBA 可以生成即席報表和進行性能調查的分析服務器該設計將對被跟蹤的服務器的影響降低至最低程度並且最大限度降低了由於造成磁盤空間不足或引起處理開銷而破壞這些服務器的工作的可能性您還可以直接在每個被跟蹤的服務器上安裝和使用該系統 — 只要該服務器具有足夠的磁盤空間和處理能力
  
  出於本文的目的讓我們將我們的中心跟蹤服務器稱為 TRACESQL並且將我們的被跟蹤的服務器稱為 PRODSQL如果您計劃使用同一服務器來跟蹤其本身則請用同一名稱來替換 TRACESQL 和 PRODSQL下面介紹如何安裝 OpenSQLTrace
  
  如果您打算只跟蹤已經安裝 OpenSQLTrace 的同一服務器(換句話說TRACESQL 和 PRODSQL 是同一服務器)則請跳過步驟
  
  配置從 TRACESQL 到 PRODSQL 的鏈接服務器連接它必須允許具有啟動用來管理服務器端跟蹤的系統存儲過程的權限最容易的方法是使用在 PRODSQL 上具有 System Administrator 角色的帳戶但是您顯然需要考慮您的特定環境中的安全要求
  
  查明哪個帳戶被用來在 PRODSQL 上運行 MSSQLServer 服務它必須是網絡帳戶
  
  在 TRACESQL 上選擇一個硬盤驅動器分區以用來存儲跟蹤文件它必須具有足夠的空間以容納來自 PRODSQL 的跟蹤文件 — 大小很可能為幾個 GB但是正如別人所說的那樣每個人需要的空間可能有所不同跟蹤文件大小取決於服務器活動事務混合和跟蹤的持續時間
  
  如果 TRACESQL 和 PRODSQL 是不同的服務器則請在 TRACESQL 上在您所選的驅動器上創建一個名為 TRACE 的共享文件夾並且將該共享上的所有權限授予在 PRODSQL 上運行 MSSQLServer 服務的網絡帳戶
  
  在 TRACESQL 上創建一個名為 Trace 的數據庫分配足夠空間以存儲多個完整的跟蹤文件另外還要分配一點兒額外的空間以存放所保存的報表將存儲在數據庫中的跟蹤文件的過期時間是可配置的在我們的環境中我們只將它們保留一個周但是我們無限期地保留已編譯的摘要報表以便進行歷史趨勢分析
  
  下載本文隨附的 OpenSQLTracesql 腳本並且從 TRACESQL 服務器上的查詢分析器中執行它這會在 Trace 數據庫中創建所有需要的對象並且創建一個每天安排一次的作業以清除過期的跟蹤表(如果您先前已經在同一數據庫中安裝了該系統則請注意該腳本刪除並重新創建了所有對象包括已保存的數據但未過期的跟蹤表除外
  
  如果 TRACESQL 和 PRODSQL 是同一服務器則改變在上一步中創建的用戶定義函數 ufn_Trace_File_Name更改以下行
  
  return( \\ + rtrim( @@servername ) + \TRACE\ +
  
  以使用您在步驟 中創建的 TRACE 文件夾的硬編碼路徑確切的路徑取決於您的環境例如如果您在驅動器 D: 上創建了 TRACE 文件夾則請按如下方式更改代碼
  
  return( D:\TRACE\ +
  
  用法示例
  
  上個月的文章提供了有關提煉跟蹤文件和生成摘要報表的存儲過程的用法示例請注意可下載的新腳本具有 Calculate_Most_Expensive_Transactions 過程的重命名版本新的名稱為 Calculate_Hit_Parade
  
  本月的腳本公開了由以下示例說明的新功能
  
  設置帶有摘要處理的一次性無人參與跟蹤 為了測試該系統讓我們設置一次性跟蹤從 TRACESQL 上的查詢分析器中執行以下過程
  
  Schedule_Trace PRODSQL default
  
  這會在 TRACESQL 上安排一個在兩分鐘內運行的作業在 PRODSQL 上啟動一個運行一分鐘的跟蹤並且將文件保存到 TRACESQL 上的 TRACE 共享中它還將在 TRACESQL 上安排另一個作業以便在跟蹤的估計結束時間之後運行 分鐘將文件加載到 Trace 數據庫中的表中提煉已記錄的 TSQL 語句(有關詳細信息請參閱上個月的文章)生成開銷最大的事務的摘要並且將其保存到 Trace 數據庫中的表中(提示您可以使用 fn_trace_getinfo() 來監視跟蹤進度
  
  這兩個作業在成功完成後都將自動刪除它們自身如果您迫不及待地希望更快地運行該測試則可以手動啟動安排的第一個作業等待一分鐘(跟蹤持續時間)然後手動啟動第二個作業
  
  在第二個作業完成後您便能夠在 Trace 數據庫的 Hit_Parade_Archive 表中找到已保存的開銷最大的事務的報表並且使用存儲過程 Retrieve_Report 來檢索它
  
  默認情況下系統會記錄 TSQL 批處理和遠程過程調用的完成如果您希望記錄其他跟蹤事件或者更進一步並分別記錄在存儲過程內部執行的每個查詢則需要通過 @Event_Class_Filter 參數向 Schedule_Trace 提供事件列表
  
  安排每日跟蹤 如果您需要每天運行跟蹤則可以如前所述安排一個跟蹤(只須指定預期跟蹤啟動時間而不是默認時間並且指定預期持續時間而不是一分鐘)然後手動更改所安排的兩個作業(運行跟蹤和處理跟蹤)的屬性以設置每天執行而不是一次性執行的計劃同時在 EM 的已安排作業對話框中取消選中Notifications選項卡上的Automatically delete job選項以防止作業在完成後刪除它們自身(通過 Schedule_Trace 設置的默認行為)
  
  檢索和分析摘要報表 要檢索任何跟蹤的摘要報表需要知道用來加載數據的跟蹤表名稱跟蹤表在過期(該參數可配置)時被自動從 TRACE 數據庫中刪除但是從它們中提取的報表總是 與原來的表名稱相關聯(Trace_Directory 表包含所有已處理的跟蹤表的目錄)可以按照服務器名稱和跟蹤時間查找跟蹤表名稱
  
  執行以下存儲過程以檢索一個摘要報表
  
  Retrieve_Report <Trace_Table_Name>
  
  您可以在上個月的文章中查看示例摘要報表我們通常將這些報表復制並粘貼到 Excel 中(在本月的下載中包含其中一個報表)在那裡可以容易地對數據進行排序和分析
  
  在我們的環境中我們還創建了一個 DTS 軟件包以便將開銷最大的事務的日常報表以電子表格格式自動發布到網絡共享開發人員可以訪問該報表以查看他們的存儲過程是如何執行的並且識別瓶頸[我為作者為開發人員反饋所做的准備以及負責任的態度而喝采 — 編者]
  
  按聚合類型獲得事務的實際源代碼 在您識別開銷最大的事務類型之後您就可能希望查看在一個類型下聚合的所有事務的未經提煉的實際 TSQL 代碼為了完成該下鑽工作請執行以下存儲過程
  
  Report_TSQL_by_ID <Trace_Table_Name> <SQL_Type_ID>
  
  其中 <SQL_Type_ID>是從指定為<Trace_Table_Name> 的跟蹤表派生的摘要報表中的事務類型的數字 ID
  
  比較兩個報表 最有效的分析方法之一是並排比較兩個不同的摘要報表您可能希望比較同一服務器的兩個不同跟蹤的性能或者比較具有相同事務混合的兩個不同服務器的性能存儲過程 Compare_Reports 采用兩個跟蹤表(來自 Trace_Directory 表)的名稱作為參數並且比較它們的已保存的報表對於每個事務類型它都會顯示來自第一個跟蹤和第二個跟蹤的統計信息以及絕對和相對差異
  
  只有當您在兩個報表中跟蹤相同的事件類型時對這兩個報表進行比較才會有意義在我們的環境中我們在同一時間同一服務器上使跟蹤運行相同的分鐘數從而使逐日比較顯得合理但是我們可以想到很多分析任務會要求比較兩個不同服務器中的跟蹤或者比較在每天的不同時間執行的跟蹤
  
  我們將跟蹤比較報表復制並保存到 Excel 中以便進行進一步的分析它們可以幫助我們回答如下問題
  
  事務混合中發生了哪些可能導致性能下降的更改?
  
  哪些事務的處理開銷變得更大?
  
  同一服務器上的特定存儲過程的執行頻率或平均持續時間在兩個日期之間是如何更改的?
  
  摘要報表中出現了哪些新的事務類型?
  
  在兩個跟蹤之間如何比較特定事務類型的 I/O 和 CPU 開銷?
  
  從所有已保存的報表中檢索特定事務類型的歷史記錄 有時您可能希望查看特定事務的性能是如何隨著時間的推移而變化的(例如當您調查瓶頸事務需要分析並且可能需要以圖形方式表示響應速度隨著時間的推移而發生的下降或提高時)我們還使用它來驗證應用於存儲過程的修改是否的確已經改善了它們的性能
  
  我們每天為我們的主要生產服務器運行跟蹤並且保存所有報表經過幾個月的收集該信息使我們可以為我們希望調查的任何
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22044.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.