Mladen Prajdić Blog

Blog about stuff and things and stuff. Mostly about SQL server and .Net

Custom user configurable SQL Server Profiler events

SQL Server Profiler is a well known tool for tracing different activity that happens between your server and the clients connected to it.

But very few people know that you can have custom events and trace them in the SQL Server Profiler. They can be found under User configurable event group:

ProfilerConfigurableEvents

We can have up to 10 custom events.

Firing these custom events is done by executing sp_trace_generateevent stored procedure which takes event id as the first input parameter. These event ID's span from 82 (UserConfigurable:0) to 91 (UserConfigurable:9)

A simple code example to demonstrate this:

<!–

Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/

–>USE AdventureWorks GO CREATE PROCEDURE spDoStuff @AddressID int AS

UPDATE Person.Address SET ModifiedDate = GETDATE() WHERE AddressID = @AddressID

IF @@ROWCOUNT = 0 BEGIN DECLARE @msg NVARCHAR(128) SELECT @msg = N'No data updated. Rowcount 0. AddressId = ' + CAST(@AddressID AS NVARCHAR(10)) EXEC sp_trace_generateevent 82, @msg END

SELECT * FROM Person.Address WHERE AddressID = @AddressID GO

UPDATES OK EXEC spDoStuff 1 GO this id doesn't exist. trace event is executed as shown in the below picture EXEC spDoStuff -22

GO DROP PROCEDURE spDoStuff

ProfilerCutomEvents

The UserConfigurable:0 event was successfully fired as is shown in the profiler trace.

 

These custom events are highly underused and there isn't even much talk about them at all. That is a shame because they can be a very helpful addition to your debugging or general execution tracing. By using them you can easily narrow the profiler trace and with that the load on the server over long periods of time making them ideal for finding seldom occurring problems in production environment.

 

There is one downside to them though. Running sp_trace_generateevent requires ALTER TRACE permissions to run. Most likely the user running the stored procedure doesn't have that permission. Unless you're running under SA of course :)

A workaround for this is to putting it simply create a wrapper stored procedure around the sp_trace_generateevent stored procedure, sign the wrapper with a certificate and allow public access to it.

 

kick it on DotNetKicks.com
 

Legacy Comments


Adam Machanic
2008-10-16
re: Custom user configurable SQL Server Profiler events
"A workaround for this is to putting it simply create a wrapper stored procedure around the sp_trace_generateevent stored procedure, sign the wrapper with a certificate and allow public access to it."

Indeed--and I put an example for that in "Inside SQL Server 2005: Query Tuning and Optimization". The downloads can be found here:

http://insidesqlserver.com/companion/#Optimization

The example stored procedure is in Chapter 2...

Enjoy!

Mladen
2008-10-16
re: Custom user configurable SQL Server Profiler events
nice adam! thanx.
i was going to leave that for another blog post but hey...if it's already out there... :))