If you’ve never used SQL Server Profiler, you truly don’t know what your missing. And that statement has an intentional double meaning. You don’t know what a powerfully informative tool you’re missing, and you really don’t know what’s going on inside your SQL Server box either. Once you try it and figure it out, it’ll be like getting a new birthday present. Or finding $20 in your pants pocket when you wash your jeans.
Using SQL Profiler
There are plenty of tutorials online to help get you started using SQL Profiler. Books Online is a great place to start. It provides a good introduction as well as showing you the permissions required to run SQL Server Profiler. Brad McGehee, who probably knows as much about it as anyone, has written a book dedicated to the subject; it’s available as a free pdf download. And SQLServerPedia and JumpStartTv both have video tutorials so you can watch someone else show you how it’s done.
But Performance was bad before…
SQL Server Profiler can be great on development and test systems, but it only takes a time or two of running SQL Server Profiler on a production server before you learn the hard way that the SQL Server Profiler can have a significantly negative impact on performance. When you fire up the trusty tool, it doesn’t take long before your phone starts ringing. If you thought performance was an issue before, you ain’t seen nothin’ yet.
A disgruntled user calling
In speaking engagements on identifying performance bottlenecks, I often refer to Linchi Shea’s eye-opening work on quantifying the performance impacts fo SQL Server Profiler. His blog post on the subject is clear and convincing. It puts numbers on something that we knew anecdotally.
But what is really noteworthy in the blog is the impact, or really the lack of impact, that Server Side Traces have on performance. I won’t repeat his proof or findings here; go read it for yourself. But suffice it to say, you should use Server Side Traces and not SQL Server Profiler on production systems. Server Side Traces are very similar to SQL Server Profiler traces only without the overhead.
Configuring Server Side Traces
Okay, okay, Server Side Traces are the way to go. But have you seen the scripts required to set one of those up? They are ugly! Just look at the Books Online example!
Here’s another example. The script below creates a really simple Server Side Trace. It only captures a few events (Stored Procedures – RPC:Completed, SP:StmtCompleted and TSQL – SQL:BatchCompleted) for the AdventureWorks and AdventureWorksDW databases, saving the output to a local drive. It’s not quite Assembler, but it’s not that fun to write either.
– Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 100
– Create the trace
exec @rc = sp_trace_create @TraceID output, 0, N’E:\demo\demotrace’, @maxfilesize, NULL
if (@rc != 0) goto error
– Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @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, 45, 8, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 28, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
– Set the Filters
declare @intfilter int
declare @bigintfilter bigint
set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter
set @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter
– display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
Here is a list event ids and filter options for Server Side Traces. Fortunately, we don’t have to create all this by hand. We can return to our beloved SQL Server Profiler and have it do the heavy lifting for us.
Turning SQL Server Profiler traces into Server Side Traces
Once you’ve configure a trace in SQL Server Profiler, you can export the trace definition. Click on File | Export | Script Trace Definition and select the version of SQL Server you have. I’m using the version of SQL Server Profiler that came with SQL Server 2008 and I have the option to script the trace definition for SQL Server 2005-2008. If you’re using SQL Server 2000, you have your own special option for their trace definition.
To see the fruits of our efforts, let’s look at the script file to see the trace definition in T-SQL. As you can see, it’s all there.
/****************************************************/
/* Created by: SQL Server 2008 Profiler */
/* Date: 08/27/2009 08:59:12 AM */
/****************************************************/
– Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 500
– Please replace the text InsertFileNameHere, with an appropriate
– filename prefixed by a path, e.g., c:\MyFolder\MyTrace. 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
exec @rc = sp_trace_create @TraceID output, 0, N’E:\demo\demotrace2′, @maxfilesize, 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, 27, 7, @on
exec sp_trace_setevent @TraceID, 27, 15, @on
exec sp_trace_setevent @TraceID, 27, 55, @on
exec sp_trace_setevent @TraceID, 27, 8, @on
exec sp_trace_setevent @TraceID, 27, 32, @on
exec sp_trace_setevent @TraceID, 27, 56, @on
exec sp_trace_setevent @TraceID, 27, 64, @on
exec sp_trace_setevent @TraceID, 27, 1, @on
exec sp_trace_setevent @TraceID, 27, 9, @on
exec sp_trace_setevent @TraceID, 27, 41, @on
exec sp_trace_setevent @TraceID, 27, 49, @on
exec sp_trace_setevent @TraceID, 27, 57, @on
exec sp_trace_setevent @TraceID, 27, 2, @on
exec sp_trace_setevent @TraceID, 27, 10, @on
exec sp_trace_setevent @TraceID, 27, 26, @on
exec sp_trace_setevent @TraceID, 27, 58, @on
exec sp_trace_setevent @TraceID, 27, 66, @on
exec sp_trace_setevent @TraceID, 27, 3, @on
exec sp_trace_setevent @TraceID, 27, 11, @on
exec sp_trace_setevent @TraceID, 27, 35, @on
exec sp_trace_setevent @TraceID, 27, 51, @on
exec sp_trace_setevent @TraceID, 27, 4, @on
exec sp_trace_setevent @TraceID, 27, 12, @on
exec sp_trace_setevent @TraceID, 27, 52, @on
exec sp_trace_setevent @TraceID, 27, 60, @on
exec sp_trace_setevent @TraceID, 27, 13, @on
exec sp_trace_setevent @TraceID, 27, 6, @on
exec sp_trace_setevent @TraceID, 27, 14, @on
exec sp_trace_setevent @TraceID, 27, 22, @on
exec sp_trace_setevent @TraceID, 189, 7, @on
exec sp_trace_setevent @TraceID, 189, 15, @on
exec sp_trace_setevent @TraceID, 189, 55, @on
exec sp_trace_setevent @TraceID, 189, 8, @on
exec sp_trace_setevent @TraceID, 189, 32, @on
exec sp_trace_setevent @TraceID, 189, 56, @on
exec sp_trace_setevent @TraceID, 189, 64, @on
exec sp_trace_setevent @TraceID, 189, 1, @on
exec sp_trace_setevent @TraceID, 189, 9, @on
exec sp_trace_setevent @TraceID, 189, 41, @on
exec sp_trace_setevent @TraceID, 189, 49, @on
exec sp_trace_setevent @TraceID, 189, 57, @on
exec sp_trace_setevent @TraceID, 189, 2, @on
exec sp_trace_setevent @TraceID, 189, 10, @on
exec sp_trace_setevent @TraceID, 189, 26, @on
exec sp_trace_setevent @TraceID, 189, 58, @on
exec sp_trace_setevent @TraceID, 189, 66, @on
exec sp_trace_setevent @TraceID, 189, 3, @on
exec sp_trace_setevent @TraceID, 189, 11, @on
exec sp_trace_setevent @TraceID, 189, 35, @on
exec sp_trace_setevent @TraceID, 189, 51, @on
exec sp_trace_setevent @TraceID, 189, 4, @on
exec sp_trace_setevent @TraceID, 189, 12, @on
exec sp_trace_setevent @TraceID, 189, 52, @on
exec sp_trace_setevent @TraceID, 189, 60, @on
exec sp_trace_setevent @TraceID, 189, 13, @on
exec sp_trace_setevent @TraceID, 189, 6, @on
exec sp_trace_setevent @TraceID, 189, 14, @on
exec sp_trace_setevent @TraceID, 189, 22, @on
exec sp_trace_setevent @TraceID, 122, 7, @on
exec sp_trace_setevent @TraceID, 122, 8, @on
exec sp_trace_setevent @TraceID, 122, 64, @on
exec sp_trace_setevent @TraceID, 122, 1, @on
exec sp_trace_setevent @TraceID, 122, 9, @on
exec sp_trace_setevent @TraceID, 122, 25, @on
exec sp_trace_setevent @TraceID, 122, 41, @on
exec sp_trace_setevent @TraceID, 122, 49, @on
exec sp_trace_setevent @TraceID, 122, 2, @on
exec sp_trace_setevent @TraceID, 122, 10, @on
exec sp_trace_setevent @TraceID, 122, 14, @on
exec sp_trace_setevent @TraceID, 122, 22, @on
exec sp_trace_setevent @TraceID, 122, 26, @on
exec sp_trace_setevent @TraceID, 122, 34, @on
exec sp_trace_setevent @TraceID, 122, 50, @on
exec sp_trace_setevent @TraceID, 122, 66, @on
exec sp_trace_setevent @TraceID, 122, 3, @on
exec sp_trace_setevent @TraceID, 122, 11, @on
exec sp_trace_setevent @TraceID, 122, 35, @on
exec sp_trace_setevent @TraceID, 122, 51, @on
exec sp_trace_setevent @TraceID, 122, 4, @on
exec sp_trace_setevent @TraceID, 122, 12, @on
exec sp_trace_setevent @TraceID, 122, 28, @on
exec sp_trace_setevent @TraceID, 122, 60, @on
exec sp_trace_setevent @TraceID, 122, 5, @on
exec sp_trace_setevent @TraceID, 122, 29, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @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, 45, 8, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 28, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
– Set the Filters
declare @intfilter int
declare @bigintfilter bigint
set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter
set @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter
– Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
– display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
This looks remarkably similiar to the one I showed earlier in the post. I used SQL Server Profiler to create that one for me, too.
Next Up: running and scheduling Server Side Traces
Whew! This blog has almost exceeded our collective attention spans, so we’ll stop here for now. But in the next post, I’ll show you how to run Server Side Traces and even schedule them to run automatically so you can capture benchmarking data.
Until then, I’d love to hear your experiences with SQL Server Profiler and Server Side Traces. Got some good scripts that you don’t mind sharing? Post or link to them in the comments section below.
Joe
