SQL Server profile提示

標籤：精確   int   使用者數   登入   插入   分享   only   模糊   執行   

介紹

經常會有人問profile工具該怎麼使用？有沒有方法擷取效能差的sql的問題。自從轉mysql我自己也差不多2年沒有使用profile，忽然profile變得有點生疏不得不重新熟悉一下。這篇文章主要對profile工具做一個詳細的介紹；包括工具的用途和使用方法等。profile是SQLServer內建的一個效能分析監控工具，它也可以產生Database Engine Tuning Advisor分析需要的負載資料，比如開發對功能進行調試需要收集執行sql使用profile就是一個非常好的辦法，profile主要用於線上即時監控和收集資料用於後期的分析使用，它可以將收集的資料儲存成檔案和插入到表。

 

 

跟蹤屬性 一、常規

將跟蹤的記錄儲存到指定的檔案。

1.最大檔案大小

指定最大檔案大小的跟蹤在達到最大檔案大小時，會停止將跟蹤資訊儲存到該檔案。使用此選項可將事件分組成更小、更容易管理的檔案。此外，限制檔案大小使得無人蔘與的跟蹤運行起來更加安全，因為跟蹤會在達到最大檔案大小後停止。可以為通過 Transact-SQL 預存程序或使用 SQL Server Profiler建立的跟蹤設定最大檔案大小。

最大檔案大小選項的上限為 1 GB。預設最大檔案大小為 5 MB

注意：最大檔案的大小建議不要設的太大，特別是需要用於Database Engine Tuning Advisor使用的檔案，太大的追蹤檔案需要很長的分析的時間而且由於Database Engine Tuning Advisor也是把收集的負載檔案執行一遍有時候可能會導致負載過大分析失敗，同時對伺服器的壓力持續的時間過長對業務影響也會比較大，預設大小即可，同時開機檔案變換，多次分析。

2.啟用檔案換用

如果使用檔案換用選項，則在達到最大檔案大小時，SQL Server 會關閉當前檔案並建立一個新檔案。新檔案與原檔案同名，但是檔案名稱後將追加一個整數以表示其序列。例如，如果原始追蹤檔案命名為 filename_1.trc，則下一追蹤檔案為 filename_2.trc，依此類推。如果指定給新換用檔案的名稱已經被現有檔案使用，則將覆蓋現有檔案，除非現有檔案為唯讀檔案。預設情況下，將跟蹤資料儲存到檔案時，會啟用檔案換用選項。

3.伺服器處理跟蹤資料

確保伺服器記錄每個跟蹤事件，如果記錄事件會顯著降低效能，可以清除伺服器處理跟蹤資料，這樣伺服器不會再記錄事件。4.最大行數

指定有最大行數的跟蹤在達到最大行數時，會停止將跟蹤資訊儲存到表。每個事件構成一行，因此該參數可設定收集的事件數目的範圍。設定最大行數使得無人蔘與的跟蹤運行起來更加方便。例如，如果需要啟動一個將跟蹤資料儲存到表的跟蹤，同時希望在該表變得過大時停止跟蹤，則可以使其自動停止。

如果已指定並且達到了最大行數，將在運行 SQL Server Profiler的同時繼續運行跟蹤，但不再記錄跟蹤資訊。SQL Server Profiler將繼續顯示跟蹤結果，直到跟蹤停止

5.啟用跟蹤停止時間 

啟用跟蹤停止時間之後，到了指定的時間跟蹤自動停止。每一次跟蹤建議都必須得設定一個跟蹤停止時間防止忘記關閉跟蹤導致伺服器空間被佔滿，預設跟蹤1小時。

 

注意：

• 從 SQL Server 2005 開始，伺服器以微秒（百萬分之一秒或 10-6 秒）為單位報告事件的期間，以毫秒（千分之一秒或 10-3 秒）為單位報告事件使用的 CPU 時間。
• 在 SQL Server 2000 中，伺服器以毫秒為單位報告期間和 CPU 時間。
• 在 SQL Server 2005 及更高版本中，SQL Server Profiler圖形化使用者介面預設以毫秒為單位顯示“期間”列，但是當跟蹤儲存到檔案或資料庫表中之後，將以微秒為單位在“期間”列中寫入值。

二、事件選擇

對於不同跟蹤選擇不同的跟蹤事件；通過勾選“顯示所有跟蹤事件”可以看到所有的跟蹤事件，總共有21個事件分類。用得最多的兩個分類就是預存程序和TSQL這兩個分類主要用來記錄執行的預存程序和SQL語句，把滑鼠移動到具體的事件上面會顯示該事件和事件列的具體說明，接下來就分析幾個常用的事件和常用的事件列。

1.顯示所有跟蹤事件

勾選之後會將所有的事件都顯示出來

2.顯示所有列

勾選之後會將所有的列顯示出來

3.列篩選

對列增加一些條件，其實可以將它理解在TSQL語句的WHERE後面添加條件，對於整形列直接輸入數值即可，對於字串列就相當於like一樣使用不帶引號的%%模糊比對方法。通過勾選“排除不包含值的行”之後跟蹤結果就會篩選掉不滿足條件的記錄。

4.列組織

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

 

事件

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

[SQL:StmtStarting]:啟動TSQL語句時記錄

[SQL:StmtCompleted]:完成TSQL語句時記錄

這兩事件的區別也同單詞的意思一樣，StmtStarting是記錄事件的開始不關注這個事件在接下來會做什麼，StmtCompleted是記錄事件結束之後在開始和結束這個過程中做的一些操作比如一些常用的列"Duration","Cpu","Reads","Writes","EndTime"這些列就會出現在StmtCompleted事件中。所以如果你需要收集的記錄不關心整個事件程序中的操作只需要收集數量那麼可以使用Starting事件比如記錄某個語句或者預存程序執行的次數等。

2.SQL:Batch******

[SQL:BatchStarting]:啟動TSQL批處理時記錄

[SQL:BatchCompleted]:完成TSQL批處理時記錄

 

這次我把兩個select語句放在一起來執行，可以從batch事件中可以看到它記錄的整個批處理的SQL同時還包括相關注釋，同時整個批處理兩個TSQL作為一條事件記錄，而stmt事件記錄具體的TSQL語句把兩個TSQL語句作為兩條記錄來記錄。同時還可以發現兩個TSQL的Duration相加是小於整個批處理的duration的，這也是正常的整個批處理在sql編譯分析執行這塊肯定比單個TSQL需要耗費更多的時間，但是相差也是非常的小。

 

batchcompleted事件多用於引擎最佳化顧問，而stmtcompleted事用於分析單個TSQL語句。同樣Stored分類裡面的starting事件和completed事件和TSQL裡面的是一樣的意思。

事件列

列舉常用的事件列

TextData:文本詳細資料，比如詳細的執行SQL語句等等。

ApplicationName:串連SQLSever的用戶端應用程式名稱。

NTUserName:windows使用者名稱

LoginName:SQLServer登入使用者名稱。

CPU:事件佔用的CPU時間，在圖形化介面但是是毫秒（千分之一秒或 10-3 秒），在文字檔或者資料庫表中單位是微妙（百萬分之一秒或 10-6 秒）。

Reads:執行邏輯讀的次數。

Writes:物理磁碟寫入的次數。

Duration:事件的期間，也就是統計資訊裡面顯示的佔用時間，在圖形化介面但是是毫秒（千分之一秒或 10-3 秒），在文字檔或者資料庫表中單位是微妙（百萬分之一秒或 10-6 秒）

ClientProcessID:調用SQLServer的應用程式進程ID。

SPID:SQLServer為串連分配的資料庫進程ID，也就是sys.processes裡面記錄的進程ID。

StartTime:事件的開始時間。

EndTime:事件的結束時間。

DBUserName:用戶端的sqlserver使用者名稱。

DatabaseID:如果指定了USE database就是指定的資料庫id,否則就是預設的資料庫id(也就是master的資料庫id)。所以該列的作用不是很大。

Error:事件的錯誤號碼，通常是sysmessage中儲存的錯誤號碼。

ObjectName:正在引用的對象名稱。

三、內建跟蹤模板

工具內建了幾個比較實用的跟蹤模板，一般的跟蹤都可以直接使用內建的跟蹤模板解決，同時自己也可以建立自訂的跟蹤事件和跟蹤屬性儲存成模板供以後使用。

SP_Counts:計算已啟動並執行預存程序數，並且按預存程序的名稱進行分組統計，此模板可以分析某時間段預存程序的行為。

Standard:記錄所有預存程序和T-SQL語句批處理啟動並執行時間，當你想要監視常規資料庫伺服器活動時即可使用該模板，一般的跟蹤需要使用該模板就可以解決，這也是預設的模板。

TSQL:記錄用戶端提交給sqlserver的所有T-SQL語句的的內容和開始時間，通常使用該模板用於程式調試。

TSQL_Duration:記錄用戶端提交給sqlserver的所有T-SQL語句批處理資訊以及執行這些語句所需的時間（毫秒），並按時間進行分組，使用該模板可以分析執行慢的查詢，此模板的追蹤記錄可以用於Database Engine Tuning Advisor分析使用。

TSQL_Grouped:按提交用戶端和登入使用者進行分組記錄所有提交給SQLServer的T-SQL批處理語句及其開始時間，此模板用於分析某個客戶或者使用者執行的查詢。

TSQL_Locks：記錄所有開始和完成的預存程序和T-SQL語句，同時記錄死結資訊，此模板用於跟蹤死結。

TSQL_Replay:記錄有關已發出的T-SQL語句的詳細資料，此模板記錄重播跟蹤所需的資訊，此模板可執行跌到最佳化，例如基準測試。

TSQL_SPs:記錄有關執行的所有預存程序的詳細資料，此模板可以分析預存程序的組成步驟。如果你懷疑正在重新編譯預存程序，請添加SP:Recomple事件

Tuning:記錄有關儲存和T-SQL語句批處理的資訊以及執行這些語句所需的時間（毫秒），使用此模板生產跟蹤輸出可用於Database Engine Tuning Advisor工作負載來最佳化索引、最佳化效能。此模板和TSQL_Druation相似後者是做了時間分組。

 

Database Engine Tuning Advisor

1.如果需要用Database Engine Tuning Advisor分析跟蹤事件記錄必須捕獲了以下跟蹤事件：

• RPC:Completed

• SQL:BatchCompleted

• SP:StmtCompleted

也可以使用這些跟蹤事件的 Starting 版本。 例如，SQL:BatchStarting。 但是，這些跟蹤事件的 Completed 版本包括 Duration 列，它能使Database Engine Tuning Advisor更有效地最佳化工作負載。 Database Engine Tuning Advisor不最佳化其他類型的跟蹤事件。

2.包含 LoginName列

Database Engine Tuning Advisor在最佳化過程中提交執行程序表請求。 當包含 LoginName 資料列的跟蹤表或追蹤檔案被用作工作負載時，Database Engine Tuning Advisor將類比 LoginName 中指定的使用者。 如果沒有為此使用者授予 SHOWPLAN 許可權（該許可權使使用者能夠為跟蹤中包含的語句執行和產生執行程序表），Database Engine Tuning Advisor將不會最佳化這些語句。 

避免為跟蹤的 LoginName 列中指定的每個使用者授予 SHOWPLAN 許可權

1. 通過從未最佳化的事件中刪除 LoginName 列來建立新的工作負載，然後只將未最佳化的事件儲存到新的追蹤檔案或跟蹤表中。

2. 將不帶 LoginName 列的新工作負載重新提交到Database Engine Tuning Advisor。

Database Engine Tuning Advisor將最佳化新的工作負載，因為跟蹤中未指定登入資訊。 如果某個語句沒有相應的 LoginName，Database Engine Tuning Advisor將通過類比啟動最佳化會話的使用者（sysadmin 固定伺服器角色或 db_owner 固定資料庫角色的成員）來最佳化該語句。

3.Database Engine Tuning Advisor不能執行下列操作：

• 建議對系統資料表建立索引。
  
  
• 添加或刪除唯一索引或強制 PRIMARY KEY 或 UNIQUE 約束的索引。
  
  
• 最佳化單使用者資料庫。

4.Database Engine Tuning Advisor具有下列限制：

• Database Engine Tuning Advisor通過資料採樣收集統計資訊。因此，在相同的工作負載上重複運行該工具可能產生不同的結果。
  
  
• Database Engine Tuning Advisor不能用於最佳化 Microsoft SQL Server 7.0 或更早版本的資料庫中的索引。
  
  
• 如果為最佳化建議指定的最大磁碟空間超過了可用空間，Database Engine Tuning Advisor將使用指定的值。但是，當您執行建議指令碼來實施它時，如果未先添加更多磁碟空間，則指令碼會失敗。可以使用 dta工具 + 生產力的 -B 選項指定最大磁碟空間，也可以通過在“進階最佳化選項”對話方塊中輸入值來指定最大磁碟空間。
  
  
• 為了安全起見，Database Engine Tuning Advisor不能最佳化駐留在遠程伺服器上的跟蹤表中的工作負載。若要解除此限制，可以選擇以下選項之一：
  
  
  • 使用追蹤檔案而不使用跟蹤表。
    
    
  • 將跟蹤表複製到遠程伺服器。
    
    
• 當強制實施約束時，例如為最佳化建議指定最大磁碟空間時強制的約束（通過使用 -B 選項或“進階最佳化選項”對話方塊），Database Engine Tuning Advisor可能會被迫刪除某些現有的索引。在此情況下，產生的Database Engine Tuning Advisor建議可能產生負的預期提高值。
  
  
• 指定限制最佳化時間的約束時（通過使用 dta 工具 + 生產力的 -A 選項或通過選擇“最佳化選項”選項卡上的“限制最佳化時間”），Database Engine Tuning Advisor可能超過該時間限制，以便針對到當時為止已處理的工作負載，產生精確預期的提高值和分析報告。

5.Database Engine Tuning Advisor可能在下列情況下不提供建議：

• 正在最佳化的表所包含的資料頁數少於 10。
  
  
• 建議的索引對當前物理資料庫設計的查詢效能預計帶來的提高值不夠。
  
  
• 運行Database Engine Tuning Advisor的使用者不是 db_owner 資料庫角色或 sysadmin 固定伺服器角色的成員。工作負載中的查詢在運行Database Engine Tuning Advisor的使用者的安全上下文中進行分析。該使用者必須是db_owner 資料庫角色的成員。

6.Database Engine Tuning Advisor可能在下列情況下不提供分區建議：

• 未啟用 xp_msver 擴充預存程序。此擴充預存程序用於提取要最佳化的資料庫所在伺服器上的處理器數目以及可用記憶體。請注意，安裝 SQL Server 後，預設情況下，此擴充預存程序處於開啟狀態。有關詳細資料，請參閱瞭解介面區配置器和 xp_msver (Transact-SQL)。

7.效能注意事項

在分析過程中，Database Engine Tuning Advisor可能佔用相當多的處理器及記憶體資源。若要避免降低生產伺服器速度，請採用下列策略之一：

• 在伺服器空閑時最佳化資料庫。Database Engine Tuning Advisor可能影響維護任務效能。
  
  
• 使用測試伺服器/生產伺服器功能。有關詳細資料，請參閱減輕生產伺服器最佳化負荷。
  
  
• 指定Database Engine Tuning Advisor僅分析物理資料庫設計結構。Database Engine Tuning Advisor提供許多選項，但是請僅指定所需選項。

注意：由於Database Engine Tuning Advisor進行效能最佳化時也是將負載記錄中的語句執行一篇查詢分析執行計畫的操作，所以對伺服器同樣存在壓力。特別是對於大的負載分析可能需要分析一個小時甚至更長，這樣可能會持續對伺服器造成壓力，所以避免在業務高峰期進行使用引擎最佳化顧問進行負載分析。

執行個體 

接下來就列舉三個案例，使用Database Engine Tuning Advisor來分析追蹤記錄最佳化索引的案例、監控死結的案例、建立自訂跟蹤模板案例。

案例1：最佳化索引

1.建立測試資料

--建立測試表CREATE TABLE [dbo].[book](    [id] [int] NOT NULL PRIMARY KEY,    [name] [varchar](50) NULL);--插入10W條測試資料DECLARE @id intSET @id=1WHILE @id<100000BEGININSERT INTO book values(@id,CONVERT(varchar(20),@id))SET @[email protected]+1END;

2.建立跟蹤

這裡使用預設的跟蹤模板“tuning”

1.建立好跟蹤後點擊運行即可，事件選擇這裡保持預設

2.執行SQL

SELECT * FROM book WHERE name=‘10001‘;

由於name欄位沒有建索引所以該查詢執行計畫分析過後會返回建立name欄位的索引，通過引擎最佳化顧問分析同樣如此

3.停止跟蹤

在使用Database Engine Tuning Advisor分析負載跟蹤之前必須先停止跟蹤。

4.開啟Database Engine Tuning Advisor

可以直接在profile的工具列選擇開啟，“檔案”選擇剛才的追蹤檔案，“負載資料庫”選擇需要進行最佳化的資料庫，“選擇要最佳化的資料庫和表”也就需要最佳化的資料庫的相關表。最佳化選項沒有特別的需求選擇預設即可，然後點擊“開始分析”。

引擎最佳化顧問會自動產生建立索引的腳步，同時還給出了建立該索引之後預計效能可以提供的百分比，如果同時存在很多表的索引建議可以勾選需要儲存的建議儲存成sql檔案在“開始分析”欄旁邊有一個儲存建議的按鈕可以將建議儲存成sql檔案。

建議：

1.Database Engine Tuning Advisor給出的建議不是每一個都是對的，自己對比該SQL的執行頻率來判斷是否需要建立該索引，比如我當前這個SQL如果我這個SQL只執行了一次後面就不會再執行了那麼這個索引就沒必要建立了。

2.修改引擎最佳化顧問給出的索引名，Database Engine Tuning Advisor給出的建立索引的索引名不夠直觀，建議自己手動更改，比如改成“ix_book_name”,“索引標示_表名_欄位描述”的規則。

3.用來分析的檔案不要太大否則可能會分析不完成，不要在業務高峰期進行分析。

案例2：監控死結

1.建立跟蹤

 

模板選擇內建的“TSQL_Locks”模板，運行跟蹤。

2.執行SQL

開啟兩個會話視窗分表執行如下SQL，先在會話1執行然後在10S內在會話2中執行，兩個會話擁有各自的獨佔鎖定同時又去申請對方擁有的獨佔鎖定造成死結。

會話1執行：當前會話1是62

BEGIN TRANSACTIONUPDATE book SET name=‘a‘WHERE ID=10--延時10s執行waitfor delay ‘0:0:10‘UPDATE book SET name=‘a‘WHERE ID=100

會話2執行：當前會話2是

BEGIN TRANSACTIONUPDATE book SET name=‘b‘WHERE ID=100--延時20執行waitfor delay ‘0:0:20‘UPDATE book SET name=‘b‘WHERE ID=10

msms用戶端返回的錯誤訊息顯示當前62會話作為死結的犧牲品。

3.跟蹤分析死結

 死結跟蹤事件使用圖形和直觀的返回了兩個會話的死結，其中62會話用了一個×表示當前的會話是死結的犧牲品。

案例三：建立自訂跟蹤模板

 標準模板就是一個比較好的參考模板，比如我們對執行語句進行監控就可以參考標準模板在其基礎上修改儲存成自己的模板。

1.建立TSQL語句跟蹤

2.建立跟蹤模板

停止當前的TSQL跟蹤，選擇“檔案”-“另存新檔跟蹤模板”就可以儲存成自己的跟蹤模板。

3.列篩選

 

當前是篩選跟蹤的TSQL語句中包含book，這裡的列篩選這執行 where like 的文法類似。

整形列的話就不需要帶模糊條件：

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

4.列組織

列組織其實就是按某列進行分組顯示跟蹤，類似select查詢裡面的group by操作。比如我當前按期間進行分組跟蹤。

通過對期間進行分組，相同的期間會放在一個分組裡。

總結

 由於篇幅有限列舉了一些簡單常用的操作，其它的分類監控的方法類似有興趣可以多去研究，profile是非常實用且介面化很好的監控工具這也是SQLServer獨特的條件，應該熟練運用。

SQL Server profile提示