Custom user configurable SQL Server Profiler events

Thu Oct 16, 2008 by Mladen Prajdić in sql-server

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:

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:


 
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.
 


    
        
              
        
        
            
             
            
        
    


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... :))


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... :))