When I’m after SQL Server performance problems, SQL Server Profiler is still my number one tool. Allthough I know that extended events provide a mor lightweight solution, those are still a bit cumbersome to use (but I’ve seen that we can expect some improvements with SQL Server 2012).
When I’m using profiler to isolate performance issues, I try to configure server side traces, whenever possible. Fortunately, SQL Server Profiler will help you creating a script for a server side trace (File/Export/Script Trace Definition), so you don’t have to figure out all the event- and column-codes. Very good!
As I was doing the same configuration again and again, I decided to separate the TSQL code for the configuration inside a stored procedure.
And here comes dbo.configureServerSideTrace:
if object_id('dbo.configureServerSideTrace', 'P') is not null
- For the parameters, see the comments.
- Don’t specify a filename extension for the trace file. .TRC will be added automatically.
- Ensure that the output file does not already exist. Otherwise you’ll get an error.
- Very often I replace the code for starting and stopping the trace inside “interesting code” inside a stored procedure. That is, I’m wrapping some more or less awkward code by starting and stopping a trace like this:
declare @traceID int