As a DBA or T-SQL programmer, you may often rely on the query execution plan to solve performance issues in your SQL Server applications. However, this approach has its limitations as it only looks at one query at a time. In a dynamic system like SQL Server, where multiple queries are executed simultaneously, the performance of one query can impact another’s. In some cases, improving the performance of one query may require modifying a different query.

In this article, we will discuss a technique that can help you analyze the overall system performance and the impact of changes made to queries. By using server-side traces, you can gather physical evidence and collect hard numbers to compare the performance before and after modifications.

Creating the Trace

To create a trace, you can use the following code:

-- Declare variables
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxFileSize bigint
DECLARE @fileName NVARCHAR(128)
DECLARE @on bit
DECLARE @off bit

-- Set values
DECLARE @CurrentDate NVARCHAR(20)
SET @CurrentDate = CONVERT(VARCHAR(20),getdate(), 112)
SET @maxFileSize = 10
SET @fileName = N'C:\Trace' + @CurrentDate
SET @on = 1
SET @off = 0

-- Create trace
EXEC @rc = sp_trace_create @TraceID output, 2, @fileName, @maxFileSize, NULL

-- If error end process
IF (@rc != 0) GOTO error

-- Set the events and data to collect
EXEC sp_trace_setevent @TraceID, 10,  2, @off   --BinaryData
EXEC sp_trace_setevent @TraceID, 10, 12, @on    --SPID
EXEC sp_trace_setevent @TraceID, 10, 13, @on    --Duration
EXEC sp_trace_setevent @TraceID, 10, 14, @on    --StartTime
EXEC sp_trace_setevent @TraceID, 10, 15, @on    --EndTime
EXEC sp_trace_setevent @TraceID, 10, 16, @on    --Reads
EXEC sp_trace_setevent @TraceID, 10, 17, @on    --Writes
EXEC sp_trace_setevent @TraceID, 10, 18, @on    --CPU
EXEC sp_trace_setevent @TraceID, 10, 24, @on    --IndexID
EXEC sp_trace_setevent @TraceID, 10, 34, @on    --ObjectName
EXEC sp_trace_setevent @TraceID, 10, 48, @on    --RowCounts
EXEC sp_trace_setevent @TraceID, 10, 4, @on     --TransactionID

-- Do not collect ADO.NET connection reset calls.
EXEC sp_trace_setfilter @traceid = @TraceID, @columnid = 1, @logical_operator = 0, @comparison_operator = 1, @value = N'exec sp_reset_connection'

-- Start the trace
EXEC sp_trace_setstatus @TraceID, 1

-- Display trace id for future references
SELECT TraceID=@TraceID

GOTO finish

-- Error trap
error:
SELECT ErrorCode=@rc

-- Exit
finish:
GO

This code creates a trace that captures RPC calls and collects various performance-related data such as duration, reads, writes, CPU usage, and more. You can customize the events and data you want to collect based on your specific needs.

Importing the Trace Data

Once you have collected the trace data, you can import it into an SQL table for analysis. Here is an example of how to load the trace data into a table called “Trace1”:

SELECT TOP 500000
ObjectName,
TransactionID,
SPID,
Duration,
StartTime,
EndTime,
Reads,
Writes,
CPU,
RowCounts
INTO Trace1
FROM ::fn_trace_gettable('C:\Trace20090918.trc', DEFAULT)

In this example, the trace data is loaded into the “Trace1” table using the SELECT/INTO statement. The TOP clause is used to capture the same number of calls after each test run, allowing for comparable analysis.

Analyzing the Trace

Once the trace data is imported into the table, you can perform various analyses to gain insights into the system performance. Here is an example of a query that provides basic performance numbers:

SELECT
ObjectName,
COUNT(*),
AVG(Duration),
MAX(Duration),
MIN(Duration)
FROM Trace1
GROUP BY ObjectName

This query calculates the count, average duration, maximum duration, and minimum duration for each stored procedure or object in the trace data. It gives you an overview of the performance of different queries and helps identify areas for improvement.

You can also analyze the distribution of code duration using a query like this:

SELECT
ObjectName,
COUNT(*),
AVG(Duration),
MAX(Duration),
MIN(Duration),
SUM(
    CASE
        WHEN Duration < 10000 THEN 1
        ELSE 0
    END),
SUM(
    CASE
        WHEN Duration BETWEEN 10000 AND 50000 THEN 1
        ELSE 0
    END),
SUM(
    CASE
        WHEN Duration BETWEEN 50000 AND 100000 THEN 1
        ELSE 0
    END),
SUM(
    CASE
        WHEN Duration BETWEEN 100000 AND 200000 THEN 1
        ELSE 0
    END),
SUM(
    CASE
        WHEN Duration BETWEEN 200000 AND 300000 THEN 1
        ELSE 0
    END),
SUM(
    CASE
        WHEN Duration BETWEEN 300000 AND 400000 THEN 1
        ELSE 0
    END),
SUM(
    CASE
        WHEN Duration BETWEEN 400000 AND 500000 THEN 1
        ELSE 0
    END),
SUM(
    CASE
        WHEN Duration > 500000 THEN 1
        ELSE 0
    END)
FROM Trace1
GROUP BY ObjectName

This query provides a distribution of the duration ranges for each stored procedure or object. It helps identify any long-running queries that may impact system stability.

Conclusion

While analyzing the execution plan of individual queries is important, it is equally crucial to analyze the overall system performance and the impact of queries on each other. By using server-side traces and analyzing the collected data, you can gain valuable insights into the performance of your SQL Server applications.

Remember to customize the trace events and data collection based on your specific needs. Experiment with different traces to discover creative ways to address technical and business requirements.

Thank you for reading!