SQL Server手把手教你使用profile進(jìn)行性能監(jiān)控

介紹

經(jīng)常會(huì)有人問(wèn)profile工具該怎么使用？有沒(méi)有方法獲取性能差的sql的問(wèn)題。自從轉(zhuǎn)mysql我自己也差不多2年沒(méi)有使用profile，忽然profile變得有點(diǎn)生疏不得不重新熟悉一下。這篇文章主要對(duì)profile工具做一個(gè)詳細(xì)的介紹；包括工具的用途和使用方法等。profile是SQLServer自帶的一個(gè)性能分析監(jiān)控工具，它也可以生成數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)分析需要的負(fù)載數(shù)據(jù)，比如開(kāi)發(fā)對(duì)功能進(jìn)行調(diào)試需要收集執(zhí)行sql使用profile就是一個(gè)非常好的辦法，profile主要用于在線(xiàn)實(shí)時(shí)監(jiān)控和收集數(shù)據(jù)用于后期的分析使用，它可以將收集的數(shù)據(jù)保存成文件和插入到表。?

跟蹤屬性

一、常規(guī)

將跟蹤的記錄保存到指定的文件。

1.最大文件大小

指定最大文件大小的跟蹤在達(dá)到最大文件大小時(shí)，會(huì)停止將跟蹤信息保存到該文件。使用此選項(xiàng)可將事件分組成更小、更容易管理的文件。此外，限制文件大小使得無(wú)人參與的跟蹤運(yùn)行起來(lái)更加安全，因?yàn)楦檿?huì)在達(dá)到最大文件大小后停止。可以為通過(guò) Transact-SQL 存儲(chǔ)過(guò)程或使用 SQL Server Profiler創(chuàng)建的跟蹤設(shè)置最大文件大小。

最大文件大小選項(xiàng)的上限為 1 GB。默認(rèn)最大文件大小為 5 MB

注意：最大文件的大小建議不要設(shè)的太大，特別是需要用于數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)使用的文件，太大的跟蹤文件需要很長(zhǎng)的分析的時(shí)間而且由于數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)也是把收集的負(fù)載文件執(zhí)行一遍有時(shí)候可能會(huì)導(dǎo)致負(fù)載過(guò)大分析失敗，同時(shí)對(duì)服務(wù)器的壓力持續(xù)的時(shí)間過(guò)長(zhǎng)對(duì)業(yè)務(wù)影響也會(huì)比較大，一般設(shè)置幾兆或者幾十兆即可，同時(shí)啟動(dòng)文件滾動(dòng)更新，多次分析。

2.啟用文件滾動(dòng)更新

如果使用文件滾動(dòng)更新選項(xiàng)，則在達(dá)到最大文件大小時(shí)，SQL Server 會(huì)關(guān)閉當(dāng)前文件并創(chuàng)建一個(gè)新文件。新文件與原文件同名，但是文件名后將追加一個(gè)整數(shù)以表示其序列。例如，如果原始跟蹤文件命名為 filename_1.trc，則下一跟蹤文件為 filename_2.trc，依此類(lèi)推。如果指定給新滾動(dòng)更新文件的名稱(chēng)已經(jīng)被現(xiàn)有文件使用，則將覆蓋現(xiàn)有文件，除非現(xiàn)有文件為只讀文件。默認(rèn)情況下，將跟蹤數(shù)據(jù)保存到文件時(shí)，會(huì)啟用文件滾動(dòng)更新選項(xiàng)。

3.服務(wù)器處理跟蹤數(shù)據(jù)

確保服務(wù)器記錄每個(gè)跟蹤事件，如果記錄事件會(huì)顯著降低性能，可以清除服務(wù)器處理跟蹤數(shù)據(jù)，這樣服務(wù)器不會(huì)再記錄事件。 4.最大行數(shù)

指定有最大行數(shù)的跟蹤在達(dá)到最大行數(shù)時(shí)，會(huì)停止將跟蹤信息保存到表。每個(gè)事件構(gòu)成一行，因此該參數(shù)可設(shè)置收集的事件數(shù)的范圍。設(shè)置最大行數(shù)使得無(wú)人參與的跟蹤運(yùn)行起來(lái)更加方便。例如，如果需要啟動(dòng)一個(gè)將跟蹤數(shù)據(jù)保存到表的跟蹤，同時(shí)希望在該表變得過(guò)大時(shí)停止跟蹤，則可以使其自動(dòng)停止。

如果已指定并且達(dá)到了最大行數(shù)，將在運(yùn)行 SQL Server Profiler的同時(shí)繼續(xù)運(yùn)行跟蹤，但不再記錄跟蹤信息。SQL Server Profiler將繼續(xù)顯示跟蹤結(jié)果，直到跟蹤停止

5.啟用跟蹤停止時(shí)間?

啟用跟蹤停止時(shí)間之后，到了指定的時(shí)間跟蹤自動(dòng)停止。每一次跟蹤建議都必須得設(shè)置一個(gè)跟蹤停止時(shí)間防止忘記關(guān)閉跟蹤導(dǎo)致服務(wù)器空間被占滿(mǎn)，默認(rèn)跟蹤1小時(shí)。

?

注意：

從 SQL Server 2005 開(kāi)始，服務(wù)器以微秒（百萬(wàn)分之一秒或 10^-6?秒）為單位報(bào)告事件的持續(xù)時(shí)間，以毫秒（千分之一秒或 10^-3?秒）為單位報(bào)告事件使用的 CPU 時(shí)間。在 SQL Server 2000 中，服務(wù)器以毫秒為單位報(bào)告持續(xù)時(shí)間和 CPU 時(shí)間。在 SQL Server 2005 及更高版本中，SQL Server Profiler圖形用戶(hù)界面默認(rèn)以毫秒為單位顯示“持續(xù)時(shí)間”列，但是當(dāng)跟蹤保存到文件或數(shù)據(jù)庫(kù)表中之后，將以微秒為單位在“持續(xù)時(shí)間”列中寫(xiě)入值。二、事件選擇

對(duì)于不同跟蹤選擇不同的跟蹤事件；通過(guò)勾選“顯示所有跟蹤事件”可以看到所有的跟蹤事件，總共有21個(gè)事件分類(lèi)。用得最多的兩個(gè)分類(lèi)就是存儲(chǔ)過(guò)程和TSQL這兩個(gè)分類(lèi)主要用來(lái)記錄執(zhí)行的存儲(chǔ)過(guò)程和SQL語(yǔ)句，把鼠標(biāo)移動(dòng)到具體的事件上面會(huì)顯示該事件和事件列的具體說(shuō)明，接下來(lái)就分析幾個(gè)常用的事件和常用的事件列。

1.顯示所有跟蹤事件

勾選之后會(huì)將所有的事件都顯示出來(lái)

2.顯示所有列

勾選之后會(huì)將所有的列顯示出來(lái)

3.列篩選

對(duì)列增加一些條件，其實(shí)可以將它理解在TSQL語(yǔ)句的WHERE后面添加條件，對(duì)于整形列直接輸入數(shù)值即可，對(duì)于字符串列就相當(dāng)于like一樣使用不帶引號(hào)的%%模糊匹配方法。通過(guò)勾選“排除不包含值的行”之后跟蹤結(jié)果就會(huì)篩選掉不滿(mǎn)足條件的記錄。

4.列組織

列組織可以理解成TSQL語(yǔ)句里面做GROUP BY操作，可以將相同的條件放在一起去重。

?

事件

1.SQL:Stmt*******

[SQL:StmtStarting]:啟動(dòng)TSQL語(yǔ)句時(shí)記錄

[SQL:StmtCompleted]:完成TSQL語(yǔ)句時(shí)記錄

這兩事件的區(qū)別也同單詞的意思一樣，StmtStarting是記錄事件的開(kāi)始不關(guān)注這個(gè)事件在接下來(lái)會(huì)做什么，StmtCompleted是記錄事件結(jié)束之后在開(kāi)始和結(jié)束這個(gè)過(guò)程中做的一些操作比如一些常用的列"Duration","Cpu","Reads","Writes","EndTime"這些列就會(huì)出現(xiàn)在StmtCompleted事件中。所以如果你需要收集的記錄不關(guān)心整個(gè)事件過(guò)程中的操作只需要收集數(shù)量那么可以使用Starting事件比如記錄某個(gè)語(yǔ)句或者存儲(chǔ)過(guò)程執(zhí)行的次數(shù)等。

2.SQL:Batch******

[SQL:BatchStarting]:啟動(dòng)TSQL批處理時(shí)記錄

[SQL:BatchCompleted]:完成TSQL批處理時(shí)記錄

?

這次我把兩個(gè)select語(yǔ)句放在一起來(lái)執(zhí)行，可以從batch事件中可以看到它記錄的整個(gè)批處理的SQL同時(shí)還包括相關(guān)注釋?zhuān)瑫r(shí)整個(gè)批處理兩個(gè)TSQL作為一條事件記錄，而stmt事件記錄具體的TSQL語(yǔ)句把兩個(gè)TSQL語(yǔ)句作為兩條記錄來(lái)記錄。同時(shí)還可以發(fā)現(xiàn)兩個(gè)TSQL的Duration相加是小于整個(gè)批處理的duration的，這也是正常的整個(gè)批處理在sql編譯分析執(zhí)行這塊肯定比單個(gè)TSQL需要耗費(fèi)更多的時(shí)間，但是相差也是非常的小。

?

batchcompleted事件多用于引擎優(yōu)化顧問(wèn)，而stmtcompleted事用于分析單個(gè)TSQL語(yǔ)句。同樣Stored分類(lèi)里面的starting事件和completed事件和TSQL里面的是一樣的意思。

事件列

列舉常用的事件列

TextData:文本詳細(xì)信息，比如詳細(xì)的執(zhí)行SQL語(yǔ)句等等。

ApplicationName:連接SQLSever的客戶(hù)端應(yīng)用程序名稱(chēng)。

NTUserName:windows用戶(hù)名

LoginName:SQLServer登入用戶(hù)名。

CPU:事件占用的CPU時(shí)間，在圖形化界面但是是毫秒（千分之一秒或 10^-3?秒），在文本文件或者數(shù)據(jù)庫(kù)表中單位是微妙（百萬(wàn)分之一秒或 10^-6?秒）。

Reads:執(zhí)行邏輯讀的次數(shù)。

Writes:物理磁盤(pán)寫(xiě)入的次數(shù)。

Duration:事件的持續(xù)時(shí)間，也就是統(tǒng)計(jì)信息里面顯示的占用時(shí)間，在圖形化界面但是是毫秒（千分之一秒或 10^-3?秒），在文本文件或者數(shù)據(jù)庫(kù)表中單位是微妙（百萬(wàn)分之一秒或 10^-6?秒）

ClientProcessID:調(diào)用SQLServer的應(yīng)用程序進(jìn)程ID。

SPID:SQLServer為連接分配的數(shù)據(jù)庫(kù)進(jìn)程ID，也就是sys.processes里面記錄的進(jìn)程ID。

StartTime:事件的開(kāi)始時(shí)間。

EndTime:事件的結(jié)束時(shí)間。

DBUserName:客戶(hù)端的sqlserver用戶(hù)名。

DatabaseID:如果指定了USE database就是指定的數(shù)據(jù)庫(kù)id,否則就是默認(rèn)的數(shù)據(jù)庫(kù)id(也就是master的數(shù)據(jù)庫(kù)id)。所以該列的作用不是很大。

Error:事件的錯(cuò)誤號(hào)，通常是sysmessage中存儲(chǔ)的錯(cuò)誤號(hào)。

ObjectName:正在引用的對(duì)象名稱(chēng)。

三、自帶跟蹤模板

工具自帶了幾個(gè)比較實(shí)用的跟蹤模板，一般的跟蹤都可以直接使用自帶的跟蹤模板解決，同時(shí)自己也可以創(chuàng)建自定義的跟蹤事件和跟蹤屬性保存成模板供以后使用。

SP_Counts:計(jì)算已運(yùn)行的存儲(chǔ)過(guò)程數(shù)，并且按存儲(chǔ)過(guò)程的名稱(chēng)進(jìn)行分組統(tǒng)計(jì)，此模板可以分析某時(shí)間段存儲(chǔ)過(guò)程的行為。

Standard:記錄所有存儲(chǔ)過(guò)程和T-SQL語(yǔ)句批處理運(yùn)行的時(shí)間，當(dāng)你想要監(jiān)視常規(guī)數(shù)據(jù)庫(kù)服務(wù)器活動(dòng)時(shí)即可使用該模板，一般的跟蹤需要使用該模板就可以解決，這也是默認(rèn)的模板。

TSQL:記錄客戶(hù)端提交給sqlserver的所有T-SQL語(yǔ)句的的內(nèi)容和開(kāi)始時(shí)間，通常使用該模板用于程序調(diào)試。

TSQL_Duration:記錄客戶(hù)端提交給sqlserver的所有T-SQL語(yǔ)句批處理信息以及執(zhí)行這些語(yǔ)句所需的時(shí)間（毫秒），并按時(shí)間進(jìn)行分組，使用該模板可以分析執(zhí)行慢的查詢(xún)，此模板的跟蹤記錄可以用于數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)分析使用。

TSQL_Grouped:按提交客戶(hù)端和登入用戶(hù)進(jìn)行分組記錄所有提交給SQLServer的T-SQL批處理語(yǔ)句及其開(kāi)始時(shí)間，此模板用于分析某個(gè)客戶(hù)或者用戶(hù)執(zhí)行的查詢(xún)。

TSQL_Locks：記錄所有開(kāi)始和完成的存儲(chǔ)過(guò)程和T-SQL語(yǔ)句，同時(shí)記錄死鎖信息，此模板用于跟蹤死鎖。

TSQL_Replay:記錄有關(guān)已發(fā)出的T-SQL語(yǔ)句的詳細(xì)信息，此模板記錄重播跟蹤所需的信息，此模板可執(zhí)行跌到優(yōu)化，例如基準(zhǔn)測(cè)試。

TSQL_SPs:記錄有關(guān)執(zhí)行的所有存儲(chǔ)過(guò)程的詳細(xì)信息，此模板可以分析存儲(chǔ)過(guò)程的組成步驟。如果你懷疑正在重新編譯存儲(chǔ)過(guò)程，請(qǐng)?zhí)砑覵P:Recomple事件

Tuning:記錄有關(guān)存儲(chǔ)和T-SQL語(yǔ)句批處理的信息以及執(zhí)行這些語(yǔ)句所需的時(shí)間（毫秒），使用此模板生產(chǎn)跟蹤輸出可用于數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)工作負(fù)載來(lái)優(yōu)化索引、優(yōu)化性能。此模板和TSQL_Druation相似后者是做了時(shí)間分組。

?

數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)

1.如果需要用數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)分析跟蹤事件記錄必須捕獲了以下跟蹤事件：

RPC:Completed

SQL:BatchCompleted

SP:StmtCompleted

也可以使用這些跟蹤事件的?Starting?版本。?例如，SQL:BatchStarting。?但是，這些跟蹤事件的?Completed?版本包括?Duration?列，它能使數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)更有效地優(yōu)化工作負(fù)荷。?數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)不優(yōu)化其他類(lèi)型的跟蹤事件。

2.包含 LoginName列

數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)在優(yōu)化過(guò)程中提交顯示計(jì)劃請(qǐng)求。?當(dāng)包含?LoginName?數(shù)據(jù)列的跟蹤表或跟蹤文件被用作工作負(fù)荷時(shí)，數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)將模擬?LoginName?中指定的用戶(hù)。?如果沒(méi)有為此用戶(hù)授予 SHOWPLAN 權(quán)限（該權(quán)限使用戶(hù)能夠?yàn)楦欀邪恼Z(yǔ)句執(zhí)行和生成顯示計(jì)劃），數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)將不會(huì)優(yōu)化這些語(yǔ)句。?

避免為跟蹤的 LoginName 列中指定的每個(gè)用戶(hù)授予 SHOWPLAN 權(quán)限

通過(guò)從未優(yōu)化的事件中刪除?LoginName?列來(lái)創(chuàng)建新的工作負(fù)荷，然后只將未優(yōu)化的事件保存到新的跟蹤文件或跟蹤表中。

將不帶?LoginName?列的新工作負(fù)荷重新提交到數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)。

數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)將優(yōu)化新的工作負(fù)荷，因?yàn)楦欀形粗付ǖ卿浶畔ⅰ?如果某個(gè)語(yǔ)句沒(méi)有相應(yīng)的?LoginName，數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)將通過(guò)模擬啟動(dòng)優(yōu)化會(huì)話(huà)的用戶(hù)（sysadmin?固定服務(wù)器角色或?db_owner?固定數(shù)據(jù)庫(kù)角色的成員）來(lái)優(yōu)化該語(yǔ)句。

3.數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)不能執(zhí)行下列操作：

建議對(duì)系統(tǒng)表建立索引。

添加或刪除唯一索引或強(qiáng)制 PRIMARY KEY 或 UNIQUE 約束的索引。

優(yōu)化單用戶(hù)數(shù)據(jù)庫(kù)。

4.數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)具有下列限制：

數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)通過(guò)數(shù)據(jù)采樣收集統(tǒng)計(jì)信息。因此，在相同的工作負(fù)荷上重復(fù)運(yùn)行該工具可能生成不同的結(jié)果。

數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)不能用于優(yōu)化 Microsoft SQL Server 7.0 或更早版本的數(shù)據(jù)庫(kù)中的索引。

如果為優(yōu)化建議指定的最大磁盤(pán)空間超過(guò)了可用空間，數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)將使用指定的值。但是，當(dāng)您執(zhí)行建議腳本來(lái)實(shí)施它時(shí)，如果未先添加更多磁盤(pán)空間，則腳本會(huì)失敗?？梢允褂?dta?實(shí)用工具的?-B?選項(xiàng)指定最大磁盤(pán)空間，也可以通過(guò)在“高級(jí)優(yōu)化選項(xiàng)”對(duì)話(huà)框中輸入值來(lái)指定最大磁盤(pán)空間。

為了安全起見(jiàn)，數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)不能優(yōu)化駐留在遠(yuǎn)程服務(wù)器上的跟蹤表中的工作負(fù)荷。若要解除此限制，可以選擇以下選項(xiàng)之一：

使用跟蹤文件而不使用跟蹤表。

將跟蹤表復(fù)制到遠(yuǎn)程服務(wù)器。

當(dāng)強(qiáng)制實(shí)施約束時(shí)，例如為優(yōu)化建議指定最大磁盤(pán)空間時(shí)強(qiáng)制的約束（通過(guò)使用?-B?選項(xiàng)或“高級(jí)優(yōu)化選項(xiàng)”對(duì)話(huà)框），數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)可能會(huì)被迫刪除某些現(xiàn)有的索引。在此情況下，生成的數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)建議可能生成負(fù)的預(yù)期提高值。

指定限制優(yōu)化時(shí)間的約束時(shí)（通過(guò)使用?dta?實(shí)用工具的?-A?選項(xiàng)或通過(guò)選擇“優(yōu)化選項(xiàng)”選項(xiàng)卡上的“限制優(yōu)化時(shí)間”），數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)可能超過(guò)該時(shí)間限制，以便針對(duì)到當(dāng)時(shí)為止已處理的工作負(fù)荷，生成精確預(yù)期的提高值和分析報(bào)告。

5.數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)可能在下列情況下不提供建議：

正在優(yōu)化的表所包含的數(shù)據(jù)頁(yè)數(shù)少于 10。

建議的索引對(duì)當(dāng)前物理數(shù)據(jù)庫(kù)設(shè)計(jì)的查詢(xún)性能預(yù)計(jì)帶來(lái)的提高值不夠。

運(yùn)行數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)的用戶(hù)不是?db_owner?數(shù)據(jù)庫(kù)角色或?sysadmin?固定服務(wù)器角色的成員。工作負(fù)荷中的查詢(xún)?cè)谶\(yùn)行數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)的用戶(hù)的安全上下文中進(jìn)行分析。該用戶(hù)必須是?db_owner?數(shù)據(jù)庫(kù)角色的成員。

6.數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)可能在下列情況下不提供分區(qū)建議：

未啟用?xp_msver?擴(kuò)展存儲(chǔ)過(guò)程。此擴(kuò)展存儲(chǔ)過(guò)程用于提取要優(yōu)化的數(shù)據(jù)庫(kù)所在服務(wù)器上的處理器數(shù)目以及可用內(nèi)存。請(qǐng)注意，安裝 SQL Server 后，默認(rèn)情況下，此擴(kuò)展存儲(chǔ)過(guò)程處于打開(kāi)狀態(tài)。有關(guān)詳細(xì)信息，請(qǐng)參閱了解外圍應(yīng)用配置器和 xp_msver (Transact-SQL)。

7.性能注意事項(xiàng)

在分析過(guò)程中，數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)可能占用相當(dāng)多的處理器及內(nèi)存資源。若要避免降低生產(chǎn)服務(wù)器速度，請(qǐng)采用下列策略之一：

在服務(wù)器空閑時(shí)優(yōu)化數(shù)據(jù)庫(kù)。數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)可能影響維護(hù)任務(wù)性能。

使用測(cè)試服務(wù)器/生產(chǎn)服務(wù)器功能。有關(guān)詳細(xì)信息，請(qǐng)參閱減輕生產(chǎn)服務(wù)器優(yōu)化負(fù)荷。

指定數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)僅分析物理數(shù)據(jù)庫(kù)設(shè)計(jì)結(jié)構(gòu)。數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)提供許多選項(xiàng)，但是請(qǐng)僅指定所需選項(xiàng)。

注意：由于數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)進(jìn)行性能優(yōu)化時(shí)也是將負(fù)載記錄中的語(yǔ)句執(zhí)行一篇查詢(xún)分析執(zhí)行計(jì)劃的操作，所以對(duì)服務(wù)器同樣存在壓力。特別是對(duì)于大的負(fù)載分析可能需要分析一個(gè)小時(shí)甚至更長(zhǎng)，這樣可能會(huì)持續(xù)對(duì)服務(wù)器造成壓力，所以避免在業(yè)務(wù)高峰期進(jìn)行使用引擎優(yōu)化顧問(wèn)進(jìn)行負(fù)載分析。

實(shí)例?

接下來(lái)就列舉三個(gè)案例，使用數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)來(lái)分析跟蹤記錄優(yōu)化索引的案例、監(jiān)控死鎖的案例、創(chuàng)建自定義跟蹤模板案例。

案例1：優(yōu)化索引

1.創(chuàng)建測(cè)試數(shù)據(jù)

--創(chuàng)建測(cè)試表
CREATE?TABLE?[dbo].[book](
????[id]?[int]?NOT?NULL?PRIMARY?KEY,
????[name]?[varchar](50)?NULL);


--插入10W條測(cè)試數(shù)據(jù)
DECLARE?@id?int
SET?@id=1
WHILE?@id<100000
BEGIN
INSERT?INTO?book?values(@id,CONVERT(varchar(20),@id))

SET?@id=@id+1
END;

2.創(chuàng)建跟蹤

這里使用默認(rèn)的跟蹤模板“tuning”

1.創(chuàng)建好跟蹤后點(diǎn)擊運(yùn)行即可，事件選擇這里保持默認(rèn)

2.執(zhí)行SQL

SELECT?*?FROM?book?WHERE?name='10001';

由于name字段沒(méi)有建索引所以該查詢(xún)執(zhí)行計(jì)劃分析過(guò)后會(huì)返回創(chuàng)建name字段的索引，通過(guò)引擎優(yōu)化顧問(wèn)分析同樣如此

3.停止跟蹤

在使用數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)分析負(fù)載跟蹤之前必須先停止跟蹤。

4.打開(kāi)數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)

可以直接在profile的工具欄選擇打開(kāi)，“文件”選擇剛才的跟蹤文件，“負(fù)載數(shù)據(jù)庫(kù)”選擇需要進(jìn)行優(yōu)化的數(shù)據(jù)庫(kù)，“選擇要優(yōu)化的數(shù)據(jù)庫(kù)和表”也就需要優(yōu)化的數(shù)據(jù)庫(kù)的相關(guān)表。優(yōu)化選項(xiàng)沒(méi)有特別的需求選擇默認(rèn)即可，然后點(diǎn)擊“開(kāi)始分析”。

引擎優(yōu)化顧問(wèn)會(huì)自動(dòng)生成創(chuàng)建索引的腳步，同時(shí)還給出了創(chuàng)建該索引之后預(yù)計(jì)性能可以提供的百分比，如果同時(shí)存在很多表的索引建議可以勾選需要保存的建議保存成sql文件在“開(kāi)始分析”欄旁邊有一個(gè)保存建議的按鈕可以將建議保存成sql文件。

建議：

1.數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)給出的建議不是每一個(gè)都是對(duì)的，自己對(duì)比該SQL的執(zhí)行頻率來(lái)判斷是否需要?jiǎng)?chuàng)建該索引，比如我當(dāng)前這個(gè)SQL如果我這個(gè)SQL只執(zhí)行了一次后面就不會(huì)再執(zhí)行了那么這個(gè)索引就沒(méi)必要?jiǎng)?chuàng)建了。

2.修改引擎優(yōu)化顧問(wèn)給出的索引名，數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)給出的創(chuàng)建索引的索引名不夠直觀(guān)，建議自己手動(dòng)更改，比如改成“ix_book_name”,“索引標(biāo)示_表名_字段描述”的規(guī)則。

3.用來(lái)分析的文件不要太大否則可能會(huì)分析不完成，不要在業(yè)務(wù)高峰期進(jìn)行分析。

案例2：監(jiān)控死鎖

1.創(chuàng)建跟蹤

?

模板選擇自帶的“TSQL_Locks”模板，運(yùn)行跟蹤。

2.執(zhí)行SQL

打開(kāi)兩個(gè)會(huì)話(huà)窗口分表執(zhí)行如下SQL，先在會(huì)話(huà)1執(zhí)行然后在10S內(nèi)在會(huì)話(huà)2中執(zhí)行，兩個(gè)會(huì)話(huà)擁有各自的排他鎖同時(shí)又去申請(qǐng)對(duì)方擁有的排他鎖造成死鎖。

會(huì)話(huà)1執(zhí)行：當(dāng)前會(huì)話(huà)1是62

BEGIN?TRANSACTION
UPDATE?book?
SET?name='a'
WHERE?ID=10

--延時(shí)10s執(zhí)行
waitfor?delay?'0:0:10'

UPDATE?book?
SET?name='a'
WHERE?ID=100

會(huì)話(huà)2執(zhí)行：當(dāng)前會(huì)話(huà)2是

BEGIN?TRANSACTION
UPDATE?book?
SET?name='b'
WHERE?ID=100

--延時(shí)20執(zhí)行
waitfor?delay?'0:0:20'

UPDATE?book?
SET?name='b'
WHERE?ID=10

msms客戶(hù)端返回的錯(cuò)誤消息顯示當(dāng)前62會(huì)話(huà)作為死鎖的犧牲品。

3.跟蹤分析死鎖

?死鎖跟蹤事件使用圖形和直觀(guān)的返回了兩個(gè)會(huì)話(huà)的死鎖，其中62會(huì)話(huà)用了一個(gè)×表示當(dāng)前的會(huì)話(huà)是死鎖的犧牲品。

案例三：創(chuàng)建自定義跟蹤模板

?標(biāo)準(zhǔn)模板就是一個(gè)比較好的參考模板，比如我們對(duì)執(zhí)行語(yǔ)句進(jìn)行監(jiān)控就可以參考標(biāo)準(zhǔn)模板在其基礎(chǔ)上修改保存成自己的模板。

1.創(chuàng)建TSQL語(yǔ)句跟蹤

2.創(chuàng)建跟蹤模板

停止當(dāng)前的TSQL跟蹤，選擇“文件”-“另存為跟蹤模板”就可以保存成自己的跟蹤模板。

3.列篩選

?

當(dāng)前是篩選跟蹤的TSQL語(yǔ)句中包含book，這里的列篩選這執(zhí)行 where like 的語(yǔ)法類(lèi)似。

整形列的話(huà)就不需要帶模糊條件：

注意：如果要取消列篩選記得把剛才的篩選條件刪除同時(shí)把“排除不包含值的行” 的勾選也去除，記得兩者都要去掉否則跟蹤還是包含篩選的跟蹤。

4.列組織

列組織其實(shí)就是按某列進(jìn)行分組顯示跟蹤，類(lèi)似select查詢(xún)里面的group by操作。比如我當(dāng)前按持續(xù)時(shí)間進(jìn)行分組跟蹤。

通過(guò)對(duì)持續(xù)時(shí)間進(jìn)行分組，相同的持續(xù)時(shí)間會(huì)放在一個(gè)分組里。

總結(jié)

?由于篇幅有限列舉了一些簡(jiǎn)單常用的操作，其它的分類(lèi)監(jiān)控的方法類(lèi)似有興趣可以多去研究，profile是非常實(shí)用且界面化很好的監(jiān)控工具這也是SQLServer獨(dú)特的條件，應(yīng)該熟練運(yùn)用。