PROFILE - Manual SQL Server profile script trace


-- sys.traces shows the existing sql traces on the server
select * from sys.traces
go
 
--create a new trace, make sure the @tracefile must NOT exist on the disk yet
declare @tracefile nvarchar(500) set @tracefile=N'M:\path_sp_Trace'
declare @trace_id int
declare @maxsize bigint
declare @stoptime datetime
set @maxsize = 5
set @stoptime = '2014-06-20 12:22'
exec sp_trace_create @trace_id output,2,@tracefile ,@maxsize, @stoptime
go

--- add the events of insterest to be traced, and add the result columns of interest
--  Note: look up in sys.traces to find the @trace_id, here assuming this is the first trace in the server, therefor @trace_id=1
declare @trace_id int
set @trace_id=2
declare @on bit
set @on=1
declare @current_num int
set @current_num =1
while(@current_num <65)
      begin
      --add events to be traced, id 14 is the login event, you add other events per your own requirements, the event id can be found @ BOL https://msdn.microsoft.com/en-us/library/ms186265.aspx
--  exec sp_trace_setevent @trace_id,14, @current_num,@on
--  exec sp_trace_setevent @trace_id,20, @current_num,@on
  exec sp_trace_setevent @trace_id,12, @current_num,@on
  exec sp_trace_setevent @trace_id,13, @current_num,@on
  exec sp_trace_setevent @trace_id,34, @current_num,@on
  exec sp_trace_setevent @trace_id,35, @current_num,@on
  exec sp_trace_setevent @trace_id,36, @current_num,@on
  exec sp_trace_setevent @trace_id,37, @current_num,@on
  exec sp_trace_setevent @trace_id,38, @current_num,@on
  exec sp_trace_setevent @trace_id,40, @current_num,@on
  exec sp_trace_setevent @trace_id,41, @current_num,@on
  exec sp_trace_setevent @trace_id,42, @current_num,@on
  exec sp_trace_setevent @trace_id,43, @current_num,@on
  exec sp_trace_setevent @trace_id,44, @current_num,@on
  exec sp_trace_setevent @trace_id,45, @current_num,@on
  exec sp_trace_setevent @trace_id,165, @current_num,@on
  exec sp_trace_setevent @trace_id,166, @current_num,@on
      set @current_num=@current_num+1
      end
go

--set filter https://msdn.microsoft.com/en-us/library/ms174404.aspx
--Column number Column name
--1 TextData
--6 NTUserName
--7 NTDomainName
--8 HostName
--10 ApplicationName
--11 LoginName
--35 DatabaseName

--exec sp_trace_setfilter @trace_id, 1, 0, 6, N'%GetReckonOnlineProductsForCustomerAndAction%%'
--exec sp_trace_setfilter @trace_id, 35, 0, 6, N'Onyx'  --Database = Onyx
--exec sp_trace_setfilter @trace_id, 35, 1, 6, N'DB2' --Database <> DB2
--exec sp_trace_setfilter @trace_id, 35, 1, 6, N'DB3' --Database LIKE 'DB3'

--turn on the trace: status=1
-- use sys.traces to find the @trace_id, here assuming this is the first trace in the server, so @trace_id=1
declare @trace_id int
set @trace_id=2
exec sp_trace_setstatus  @trace_id,1
 
--pivot the traced event
select
 case EventClass
--  when 14 then 'Audit Login'
--  when 20 then 'Audit Login Failed'
  when 12 then 'SQL:BatchCompleted'
  when 13 then 'SQL:BatchCompleting'
  when 34 then 'SP:CacheMiss'
  when 35 then 'SP:CacheInsert'
  when 36 then 'SP:CacheRemove'
  when 37 then 'SP:Recompile'
  when 38 then 'SP:CacheHit'
  when 40 then 'SQL:StmtStarting'
  when 41 then 'SQL:StmtStarted'
  when 42 then 'SP:Starting'
  when 43 then 'SP:Completed'
  when 44 then 'SP:StmtStarting'
  when 45 then 'SP:StmtCompleted'
  when 165 then 'Performance Statistics' --Occurs when a compiled query plan has been cached for the first time, recompiled, or removed from the plan cache.
  when 166 then 'SQL:StmtRecompile' --Occurs when a statement-level recompilation occurs.
 else 'Others' end as EventClass,
-- case EventSubClass when 1 then 'Nonpooled' else 'Pooled' end as EventSubClass,
 HostName,LoginName,DatabaseName,ApplicationName,StartTime,EndTime,*
 from ::fn_trace_gettable(N'C:\Users\path\Documents\myAPS\SQL Scripts\Trace\path Trace Cache Miss\path Trace Cache Misspath_sp_Trace.trc',default)
--where objectname like '%qsp_GetReckonOnlineProductsForCustomerAndAction%'
order by 3, 5, 2, 6
go
 
-- stop trace. Please manually delete the trace file on the disk
-- use sys.traces to find the @trace_id, here assuming this is the first trace in the server, so @trace_id=1
declare @trace_id int
set @trace_id=2
exec sp_trace_setstatus @trace_id,0 -- stop
exec sp_trace_setstatus @trace_id,2 -- closes and delete
go