SQL 探查器跟蹤使用系統(tǒng)存儲(chǔ)過程來建立。你可以從一個(gè)現(xiàn)有的探查器跟蹤中使用SQL命令，并構(gòu)造你自己的存儲(chǔ)過程來創(chuàng)建和啟動(dòng)一個(gè)SQL探查器跟蹤。你需要使用自己的存儲(chǔ)過程來指定一些額外的設(shè)置。這些設(shè)置包括運(yùn)行時(shí)間、文件大小和跟蹤輸出文件位置。下面列出了關(guān)于如何使用這個(gè)存儲(chǔ)過程來創(chuàng)建、執(zhí)行并且關(guān)閉探查器跟蹤的詳細(xì)過程。
創(chuàng)建跟蹤定義
定義用于構(gòu)造一個(gè)探查器跟蹤的SQL命令的方法是使用SQL探查器。
1. 啟動(dòng)SQL探查器并選擇File > New Trace。指定你在跟蹤中想要的事件、字段和過濾器。
2. 啟動(dòng)跟蹤然后停止它。
3. 輸出定義。點(diǎn)擊File > Export > Script Trace Definition > For SQL Server 2005。
注意，對(duì)于SQL Sever 2000 和 2008，請選擇適當(dāng)?shù)妮敵鲱愋汀?BR> 4. 保存跟蹤文件。
創(chuàng)建一個(gè)探查器跟蹤存儲(chǔ)過程
接著，選擇這些輸出跟蹤定義并且用它來創(chuàng)建一個(gè)存儲(chǔ)過程。
1. 使用SSMS來打開上面創(chuàng)建的輸出跟蹤定義。
2. 在SSMS中打開另一個(gè)查詢窗口并粘貼下面的trc_template存儲(chǔ)過程代碼。
/*
use Admin
go
*/
CREATE procedure trc_Template @Folder nvarchar(200)
as
/*
Start a 60 minute profiler trace storing the captured output in
provider folder.
The folder must exist. A subfolder will be created using the start date
and time to allow for repeated running of this profile without
replacing the
previuos captured trace files.
On SQL Server 2005, XP_CMDSHELL needs to be enable to create the
subfolder. You
might want to disable it when you are done running your scheduled
trace.
Sample Command: exec trc_Template @Folder =
'C:\Output\ProfilerTrace\Template'
*/
set nocount on
-- To change the traces duration, modify the following statement
declare @StopTime datetime ; set @StopTime = dateadd(mi,60,getdate())
declare @StartDatetime varchar(13) ; set @StartDatetime =
convert(char(8),getdate(),112) + '_' +
cast(replace(convert(varchar(5),getdate(),108),':','') as char(4)) --['YYYYMMDD_HHMM']
declare @rc int
declare @TraceID int
declare @TraceFile nvarchar(100)
declare @MaxFileSize bigint ; set @MaxFileSize = 50 -- The maximum trace file in megabytes
declare @cmd nvarchar(2000)
declare @msg nvarchar(200)
If right(@Folder,1)<>'\' set @Folder = @Folder + '\'
-- Check if Folder exists
set @cmd = 'dir ' +@Folder
exec @rc = master..xp_cmdshell @cmd,no_output
if (@rc != 0) begin set @msg = 'The specified folder ' + @Folder + '
does not exist, Please specify an existing drive:\folder '+ cast(@rc as
varchar(10)) raiserror(@msg,10,1) return(-1)
end
--Create new trace file folder
set @cmd = 'mkdir ' +@Folder+@StartDatetime
exec @rc = master..xp_cmdshell @cmd,no_output
if (@rc != 0) begin set @msg = 'Error creating trace folder : ' +
cast(@rc as varchar(10)) set @msg = @msg + 'SQL Server 2005 or later
instance require OLE Automation to been enabled' raiserror(@msg,10,1)
return(-1)
end
set @TraceFile = @Folder+@StartDatetime+'\trace'
exec @rc = sp_trace_create @TraceID output, 2, @TraceFile,
@MaxFileSize, @StopTime
if (@rc != 0) begin set @msg = 'Error creating trace : ' + cast(@rc as
varchar(10)) raiserror(@msg,10,1) return(-1)
end --> Using your saved trace file, add the '-- Set the events' section below <--
--> Using your saved trace file, add the '-- Set the Filters' section below <--
--> Customization is now completed <--
-----------------------------------------------
-- This filter is added to exclude all profiler traces.
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler%'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1 -- start trace
select 'Trace id = ', @TraceID, 'Path=', @Folder+@StartDatetime+'\'
select 'To Stop this trace sooner, execute these two commands'
select ' EXEC sp_trace_setstatus @traceid = ' , @TraceID , ', @status = 0; -- Stop/pause Trace'
select ' EXEC sp_trace_setstatus @traceid = ' , @TraceID , ', @status = 2; -- Close trace and delete it from the server'
return
go
3. 在輸出跟蹤定義中，找到'-- Set the events'部分。復(fù)制所有的代碼行直到下一個(gè)注釋行為'-- Set the Filters'。
4. 把這些代碼行粘貼到代碼行"add the '-- Set the events' section below"后面的存儲(chǔ)過程模板。
5. 接著，找到輸出跟蹤定義的'-- Set the Filters'部分。粘貼'-- Set the trace status to start'之前的代碼行。
6. 把這些代碼行粘貼到"add the '-- Set the Filters' section below"后面的存儲(chǔ)過程模版。
7. 使用你的命名規(guī)則更改存儲(chǔ)過程名
8. 在SQL代碼中，探查器終止時(shí)間通過變量@StopTime來設(shè)置。在跟蹤啟動(dòng)后，當(dāng)前的設(shè)置是60分鐘。你可以調(diào)整這個(gè)設(shè)置成適合你具體情況的。
9. 把@MaxFileSize設(shè)置成適合跟蹤輸出文件的大小。目前，它被設(shè)置成50兆字節(jié)。
10. 把存儲(chǔ)過程代碼保存在一個(gè)非trc__Template.sql的新文件名下而。
準(zhǔn)備服務(wù)器
1. 在將要用于包含探查器跟蹤文件的服務(wù)器下創(chuàng)建一個(gè)文件夾。
2. 在SQL Server 2005和 2008中啟用XP_CMDSHELL。如果你不想一直啟用這個(gè)功能，那么你可以限制啟動(dòng)程序命令的命令，該命令啟用XP_CMDSHELL，啟動(dòng)這個(gè)跟蹤然后停用XP_CMDSHELL(看下面的示例代碼)。
3. 使用你上面創(chuàng)建的文件在SQL Server實(shí)例上創(chuàng)建存儲(chǔ)過程。它可以在任何用戶數(shù)據(jù)庫中創(chuàng)建。在下面的例子中，它在數(shù)據(jù)庫Admin中創(chuàng)建。
4. 在SQL代理中創(chuàng)建一個(gè)作業(yè)來執(zhí)行這個(gè)存儲(chǔ)過程。不要啟用該作業(yè)，除非你已經(jīng)測試了這個(gè)存儲(chǔ)過程。
-- Enable xp_cmdshell
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
RECONFIGURE
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
-- To update the currently configured value for this feature.
RECONFIGURE
-- Start profiler trace
EXEC Admin.dbo.trc_PerformanceTuning @Folder = 'e:\Output\ProfilerTrace\PerformanceTuning'
-- Disable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
-- To update the currently configured value for this feature.
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
-- To update the currently configured value for advanced options.
RECONFIGURE
5.@Folder必須在服務(wù)器上指定一個(gè)現(xiàn)有的文件夾。通過啟動(dòng)日期和時(shí)間一個(gè)子文件夾可以由存儲(chǔ)過程創(chuàng)建。這就確保了創(chuàng)建的跟蹤一直都是新的、獨(dú)一無二的。
運(yùn)行探查器跟蹤
1. 在運(yùn)行你的SQL代理作業(yè)之前，你應(yīng)該測試下存儲(chǔ)過程。
2. 通過帶有激活的"Results to Text'的SSMS使用在SQL代理作業(yè)中定義的命令來執(zhí)行這個(gè)存儲(chǔ)過程。
3. 如果沒有錯(cuò)誤發(fā)生，那么它應(yīng)該正在運(yùn)行。要確認(rèn)這一點(diǎn)，你可以執(zhí)行查詢"select * FROM ::fn_trace_getinfo(default)"。
示例輸出結(jié)果
停止探查器跟蹤
1. 要在它的結(jié)束時(shí)間之前停止探查器跟蹤，你可以執(zhí)行兩個(gè)命令。一個(gè)命令停止這個(gè)跟蹤，另一個(gè)關(guān)閉這個(gè)跟蹤文件。
2. 下面是這兩個(gè)命令：
a. 執(zhí)行"select * FROM ::fn_trace_getinfo(default)"
b. 使用啟動(dòng)這個(gè)跟蹤時(shí)指定的文件夾確認(rèn)這個(gè)跟蹤。
c. 用#代替trace id執(zhí)行這兩個(gè)命令。
EXEC sp_trace_setstatus @traceid = #, @status = 0; -- Stop/pause Trace
EXEC sp_trace_setstatus @traceid = #, @status = 2; -- Close trace and delete it from the server
3. 執(zhí)行 "select * FROM ::fn_trace_getinfo(default)" 確認(rèn)它停止了。
4. 要確認(rèn)捕捉的跟蹤文件，你可以使用SQL Server探查器打開它們。
5. 舊的探查器跟蹤文件不會(huì)自動(dòng)刪除。你需要在完成時(shí)自己刪除它們。
結(jié)論
這個(gè)安排SQL探查器跟蹤的方法已經(jīng)在SQL Server 2000，2005和2008上測試通過。在第一次創(chuàng)建時(shí)，這些跟蹤可能看起來有些復(fù)雜，但是后來它將變成一種習(xí)慣。擁有一個(gè)準(zhǔn)備好的跟蹤，無論何時(shí)發(fā)生問題你都可以節(jié)省解決問題的時(shí)間。
創(chuàng)建跟蹤定義
定義用于構(gòu)造一個(gè)探查器跟蹤的SQL命令的方法是使用SQL探查器。
1. 啟動(dòng)SQL探查器并選擇File > New Trace。指定你在跟蹤中想要的事件、字段和過濾器。
2. 啟動(dòng)跟蹤然后停止它。
3. 輸出定義。點(diǎn)擊File > Export > Script Trace Definition > For SQL Server 2005。
注意，對(duì)于SQL Sever 2000 和 2008，請選擇適當(dāng)?shù)妮敵鲱愋汀?BR> 4. 保存跟蹤文件。
創(chuàng)建一個(gè)探查器跟蹤存儲(chǔ)過程
接著，選擇這些輸出跟蹤定義并且用它來創(chuàng)建一個(gè)存儲(chǔ)過程。
1. 使用SSMS來打開上面創(chuàng)建的輸出跟蹤定義。
2. 在SSMS中打開另一個(gè)查詢窗口并粘貼下面的trc_template存儲(chǔ)過程代碼。
/*
use Admin
go
*/
CREATE procedure trc_Template @Folder nvarchar(200)
as
/*
Start a 60 minute profiler trace storing the captured output in
provider folder.
The folder must exist. A subfolder will be created using the start date
and time to allow for repeated running of this profile without
replacing the
previuos captured trace files.
On SQL Server 2005, XP_CMDSHELL needs to be enable to create the
subfolder. You
might want to disable it when you are done running your scheduled
trace.
Sample Command: exec trc_Template @Folder =
'C:\Output\ProfilerTrace\Template'
*/
set nocount on
-- To change the traces duration, modify the following statement
declare @StopTime datetime ; set @StopTime = dateadd(mi,60,getdate())
declare @StartDatetime varchar(13) ; set @StartDatetime =
convert(char(8),getdate(),112) + '_' +
cast(replace(convert(varchar(5),getdate(),108),':','') as char(4)) --['YYYYMMDD_HHMM']
declare @rc int
declare @TraceID int
declare @TraceFile nvarchar(100)
declare @MaxFileSize bigint ; set @MaxFileSize = 50 -- The maximum trace file in megabytes
declare @cmd nvarchar(2000)
declare @msg nvarchar(200)
If right(@Folder,1)<>'\' set @Folder = @Folder + '\'
-- Check if Folder exists
set @cmd = 'dir ' +@Folder
exec @rc = master..xp_cmdshell @cmd,no_output
if (@rc != 0) begin set @msg = 'The specified folder ' + @Folder + '
does not exist, Please specify an existing drive:\folder '+ cast(@rc as
varchar(10)) raiserror(@msg,10,1) return(-1)
end
--Create new trace file folder
set @cmd = 'mkdir ' +@Folder+@StartDatetime
exec @rc = master..xp_cmdshell @cmd,no_output
if (@rc != 0) begin set @msg = 'Error creating trace folder : ' +
cast(@rc as varchar(10)) set @msg = @msg + 'SQL Server 2005 or later
instance require OLE Automation to been enabled' raiserror(@msg,10,1)
return(-1)
end
set @TraceFile = @Folder+@StartDatetime+'\trace'
exec @rc = sp_trace_create @TraceID output, 2, @TraceFile,
@MaxFileSize, @StopTime
if (@rc != 0) begin set @msg = 'Error creating trace : ' + cast(@rc as
varchar(10)) raiserror(@msg,10,1) return(-1)
end --> Using your saved trace file, add the '-- Set the events' section below <--
--> Using your saved trace file, add the '-- Set the Filters' section below <--
--> Customization is now completed <--
-----------------------------------------------
-- This filter is added to exclude all profiler traces.
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler%'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1 -- start trace
select 'Trace id = ', @TraceID, 'Path=', @Folder+@StartDatetime+'\'
select 'To Stop this trace sooner, execute these two commands'
select ' EXEC sp_trace_setstatus @traceid = ' , @TraceID , ', @status = 0; -- Stop/pause Trace'
select ' EXEC sp_trace_setstatus @traceid = ' , @TraceID , ', @status = 2; -- Close trace and delete it from the server'
return
go
3. 在輸出跟蹤定義中，找到'-- Set the events'部分。復(fù)制所有的代碼行直到下一個(gè)注釋行為'-- Set the Filters'。
4. 把這些代碼行粘貼到代碼行"add the '-- Set the events' section below"后面的存儲(chǔ)過程模板。
5. 接著，找到輸出跟蹤定義的'-- Set the Filters'部分。粘貼'-- Set the trace status to start'之前的代碼行。
6. 把這些代碼行粘貼到"add the '-- Set the Filters' section below"后面的存儲(chǔ)過程模版。
7. 使用你的命名規(guī)則更改存儲(chǔ)過程名
8. 在SQL代碼中，探查器終止時(shí)間通過變量@StopTime來設(shè)置。在跟蹤啟動(dòng)后，當(dāng)前的設(shè)置是60分鐘。你可以調(diào)整這個(gè)設(shè)置成適合你具體情況的。
9. 把@MaxFileSize設(shè)置成適合跟蹤輸出文件的大小。目前，它被設(shè)置成50兆字節(jié)。
10. 把存儲(chǔ)過程代碼保存在一個(gè)非trc__Template.sql的新文件名下而。
準(zhǔn)備服務(wù)器
1. 在將要用于包含探查器跟蹤文件的服務(wù)器下創(chuàng)建一個(gè)文件夾。
2. 在SQL Server 2005和 2008中啟用XP_CMDSHELL。如果你不想一直啟用這個(gè)功能，那么你可以限制啟動(dòng)程序命令的命令，該命令啟用XP_CMDSHELL，啟動(dòng)這個(gè)跟蹤然后停用XP_CMDSHELL(看下面的示例代碼)。
3. 使用你上面創(chuàng)建的文件在SQL Server實(shí)例上創(chuàng)建存儲(chǔ)過程。它可以在任何用戶數(shù)據(jù)庫中創(chuàng)建。在下面的例子中，它在數(shù)據(jù)庫Admin中創(chuàng)建。
4. 在SQL代理中創(chuàng)建一個(gè)作業(yè)來執(zhí)行這個(gè)存儲(chǔ)過程。不要啟用該作業(yè)，除非你已經(jīng)測試了這個(gè)存儲(chǔ)過程。
-- Enable xp_cmdshell
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
RECONFIGURE
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
-- To update the currently configured value for this feature.
RECONFIGURE
-- Start profiler trace
EXEC Admin.dbo.trc_PerformanceTuning @Folder = 'e:\Output\ProfilerTrace\PerformanceTuning'
-- Disable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
-- To update the currently configured value for this feature.
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
-- To update the currently configured value for advanced options.
RECONFIGURE
5.@Folder必須在服務(wù)器上指定一個(gè)現(xiàn)有的文件夾。通過啟動(dòng)日期和時(shí)間一個(gè)子文件夾可以由存儲(chǔ)過程創(chuàng)建。這就確保了創(chuàng)建的跟蹤一直都是新的、獨(dú)一無二的。
運(yùn)行探查器跟蹤
1. 在運(yùn)行你的SQL代理作業(yè)之前，你應(yīng)該測試下存儲(chǔ)過程。
2. 通過帶有激活的"Results to Text'的SSMS使用在SQL代理作業(yè)中定義的命令來執(zhí)行這個(gè)存儲(chǔ)過程。
3. 如果沒有錯(cuò)誤發(fā)生，那么它應(yīng)該正在運(yùn)行。要確認(rèn)這一點(diǎn)，你可以執(zhí)行查詢"select * FROM ::fn_trace_getinfo(default)"。
示例輸出結(jié)果
停止探查器跟蹤
1. 要在它的結(jié)束時(shí)間之前停止探查器跟蹤，你可以執(zhí)行兩個(gè)命令。一個(gè)命令停止這個(gè)跟蹤，另一個(gè)關(guān)閉這個(gè)跟蹤文件。
2. 下面是這兩個(gè)命令：
a. 執(zhí)行"select * FROM ::fn_trace_getinfo(default)"
b. 使用啟動(dòng)這個(gè)跟蹤時(shí)指定的文件夾確認(rèn)這個(gè)跟蹤。
c. 用#代替trace id執(zhí)行這兩個(gè)命令。
EXEC sp_trace_setstatus @traceid = #, @status = 0; -- Stop/pause Trace
EXEC sp_trace_setstatus @traceid = #, @status = 2; -- Close trace and delete it from the server
3. 執(zhí)行 "select * FROM ::fn_trace_getinfo(default)" 確認(rèn)它停止了。
4. 要確認(rèn)捕捉的跟蹤文件，你可以使用SQL Server探查器打開它們。
5. 舊的探查器跟蹤文件不會(huì)自動(dòng)刪除。你需要在完成時(shí)自己刪除它們。
結(jié)論
這個(gè)安排SQL探查器跟蹤的方法已經(jīng)在SQL Server 2000，2005和2008上測試通過。在第一次創(chuàng)建時(shí)，這些跟蹤可能看起來有些復(fù)雜，但是后來它將變成一種習(xí)慣。擁有一個(gè)準(zhǔn)備好的跟蹤，無論何時(shí)發(fā)生問題你都可以節(jié)省解決問題的時(shí)間。