问题引出

自从菜鸟的上一篇《如何分析SQL Server Trace文件》文章以后，老鸟对菜鸟是另眼相看：“鸟哥，上篇文章你总结得还不错，当然要赶上我还有点距离”。老鸟还是改不了他得意忘形的模样：“关于Trace文件的自动化分析你研究透了，那怎么自动化部署SQL Trace和Windows性能监视器，你也得研究研究吧”。

自动部署SQL Trace

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

自动部署SQL Trace代码

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

use masterGOset nocount ondeclare     @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 variablesdeclare    @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_folderEND;select    @trace_file = @trace_folder + REPLACE(@@SERVERNAME, N'\', N'');IF @int_filter_spid IS NOT NULLBEGIN    select        @trace_file = @trace_file + cast(@int_filter_spid as varchar)    ;END--select @trace_fileselect top 1    @trace_id = idfrom sys.traceswhere path like @trace_file + N'%'if @trace_id is not nullbegin        -- Start Trace (status 1 = start)    EXEC sys.sp_trace_setstatus @trace_id, @set_trace_status    returnendif OBJECT_ID('tempdb..#trace_event','u') is not null    drop table #trace_eventcreate 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_eventas(        --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_columnas(    select         *        ,trace_column_list_xml =                                 CAST(                                        '
    
    
    
    
    
    
    
    '                                    as xml                                )    from trace_event    where is_trace = 1),dataas(    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_eventselect     trace_event_id = ev.trace_event_id    ,trace_column_id = col.trace_column_id    ,a.event_name    ,trace_column_name = a.trace_columnfrom 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.namewhere col.trace_column_id is not nullorder by ev.trace_event_id;--select * from #trace_event---private variablesselect     @trace_id = 0    ,@do = 1    ,@loop = @@ROWCOUNT    ,@trace_event_id = 0    ,@trace_column_id = 0    ,@return_code = 0    ,@return_decription = '';--create traceexec @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 columnwhile @do <= @loopbegin    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 = ] valuecolumn_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 operator0        = (Equal)1        <> (Not Equal)2        > (Greater Than)3        < (Less Than)4        >= (Greater Than Or Equal)5        <= (Less Than Or Equal)6        LIKE7        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 = 18IF @int_filter_cpu IS NOT NULL    EXEC sys.sp_trace_setfilter @trace_id, 18, 0, 4, @int_filter_cpu--duration filter/ duration columnid=13IF @int_filter_duration IS NOT NULL    EXEC sys.sp_trace_setfilter @trace_id, 13, 0, 4, @int_filter_duration--spid filter/ spid columnid=12IF @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_statusGO--EXEC sys.sp_trace_setstatus 2, 0--GOselect * 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 ondeclare    @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 laterif     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 serverif object_id('tempdb..#drives','U') is not null    drop table #drivescreate table #drives(    id int identity(1,1) not null primary key    ,drive  varchar(2)    ,free_Mb int);insert into #drivesexec sys.xp_fixeddrives--driver's capacity should be double of @file_max_size_mbif 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 nameset    @perfmon_folder_path = @perfmon_folder_path + replace(newid(),'-','') + '\';--print @perfmon_folder_path--create folderexec sys.xp_create_subdir @perfmon_folder_path--raiserror('%s',10,1,@perfmon_folder_path) with nowait--private parametersdeclare    @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 = '
    
    
    
    
    
    
    
    ';--===============================generate counters configure fileif object_id('tempdb..#temp','U') is not null    drop table #tempcreate table #temp(    id int identity(1,1) not null primary key    ,fileid  int    ,command varchar(8000) null);-- split the performance counters;WITH dataAS (    SELECT cast(@perf_counts_string AS XML) as c)INSERT INTO #tempSELECT     fileid = 1    ,command =  T.C.value('(./text())[1]','sysname') FROM data as aCROSS APPLY C.nodes('./V') AS T(C)--select * from #temp--===============================end generate counters configure file--===============================generate data gather bat;with DATAAS(SELECT     fileid = 2    ,command =  '@echo off'union allSELECT     fileid = 2    ,command =  '::#####setlocal enabledelayedexpansion'union allSELECT     fileid = 2    ,command =  'setlocal enabledelayedexpansion'union allSELECT     fileid = 2    ,command =  '::variables'union allSELECT     fileid = 2    ,command =  'set path_root=.'union allSELECT     fileid = 2    ,command =  'set file=%path_root%\' + @perfmon_data_file_nameunion allSELECT     fileid = 2    ,command =  'set cfg_file=' + @counter_conf_file_nameunion allSELECT     fileid = 2    ,command =  'Logman stop ' + @data_collector_set_nameunion allSELECT     fileid = 2    ,command =  'Logman delete ' + @data_collector_set_nameunion allSELECT     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%\%cfg_file%"'union allSELECT     fileid = 2    ,command =  'timeout /t 5')INSERT INTO #tempSELECT * from DATA--select * from #temp--===============================end generate data gather bat--===============================generate start bat file;with DATAAS(SELECT     fileid = 3    ,command =  '@echo off'union allSELECT     fileid = 3    ,command =  'title User defined DCT starting...'union allSELECT     fileid = 3    ,command =  'logman start ' + @data_collector_set_nameunion allSELECT     fileid = 3    ,command =  'echo started.'union allSELECT     fileid = 3    ,command =  'timeout /t 5')INSERT INTO #tempSELECT * from DATA--select * from #temp--===============================end generate start bat fileset    @counter_conf_file_name = @perfmon_folder_path + @counter_conf_file_name;declare cur_command cursor local static forward_only read_onlyforselect fileid,commandfrom #tempwhere command is not nullopen cur_commandfetch next from cur_command into @fileid, @commandwhile(@@FETCH_STATUS = 0)begin    set         @sql = 'echo ' + @command + '>> ' + case @fileid                                                 when 1 then @counter_conf_file_name                                                when 2 then @data_gather_bat_file_name                                                when 3 then @data_gether_start_file_name                                                else ''                                            end    ;    --echo to files    --print @sql    exec sys.xp_cmdshell @sql, no_output    fetch next from cur_command into @fileid, @commandendclose cur_commanddeallocate cur_command--===============================data collection instance deploymentset    @sql = 'cd /d '+ @perfmon_folder_path +' & ' + @data_gather_bat_file_name;--print @sql--run "C:\Temp\perfmon\9E785D1C91FC4171B4241F024FDBD442\step1.SQLTuning_data_gather.bat"exec sys.xp_cmdshell @sql, no_output--===============================output infoselect     begin_time = @begin_time    ,end_time = @end_time    ,perfmon_start_command = 'exec sys.xp_cmdshell ''logman start ' + @data_collector_set_name + ''''    ,perfmon_stop_command = 'exec sys.xp_cmdshell ''logman stop ' + @data_collector_set_name + ''''    ,perfmon_delete_command = 'exec sys.xp_cmdshell ''Logman delete ' + @data_collector_set_name + ''''    ,perfmon_data_file = replace(@perfmon_folder_path,'"','') + @perfmon_data_file_name + '.' + @perfmon_log_format;

Windows性能监视器代码分析

输入参数分析

• @file_max_size_mb：生成的Log文件最大大小，单位MB
• @perfmon_folder_path：Log文件根目录
• @perfmon_log_format：数据收集器Log文件格式，在bin|bincirc|csv|tsv|sql中选择一种
• @Sample_interval：数据收集器数据采集的时间间隔
• @begin_time：数据收集器开始运行的时间
• @end_time：数据收集器停止运行的时间
• @data_collector_set_name：数据收集器的名字
• @counter_conf_file_name：数据收集器配置文件名
• @data_gather_bat_file_name：bat文件名，用于存放数据收集器部署代码
• @data_gether_start_file_name：bat文件名，用于存放数据收集器启动代码
• @perfmon_data_file_name：数据收集器生成的log文件名
• @perf_counts_string：性能监视器抓取的性能指标集

输出分析

• begin_time：收集器开始运行的时间
• end_time：收集器结束的时间
• perfmon_start_command：SSMS中启动收集器的代码
• perfmon_stop_command：SSMS中停止收集器的代码
• perfmon_delete_command：SSMS中删除收集器的代码
• perfmon_data_file：收集器生成的log文件详细地址

样例