如何以SQL指令作Performance Tuning

一般而言SQL 的Performance Tuning 可使用SQL Profiler , 但是如果要調教的機器上SQL Server上並未安裝SQL Profiler 或使用者不會使用SQL Profiler時, 則可用指令的方式執行.

本案例的狀況是: 對方的機器是SQL Server 2008 且未安裝SQL Profiler.

筆者手上的機器只有SQL Server 2005 , 但有安裝SQL Profiler .

1.建立SQL Profiler 的指令檔.

1.1 開啟SQL Server 2005 , 進入SQL Profiler.

 

1.2. 選擇追蹤的事件

   可設定常用的事件, 包括 RFC:Completed, SP:StmtCompleted,SQL:BatchCompleted

1.3.設定資料行篩選,

    可設定篩選條件例如針對Reads 的次數大於1000以上才記錄.

1.4. 設定完成後, 點選執行後, 在將其停止, 並匯出指令檔.

1.5. 產生的SQL 如下:

(若上述的追蹤項目可用, 可直接使用以下的SQL 追蹤）

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @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, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 9, @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, 6, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 8a1549c3-045c-464d-9308-01a9e4d11f51'
set @bigintfilter = 1000
exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

1.6. 調整SQL 中相關參數.

上述SQL 可調整的參數如下:

檔案大小, 預設為 5M.

           set @maxfilesize = 5 

輸出檔案名稱:

            exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere',

讀取次數篩選條件(本例為1000)

              set @bigintfilter = 1000

1.7. 停止追蹤的指令

上述指令當追蹤檔案達到5M時會自動停止無需特別操作, 若設定檔案太大, 欲手動停止可執行下列指令.

  /* 參數 @status 的說明： 

0：停止指定的追蹤。 

1：啟動指定的追蹤。 

2：關閉指定的追蹤，並從伺服器中刪除其定義。 

*/

--停止追蹤 

EXEC sp_trace_setstatus @traceid = 2 , @status = 0 

GO 

-- 關閉指定的追蹤，並從伺服器中刪除其定義。

EXEC sp_trace_setstatus @traceid = 2 , @status = 2 

GO 

-- 查詢系統目前正在執行的追蹤

SELECT * FROM sys.traces  WHERE is_default <> 1 

2.執行追蹤

2.1 將上述指令複製至調教的主機,並執行

2.2檢查執行狀態

2.3 取回執行結果

SQL Profile 的追蹤檔是一個Binary 的檔案, 可先進行壓縮, 以減少傳送的大小.

2.4 若欲開啟追蹤檔的SQL 版本較低, 如本例要以SQL Server 2005 調教SQL Server 2008 , 需將SQL Server 2008 一併覆製SQL Server 2008 上的定義檔.

SQL Server 2008 的Trace定義檔為Microsoft SQL Server TraceDefinition 10.0.0.xml, 可由SQL Server 2008 的主機上找到將其複製至

C:\Program Files\Microsoft SQL Server\90\Tools\Profiler\TraceDefinitions

 

3. 效能調教

 

3.1 將追蹤的結果, 複製至SQL Server 2005, 並以SQL Profiler 開啟.

3.1 檢查Read次數較高的SQL , 並調整SQL 語法或增加Index.

4. 備註

4.1 缺少追蹤定義檔.

如以SQL Server 2005 開啟SQL Server 2008 的追蹤檔, 但追蹤定議檔不存在會出現以下錯誤, 只需複製定議檔即可.

4.2 如何用遠端連線傳輸檔案

如追蹤的機器是以遠端連線進行, 在進行連線時需先開啟磁碟共享, 以利傳回追蹤結果,設定後開啟則遠端主機可看到本地的磁碟機.

點選選項-->本機資源->詳細資料

勾選磁碟機, 連線後可發現已經將本地的磁碟機Mount 至遠端的主機, 再用一般複製貼上即可.