概述

　　當你的sql server數(shù)據(jù)庫系統(tǒng)運行緩慢的時候，你或許多多少少知道可以使用sql server profiler（中文叫sql事件探查器）工具來進行跟蹤和分析。是的，profiler可以用來捕獲發(fā)送到sql server的所有語句以及語句的執(zhí)行性能相關(guān)數(shù)據(jù)（如語句的read/writes頁面數(shù)目，cpu的使用量，以及語句的duration等）以供以后分析。但本文并不介紹如何使用profiler 工具，而是將介紹如何使用read80trace（有關(guān)該工具見后面介紹）工具結(jié)合自定義的存儲過程來提綱挈領(lǐng)地分析profiler捕獲的trace文件，最終得出令人興奮的數(shù)據(jù)分析報表，從而使你可以高屋建瓴地優(yōu)化sql server數(shù)據(jù)庫系統(tǒng)。

　　本文對那些需要分析sql server大型數(shù)據(jù)庫系統(tǒng)性能的讀者如dba等特別有用。在規(guī)模較大、應用邏輯復雜的數(shù)據(jù)庫系統(tǒng)中profiler產(chǎn)生的文件往往非常巨大，比如說在profiler中僅僅配置捕獲基本的語句事件，運行二小時后捕獲的trace文件就可能有g(shù)b級的大小。應用本文介紹的方法不但可以大大節(jié)省分析trace的時間和金錢，把你從trace文件的海量數(shù)據(jù)中解放出來，更是讓你對數(shù)據(jù)庫系統(tǒng)的訪問模式了如指掌，從而知道哪一類語句對性能影響最大，哪類語句需要優(yōu)化等等。

profiler trace文件性能分析的傳統(tǒng)方法以及局限

　　先說一下什么是數(shù)據(jù)庫系統(tǒng)的訪問模式。除了可以使用trace文件解決如死鎖，阻塞，超時等問題外，最常用也是最主要的功能是可以從trace文件中得到如下三個非常重要的信息：

　　1.運行最頻繁的語句

　　2.最影響系統(tǒng)性能的關(guān)鍵語句

　　3.各類語句群占用的比例以及相關(guān)性能統(tǒng)計信息

　　本文提到的訪問模式就是上面三個信息。我們知道，數(shù)據(jù)庫系統(tǒng)的模塊是基本固定的，每個模塊訪問sql server的方式也是差不多固定的，具體到某個菜單，某個按鈕，都是基本不變的，所以，在足夠長的時間內(nèi)，訪問sql server的各類語句及其占用的比例也基本上是固定的。換句話說，只要profiler采樣的時間足夠長（我一般運行2小時以上），那么從trace文件中就肯定可以統(tǒng)計出數(shù)據(jù)庫系統(tǒng)的訪問模式。每一個數(shù)據(jù)庫系統(tǒng)都有它自己獨一無二的訪問模式。分析profiler trace文件的一個重要目標就是找出數(shù)據(jù)庫系統(tǒng)的訪問模式。一旦得到訪問模式，你就可以在優(yōu)化系統(tǒng)的時候做到胸有成竹，心中了然?？上е钡侥壳盀橹惯€沒有任何工具可以方便地得到這些信息。

　　傳統(tǒng)的trace分析方法有兩種。一種是使用profiler工具本身。比如說可以使用profiler的filter功能過濾出那些運行時間超過10秒以上的語句，或按照cpu排序找出最耗費cpu的語句等。另一種是把trace文件導入到數(shù)據(jù)庫中，然后使用t-sql語句來進行統(tǒng)計分析。這兩種方法對較小的trace文件是有效的。但是，如果trace文件數(shù)目比較多比較大（如4個500mb以上的trace文件），那么這兩種方法就有很大的局限性。其局限性之一是因為文件巨大的原因，分析和統(tǒng)計都非常不易，常常使你無法從全局的高度提綱挈領(lǐng)地掌握所有語句的執(zhí)行性能。你很容易被一些語句迷惑而把精力耗費在上面，而實際上它卻不是真正需要關(guān)注的關(guān)鍵語句。局限性之二是你發(fā)現(xiàn)盡管很多語句模式都非常類似（僅僅是執(zhí)行時參數(shù)不同），卻沒有一個簡單的方法把他們歸類到一起進行統(tǒng)計。簡而言之，你無法輕而易舉地得到數(shù)據(jù)庫系統(tǒng)的訪問模式，無法在優(yōu)化的時候做到高屋建瓴，綱舉目張。這就是傳統(tǒng)分析方法的局限性。使用下面介紹的read80trace工具以及自定義的存儲過程可以克服這樣的局限性。

read80trace工具介紹以及它的normalization 功能

　　read80trace工具是一個命令行工具。使用read80trace工具可以大大節(jié)省分析trace文件的時間，有事半功倍的效果。read80trace的主要工作原理是讀取trace文件，然后對語句進行normalize (標準化)，導入到數(shù)據(jù)庫，生成性能統(tǒng)計分析的html頁面。另外，read80trace可以生成rml文件，然后ostress工具使用rml文件多線程地重放trace文件中的所有事件。這對于那些想把profiler捕獲的語句在另外一臺服務(wù)器上重放成為可能。本文不詳細介紹read80trace或ostress工具，有興趣的讀者請自行參閱相關(guān)資料，相關(guān)軟件可以從微軟網(wǎng)站下載（注：軟件名稱為rml）

　　http://www.microsoft.com/downloads/

　我要利用的是read80trace的標準化功能。什么是標準化？就是把那些語句模式類似，但參數(shù)不一樣的語句全部歸類到一起。舉例說trace中有幾條語句如下：

　　select * from authors where au_lname = 'white'
　　select * from authors where au_lname = 'green'
　　select * from authors where au_lname = 'carson'

　　經(jīng)過標準化后，上面的語句就變成如下的樣子：
　　select * from authors where au_lname = {str}
　　select * from authors where au_lname = {str}
　　select * from authors where au_lname = {str}

　　有了標準化后的語句，統(tǒng)計出數(shù)據(jù)庫系統(tǒng)的訪問模式就不再是難事。運行read80trace 的時候我一般使用如下的命令行：
　　read80trace –f –dmydb –imytrace.trc

　　其中－f開關(guān)是不生成rml文件，因為我不需要重放的功能。生成的rml文件比較大，建議讀者如果不需要重放的話，也使用－f開關(guān)。

　?。璬開關(guān)告訴read80trace把trace文件的處理結(jié)果存到mydb數(shù)據(jù)庫中。我們后面創(chuàng)建的存儲過程正是訪問read80trace在mydb中生成的表來進行統(tǒng)計的。－i開關(guān)是指定要分析的的trace文件名。read80trace工具很聰明，如果該目錄下有profiler產(chǎn)生的一系列trace文件，如mytrace.trc,mytrace1.trc,mytrace2.trc等，那么它會一一順序讀取進行處理。

　　除了上面介紹的外，read80trace還有很多其它有趣的開關(guān)。比如說使用－i開關(guān)使得read80trace可以從zip或cab文件中讀取trace文件，不用自己解壓。所有開關(guān)在read80trace.chm中有詳細介紹。我最欣賞的地方是read80trace的性能。分析幾個gb大小的trace文件不足一小時就搞定了。我的計算機是一臺內(nèi)存僅為512mb的老機器，有這樣的性能我很滿意。

　　你也許會使用read80trace分析壓力測試產(chǎn)生的trace文件。我建議還是分析從生產(chǎn)環(huán)境中捕獲的trace文件為好。因為很多壓力測試工具都不能夠真正模擬現(xiàn)實的環(huán)境，其得到的trace文件也就不能真實反映實際的情況。甚至有些壓力測試工具是循環(huán)執(zhí)行自己寫的語句，更不能反映準確的訪問模式。建議僅僅把壓力測試產(chǎn)生的trace作為參考使用。

使用存儲過程分析normalize后的數(shù)據(jù)

　　有了標準化后的語句就可以使用存儲過程進行統(tǒng)計分析了。分析的基本思想是把所有模式一樣的語句的reads，cpu和duration做group by統(tǒng)計，得出訪問模式信息：

　　1.某類語句的總共執(zhí)行次數(shù)，平均讀頁面數(shù)（reads）/平均cpu時間/平均執(zhí)行時間等。

　　2.該類語句在所有語句的比例，如執(zhí)行次數(shù)比例，reads比例，cpu比例等。

　　存儲過程的定義以及說明如下：

create procedure usp_getaccesspattern 8000
@duration_filter int=-1 --傳入的參數(shù)，可以按照語句執(zhí)行的時間過濾統(tǒng)計
as begin

/*首先得到全部語句的性能數(shù)據(jù)的總和*/
declare @sum_total float,@sum_cpu float,@sum_reads float,@sum_duration float,@sum_writes float
select @sum_total=count(*)*0.01,--這是所有語句的總數(shù)。
@sum_cpu=sum(cpu)*0.01, --這是所有語句耗費的cpu時間
@sum_reads=sum(reads)*0.01, --這是所有語句耗費的reads數(shù)目，8k為單位。
@sum_writes=sum(writes)*0.01,--這是所有語句耗費的writes數(shù)目，8k為單位。
@sum_duration=sum(duration)*0.01--這是所有語句的執(zhí)行時間總和。
from tblbatches --這是read80trace產(chǎn)生的表，包括了trace文件中所有的語句。
where duration>[email protected]_filter --是否按照執(zhí)行時間過濾

/*然后進行g(shù)roup by，得到某類語句占用的比例*/
select ltrim(str(count(*))) exec_stats,''+ str(count(*)/@sum_total,4,1)+'%' execratio,
ltrim(str(sum(cpu)))+' : '++ltrim(str(avg(cpu))) cpu_stats,''+str(sum(cpu)/@sum_cpu,4,1)+'%' cpuratio,
ltrim(str(sum(reads) ))+' : '+ltrim(str(avg(reads) )) reads_stats,''+str(sum(reads)/@sum_reads,4,1) +'%' readsratio ,
--ltrim(str(sum(writes) ))+' : '+ltrim(str(avg(writes) )) --writes_stats,''+str(sum(writes)/@sum_writes,4,1) +'%)',
ltrim(str(sum(duration) ))+' : '+ltrim(str(avg(duration))) duration_stats,''+str(sum(duration)/@sum_duration,4,1)+'%' durratio ,
textdata,count(*)/@sum_total tp,sum(cpu)/@sum_cpu cp,sum(reads)/@sum_reads rp,sum(duration)/@sum_duration dp
into #queries_staticstics from
/* tbluniquebatches表中存放了所有標準化的語句。*/
(select reads,cpu,duration,writes,convert(varchar(2000),normtext)textdata from tblbatches
inner join tbluniquebatches on tblbatches.hashid=tbluniquebatches.hashid where duration>@duration_filter
) b group by textdata --這個group by很重要，它對語句進行歸類統(tǒng)計。

print 'top 10 order by cpu+reads+duration'
select top 10 * from #queries_staticstics order by cp+rp+dp desc
print 'top 10 order by cpu'
select top 10 * from #queries_staticstics order by cp desc
print 'top 10 order by reads'
select top 10 * from #queries_staticstics order by rp desc
print 'top 10 order by duration'
select top 10 * from #queries_staticstics order by dp desc
print 'top 10 order by batches'
select top 10 * from #queries_staticstics order by tp desc

end
考慮到輸出結(jié)果橫向較長，存儲過程中把writes去掉了。這是因為大部分的數(shù)據(jù)庫系統(tǒng)都是reads為主的。你可以輕易的修改存儲過程把write也包括進去。

　　存儲過程并不復雜，很容易理解?？梢钥吹浇y(tǒng)計的結(jié)果放在queries_staticstics表中，然后按照不同的條件排序后輸出。舉例說：
　　select top 10 * from #queries_staticstics order by cp desc

　　上面的語句將把queries_staticstics表中的記錄按照某類語句占用總cpu量的比例cp(即sum(cpu)/@sum_cpu)進行排序輸出。這讓你在分析服務(wù)器cpu性能問題的時候快速定位哪一類語句最耗cpu資源，從而對癥下藥。

　　現(xiàn)在讓我們看一個實例的輸出：
　　use mydb
　　exec usp_getaccesspattern
　　/*你可以輸入一個執(zhí)行時間作為過濾參數(shù)，毫秒為單位。如usp_getaccesspattern 1000*/

　　輸出結(jié)果如圖 1所示（是部分結(jié)果，另外，因為原輸出結(jié)果橫向很長，為方便閱讀，把結(jié)果從中截斷為兩部分）：

圖 1：輸出結(jié)果采樣一

　　上面的例子采樣于一家大型公司的業(yè)務(wù)系統(tǒng)。該系統(tǒng)的問題是應用程序運行緩慢，sql server 服務(wù)器的cpu高居不下（8個cpu都在90%～100%間波動）。我使用pssdiag工具采樣2小時左右的數(shù)據(jù)，然后運行read80trace和usp_getaccesspattern得出上面的結(jié)果。報表一目了然。存儲過程dbo.x_dedup_proc在兩小時內(nèi)共運行75次，卻占用了90.8％的cpu資源，94.6%的reads，從訪問模式的角度，該存儲過程正是導致cpu高和系統(tǒng)性能慢的關(guān)鍵語句。一旦優(yōu)化了該存儲過程，系統(tǒng)的性能問題將迎刃而解。你也許有疑問，兩小時內(nèi)共運行75次,不是很頻繁啊。其實你看看這條存儲過程的平均cpu時間是681961毫秒，大概11分鐘左右。也就是說一個cpu兩小時內(nèi)最多可以執(zhí)行（60*2）/11＝10條左右，該系統(tǒng)總共有8個cpu，即使全部cpu都用來運行該語句，那么最多也就是10*8＝80條左右。上面執(zhí)行總數(shù)是75，說明該存儲過程一直在連續(xù)不斷地運行。

　　那么該系統(tǒng)運行最頻繁的語句是什么呢？我從結(jié)果中摘取另外一部分如下（圖 2）：

圖 2：輸出結(jié)果采樣二

　　從上表可以看出，最頻繁運行的語句是

　　use xb set quoted_identifier,ansi_null_dflt_on…

　　顯然這是一條執(zhí)行環(huán)境配置語句，沒有參考價值。倒是另外兩條占用語句總數(shù)8.2%的語句值得關(guān)注：
　　select count(*) from x_process_stats where process……
　　select count(*) from x_process_stats where process……

　　在這個例子中，因為關(guān)鍵語句dbo.x_dedup_proc非常突出，甚至上面的兩條語句都可以忽略了。

　　讓我們再多看一個例子（圖 3）：

圖 3：輸出結(jié)果采樣三

　　從上面的例子中, 可以得出關(guān)鍵的語句是:

　　select count(*) from gtbl7ms
　　select caseno from patientdata_sum where mrn = @p1

 　后續(xù)的檢查發(fā)現(xiàn)相關(guān)的表沒有有效的索引，加上索引后性能立即整體地提高了不少.。解決了這兩個語句，需要使用同樣的手段繼續(xù)分析和優(yōu)化，直到系統(tǒng)的性能能夠接受為止.。注意性能調(diào)優(yōu)是一個長期的過程，你不太可能一兩天就可以把所有的問題都解決。也許一開始可以解決80%的問題，但是后面20%的問題卻需要另外80%的時間。

使用usp_getaccesspattern的一些技巧

　　usp_getaccesspattern的輸出報表包含了非常豐富的信息。分析報表的時候需要有大局觀。你也可以有目的性地選擇你需要的信息。如果是cpu性能瓶頸的系統(tǒng)，那么你需要關(guān)注cpu占用比例高的那類語句。如果是磁盤io出現(xiàn)性能瓶頸那么你需要找到那些reads占用比例大而且平均reads也很高的語句。需要注意的是有時候運行頻繁的語句未必就是你需要關(guān)注的關(guān)鍵語句。一個最理想的情況是關(guān)鍵語句正好就是最頻繁的語句。有時候即使最頻繁語句占用的資源比例不高，但如果還可以優(yōu)化，那么因為放大效應，微小的優(yōu)化也會給系統(tǒng)帶來可觀的好處。

　　在使用usp_getaccesspattern的時候多結(jié)合@duration_filter參數(shù)使用。因為參數(shù)以毫秒為單位，建議參數(shù)不要小于1000，而應該是1000的倍數(shù) 如3000，5000等。該參數(shù)常常會給出非常有意思的輸出。該輸出和不帶參數(shù)運行的結(jié)果會有某些重疊。重疊出現(xiàn)的語句通常正是需要關(guān)注的語句。要注意運行最多最密的語句未必有超過1000毫秒的執(zhí)行時間，所有帶參數(shù)運行的結(jié)果有可能不包括最頻繁語句。我常常同時交叉分析四個結(jié)果，一個是不帶參數(shù)運行得到的，另三個分別是使用1000，3000和5000毫秒為參數(shù)運行的結(jié)果。比較分析這四個結(jié)果往往使我對數(shù)據(jù)庫系統(tǒng)的訪問模式有非常清晰透徹的理解。

　　運行存儲過程時你也許會碰到int整數(shù)溢出的錯誤。這是因為表tblbatches 中的reads,cpu 和writes字段是int而不是bigint?？梢赃\行如下語句進行修正：

　　alter table tblbatches alter column reads bigint
　　alter table tblbatches alter column cpu bigint
　　alter table tblbatches alter column writes bigint

　　修正后溢出問題就會解決。

蛇足：哪個是hot 數(shù)據(jù)庫？

　　本文到這里就基本上結(jié)束了。你已經(jīng)知道如何使用read80trace和usp_getaccesspattern得到數(shù)據(jù)庫系統(tǒng)的訪問模式，以及如何從全局的高度去分析訪問模式報表，從而在優(yōu)化系統(tǒng)的時候做到提綱挈領(lǐng)，胸有成竹。

　　除此之外，你還可以應用類似的分析思想得到每個數(shù)據(jù)庫的占用資源比例。這對于sql server有多個數(shù)據(jù)庫的情況非常有用。從報表中你可以立即知道哪個數(shù)據(jù)庫是最hot最消耗系統(tǒng)資源的數(shù)據(jù)庫。語句如下：

print 'group by dbid'
declare @sum_total float,@sum_cpu float,@sum_reads float,@sum_duration float,@sum_writes float
select @sum_total=count(*)*0.01,@sum_cpu=sum(cpu)*0.01,@sum_reads=sum(reads)*0.01,@sum_writes=sum(writes)*0.01,
@sum_duration=sum(duration)*0.01 from tblbatches

select dbid,
ltrim(str(count(*))) exec_stats,''+ str(count(*)/@sum_total,4,1)+'%' execratio,
ltrim(str(sum(cpu)))+' : '++ltrim(str(avg(cpu))) cpu_stats,''+str(sum(cpu)/@sum_cpu,4,1)+'%' cpuratio ,
ltrim(str(sum(reads) ))+' : '+ltrim(str(avg(reads) )) reads_stats,''+str(sum(reads)/@sum_reads,4,1) +'%' readsratio ,
ltrim(str(sum(duration) ))+' : '+ltrim(str(avg(duration))) duration_stats,''+str(sum(duration)/@sum_duration,4,1)+'%' durratio ,
count(*)/@sum_total tp,sum(cpu)/@sum_cpu cp,sum(reads)/@sum_reads rp,sum(duration)/@sum_duration dp
into #queries_staticstics_groupbydb from

(select reads,cpu,duration,writes,convert(varchar(2000),normtext)textdata,dbid from tblbatches
inner join tbluniquebatches on tblbatches.hashid=tbluniquebatches.hashid
) b group by dbid order by sum(reads) desc

select dbid,execratio batches,cpuratio cpu,readsratio reads,durratio duration
from #queries_staticstics_groupbydb

　　下面是一個上面語句結(jié)果的一個例子：
　　dbid 　batches 　cpu 　reads　 duration
　　------ 　-------　 ----- 　-------　 --------
　　37 　　21.1% 　18.7% 　29.1%　 27.1%
　　33 　　12.7% 　32.4% 　19.5% 　24.8%
　　36 　　5.6% 　28.3% 　15.6% 　26.1%
　　20 　　53.9% 　2.9% 　14.2% 　2.1%
　　22 　　0.8% 　　7.2% 　13.2% 　6.6%
　　25 　　1.0%　　 3.6% 　5.4% 　3.5%
　　16 　　0.0% 　　1.5% 　1.9% 　0.7%
　　35 　　2.0% 　　2.7% 　1.8% 　5.7%
　　7 0.　　1% 　　0.1% 　1.1% 　　0.3%

　　上面的結(jié)果明確地告訴我們id為37，33和36的數(shù)據(jù)庫是最活躍的數(shù)據(jù)庫。一個有趣的事實是數(shù)據(jù)庫20發(fā)出的語句總數(shù)比例是53.9%，但是其占用的系統(tǒng)資源比例卻不高。