1.問題引出

老鳥為了重點栽培菜鳥，決定交給菜鳥一個艱鉅而光榮的任務。這天，菜鳥剛到公司還未坐下，老鳥便劈頭蓋臉的問道：“你知道，我們如何Trace SQL Server執行語句嗎？怎麼手動分析這些Trace檔案？如何將Trace File與Windows的效能監視器結合，看到每個語句執行時的效能開銷？以及如何自動分析SQL Server Trace檔案？”。

菜鳥還沒有反應過來，就被殺死了99%的老細胞，下意識的回答：“啊？”。

“去研究下吧”，這個周給我答覆就好了，老鳥看出來了菜鳥的困惑，心理暗自驕傲。

2.手動Trace SQL Server

菜鳥懷著一顆萬事不著急，先問G哥的平穩心態，開始瘋狂的問G哥，如何“手動Trace SQL Server”。G哥開始調動億萬個神經細胞，搜尋著散落在地球上每一個角落的問題與答案，哦，原來使用SQL Server Profiler工具：

Start => All Programs => Microsoft SQL Server R2 => Performance Tools => SQL Server Profiler

或者是： Start => Run => Profiler

開啟SQL Server Profiler後， File => New Trace => Server type: Database Engine => Server name: XXXX => Login: XXXX => Password: XXXX => Connect

連線完畢以後，設定Trace Properties：

Events Selection => Show all Events => 選擇要Trace的事件和欄位 => Run。一個簡單的Demo長相如下：

Trace啟動後，一會兒就有被抓到的語句跑出來：

歐耶，手動部署SQL Server Trace很簡單嘛，搞定。菜鳥迫不及待的問G哥，“如何手動部署Windows效能監視器”。

3.部署Windows 效能監視器

G哥心想，這兩個是關聯問題吧，早知道你要問這個問題了，早已在你問第一個問題時，已經幫你做了最佳推薦：

Start => Run => Perfmon => User Defined => right click on the right side blank space => New => Data Collector Set => type Name: PERFMON_BASE => Create manually(Advanced) => Next

Create data logs => Performance counter => Event trace data => Next => Select counters from computer => 展開效能指標分類，比如：SQL Server:Buffer Manager => Page read/sec, page writes/sec等 => Add => OK

Next => Root directory => Finish

效能監視器建立完畢後，最後一個步驟需要啟動這個效能監視器，來抓取SQL Server效能指標。選擇剛才建立的資料收集器 => 右鍵點選 => Start。

4.Trace檔案關聯效能監視器log檔案

當菜鳥完成手動蒐集Windows效能監視器以後，信心大增。再回過頭來問G哥，哪知道G哥早已推薦他：“SQL Server Trace file與效能監視器log檔案關聯”。G哥是何等聰明絕頂啊。

當SQL Trace檔案與效能監視器log檔案關聯以後，畫面為分為四個區域：

• 查詢語句區域：檢視和選擇查詢語句
• 效能指標做圖區域：可以看到選中的查詢語句對應的各項效能指標做圖（效能指標做圖區域中紅色的豎線）
• 效能指標選擇區域：效能指標選擇區域選中或者取消相關的效能指標
• 查詢語句顯示區域：檢視被選中的相關查詢語句詳情
  

5.手動分析Trace檔案

菜鳥能夠將SQL Server Trace檔案與效能監視器log檔案關聯在一起分析，是巨大的進步啊。可是，他還是不滿足於現狀，覺得一個個去點選查詢語句，太過於原始，效率很難提升，而且不方便過濾出自己關心的查詢語句。比如過濾出CPU佔用最高的TOP 5查詢；I/O最高的TOP 5查詢；執行最為頻繁的查詢語句TOP 5等。

於是菜鳥想到了將SQL Server Trace File檔案通過系統函式將其讀出來，存到一個表裡面。這樣，就可以利用資料庫強大的篩選，過濾，聚合功能得到自己關心的查詢語句了。

declare
    @trace_file_path sysname
;

select
    @trace_file_path = N`C:TempperfmonXXXX.trc`
;

SELECT *
FROM sys.fn_trace_gettable(@trace_file_path, NULL) AS T

到目前為止，菜鳥已經找到了人生努力的方向，成為高富帥，迎娶白富美是指日可待了。可是菜鳥在分析效能的過程中逐漸意識到，由於查詢語句或者儲存過程的引數值是“亂七八糟”的，很難做有效的聚合統計。

6.自動化分析Trace檔案

革命尚未成功，同志還需努力，到底如何實現自動化分析Trace檔案呢？菜鳥又想到了G哥，G哥很豪爽並不加思索的說“用RML Utilities for SQL Server啊”。看來現在G哥已是菜鳥的救火隊長了，度娘表示很受傷，羨慕滴妒恨。

6.1.RML Utilities功能介紹

RML（Replay Markup Language）Utilities是MS SQL Server產品支援服務團隊內部開發使用的一個trace檔案分析工具。支援SQL Server 2005，2008，2008R2，2012和SQL Server 2014。

主要的功能包括以下幾個個方面：

• 分析最消耗SQL Server系統資源的應用和查詢
• 去除引數干擾，統計彙總查詢效能消耗
• 生成視覺化報表，效能消耗大戶一目瞭然

6.2.RML Utilities安裝

首先從下面的地址下載對應的版本，分為X86的32位版和64位版：

X86：[https://www.microsoft.com/en-us/download/details.aspx?id=8161]

X64：[https://www.microsoft.com/en-us/download/details.aspx?id=4511]

RML安裝檔案(RMLSetup_AMD64.msi)是基於Windows MSI的檔案。因此安裝過程你懂的，非常簡單，在此不一一截圖說明安裝步驟了。安裝完畢後，RML的預設安裝目錄會在“C:Program FilesMicrosoft CorporationRMLUtils”

6.3.使用方法

開啟RML Command：Start => All programs => RML Utilities for SQL Server => RML Cmd Prompt

執行如下命令：

ReadTrace -I"C:TempperfmonXXX.trc" -o"C:TempOUTPUT" -S"." -d"PerfAnalysis" -E -f

Readtrace的引數是區分大小寫的，並且輸入檔案和輸出目錄不能夠在同一個目錄下。詳情參見Readtrace -?的引數描述，這條語句的引數說明：

-I: 輸入的Trace檔案目錄和檔名

-o: 日誌檔案輸出目錄

-S: 資料存放的資料庫伺服器

-d: 資料庫名字

-E: Windows認證模式連結資料庫

-f: 不用生成每一個會話和請求的詳細RML輸出檔案

命令執行完畢後會自動化生成以下的分類報表：

• Performance Overview
  Performance Overview分類報表包括其他分類報表的入口，資源使用率的統計，效能總覽的詳細資訊。

• Application Name
  按照應用程式名稱做分類統計，這個統計報表可以知道每個應用程式對SQL Server系統資源的消耗情況。利用這個報表，我們很輕鬆就可以找到資源使用的大戶，針對性的採取必要的措施。

• Unique Batches
  這個分類報表非常有價值，在這個報表中，我們非常輕鬆的就可以發現佔用CPU，Run Duration，IO Read，IO Write TOP Batches語句塊。這個為我們優化具體的SQL語句塊提供了非常方便的統計彙總。

• Interesting Events
  針對SQL Server資料庫事件的統計分類報表。

• Database ID
  按照資料庫標識ID的分類報表，從這個報表，我們可以很容易發現哪個資料庫的壓力比較大，可以選擇作為我們重點優化的資料庫。

• Unique Statements
  這個與Unique Batches分類統計報表類似，也是非常有價值的報表，只不過這個是針對特定語句的分類統計報表，而不是針對語句塊，所以，這兩個報表的分析方式非常類似。

這個報表分別從CPU，Duration，Reads和Writes四個角度統計出查詢語句執行次數及所佔百分比。當我們檢視具體的執行語句的時候（點選處的執行語句），會開啟下圖中的Unique Statement Details頁面。這個頁面展示了相應查詢語句非常詳細的資訊，包括：執行語句模版，按照CPU，Duration，Reads和Writes的統計彙總圖展示，按照時間順序的統計彙總表格，格式化後的查詢語句。
既然拿到TOP Unique Query語句，接下來的要做的事情就是花80%的時間和精力去優化這20%的TOP Query並加以改善到產品環境，這樣我們SQL Server效能問題就可以解決了。

• Data Lineage
  這個報表是Readtrace資料匯入的日誌資訊統計，包含你使用的引數資訊和RML的錯誤警告資訊等。比如，這裡就提示，我們的SQL Server Trace檔案少了SP:StmtStarting事件的跟蹤。

• Login Name
  按照登入使用者聚合的分類統計報表，從這報表我們可以很容易發現哪些使用者是資料庫系統資源的大戶。

總結來看，RML Utilities for SQL Server是一款與SQL Profiler結合得非常好的自動化Trace檔案分析工具。利用這個工具強大的統計彙總功能，使得我們可以非常容易從多角度，多視野，多維度來發現問題，分析問題，解決問題，真正做到工欲善其事必先利其器的效果。

7.寫在最後

當菜鳥把這份心得呈現在老鳥面前的時候，老鳥簡直驚呆了：“不錯啊，來，給你十三個贊，每月一個贊”。

“每年不是十二個月嗎？”，菜鳥疑惑的問道。

“多的一個是十三薪”，老鳥一邊走著一邊回答。留著菜鳥一個人在風中凌亂。