SQL Server Profiler Trace

A Trace can easily be used to monitor what is happening within SQL Server, just start SQL Server Profiler via the Tools Menu in SSMS or via a shortcut.

Once started it is a relatively easy process:

1. Connect to the SQL Server you wish to run the trace on.
2. Either use the default trace properties or specify what you want to trace, but note for performance reasons it is good to be narrow and specific with this.
3. Watch as your trace data gets collected

***SQL Profiler will IMPACT your system*** so just bear this in mind and do not run intensive traces on your system 24/7.

Default Trace

Although setting up a trace will have an impact, there is already a default trace run on SQL 2005 and later versions of SQL Server. You can confirm this for yourself by running the script below, note the column is_default:

SELECT * FROM sys.traces

If you look in the path column you will see a value something like: ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_XX.trc’. Another thing worth mentioning is the _XX which is basically here because the default trace will roll over to new files based on the default 20MB and 5 file rotation.

We can use the following function to have a look at this trace, although I am allowed to omit the _XX which will get me the current trace.

FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', DEFAULT) gt

or you can look at my more detailed script:

SET @tracefile = (SELECT LEFT([path],LEN([path])-CHARINDEX('\',REVERSE([path])))+ '\log.trc' FROM sys.traces WHERE [is_default] = 1) 

,gt.[objecttype] [ObjectTypeID]--http://msdn.microsoft.com/en-us/library/ms180953.aspx 
,sv.[subclass_name] [ObjectType] 
,e.[category_id] [CategoryID] 
,c.[Name] [Category] 
,gt.[EventClass] [EventID] 
,e.[Name] [EventName] 
FROM fn_trace_gettable(@tracefile, DEFAULT) gt 
LEFT JOIN sys.trace_subclass_values sv ON gt.[eventclass] = sv.[trace_event_id] AND sv.[subclass_value] = gt.[objecttype] 
INNER JOIN sys.trace_events e ON gt.[eventclass] = e.[trace_event_id] 
INNER JOIN sys.trace_categories c ON e.[category_id] = c.[category_id] 
WHERE gt.[spid] > 50 
AND (gt.[objecttype] <> 21587 /*Ignore Statistics*/ OR gt.[objecttype] IS NULL) 
AND gt.[databasename] <> 'tempdb' --Ignore tempdb 
AND gt.[starttime] >= DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE())) --From Today 00:00:00.000


Using T-SQL to setup a Trace

So create a trace using the the graphical tool as you want it and then run the following statement which will get the events and filters for you, stop your trace that you had started with the gui and alter your produced script as needed.

*Note the @trace_id variable gets the highest/latest trace run by default

DECLARE @trace_id INT
SET @trace_id=ISNULL(@trace_id,(SELECT MAX([id]) FROM sys.traces))
SELECT * FROM sys.traces WHERE id = @trace_id

SELECT 'EXEC sp_trace_setevent @trace_id,' + LTRIM(STR(gei.[eventid]))+','+LTRIM(STR(gei.[columnid]))+',@on'
+' --'+e.[name]+': '+c.[name]
FROM fn_trace_geteventinfo(@trace_id) gei INNER JOIN sys.trace_events e ON gei.[eventid]=e.[trace_event_id] INNER JOIN sys.trace_columns c ON gei.[columnid]=c.[trace_column_id]

SELECT 'EXEC sp_trace_setfilter  @trace_id,'+LTRIM(STR(gfi.[columnid]))+','+ LTRIM(STR(gfi.[logical_operator]))+','+ LTRIM(STR(gfi.[comparison_operator]))+','
+ (CASE WHEN c.[type_name] IN ('tinyint','smallint','int','real','money','float','bit','decimal','numeric','smallmoney','bigint','varbinary','binary') THEN CONVERT(VARCHAR(MAX),gfi.value)WHEN c.[type_name] IN ('ntext','nvarchar','nchar') THEN ISNULL('N'''+CONVERT(VARCHAR(MAX),gfi.[value])+'''','NULL')ELSE ISNULL(''''+CONVERT(VARCHAR(MAX),gfi.[value])+'''','NULL')END)
+ ' --' + (CASE WHEN gfi.[logical_operator]=0 THEN ' AND ' ELSE ' OR ' END)+c.[name]+(CASE gfi.[comparison_operator] WHEN 0 THEN ' = ' WHEN 1 THEN ' <> ' WHEN 2 THEN ' > ' WHEN 3 THEN ' < ' WHEN 4 THEN ' >= ' WHEN 5 THEN ' <= ' WHEN 6 THEN ' LIKE ' WHEN 7 THEN ' NOT LIKE ' END)+CONVERT(VARCHAR(MAX),ISNULL(gfi.[value],'NULL'))
FROM fn_trace_getfilterinfo(@trace_id) gfi INNER JOIN sys.trace_columns c ON gfi.[columnid] = c.[trace_column_id]

The above generated sql can then be copied and pasted into the following template in the appropriate placeholders.

SET @on=1
DECLARE @maxsize BIGINT,@tracefile NVARCHAR(256);
SET @maxsize = 20
--SET @tracefile=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\test' --Do NOT add .trc
SET @tracefile = (SELECT LEFT([path],LEN([path])-CHARINDEX('\',REVERSE([path])))+ '\test' FROM sys.traces WHERE [is_default] = 1) -- We shall use the path of the default trace
SELECT @tracefile
DECLARE @trace_id INT
EXEC sp_trace_create @trace_id output,2,@tracefile ,@maxsize --The 2 means the file will roll over



EXEC sp_trace_setstatus  @trace_id,1 --Start the Trace

WAITFOR DELAY '00:10:00'; --How Long to run trace? 1 minutes?

EXEC sp_trace_setstatus  @trace_id,0 --Stop the Trace
EXEC sp_trace_setstatus  @trace_id,2 --Delete the Trace

SELECT * FROM fn_trace_gettable(@tracefile+'.trc', DEFAULT) gt

How to: Create a Trace (Transact-SQL)


Permissions Required to Run SQL Server Profiler

How to: Correlate a Trace with Windows Performance Log Data (SQL Server Profiler)

One thought on “SQL Server Profiler Trace

  1. Pingback: Capturing Deadlocks | Jon Gurgul's Blog

Leave a Reply