SQL Server效能調教系列(4)–Profiler(上)

一：簡介

在處理效能問題是，DBA傾向於關注系統技術層面，如資源隊列，資源使用率，系統loading等。而使用者只把效能問題認為是等待，他們從商務邏輯層面發出一個請求，等待返回結果，後台資料庫就需要去響應這個請求。從使用者角度來看，一般認為等待三秒才返回就屬於效能問題（特殊的系統除外：比如需要大量的資料操作），他們並不關心系統的資料層，比如有多少個命令在等待處理，CPU利用率，RAM使用率等。在遇到這些問題之後，我們需要找到這個問題，請著手最佳化，找到合理的解決方案。

 

註：硬體方面的問題請參照系列（2）

SQL Server效能調教系列(2)--Server Performance Monitor(Perfmon)

 

二：理論

要做最佳化，首先要找出需要最佳化的部分（如找到效率低的SQL或SP等），引用SQL技術內幕中介紹的最佳化步驟：

1.分析執行個體級的等待

2.聯絡等待的隊列

3.確定方案

4.細化到資料庫/檔案級

5.細化到進程級

6.最佳化索引/查詢

 

三：方法

本章主要介紹Profiler工具來跟蹤效能工作負載。

1. Profiler簡介

通過SQL Server—>Tools—>SQL Server Profiler啟動

General頁：跟蹤的記錄有兩種儲存方式:儲存到檔案和儲存到表。通常選擇儲存到檔案，因為儲存到表會增加較多的額外系統開銷。

Events Selection頁：能夠選擇跟蹤的事件。更多的跟蹤事件請參考MSDN。

註：

• 不要使用GUI跟蹤，應該使用T-SQL。因為使用GUI會有兩個跟蹤，一個把跟蹤資訊寫入目標檔案，一個把跟蹤資訊寫入啟動並執行GUI，會增加系統的額外開銷。
• 不要把跟蹤直接寫入到表，這樣會嚴重影響效能，把檔案寫到磁碟是最快的方案。
• 跟蹤會產生大量和額外的IO操作。不要把追蹤檔案放到包含資料庫檔案（如資料，日誌和tempdb）的磁碟上。
• 選擇事件類別的資料列，只跟蹤需要的資訊。
• 使用跟蹤篩選需要的事件。

2.啟動跟蹤

2.1  可以先在GUI中設定需要跟蹤的事件類別，然後在匯出指令碼(File—>Export-->Script Trace Definition)，通常篩選Duration數列大於某些值(比如3000毫秒)的事件來跟蹤運行得比較慢的進程。

 

如:匯出的指令碼如下,這裡把trace的指令碼整理為一個預存程序,以方便執行.

CREATE PROC [dbo].[sp_perfworkload_trace_start]  @dbid      AS INT,  @tracefile AS NVARCHAR(254),  @traceid   AS INT OUTPUTAS-- Create a QueueDECLARE @rc          AS INT;DECLARE @maxfilesize AS BIGINT;SET @maxfilesize = 100;EXEC @rc = sp_trace_create @traceid OUTPUT, 0, @tracefile, @maxfilesize, NULLIF (@rc != 0) GOTO error;-- Client side File and Table cannot be scripted-- Set the eventsDECLARE @on AS BIT;SET @on = 1;EXEC sp_trace_setevent @traceid, 10, 15, @on;EXEC sp_trace_setevent @traceid, 10, 8, @on;EXEC sp_trace_setevent @traceid, 10, 16, @on;EXEC sp_trace_setevent @traceid, 10, 48, @on;EXEC sp_trace_setevent @traceid, 10, 1, @on;EXEC sp_trace_setevent @traceid, 10, 17, @on;EXEC sp_trace_setevent @traceid, 10, 10, @on;EXEC sp_trace_setevent @traceid, 10, 18, @on;EXEC sp_trace_setevent @traceid, 10, 11, @on;EXEC sp_trace_setevent @traceid, 10, 12, @on;EXEC sp_trace_setevent @traceid, 10, 13, @on;EXEC sp_trace_setevent @traceid, 10, 14, @on;EXEC sp_trace_setevent @traceid, 45, 8, @on;EXEC sp_trace_setevent @traceid, 45, 16, @on;EXEC sp_trace_setevent @traceid, 45, 48, @on;EXEC sp_trace_setevent @traceid, 45, 1, @on;EXEC sp_trace_setevent @traceid, 45, 17, @on;EXEC sp_trace_setevent @traceid, 45, 10, @on;EXEC sp_trace_setevent @traceid, 45, 18, @on;EXEC sp_trace_setevent @traceid, 45, 11, @on;EXEC sp_trace_setevent @traceid, 45, 12, @on;EXEC sp_trace_setevent @traceid, 45, 13, @on;EXEC sp_trace_setevent @traceid, 45, 14, @on;EXEC sp_trace_setevent @traceid, 45, 15, @on;EXEC sp_trace_setevent @traceid, 41, 15, @on;EXEC sp_trace_setevent @traceid, 41, 8, @on;EXEC sp_trace_setevent @traceid, 41, 16, @on;EXEC sp_trace_setevent @traceid, 41, 48, @on;EXEC sp_trace_setevent @traceid, 41, 1, @on;EXEC sp_trace_setevent @traceid, 41, 17, @on;EXEC sp_trace_setevent @traceid, 41, 10, @on;EXEC sp_trace_setevent @traceid, 41, 18, @on;EXEC sp_trace_setevent @traceid, 41, 11, @on;EXEC sp_trace_setevent @traceid, 41, 12, @on;EXEC sp_trace_setevent @traceid, 41, 13, @on;EXEC sp_trace_setevent @traceid, 41, 14, @on;-- Set the FiltersDECLARE @intfilter AS INT;DECLARE @bigintfilter AS BIGINT;-- Application name filterEXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Server Profiler%';-- Database ID filterEXEC sp_trace_setfilter @traceid, 3, 0, 0, @dbid;-- Set the trace status to startEXEC sp_trace_setstatus @traceid, 1;-- Print trace id and file name for future referencesPRINT 'Trce ID: ' + CAST(@traceid AS VARCHAR(10))  + ', Trace File: ''' + @tracefile + '''';GOTO finish;error:PRINT 'Error Code: ' + CAST(@rc AS VARCHAR(10));finish:

 

2.2 用以下T-SQL代碼啟動跟蹤：

declare @dbid int,@traceid int;set @dbid=DB_ID()---可以為預設的DB，或者DB_ID('Your_dbname')EXEC sp_perfworkload_trace_start @dbid,'C:\test\performancetrace_20100802.trc',@traceid output

 

執行之後會顯示出traceid，請記住這個traceid，會用它來停止和關閉追蹤。

Trce ID: 2, Trace File: 'C:\test\performancetrace_20100802.trc'

 

2.3 停止和關閉追蹤(sp_trace_setstatus,如果traceid是2，則停止和關閉的代碼如下)：

EXEC sp_trace_setstatus 2,0 EXEC sp_trace_setstatus 2,2

2.4

如果忘記traceid，可以在查詢試圖sys.traces找到。

 

接下篇:

SQL Server效能調教系列(4)--Profiler(下)

 

>>>SQL Server效能調校系列入口地址