應用事件探查器優化SQL Server(圖)

　　概述
　　
　　當你的SQL Server數據庫系統運行緩慢的時候你或許多多少少知道可以使用SQL Server Profiler（中文叫SQL事件探查器）工具來進行跟蹤和分析是的Profiler可以用來捕獲發送到SQL Server的所有語句以及語句的執行性能相關數據（如語句的read/writes頁面數目CPU的使用量以及語句的duration等）以供以後分析但本文並不介紹如何使用Profiler 工具而是將介紹如何使用readtrace（有關該工具見後面介紹）工具結合自定義的存儲過程來提綱挈領地分析Profiler捕獲的Trace文件最終得出令人興奮的數據分析報表從而使你可以高屋建瓴地優化SQL Server數據庫系統
　　
　　本文對那些需要分析SQL Server大型數據庫系統性能的讀者如DBA等特別有用在規模較大應用邏輯復雜的數據庫系統中Profiler產生的文件往往非常巨大比如說在Profiler中僅僅配置捕獲基本的語句事件運行二小時後捕獲的Trace文件就可能有GB級的大小應用本文介紹的方法不但可以大大節省分析Trace的時間和金錢把你從Trace文件的海量數據中解放出來更是讓你對數據庫系統的訪問模式了如指掌從而知道哪一類語句對性能影響最大哪類語句需要優化等等
　　
　　Profiler trace文件性能分析的傳統方法以及局限
　　
　　先說一下什麼是數據庫系統的訪問模式除了可以使用Trace文件解決如死鎖阻塞超時等問題外最常用也是最主要的功能是可以從Trace文件中得到如下三個非常重要的信息
　　
　　運行最頻繁的語句
　　
　　最影響系統性能的關鍵語句
　　
　　各類語句群占用的比例以及相關性能統計信息
　　
　　本文提到的訪問模式就是上面三個信息我們知道數據庫系統的模塊是基本固定的每個模塊訪問SQL Server的方式也是差不多固定的具體到某個菜單某個按鈕都是基本不變的所以在足夠長的時間內訪問SQL Server的各類語句及其占用的比例也基本上是固定的換句話說只要Profiler采樣的時間足夠長（我一般運行小時以上）那麼從Trace文件中就肯定可以統計出數據庫系統的訪問模式每一個數據庫系統都有它自己獨一無二的訪問模式分析Profiler Trace文件的一個重要目標就是找出數據庫系統的訪問模式一旦得到訪問模式你就可以在優化系統的時候做到胸有成竹心中了然可惜直到目前為止還沒有任何工具可以方便地得到這些信息
　　
　　傳統的Trace分析方法有兩種一種是使用Profiler工具本身比如說可以使用Profiler的Filter功能過濾出那些運行時間超過秒以上的語句或按照CPU排序找出最耗費CPU的語句等另一種是把Trace文件導入到數據庫中然後使用TSQL語句來進行統計分析這兩種方法對較小的Trace文件是有效的但是如果Trace文件數目比較多比較大（如個MB以上的trace文件）那麼這兩種方法就有很大的局限性其局限性之一是因為文件巨大的原因分析和統計都非常不易常常使你無法從全局的高度提綱挈領地掌握所有語句的執行性能你很容易被一些語句迷惑而把精力耗費在上面而實際上它卻不是真正需要關注的關鍵語句局限性之二是你發現盡管很多語句模式都非常類似（僅僅是執行時參數不同）卻沒有一個簡單的方法把他們歸類到一起進行統計簡而言之你無法輕而易舉地得到數據庫系統的訪問模式無法在優化的時候做到高屋建瓴綱舉目張這就是傳統分析方法的局限性使用下面介紹的Readtrace工具以及自定義的存儲過程可以克服這樣的局限性
　　
　　Readtrace工具介紹以及它的Normalization 功能
　　
　　ReadTrace工具是一個命令行工具使用ReadTrace工具可以大大節省分析Trace文件的時間有事半功倍的效果ReadTrace的主要工作原理是讀取Trace文件然後對語句進行Normalize (標准化)導入到數據庫生成性能統計分析的HTML頁面另外Readtrace可以生成RML文件然後OSTRESS工具使用RML文件多線程地重放Trace文件中的所有事件這對於那些想把Profiler捕獲的語句在另外一台服務器上重放成為可能本文不詳細介紹Readtrace或OStress工具有興趣的讀者請自行參閱相關資料相關軟件可以從微軟網站下載（注軟件名稱為RML）
　　
　　我要利用的是ReadTrace的標准化功能什麼是標准化？就是把那些語句模式類似但參數不一樣的語句全部歸類到一起舉例說Trace中有幾條語句如下
　　
　　select * from authors where au_lname = white
　　select * from authors where au_lname = green
　　select * from authors where au_lname = carson
　　
　　經過標准化後上面的語句就變成如下的樣子
　　select * from authors where au_lname = {str}
　　select * from authors where au_lname = {str}
　　select * from authors where au_lname = {str}
　　
　　有了標准化後的語句統計出數據庫系統的訪問模式就不再是難事運行Readtrace 的時候我一般使用如下的命令行
　　Readtrace –f –dmydb –Imytracetrc
　　
　　其中－f開關是不生成RML文件因為我不需要重放的功能生成的RML文件比較大建議讀者如果不需要重放的話也使用－f開關
　　
　　－d開關告訴readtrace把trace文件的處理結果存到mydb數據庫中我們後面創建的存儲過程正是訪問readtrace在mydb中生成的表來進行統計的－I開關是指定要分析的的trace文件名Readtrace工具很聰明如果該目錄下有Profiler產生的一系列Trace文件如mytracetrcmytracetrcmytracetrc等那麼它會一一順序讀取進行處理
　　
　　除了上面介紹的外Readtrace還有很多其它有趣的開關比如說使用－i開關使得Readtrace可以從zip或CAB文件中讀取trace文件不用自己解壓所有開關在Readtracechm中有詳細介紹我最欣賞的地方是readtrace的性能分析幾個GB大小的trace文件不足一小時就搞定了我的計算機是一台內存僅為MB的老機器有這樣的性能我很滿意
　　
　　你也許會使用readtrace分析壓力測試產生的trace文件我建議還是分析從生產環境中捕獲的Trace文件為好因為很多壓力測試工具都不能夠真正模擬現實的環境其得到的trace文件也就不能真實反映實際的情況甚至有些壓力測試工具是循環執行自己寫的語句更不能反映准確的訪問模式建議僅僅把壓力測試產生的trace作為參考使用
　　
　　使用存儲過程分析Normalize後的數據
　　
　　有了標准化後的語句就可以使用存儲過程進行統計分析了分析的基本思想是把所有模式一樣的語句的ReadsCPU和Duration做group by統計得出訪問模式信息
　　
　　某類語句的總共執行次數平均讀頁面數（reads）/平均CPU時間/平均執行時間等
　　
　　該類語句在所有語句的比例如執行次數比例reads比例CPU比例等
　　
　　存儲過程的定義以及說明如下
　　
　　Create procedure usp_GetAccessPattern
　　@duration_filter int= 傳入的參數可以按照語句執行的時間過濾統計
　　as begin
　　
　　/*首先得到全部語句的性能數據的總和*/
　　declare @sum_total float@sum_cpu float@sum_reads float@sum_duration float@sum_writes float
　　select @sum_total=count(*)*這是所有語句的總數
　　@sum_cpu=sum(cpu)* 這是所有語句耗費的CPU時間
　　@sum_reads=sum(reads)* 這是所有語句耗費的Reads數目K為單位
　　@sum_writes=sum(writes)*這是所有語句耗費的Writes數目K為單位
　　@sum_duration=sum(duration)*這是所有語句的執行時間總和
　　from tblBatches 這是ReadTrace產生的表包括了Trace文件中所有的語句
　　where duration>=@duration_filter 是否按照執行時間過濾
　　
　　/*然後進行Group by得到某類語句占用的比例*/
　　Select ltrim(str(count(*))) exec_stats+ str(count(*)/@sum_total)+% ExecRatio
　　ltrim(str(sum(cpu)))+ : ++ltrim(str(avg(cpu))) cpu_stats+str(sum(cpu)/@sum_cpu)+% CpuRatio
　　ltrim(str(sum(reads) ))+ : +ltrim(str(avg(reads) )) reads_stats+str(sum(reads)/@sum_reads) +% ReadsRatio
　　ltrim(str(sum(writes) ))+ : +ltrim(str(avg(writes) )) writes_stats+str(sum(writes)/@sum_writes) +%)
　　ltrim(str(sum(duration) ))+ : +ltrim(str(avg(duration))) duration_stats+str(sum(duration)/@sum_duration)+% DurRatio
　　textdatacount(*)/@sum_total tpsum(cpu)/@sum_cpu cpsum(reads)/@sum_reads rpsum(duration)/@sum_duration dp
　　into #queries_staticstics from
　　/* tblUniqueBatches表中存放了所有標准化的語句*/
　　(select readscpudurationwritesconvert(varchar()NormText)textdata from tblBatches
　　inner join tblUniqueBatches on tblBatchesHashId=tblUniqueBatcheshashid where duration>@duration_filter
　　) B group by textdata 這個group by很重要它對語句進行歸類統計
　　
　　print Top order by cpu+reads+duration
　　select top * from #queries_staticstics order by cp+rp+dp desc
　　print Top order by cpu
　　select top * from #queries_staticstics order by cp desc
　　print Top order by reads
　　select top * from #queries_staticstics order by rp desc
　　print Top order by
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22012.html