Skip to main content Skip to footer

SQL Server Trace

Analysing the performance of a database or database server is pretty easy with all the great tools Microsoft has built into the platform. One thing I use from time to time is a custom built database. This allows you to import the SQL Server trace files into a table. You can then slice and dice it however you want, find the top ten highest CPU stored procedures, top ten with the biggest disk IO and so on. 

1. Create a blank database of your choosing

2. Create the following table

CREATE TABLE [dbo].[TraceResults]
[TextData] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Duration] [int] NULL,
[Reads] [int] NULL,
[Writes] [int] NULL,
[CPU] [int] NULL,
[StartTime] [datetime] NULL,
[ProcedureName] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

3. Create the running traces stored procedure

CREATE PROCEDURE [dbo].[RunningTraces] AS 
SELECT * FROM :: fn_trace_getinfo(default) 

4. Create a stored procedure to import the data

CREATE PROCEDURE [dbo].[ImportTraceResults] 
@TraceFile VARCHAR(8000) 
-- Import the trace results to the database 
INSERT INTO TraceResults(TextData, Duration, Reads, Writes, CPU, StartTime) SELECT TextData, Duration/1000, Reads, Writes, CPU, StartTime FROM fn_trace_gettable(@TraceFile,1) 

-- Populate the procedure name column where applicable 
UPDATE TraceResults SET ProcedureName = LEFT(RIGHT(TextData, LEN(TextData) - CHARINDEX(' ',TextData, CHARINDEX('Exec',TextData))),CHARINDEX(' ', RIGHT(TextData, LEN(TextData) - CHARINDEX(' ',TextData, CHARINDEX('Exec',TextData))) + ' ')) WHERE TextData like '%exec%' AND ProcedureName IS NULL 

5. Create a stored procedure to run a SQL Server trace

    ALTER PROCEDURE [dbo].[StartTrace] 
@OutputFileName NVARCHAR(256) = 'C:\Databases\Data\Traces\Trace', @EndTime DATETIME = NULL 
-- SET NOCOUNT ON added to prevent extra result sets from— interfering with SELECT statements. 
-- if the endtime hasn’t been set, then get the current date plus 30 minutes. IF @EndTime IS NULL BEGIN SET @EndTime = DATEADD(mi,30,getdate()) END 
PRINT 'Ending at' 
PRINT @EndTime 
SET @MaxFileSize = 500 
SET @OutputFileName = @OutputFileName + CONVERT(VARCHAR(20), GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','') 

-- create a trace 
exec @rc = sp_trace_create @TraceID output, 0, @OutputFileName, @MaxFileSize, @EndTime 

-- Set the events and columns, this is equivilent to the columns in sql profiler. 
declare @on bit 
set @on = 1 
exec sp_trace_setevent @TraceID, 10, 16, @on 
exec sp_trace_setevent @TraceID, 10, 1, @on 
exec sp_trace_setevent @TraceID, 10, 17, @on 
exec sp_trace_setevent @TraceID, 10, 18, @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, 12, 16, @on 
exec sp_trace_setevent @TraceID, 12, 1, @on 
exec sp_trace_setevent @TraceID, 12, 17, @on
 exec sp_trace_setevent @TraceID, 12, 14, @on 
exec sp_trace_setevent @TraceID, 12, 18, @on 
exec sp_trace_setevent @TraceID, 12, 12, @on 
exec sp_trace_setevent @TraceID, 12, 13, @on 

-- Set the trace status to start 
exec sp_trace_setstatus @TraceID, 1 
PRINT 'TraceID' 
SET @OutputFileName = @OutputFileName + '.trc' 
PRINT 'Trace File' 
PRINT @OutputFileName 
-- keep cheking the status of the trace until it has completed. 
SELECT @Count = COUNT(*) FROM :: fn_trace_getinfo(default) WHERE TraceID = @TraceID WHILE @Count > 0 BEGIN WaitFor Delay '00:00:10' SELECT @Count = COUNT(*) FROM :: fn_trace_getinfo(default) WHERE TraceID = @TraceID END -- read in the data to the table 
PRINT 'Importing Results' 
EXEC dbo.ImportTraceResults @TraceFile = @OutputFileName 

6. Create views to pull out Stored Procedures and Non Stored Procedures from results

    CREATE VIEW [dbo].[v_NonStoredProcedures] 
SELECT TextData, SUM(DURATION) AS TimeImpact, SUM(reads) AS IOImpact, SUM(CPU) AS CpuImpact, COUNT(*) AS ExecutionCount
FROM TraceResults
WHERE ProcedureName IS NULL
GROUP BY TextData, Duration

CREATE VIEW [dbo].[v_StoredProcedures] AS
SELECT ProcedureName,SUM(DURATION) AS TimeImpact,SUM(reads) AS IOImpact,SUM(CPU) AS CpuImpact,AVG(Duration) AS AverageTimeImpact,AVG(reads) AS AverageIOImpact,AVG(CPU) AS AverageCpuImpact,COUNT(*) AS ExecutionCount
FROM TraceResults
GROUP BY ProcedureName

The system is really easy to use, you can run the process by doing the following:

    -- 5 minutes of stats
 DECLARE @End DateTime
 SET @End = DateAdd(Minute, 5, GetDate())
 EXEC [dbo].[StartTrace] @EndTime = @End

You can use any amount of time you like, obviously when this is running the server will be under more load. However, you also don't want to run it out of hours because you want a true representation of the server. When the process is running you can check its status by calling [dbo].[RunningTraces]. Once complete you can analyse the results by using the views. For example SELECT * FROM dbo.V_StoredProcedures. It's pretty likely you are using .NET applications accessing the database. If so the logs will have a ton of sp_reset_connection calls. It will open a connection and keep it open as long as it thinks it's useful to do so, connection sharing. To ensure that the connections don't interfere with each other it calls sp_reset_connection. So you probably want to delete everything in the TraceResults for sp_reset_connection, as its just fluff that you don't need to worry about. Hope you find the above useful, it's served me a bunch of times so I'm sure it will help you too.

About the author

Richard Brisley

I'm a multi-award winning Sitecore developer. Currently working for Sagittarius Marketing as a solutions architect to understand customer needs and produce multi-national high-performance websites.

About Me

As a Tech Lead for Sagittarius marketing who I have been with for the last twelve years. I oversee a team of seven working pods, including numerous developers and contractors in multiple global locations. This involves supporting the developers with coding issues, meetings and phone calls with their clients and going out of pitches with potential new clients.

I have extensive experience building and supporting Sitecore websites from Sitecore 6+ including Helix pattern, I scored 100% in the Sitecore 7 certification exam. Experience managing and maintaining SQL Server, integration with numerous third parties such as Salesforce, AppDynamics, New Relic, Dynamics CRM and many payment gateways.

The first Sitecore website I developed was Skiweekends which was architected and developed by me. It won the Sitecore Experience Award the main award during the Sitecore Experience awards ceremony and the Sitecore Best Travel & Tourism award. I also was lucky enough to perform the first Sitecore 8 upgrade within the United Kingdom for Liberon.

Personally I have had the honour of being recognised in several award ceremonies. Including the BIMA 100 awards in 2019 in the Tech Trailblazers category and previously in the Dev's and Makers category. I’ve been highly commended twice in the Wirehive 100 Techie of the Year awards. Due to my involvement in many aspects of Sagittarius work, many of the awards for their clients I've also been involved in.