问题引出
自从菜鸟的上一篇《如何分析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.tracesSQL 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%\%cfg_file%"'
union all
SELECT fileid = 2 ,command = 'timeout /t 5'
)
INSERT INTO #temp
SELECT * from DATA
--select * from #temp
--===============================end generate data gather bat
--===============================generate start bat file
;with DATA
AS(
SELECT fileid = 3 ,command = '@echo off'
union all
SELECT fileid = 3 ,command = 'title User defined DCT starting...'
union all
SELECT fileid = 3 ,command = 'logman start ' + @data_collector_set_name
union all
SELECT fileid = 3 ,command = 'echo started.'
union all
SELECT fileid = 3 ,command = 'timeout /t 5'
)
INSERT INTO #temp
SELECT * from DATA
--select * from #temp
--===============================end generate start bat file
set
@counter_conf_file_name = @perfmon_folder_path + @counter_conf_file_name
;
declare cur_command cursor local static forward_only read_only
for
select fileid,command
from #temp
where command is not null
open cur_command
fetch next from cur_command into @fileid, @command
while(@@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, @command
end
close cur_command
deallocate cur_command
--===============================data collection instance deployment
set
@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 info
select
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文件详细地址
