问题引出

自从菜鸟的上一篇《如何分析SQL Server Trace文件》文章以后，老鸟对菜鸟是另眼相看：“鸟哥，上篇文章你总结得还不错，当然要赶上我还有点距离”。老鸟还是改不了他得意忘形的模样：“关于Trace文件的自动化分析你研究透了，那怎么自动化部署SQL Trace和Windows性能监视器，你也得研究研究吧”。“虽然老鸟骄傲自满的样子让人讨厌，但是这个问题还是问的很不错的。”，于是乎，菜鸟领了任务，便开始埋头苦干起来。

自动部署SQL Trace

终于，在费了九牛二虎之力后，菜鸟拿出了满意的答案。话不瞎掰，直接上代码。

自动部署SQL Trace代码

洋洋洒洒的SQL Trace自动化部署代码如下：

use master GO set nocount on declare @trace_folder nvarchar(256) ,@trace_file nvarchar(256) ,@max_files_size bigint ,@stop_time datetime ,@file_count int ,@int_filter_cpu int ,@int_filter_duration bigint ,@int_filter_spid int ,@set_trace_status int ; select @trace_folder=N'C:\Temp\perfmon' ,@max_files_size = 500 --max file size for each trace file ,@file_count = 10 --max file count ,@stop_time = '10/21/2016 10:50' --null: stop trace manully; specify time (stop at the specify time) ,@int_filter_cpu = 1 -- >= @int_filter_cpu ms will be traced. or else, skipped. --NULL: ignore this filter ,@int_filter_duration = 10 --execution duration filter: millisecond --NULL: ignore this filter --,@int_filter_spid = 151 --integer: specify a spid to trace -- ,@set_trace_status = 2 --0: Stops the specified trace.; --1: Starts the specified trace.; --2: Closes the specified trace and deletes its definition from the server.; ; /* select * from sys.traces */ --private variables declare @trace_id int ,@do int ,@loop int ,@trace_event_id int ,@trace_column_id int ,@return_code tinyint ,@return_decription varchar(200) ,@field_separator char(1) ; select @field_separator = ',' --trace columns list separator ; IF right(ltrim(rtrim(@trace_folder)), 1 ) <> '\' BEGIN SELECT @trace_folder = ltrim(rtrim(@trace_folder)) + N'\' ; exec sys.xp_create_subdir @trace_folder END ; select @trace_file = @trace_folder + REPLACE(@@SERVERNAME, N'\', N'') ; IF @int_filter_spid IS NOT NULL BEGIN select @trace_file = @trace_file + cast(@int_filter_spid as varchar) ; END --select @trace_file select top 1 @trace_id = id from sys.traces where path like @trace_file + N'%' if @trace_id is not null begin -- Start Trace (status 1 = start) EXEC sys.sp_trace_setstatus @trace_id, @set_trace_status return end if OBJECT_ID('tempdb..#trace_event','u') is not null drop table #trace_event create table #trace_event ( id int identity(1,1) not null primary key ,trace_event_id int not null ,trace_column_id int not null ,event_name sysname null ,trace_column_name sysname null ) ;with trace_event as ( --select * from sys.trace_events order by trace_event_id select is_trace = 1 , event_name = 'RPC:Completed' ,trace_column_list = 'TextData,Duration,CPU,SPID,DatabaseName,DatabaseID,LoginName,HostName,ApplicationName,StartTime,EndTime,Reads,Writes,ObjectID,ObjectName,BINARYDATA,EventSequence,' union all select is_trace = 0 , event_name = 'RPC:Starting' ,trace_column_list = 'TextData,Duration,CPU,SPID,DatabaseName,DatabaseID,LoginName,HostName,ApplicationName,StartTime,EndTime,Reads,Writes,ObjectID,ObjectName,BINARYDATA,EventSequence' union all select is_trace = 1 , event_name = 'SP:StmtCompleted' ,trace_column_list = 'TextData,Duration,CPU,SPID,DatabaseName,DatabaseID,LoginName,HostName,ApplicationName,StartTime,EndTime,Reads,Writes,ObjectID,ObjectName,BINARYDATA,EventSequence,NestLevel,IntegerData' union all select is_trace = 1 , event_name = 'SP:StmtStarting' ,trace_column_list = 'TextData,Duration,CPU,SPID,DatabaseName,DatabaseID,LoginName,HostName,ApplicationName,StartTime,EndTime,Reads,Writes,ObjectID,ObjectName,BINARYDATA,EventSequence,NestLevel' union all select is_trace = 1 , event_name = 'SQL:BatchCompleted' ,trace_column_list = 'TextData,Duration,CPU,SPID,DatabaseName,DatabaseID,LoginName,HostName,ApplicationName,StartTime,EndTime,Reads,Writes,ObjectID,ObjectName,BINARYDATA,EventSequence' union all select is_trace = 0 , event_name = 'SQL:BatchStarting' ,trace_column_list = 'TextData,Duration,CPU,SPID,DatabaseName,DatabaseID,LoginName,HostName,ApplicationName,StartTime,EndTime,Reads,Writes,ObjectID,ObjectName,BINARYDATA,EventSequence' union all select is_trace = 1 , event_name = 'Showplan XML' ,trace_column_list = 'TextData,Duration,CPU,SPID,DatabaseName,DatabaseID,LoginName,HostName,ApplicationName,StartTime,EndTime,Reads,Writes,ObjectID,ObjectName,BINARYDATA,EventSequence' union all select is_trace = 1 , event_name = 'Server Memory Change' ,trace_column_list = 'SPID,StartTime,EventSequence' ), trace_column as( select * ,trace_column_list_xml = CAST( '<V><![CDATA[' + REPLACE( REPLACE( REPLACE( trace_column_list,CHAR(10),']]></V><V><![CDATA[' ),@field_separator,']]></V><V><![CDATA[' ),CHAR(13),']]></V><V><![CDATA[' ) + ']]></V>' as xml ) from trace_event where is_trace = 1 ) ,data as( select trace_column = T.C.value('(./text())[1]','sysname') ,event_name from trace_column AS a CROSS APPLY trace_column_list_xml.nodes('./V') AS T(C) ) INSERT INTO #trace_event select trace_event_id = ev.trace_event_id ,trace_column_id = col.trace_column_id ,a.event_name ,trace_column_name = a.trace_column from data as a inner join sys.trace_columns as col on a.trace_column = col.name inner join sys.trace_events as ev on a.event_name = ev.name where col.trace_column_id is not null order by ev.trace_event_id ; --select * from #trace_event ---private variables select @trace_id = 0 ,@do = 1 ,@loop = @@ROWCOUNT ,@trace_event_id = 0 ,@trace_column_id = 0 ,@return_code = 0 ,@return_decription = '' ; --create trace exec @return_code = sys.sp_trace_create @traceid = @trace_id OUTPUT , @options = 2 , @tracefile = @trace_file , @maxfilesize = @max_files_size , @stoptime = @stop_time , @filecount = @file_count ; select trace_id = @trace_id ,[current_time] = getdate() ,[stop_time] = @stop_time ; /* sp_trace_create [ @traceid = ] trace_id OUTPUT , [ @options = ] option_value , [ @tracefile = ] 'trace_file' [ , [ @maxfilesize = ] max_file_size ] [ , [ @stoptime = ] 'stop_time' ] [ , [ @filecount = ] 'max_rollover_files' ] @options: 2(TRACE_FILE_ROLLOVER);4(SHUTDOWN_ON_ERROR);8(TRACE_PRODUCE_BLACKBOX) @maxfilesize:Specifies the maximum size in megabytes (MB) a trace file can grow. max_file_size is bigint, with a default value of 5. @stoptime:Specifies the date and time the trace will be stopped. stop_time is datetime, with a default of NULL. If NULL, the trace runs until it is manually stopped or until the server shuts down. @filecount:SQL Server tries to maintain no more than max_rollover_files trace files by deleting the oldest trace file before opening a new trace file. */ set @return_decription = case @return_code when 0 then 'No error.' when 1 then 'Unknown error.' when 10 then 'Invalid options. Returned when options specified are incompatible.' when 12 then 'File not created.' when 13 then 'Out of memory. Returned when there is not enough memory to perform the specified action.' when 14 then 'Invalid stop time. Returned when the stop time specified has already happened.' when 15 then 'Invalid parameters. Returned when the user supplied incompatible parameters.' else '' end ; raiserror('Trace create with: %s',10,1,@return_decription) with nowait --loop set trace event & event column while @do <= @loop begin select top 1 @trace_event_id = trace_event_id ,@trace_column_id = trace_column_id from #trace_event where id = @do ; --set trace event exec sys.sp_trace_setevent @trace_id, @trace_event_id, @trace_column_id, 1 raiserror('exec sys.sp_trace_setevent @trace_id, %d, %d, 1',10,1,@trace_event_id,@trace_column_id) with nowait set @do = @do + 1; end -- Set any filter. /* sp_trace_setfilter [ @traceid = ] trace_id , [ @columnid = ] column_id , [ @logical_operator = ] logical_operator , [ @comparison_operator = ] comparison_operator , [ @value = ] value column_id: select * from sys.trace_columns where name IN('EventSequence') logical_operator: Specifies whether the AND (0) or OR (1) operator is applied comparison_operator: Value Comparison operator 0 = (Equal) 1 <> (Not Equal) 2 > (Greater Than) 3 < (Less Than) 4 >= (Greater Than Or Equal) 5 <= (Less Than Or Equal) 6 LIKE 7 NOT LIKE example: sp_trace_setfilter 1, 10, 0, 6, N'SQLT%'; sp_trace_setfilter 1, 10, 0, 6, N'MS%'; sp_trace_setfilter 1, 11, 0, 0, N'joe'; */ --CPU >= 500/ cpu columnid = 18 IF @int_filter_cpu IS NOT NULL EXEC sys.sp_trace_setfilter @trace_id, 18, 0, 4, @int_filter_cpu --duration filter/ duration columnid=13 IF @int_filter_duration IS NOT NULL EXEC sys.sp_trace_setfilter @trace_id, 13, 0, 4, @int_filter_duration --spid filter/ spid columnid=12 IF @int_filter_spid IS NOT NULL exec sys.sp_trace_setfilter @trace_id, 12, 0, 0, @int_filter_spid --applicationName not like 'SQL Server Profiler%' EXEC sys.sp_trace_setfilter @trace_id, 10, 0, 7, N'SQL Server Profiler%' -- Start Trace (status 1 = start) EXEC sys.sp_trace_setstatus @trace_id, @set_trace_status GO --EXEC sys.sp_trace_setstatus 2, 0 --GO select * from sys.traces

SQL Trace代码分析

展示完自动化部署SQL Trace代码后，让我们来做过简单的分析。

输入参数分析

@trace_folder：Trace文件存放的位置@max_files_size：每一个Trace文件大小@file_count：Trace滚动最多的文件数量@stop_time：Trace停止的时间@int_filter_cpu：CPU过滤阈值，CPU使用率超过这个值会被记录下来，单位毫秒@int_filter_duration：执行时间过滤阈值，执行时间超过这个值会被记录，单位毫秒@set_trace_status：Trace的状态：0停止；1启动；2删除

样例

自动部署Windows性能监视器

完成SQL Trace自动化部署代码后，菜鸟马不停蹄，一气呵成的完成Windows性能监视器的代码。

自动部署Windows性能监视器代码

set nocount on declare @file_max_size_mb varchar(8) ,@perfmon_folder_path sysname --counter conf file info ,@data_collector_set_name sysname ,@counter_conf_file_name sysname ,@perf_counts_string varchar(max) --data gather file info ,@Sample_interval char(8) ,@data_gather_bat_file_name sysname ,@begin_time varchar(20) ,@end_time varchar(20) ,@data_gether_start_file_name sysname ,@perfmon_data_file_name sysname ,@perfmon_log_format varchar(10) ; select @file_max_size_mb = '50' --MB ,@perfmon_folder_path = 'C:\Temp\perfmon\'--file root folder ,@perfmon_log_format = 'bincirc'-- bin|bincirc|csv|tsv|sql --data gather file info ,@Sample_interval = '00:00:05' --sample interval (every 1 secs) ,@begin_time = '' --format: M/d/yyyy hh:mm:ss [AM|PM]; --null/'': start in two minutes later ,@end_time = '10/26/2016 15:30' --format:M/d/yyyy hh:mm:ss [AM|PM]; --null/'': stop after starting 24 hours --counter conf file info ,@data_collector_set_name = N'PERFMON_BASE' ,@counter_conf_file_name = 'SQLTuning_counters.cfg.txt' ,@data_gather_bat_file_name = 'step1.SQLTuning_data_gather.bat' ,@data_gether_start_file_name = 'step2.perfmon_start_data_gather.bat' ,@perfmon_data_file_name = 'perfmon_data' --memory monitor ,@perf_counts_string = '"\SQLServer:Buffer Manager\Lazy writes/sec" "\SQLServer:Buffer Manager\Page reads/sec" "\SQLServer:Buffer Manager\Page writes/sec" "\SQLServer:Buffer Manager\Page lookups/sec" "\SQLServer:Buffer Manager\Stolen pages" "\SQLServer:Buffer Manager\Page life expectancy" "\SQLServer:Buffer Manager\Free pages" "\SQLServer:Buffer Manager\Target pages" "\SQLServer:Buffer Manager\Total pages" "\SQLServer:Memory Manager\Total Server Memory (KB)" "\SQLServer:Memory Manager\Target Server Memory (KB)" "\Memory\Available MBytes" "\Memory\Pages/sec" "\Memory\System Cache Resident Bytes" "\Memory\Cache Bytes" "\Process(sqlservr)\Private Bytes" "\Process(sqlservr)\Working Set" ' /* --performance monitor ,@perf_counts_string = '"\Processor(_Total)\% Processor Time" "\SQLServer:Broker Statistics\Enqueued Transport Msgs/sec" "\SQLServer:Broker Statistics\Enqueued TransmissionQ Msgs/sec" "\PhysicalDisk(_Total)\Avg. Disk Queue Length" "\PhysicalDisk(_Total)\Disk Read Bytes/sec" "\PhysicalDisk(_Total)\Disk Write Bytes/sec" "\SQLServer:SQL Statistics\Batch Requests/sec" ' */ ; declare @current_time datetime ; select @current_time = GETDATE() ; --by default, the begin time is 2 minutes later if ISNULL(@begin_time,'') = '' set @begin_time = convert(varchar,@current_time,101) + ' ' + convert(char(5),DATEADD(minute,2,@current_time),114) ; --by default, the end time is the same time tomorrow (24 hours) if isnull(@end_time,'') = '' set @end_time = convert(varchar,dateadd(day,1,@begin_time),101) + ' ' + convert(char(5),dateadd(day,1,@begin_time),114) ; ---------checking drivers capacity on server if object_id('tempdb..#drives','U') is not null drop table #drives create table #drives ( id int identity(1,1) not null primary key ,drive varchar(2) ,free_Mb int ) ; insert into #drives exec sys.xp_fixeddrives --driver's capacity should be double of @file_max_size_mb if not exists( --if the driver's capacity is less than size double of @file_max_size_mb select top 1 1 from #drives where drive = left(@perfmon_folder_path,1) and free_Mb > cast(@file_max_size_mb as int) * 2 ) begin declare @drives varchar(2) ; select top 1 @drives = drive --choice another drivers from #drives where free_Mb > cast(@file_max_size_mb as int) * 2 order by free_Mb desc if @drives is null --if there is no another drives, choice C driver begin set @drives = 'C' ; raiserror('file should be saved into C driver',10,1) with nowait end set @perfmon_folder_path = @drives + right(@perfmon_folder_path, len(@perfmon_folder_path)-1) ; end --in order to keep folder identical: --using newid as the folder name set @perfmon_folder_path = @perfmon_folder_path + replace(newid(),'-','') + '\' ; --print @perfmon_folder_path --create folder exec sys.xp_create_subdir @perfmon_folder_path --raiserror('%s',10,1,@perfmon_folder_path) with nowait --private parameters declare @sql varchar(8000) ,@fileid int ,@command varchar(2000) ; select @sql = '' ,@fileid = 0 ,@command = '' ,@perfmon_folder_path = quotename(@perfmon_folder_path,'"') ,@data_gather_bat_file_name = @perfmon_folder_path + @data_gather_bat_file_name ,@data_gether_start_file_name = @perfmon_folder_path + @data_gether_start_file_name ,@perf_counts_string = '<V><![CDATA[' + replace( replace( replace( @perf_counts_string,CHAR(10),']]></V><V><![CDATA[' ),',',']]></V><V><![CDATA[' ),CHAR(13),']]></V><V><![CDATA[' ) + ']]></V>' ; --===============================generate counters configure file if object_id('tempdb..#temp','U') is not null drop table #temp create table #temp ( id int identity(1,1) not null primary key ,fileid int ,command varchar(8000) null ) ; -- split the performance counters ;WITH data AS ( SELECT cast(@perf_counts_string AS XML) as c ) INSERT INTO #temp SELECT fileid = 1 ,command = T.C.value('(./text())[1]','sysname') FROM data as a CROSS APPLY C.nodes('./V') AS T(C) --select * from #temp --===============================end generate counters configure file --===============================generate data gather bat ;with DATA AS( SELECT fileid = 2 ,command = '@echo off' union all SELECT fileid = 2 ,command = '::#####setlocal enabledelayedexpansion' union all SELECT fileid = 2 ,command = 'setlocal enabledelayedexpansion' union all SELECT fileid = 2 ,command = '::variables' union all SELECT fileid = 2 ,command = 'set path_root=.' union all SELECT fileid = 2 ,command = 'set file=%path_root%\' + @perfmon_data_file_name union all SELECT fileid = 2 ,command = 'set cfg_file=' + @counter_conf_file_name union all SELECT fileid = 2 ,command = 'Logman stop ' + @data_collector_set_name union all SELECT fileid = 2 ,command = 'Logman delete ' + @data_collector_set_name union all SELECT fileid = 2 ,command = 'logman create counter ' + @data_collector_set_name + case when @begin_time = '' then '' when @begin_time is null then '' else ' -b ' + @begin_time end + case when @end_time = '' then '' when @end_time is null then '' else + ' -e ' + @end_time end + ' -f ' + @perfmon_log_format + ' -max ' + @file_max_size_mb + ' -si ' + @Sample_interval + ' --v -o "%file%" -cf "%path_root%\