Configuring a server side trace

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   drop procedure dbo.configureServerSideTrace go -- Example for: -- Start Trace --   declare @traceID int --   exec dbo.configureServerSideTrace @traceStatus = 1 --                                    ,@traceID = @traceID output --                                    ,@maxFileSize = 10000 --                                    ,@traceFileName = N'e:\VMITrace\Undo' --                                    ,@spId = @@spid -- -- End Trace --   exec dbo.configureServerSideTrace @traceStatus = 0, @traceID = @traceID create procedure dbo.configureServerSideTrace                                  (@traceStatus   bit                  -- 1 => Start Trace                                                                       -- 0 => Stop Trace                                  ,@traceID       int output           -- If the Trace is started, this param will return the TraceID                                                                       -- For stopping the trace, the param has to be provided                                  ,@spId          int           = null -- provide the @@spid, if you want to filter only events for this conection                                                                       -- Optional. If not provided => no filter. Not needed for stopping the trace                                  ,@maxFileSize   bigint        = 5000 -- Maximum Trace File Size in Megabyte. Trace will be stopped, if the filesize is reached.                                  ,@traceFileName nvarchar(200) = null -- Name of the trace file (server side!)                                                                       -- Optional. Not neded for stoping the trace                                                                       -- Attention! If the file already exists, the SP will yield an error                                                                       -- and no trace is started.                                  ) as begin if (@traceStatus = 0) begin    exec sp_trace_setstatus @TraceID, 0    exec sp_trace_setstatus @TraceID, 2    return; end -- Create a Queue declare @rc int exec @rc = sp_trace_create @TraceID output, 0, @traceFileName, @maxfilesize, NULL if (@rc != 0) goto error -- Set the events declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 43, 15, @on exec sp_trace_setevent @TraceID, 43, 48, @on exec sp_trace_setevent @TraceID, 43, 1, @on exec sp_trace_setevent @TraceID, 43, 34, @on exec sp_trace_setevent @TraceID, 43, 35, @on exec sp_trace_setevent @TraceID, 43, 51, @on exec sp_trace_setevent @TraceID, 43, 4, @on exec sp_trace_setevent @TraceID, 43, 12, @on exec sp_trace_setevent @TraceID, 43, 13, @on exec sp_trace_setevent @TraceID, 43, 14, @on exec sp_trace_setevent @TraceID, 43, 22, @on exec sp_trace_setevent @TraceID, 42, 1, @on exec sp_trace_setevent @TraceID, 42, 14, @on exec sp_trace_setevent @TraceID, 42, 22, @on exec sp_trace_setevent @TraceID, 42, 34, @on exec sp_trace_setevent @TraceID, 42, 35, @on exec sp_trace_setevent @TraceID, 42, 51, @on exec sp_trace_setevent @TraceID, 42, 4, @on exec sp_trace_setevent @TraceID, 42, 12, @on exec sp_trace_setevent @TraceID, 45, 16, @on exec sp_trace_setevent @TraceID, 45, 48, @on exec sp_trace_setevent @TraceID, 45, 1, @on exec sp_trace_setevent @TraceID, 45, 17, @on exec sp_trace_setevent @TraceID, 45, 18, @on exec sp_trace_setevent @TraceID, 45, 34, @on exec sp_trace_setevent @TraceID, 45, 35, @on exec sp_trace_setevent @TraceID, 45, 51, @on exec sp_trace_setevent @TraceID, 45, 4, @on exec sp_trace_setevent @TraceID, 45, 12, @on exec sp_trace_setevent @TraceID, 45, 13, @on exec sp_trace_setevent @TraceID, 45, 14, @on exec sp_trace_setevent @TraceID, 45, 22, @on exec sp_trace_setevent @TraceID, 45, 15, @on -- XML Statistics Profile exec sp_trace_setevent @TraceID, 146, 1, @on exec sp_trace_setevent @TraceID, 146, 51, @on exec sp_trace_setevent @TraceID, 146, 4, @on exec sp_trace_setevent @TraceID, 146, 12, @on -- Filter: Log only events for the provided @@spid if @spId is not null   exec sp_trace_setfilter @TraceID, 12, 0, 0, @spID -- Set the trace status to start exec sp_trace_setstatus @TraceID, 1 goto finish error: select ErrorCode=@rc finish: end go

Some annotations:

• 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 exec dbo.configureServerSideTrace @traceStatus = 1                                  ,@traceID = @traceID output                                  ,@maxFileSize = 10000                                  ,@traceFileName = N'e:\MyTrace\Test'                                  ,@spId = @@spid -- -- Code of interest -- exec dbo.configureServerSideTrace @traceStatus = 0, @traceID = @traceID

Cheers.

SQL Server Start Time

Have you tried finding out the time, your SQL Server instance has been started? There are some sophisticated solutions, like the one from Tracy Hamlin (twitter), which takes advantage of the fact that tempdb is re-created every time, SQL Server starts. Her solution goes like this:

select create_date   from sys.databases where database_id=2

Another answer to the question, I’ve seen sometimes on the internet queries the login time for any of the system processes:

select login_time   from sys.dm_exec_sessions where session_id=1

This was my preferred way – until yesterday, when I discovered the following simple method:

select sqlserver_start_time   from sys.dm_os_sys_info

Easy, isn’t it? Interestingly though, every of the above three queries yields a different result. Here’s a query with a sample output:

select (select sqlserver_start_time           from sys.dm_os_sys_info) as sql_server_start_time       ,(select create_date           from sys.databases where database_id=2) as tempdb_creation_time       ,(select login_time           from sys.dm_exec_sessions where session_id=1) as sysprocess_login_time

Result:

It seems the SQL Server service must be started first. Only after the service is running, tempdb is created followed by a subsequent start of all sysprocesses. I can’t imagine that the diverse three times make any difference in practice, e.g. if you try finding out for how many hours your SQL Server instance is running. But out there may be existing applications that have to be aware of the difference.

Exploring SQL Server Blockings and Timeouts

Last Thursday I was giving a presentation about information collection and evaluation of SQL Server Blockings and Timeouts at the regional PASS chapter meeting in Munich.

You may download the presentation as well as the corresponding scripts here (German only).