Published on

September 22, 2007

Optimizing Stored Procedures with SQL Server Execution Plans

As a SQL Server developer, you may often find yourself in a situation where you need to optimize your stored procedures for better performance. One of the most effective ways to do this is by utilizing execution plans. In this article, we will discuss how you can use SQL Profiler to obtain execution plans and associate them with the corresponding queries.

Many SQL Server users are familiar with obtaining execution plans through Query Analyzer. However, not everyone knows that SQL Profiler can also be used to capture execution plans of queries running on a specific system. The challenge arises when you have a SQL Profiler trace file and need to extract only the execution plans and their associated queries from the file.

To start, you can execute the SQL Profiler trace using a script instead of the GUI. This method reduces the impact on the system and allows you to capture traces even on highly active systems. When setting up the trace, make sure to include the following code to obtain the execution plans:

exec sp_trace_setevent @traceid, 68, 1, @on --Execution Plan, TextData
exec sp_trace_setevent @traceid, 68, 2, @on --Execution Plan, BinaryData
exec sp_trace_setevent @traceid, 68, 3, @on --Execution Plan, DatabaseID
exec sp_trace_setevent @traceid, 68, 4, @on --Execution Plan, TransactionID
exec sp_trace_setevent @traceid, 68, 6, @on --Execution Plan, NTUserName
exec sp_trace_setevent @traceid, 68, 7, @on --Execution Plan, NTDomainName
exec sp_trace_setevent @traceid, 68, 8, @on --Execution Plan, ClientHostName
exec sp_trace_setevent @traceid, 68, 9, @on --Execution Plan, ClientProcessID
exec sp_trace_setevent @traceid, 68, 10, @on --Execution Plan, ApplicationName
exec sp_trace_setevent @traceid, 68, 11, @on --Execution Plan, SQLSecurityLoginName
exec sp_trace_setevent @traceid, 68, 12, @on --Execution Plan, SPID
exec sp_trace_setevent @traceid, 68, 13, @on --Execution Plan, Duration
exec sp_trace_setevent @traceid, 68, 14, @on --Execution Plan, StartTime
exec sp_trace_setevent @traceid, 68, 15, @on --Execution Plan, EndTime
exec sp_trace_setevent @traceid, 68, 16, @on --Execution Plan, Reads
exec sp_trace_setevent @traceid, 68, 17, @on --Execution Plan, Writes
exec sp_trace_setevent @traceid, 68, 18, @on --Execution Plan, CPU
exec sp_trace_setevent @traceid, 68, 21, @on --Execution Plan, EventSubClass
exec sp_trace_setevent @traceid, 68, 22, @on --Execution Plan, ObjectID
exec sp_trace_setevent @traceid, 68, 25, @on --Execution Plan, IntegerData
exec sp_trace_setevent @traceid, 68, 27, @on --Execution Plan, Eventclass

Once you have started the trace and allowed it to run for a sufficient amount of time based on your system’s activity, you can stop the trace using the following script:

use master
go
-- Declare and set trace ID
declare @TraceID int
set @traceid = 1
-- stop trace
exec sp_trace_setstatus @TraceID, 0
-- close trace file
exec sp_trace_setstatus @TraceID, 2
Print 'TraceID stopping...'

After obtaining the trace file, you can use the fn_trace_gettable function to load the file into a series of tables for data analysis. Here is an example of how to load the trace file into a table:

SELECT IDENTITY(INT, 1,1) AS trc_id , *  INTO trace_tables.dbo.trace_TRC1 
--Increment number if multiple traces exist
FROM ::fn_trace_gettable('C:\SQLProfilerTrace_SCALABIL-_20050331151958.trc', default) nolock
 --Change path if multiple traces exist

If you have multiple trace files or want to load them into different tables, you can use a more complex script to create the necessary tables and load the files accordingly.

Once the trace file has been loaded into the table(s), you can associate the queries with their execution plans by using a script that looks for the execution plan and finds the preceding statement for the corresponding SPID. Although there may be multiple lines of information between the query and its execution plan, you can easily identify the lines belonging to different SPIDs. Here is an example script:

USE Trace_Tables
GO
SET NOCOUNT ON
DECLARE @executiontree TABLE (id INTEGER IDENTITY(1,1), idnumber INTEGER, spid INTEGER) 
DECLARE @trc_id INTEGER
DECLARE @spid INTEGER
DECLARE @rowcounts INTEGER
INSERT INTO @executiontree (idnumber, spid) 
SELECT trc_id, SPID FROM Trace_Tables_TRC1  
WHERE TextData LIKE '%Execution Tree%'
SET @rowcounts = @@ROWCOUNT
WHILE @rowcounts > 0
BEGIN
SELECT @trc_id = (SELECT idnumber FROM @executiontree WHERE id = @rowcounts) 
SELECT @spid = (SELECT SPID FROM @executiontree WHERE id = @rowcounts) 

SELECT TOP 1 TextData FROM Trace_Tables_TRC1 
WHERE TextData IS NOT NULL
AND trc_id < @trc_id
AND SPID = @spid
ORDER BY trc_id DESC
SELECT TextData FROM Trace_Tables_TRC1 
WHERE trc_id = @trc_id
SET @rowcounts = @rowcounts- 1
END

By utilizing the SPID column of the trace file, you can easily associate queries with their execution plans. Although some adjustments may be required based on your environment, the information provided in this article will help you get started with optimizing your stored procedures using SQL Profiler traces.

About the Author

The author of this article has been working with SQL Server since the mid-1990s. With extensive experience as a SQL Server DBA, they have worked on various projects involving SQL Server, Oracle, Sybase, DB2, Access, and other database platforms. Their expertise ranges from production DBA to database architect, and they have worked with databases ranging from a few megabytes to over 30 terabytes in size. To learn more about the author and their work, visit their website: www.TransactSQL.Com

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.