DBA

How to use server side trace to capture running query T SQL

Advertisements

Capturing queries by using server side trace

In general, DBA will have a report most of time the server and application web is running slow. You have to find yourself what query is running slow and benchmark and tune it.

Profiler trace is good to capture the running code. But it is a GUI and we have to start, capture and stop it. This will also make some performance slowness to the server and you it is hard to read and store data.

The server side trace is another option and it is a lightweight and T-SQL code, we can create a stored procedure and schedule it on SQL server agent job to automate this.

Here are the steps and scripts:

Go to SQL server version — open performance tools  — SQL server profiler — File menu — New trace — Event selections — check show all events and columns — in the event selection go to SP: RPC completed, SP completed, SP stmt completed in TSQL: SQL batch completed and SQL stmt completed– Run and stop the trace. Now goto file menu again — Export  — Script Trace Definition  — choose the version and save it.

In stored procedure — RPC completed, SP completed, SP stmt completed,

T-SQL event category – SQL BatchCompleted, SQL stmt completed.

Scripts

From the trace export script find between ‘– Set the events’ and ‘– Set the Filters’ and replace the script in the SP based the events you have seleted.

 

Create a table, this will have a trace location, it will helpful to find the location and timeframe, you can also write a dynamic query to export the trace data into a table.

Create Table tbl_audit_log
(
ID int identity primary key,
Trc_Date Datetime,
Trc_Filename varchar(1000),
Trc_message varchar(1000),
Trc_ID Int
)

go

Create PROC [dbo].[USP_DBATRACE_Capture]

@TRC_PATH VARCHAR(1000)='D:\SQL_Trace\',
@MESSAGE varchar(1000)=NULL
AS
BEGIN

/****************************************************/
/* Created by: Muthukkumaran Kaliyamoorthy          */
/* Date: 03/13/2019  06:06:13 AM         */
/****************************************************/

if exists (select 1 from fn_trace_getinfo(default) where cast ([value] as varchar) like '%\AuditTrace_%')
Begin
goto Existing
end
else
begin

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
declare @File_name nvarchar(256)
set @File_name=@TRC_PATH + 'AuditTrace_'+convert(varchar(20),getdate(),12)+'_'+cast(datepart(hour,getdate()) as varchar)+'_'+cast(datepart(minute,getdate()) as varchar)+'_'+cast(datepart(Second,getdate()) as varchar)
set @DateTime =dateAdd(minute,5,getdate())
set @maxfilesize = 500

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., H:\Audit_login_Trace\. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

/* additional code for log

drop table tbl_audit_log

Create Table tbl_audit_log
(
ID int identity primary key,
Trc_Date Datetime,
Trc_Filename varchar(1000),
Trc_message varchar(1000),
Trc_ID Int
)

*/


exec @rc = sp_trace_create 
 @traceid =@TraceID output
,@options =0
,@tracefile=@File_name
,@maxfilesize= @maxfilesize
,@stoptime = @Datetime
,@filecount = NULL 

if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 121, 1, @on
exec sp_trace_setevent @TraceID, 121, 9, @on
exec sp_trace_setevent @TraceID, 121, 3, @on
exec sp_trace_setevent @TraceID, 121, 11, @on
exec sp_trace_setevent @TraceID, 121, 4, @on
exec sp_trace_setevent @TraceID, 121, 6, @on
exec sp_trace_setevent @TraceID, 121, 7, @on
exec sp_trace_setevent @TraceID, 121, 8, @on
exec sp_trace_setevent @TraceID, 121, 10, @on
exec sp_trace_setevent @TraceID, 121, 12, @on
exec sp_trace_setevent @TraceID, 121, 13, @on
exec sp_trace_setevent @TraceID, 121, 14, @on
exec sp_trace_setevent @TraceID, 121, 15, @on
exec sp_trace_setevent @TraceID, 121, 21, @on
exec sp_trace_setevent @TraceID, 121, 31, @on
exec sp_trace_setevent @TraceID, 121, 35, @on
exec sp_trace_setevent @TraceID, 121, 41, @on
exec sp_trace_setevent @TraceID, 121, 45, @on
exec sp_trace_setevent @TraceID, 121, 46, @on
exec sp_trace_setevent @TraceID, 121, 47, @on
exec sp_trace_setevent @TraceID, 121, 49, @on
exec sp_trace_setevent @TraceID, 121, 51, @on
exec sp_trace_setevent @TraceID, 121, 60, @on
exec sp_trace_setevent @TraceID, 121, 64, @on
exec sp_trace_setevent @TraceID, 121, 66, @on
exec sp_trace_setevent @TraceID, 120, 1, @on
exec sp_trace_setevent @TraceID, 120, 9, @on
exec sp_trace_setevent @TraceID, 120, 3, @on
exec sp_trace_setevent @TraceID, 120, 11, @on
exec sp_trace_setevent @TraceID, 120, 4, @on
exec sp_trace_setevent @TraceID, 120, 6, @on
exec sp_trace_setevent @TraceID, 120, 7, @on
exec sp_trace_setevent @TraceID, 120, 8, @on
exec sp_trace_setevent @TraceID, 120, 10, @on
exec sp_trace_setevent @TraceID, 120, 12, @on
exec sp_trace_setevent @TraceID, 120, 13, @on
exec sp_trace_setevent @TraceID, 120, 14, @on
exec sp_trace_setevent @TraceID, 120, 15, @on
exec sp_trace_setevent @TraceID, 120, 21, @on
exec sp_trace_setevent @TraceID, 120, 31, @on
exec sp_trace_setevent @TraceID, 120, 35, @on
exec sp_trace_setevent @TraceID, 120, 41, @on
exec sp_trace_setevent @TraceID, 120, 45, @on
exec sp_trace_setevent @TraceID, 120, 46, @on
exec sp_trace_setevent @TraceID, 120, 47, @on
exec sp_trace_setevent @TraceID, 120, 49, @on
exec sp_trace_setevent @TraceID, 120, 51, @on
exec sp_trace_setevent @TraceID, 120, 60, @on
exec sp_trace_setevent @TraceID, 120, 64, @on
exec sp_trace_setevent @TraceID, 120, 66, @on
exec sp_trace_setevent @TraceID, 28, 2, @on
exec sp_trace_setevent @TraceID, 28, 66, @on
exec sp_trace_setevent @TraceID, 28, 10, @on
exec sp_trace_setevent @TraceID, 28, 3, @on
exec sp_trace_setevent @TraceID, 28, 11, @on
exec sp_trace_setevent @TraceID, 28, 4, @on
exec sp_trace_setevent @TraceID, 28, 6, @on
exec sp_trace_setevent @TraceID, 28, 7, @on
exec sp_trace_setevent @TraceID, 28, 8, @on
exec sp_trace_setevent @TraceID, 28, 9, @on
exec sp_trace_setevent @TraceID, 28, 12, @on
exec sp_trace_setevent @TraceID, 28, 14, @on
exec sp_trace_setevent @TraceID, 28, 21, @on
exec sp_trace_setevent @TraceID, 28, 25, @on
exec sp_trace_setevent @TraceID, 28, 26, @on
exec sp_trace_setevent @TraceID, 28, 35, @on
exec sp_trace_setevent @TraceID, 28, 41, @on
exec sp_trace_setevent @TraceID, 28, 49, @on
exec sp_trace_setevent @TraceID, 28, 51, @on
exec sp_trace_setevent @TraceID, 28, 60, @on
exec sp_trace_setevent @TraceID, 28, 64, @on
exec sp_trace_setevent @TraceID, 96, 2, @on
exec sp_trace_setevent @TraceID, 96, 66, @on
exec sp_trace_setevent @TraceID, 96, 10, @on
exec sp_trace_setevent @TraceID, 96, 3, @on
exec sp_trace_setevent @TraceID, 96, 11, @on
exec sp_trace_setevent @TraceID, 96, 4, @on
exec sp_trace_setevent @TraceID, 96, 5, @on
exec sp_trace_setevent @TraceID, 96, 7, @on
exec sp_trace_setevent @TraceID, 96, 8, @on
exec sp_trace_setevent @TraceID, 96, 9, @on
exec sp_trace_setevent @TraceID, 96, 12, @on
exec sp_trace_setevent @TraceID, 96, 14, @on
exec sp_trace_setevent @TraceID, 96, 22, @on
exec sp_trace_setevent @TraceID, 96, 25, @on
exec sp_trace_setevent @TraceID, 96, 26, @on
exec sp_trace_setevent @TraceID, 96, 28, @on
exec sp_trace_setevent @TraceID, 96, 29, @on
exec sp_trace_setevent @TraceID, 96, 34, @on
exec sp_trace_setevent @TraceID, 96, 35, @on
exec sp_trace_setevent @TraceID, 96, 41, @on
exec sp_trace_setevent @TraceID, 96, 49, @on
exec sp_trace_setevent @TraceID, 96, 50, @on
exec sp_trace_setevent @TraceID, 96, 51, @on
exec sp_trace_setevent @TraceID, 96, 60, @on
exec sp_trace_setevent @TraceID, 96, 64, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 2, @on
exec sp_trace_setevent @TraceID, 17, 66, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 3, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 7, @on
exec sp_trace_setevent @TraceID, 17, 8, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 25, @on
exec sp_trace_setevent @TraceID, 17, 26, @on
exec sp_trace_setevent @TraceID, 17, 35, @on
exec sp_trace_setevent @TraceID, 17, 41, @on
exec sp_trace_setevent @TraceID, 17, 49, @on
exec sp_trace_setevent @TraceID, 17, 51, @on
exec sp_trace_setevent @TraceID, 17, 55, @on
exec sp_trace_setevent @TraceID, 17, 60, @on
exec sp_trace_setevent @TraceID, 17, 64, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 66, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 4, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 7, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 25, @on
exec sp_trace_setevent @TraceID, 10, 26, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 41, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 49, @on
exec sp_trace_setevent @TraceID, 10, 50, @on
exec sp_trace_setevent @TraceID, 10, 51, @on
exec sp_trace_setevent @TraceID, 10, 60, @on
exec sp_trace_setevent @TraceID, 10, 64, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 4, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 7, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 26, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 41, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 49, @on
exec sp_trace_setevent @TraceID, 12, 50, @on
exec sp_trace_setevent @TraceID, 12, 51, @on
exec sp_trace_setevent @TraceID, 12, 60, @on
exec sp_trace_setevent @TraceID, 12, 64, @on
exec sp_trace_setevent @TraceID, 12, 66, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 4, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 7, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 26, @on
exec sp_trace_setevent @TraceID, 13, 35, @on
exec sp_trace_setevent @TraceID, 13, 41, @on
exec sp_trace_setevent @TraceID, 13, 49, @on
exec sp_trace_setevent @TraceID, 13, 50, @on
exec sp_trace_setevent @TraceID, 13, 51, @on
exec sp_trace_setevent @TraceID, 13, 60, @on
exec sp_trace_setevent @TraceID, 13, 64, @on
exec sp_trace_setevent @TraceID, 13, 66, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - f61be77a-3ee3-4104-8c4e-8a879db24354'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

Insert into tbl_Audit_log
values (getdate(),@File_name,@MESSAGE,@TraceID)

-- display trace id for future references
select TraceID=@TraceID
goto finish

end
error: 
select ErrorCode=@rc
Existing:
select 'Trace already running' as Info
finish: 


END

——— Run the SP

Following is the excute code to start the trace, you can schedule it in the agent job to capture overnight and days.

exec [USP_DBATRACE_Capture] 'D:\SQL_Trace\','loginaudit'

select * from tbl_Audit_log order by trc_date desc

exec sp_trace_setstatus 2, 0 --Stops the specified trace.
exec sp_trace_setstatus 2, 2 --Closes the specified trace and deletes its definition from the server.

View the trace results:

select sessionLoginname,databaseName,objectname,servername,duration,loginname,applicationname,hostname,textdata,* from fn_trace_gettable('D:\SQL_Trace\AuditTrace_191019_19_38_58.TRC',default)

select * from fn_trace_getinfo (default)

Example – Load data dynamicly

select 
--'insert into [M_tbl_trace_top_10_data_from_AuditTrace_170705_0_40_0_AuditTrace_170705_6_30_0]'+
' select top 10000 starttime,endtime,
datediff(minute, starttime, endtime)as ''Code run Minute'',Objectname 
, ''textdata'' = cast(textdata as varchar(4000)) 
,databaseName,applicationname,
CPU ,Reads ,Writes , RowCounts,
loginname,NTUserName,
hostname,sessionLoginname,TE.name
from fn_trace_gettable(''I:\DBA_Trace_file\'+SUBSTRING(trc_filename,CHARINDEX ('a',trc_filename,15),50)
+ '.trc'',default) T
LEFT JOIN sys.trace_events TE ON  T.EventClass = TE.trace_event_id
where databaseName=''tandem'' and datediff(minute, starttime, endtime)>=1 
and Objectname  in (''SP Name'')

order by ''Code run Minute'' desc
'
,SUBSTRING(trc_filename,CHARINDEX ('a',trc_filename,15),50)
from dba.dbo.tbl_Audit_log 
where trc_date between '2017-07-18 02:05:29.000' and '2017-07-18 15:02:14.000'
order by trc_date

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: