数据库内核月报 － 2017 / 06

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 = 50			--max file size for each trace file
	,@file_count = 10				--max file count
	
	,@stop_time = '6/13/2017 10:50'	--null: stop trace manully; specify time (stop at the specify time)
	,@int_filter_cpu = NULL				-- >= @int_filter_cpu ms will be traced. or else, skipped.
										--NULL: ignore this filter
	,@int_filter_duration = NULL		--execution duration filter: millisecond
										--NULL: ignore this filter
	--,@int_filter_spid = 151			--integer: specify a spid to trace
										--				
										
	,@set_trace_status = 1	--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 = 'SQL:StmtCompleted'
		,trace_column_list = 'NestLevel,ClientProcessID,EndTime,DatabaseID,GroupID,ServerName,SPID,DatabaseName,NTUserName,IntegerData2,RequestID,EventClass,SessionLoginName,NTDomainName,TextData,XactSequence,CPU,ApplicationName,Offset,LoginSid,TransactionID,IntegerData,Duration,SourceDatabaseID,LineNumber,ObjectID,Reads,RowCounts,Writes,IsSystem,ObjectName,LoginName,ObjectType,StartTime,HostName,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
;

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

--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

USE [master]
GO

CREATE SERVER AUDIT [Audit_Svr_User_Defined_for_Testing]
TO FILE 
( FILEPATH = N'C:\Temp\Audit'
 ,MAXSIZE = 10 MB
 ,MAX_ROLLOVER_FILES = 10
 ,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
 ,ON_FAILURE = CONTINUE
)
GO

USE [master]
GO
ALTER SERVER AUDIT [Audit_Svr_User_Defined_for_Testing] WITH(STATE=ON)
;
GO

USE [testdb]
GO
CREATE DATABASE AUDIT SPECIFICATION [Audit_Spec_for_TestDB]
FOR SERVER AUDIT [Audit_Svr_User_Defined_for_Testing] 
ADD (SELECT, INSERT, UPDATE, DELETE, EXECUTE ON DATABASE::[testdb] BY [public])
WITH (STATE = ON);
GO

DECLARE 
	@AuditFilePath sysname
;
 
SELECT 
   @AuditFilePath = audit_file_path
FROM sys.dm_server_audit_status
WHERE name = 'Audit_Svr_User_Defined_for_Testing'
 
SELECT statement,* 
FROM sys.fn_get_audit_file(@AuditFilePath,default,default)
;

USE master
GO

CREATE EVENT SESSION [svrXEvent_User_Define_Testing] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed
( 
	ACTION 
	( 
		sqlserver.database_id,
		sqlserver.database_name,
		sqlserver.session_id, 
		sqlserver.username, 
		sqlserver.client_hostname,
		sqlserver.client_app_name,
		sqlserver.sql_text, 
		sqlserver.query_hash,
		sqlserver.query_plan_hash,
		sqlserver.plan_handle,
		sqlserver.tsql_stack,
		sqlserver.is_system,
		package0.collect_system_time
	) 
	WHERE sqlserver.username <> N'NT AUTHORITY\SYSTEM'
		AND sqlserver.username <> 'sa'
		AND (NOT sqlserver.like_i_sql_unicode_string(sqlserver.client_app_name, '%IntelliSense'))
		AND sqlserver.is_system = 0
		
)
ADD TARGET package0.asynchronous_file_target
( 
	SET 
		FILENAME = N'C:\Temp\svrXEvent_User_Define_Testing.xel', 
		MAX_FILE_SIZE = 10,
		MAX_ROLLOVER_FILES = 100
)
WITH (
	EVENT_RETENTION_MODE = NO_EVENT_LOSS,
	MAX_DISPATCH_LATENCY = 5 SECONDS
);
GO

USE master
GO

-- We need to enable event session to capture event and event data 
ALTER EVENT SESSION [svrXEvent_User_Define_Testing]
ON SERVER STATE = START;
GO

USE master
GO
SELECT *
FROM sys.fn_xe_file_target_read_file('C:\Temp\svrXEvent_User_Define_Testing*.xel', null, null, null)