What is Trace and How to implement trace on a server? Tech Arena

 Overview

SQL Server Trace is a powerful tool that is available with SQL Server for a long time; however, it has mostly been underutilized by DBAs. It performs various significant functions such as what is/was running under the SQL Server hood and finding out how queries are resolved internally and what scripts are running to accomplish any T-SQL command. This Document will help you to understand this better and how to implement trace in any environment.

What is Trace?

SQL Trace is a lightweight, but powerful, technology that can run on SQL Server; it collects performance data selected from hundreds of possible performance data points ranging from locks, to connections, to SQL DML statements, to recompiles, you name it”.

The basic elements that we need to set up and implement a trace include some tables and stored procedures, which will be covered in the section below.

 

Table Name.	Details
T_DBA_Trc_TraceLoad_Info	This table includes all general details about a trace. Example. When a trace was started, the last time when data was inserted in a trace file, a path where trace file is saved.
T_DBA_Trc_Master_TraceData	This table includes all the data present in the trace file present at OS Level. The table holds information about SQL queries running on the server, their start time and end time, duration of the queries, name of the database in which queries were/was running and many more pieces of information as per our definition of the trace.
T_DBA_Trace_Filters	In our implementation of trace, this table includes names of the databases which are excluded from the trace table.

 

Procedure Name	Details.
P_DBA_Autotrace_Startup	This Stored Procedure starts the trace on the server. It is implemented in the master database.
P_DBA_Trc_Load_Master_TraceData	This Stored Procedure pulls the data from the trace file at the OS level and pushes that data into the trace table named as T_Dba_Trc_Master_TraceData.
P_DBA_Running_Trace_Stop_Notification	This Stored Procedure stops the existing trace when the Row_count in the OS level file exceeds the specified value in the tb_info table and initiates a new trace. The defined Row_count works as a threshold.
P_DBA_Check_Captured_Trace_Data_Info	This Stored Procedure runs at a fixed interval in the background and keeps a check on the last time when data was inserted in the table. It sends an alert when the timestamp exceeds the specified threshold for data capture.
P_DBA_Purge_Master_TraceData_Table*1.	This Stored procedure purges the old data in the Master trace table.
P_DBA_Delete_Files_By_Date. *1	This Stored procedure purges the old trace files (OS level)

 

*1. Deleting old trace files and data is a healthy practice for query optimization and it frees up some space on your hard drive. Make sure you understand your industry requirements, everyone has different retention requirements.

 

Steps to implement the trace on the server:

Step 1: Insert the following data into the tb_info table.

 

Current-DBA-Trace-Location                         -- Path for the trace at OS level

Current-DBA-Trace-Duration-Filter (Sec)   -- Threshold beyond which events get captured

Current-DBA-Trace-Threshold-RowCount -- Maximum row count for the table

Current-DBA-Trace-MAX-File-Size (GB)      -- Maximum size of the file

Last-Trace-Captured-Threshold (Min)         -- Threshold for Trace Data Capture Notification if the trace                                                                                is not running

 

What is Trace and How to implement trace on a server?

 

Reason: The Conditions in Stored Procedure for the trace pick their values from the tb_info table which act as a threshold for them.

 

Step 2: Create the table -- t_dba_trc_TraceLoad_Info in Admin Database.

Step 3: Implement the Stored Procedure -- P_DBA_Autotrace_Startup*² in the Master Database.

*2 After implementing the Stored Procedure please add it in an auto startup. This will help to auto-start the trace after the Windows restarts.

 

Step 4: Create the table -- t_Dba_trc_Master_TraceData in Admin database.

Step 5: Implement the Stored Procedure -- p_dba_trc_Load_Master_TraceData in the Admin Database.

Step 6: Implement the Stored Procedure -- p_dba_Running_Trace_Stop_Notification in the Admin Database.

Step 7: Implement the Stored Procedure -- p_dba_Check_Captured_Trace_Data_Info in the Admin Database.

Step 8: Implement the Stored Procedure -- p_dba_Purge_Master_TraceData_Table in the Admin Database.

Step 9: Implement the Stored Procedure -- p_dba_Delete_Files_By_Date in the Admin Database.

Step 10: After implementation of the Tables and the Stored Procedures, we need to create jobs to automate the entire setup.

Jobs:

Job Name	Procedure\Command	Schedule
DBA - Collect - Load Master Trace Data	p_dba_trc_Load_Master_TraceData. **	Everyday 15 minutes
DBA - Alert - Running Trace Stop Notification	p_dba_Running_Trace_Stop_Notification**	Everyday 5 minutes
DBA - Check Captured Trace Data Notification	P_DBA_Check_Captured_Trace_Data_Info**	Everyday 5 minutes
DBA - Maint - Reclaim Space	p_dba_Purge_Master_TraceData_Table **	Every Sunday 6:30 am
DBA - Maint - Reclaim OS Trace space	p_dba_Delete_Files_By_Date**	Everyday 12 am
DBA - Maint - Trace Waitfor Delay	use Tempdb                                      Waitfor delay '00:00:06'	Everyday 5 minutes

 

** If not sure about stored procedures, please check the above section dedicated to these stored procedures.

 

POINTS TO REMEMBER:

1. Before making changes in any Stored Procedure on any environment, Please make sure you make those changes and test them in your Local Admin Database.

2. Remember to Check the Recipients or Members in the Job/ Stored Procedure.

 

FREQUENTLY FACED CHALLENGES

1. How to skip a trace file.

 

To skip a trace file, Update the table t_dba_trc_TraceLoad_Info in the admin database. Change the status of column [Filestatus] and [Completed_flag] to 2 and 1 respectively.

 

 

 

2. How to reload a trace file.

 

Please Use the Query below to insert the data in a table.

SET IDENTITY_INSERT t_Dba_trc_Master_TraceData OFF

INSERT INTO t_Dba_trc_Master_TraceData(ServerName,DatabaseName,EventClass,SPID,Duration,StartTime,EndTime,ObjectName,ApplicationName,

Reads,Writes,CPU,RowCounts,TextData,LoginName,HostName)

SELECT '@ServerName',DatabaseName,EventClass,SPID,Duration,StartTime,EndTime,ObjectName,ApplicationName,Reads,Writes,CPU,RowCounts,TextData  ,LoginName,HostName

FROM  ::fn_trace_gettable(@TraceFile, default)

WHERE endtime>'@Starttime' AND endtime<'@EndTime'

SET IDENTITY_INSERT t_Dba_trc_Master_TraceData ON

 

@ServerName -- Server Name

@Starttime -- Begin time.

@EndTime -- End Time.

@TraceFile – Path with the name of the file.

                            E.g. = 'E:\DBA_Trace_Data_Trc_File\DbaTrace20150905002810.trc'

 

3. Step to start a trace new trace.

To start the new trace we need to run the do.p_dba_autotrace_Startup which is in the master database for that server.

 

e.g.-

Exec master. dbo.p_dba_autotrace_Startup

                                                               

4. Steps to change the Trace Duration Filter.

 

To update the new trace duration need to update the value of the Current-DBA-Trace-Duration-FIlter

in the Admin. dbo.tb_Info table.

 

 

 

What is Trace and How to implement trace on a server?

 

 

 

5. Steps to change trace physical file location.

 

To update the new trace duration need to update the value of the Current-DBA-Trace-Location

in the Admin. dbo.tb_Info table.

 

 

What is Trace and How to implement trace on a server?

 

6. Steps to change Trace-MAX-File-Size.

 

To update the new trace duration need to update the value of Current-DBA-Trace-MAX-Size(GB)

in the Admin. dbo.tb_Info table.

 

 

What is Trace and How to implement trace on a server?

 

7. Steps to change the Alert duration for Last-Trace-Captured-Threshold (Min) if a trace is not running on Server.

 

We need to update the value of Last-Trace-Captured-Threshold(Min).

 

 

 

 

 

 

8. Steps to change Current-DBA-Trace-Threshold-RowCount.

 

To update the new trace duration need to update the value of the Current-DBA-Tracehold-Rowcount

in the Admin. dbo.tb_Info table.