After news of the Meltdown and Specter vulnerabilities hit I needed to understand the performance impact. I performed a series of benchmark tests and found myself using SQL Server profiler extensively. It quickly became apparent the overhead introduced by the profiler session was impacting performance of the tests so I switched to a server side trace. The server side trace not only reduced monitoring overhead but also allowed me to work quicker through the tests (GUI vs scripting). Here's how one can convert a profiler session to a server side trace:
Step 1) Configure a trace session through profiler
I'm using the default trace template. One can customize the trace in the event selection tab.
Step 2) Stop the trace and export the script definition
Step 3) Execute the trace definition in SSMS
You will now be able to run a trace session by executing the trace definition.
Here are some useful commands to help manage the server side trace:
a) Start a Trace - Replace D:\SQLTRACE with where you want the trace file
exec @rc = sp_trace_create @TraceID output, 0, N'D:\SQLTRACE', @maxfilesize, NULL
if (@rc != 0) goto error
sp_trace_setstatus @traceid = <TRACE NUM HERE> , @status = 1
b) Find the @traceid value
You can find the trace number in sys.traces
SELECT * FROM sys.traces
c) Stop a Trace and Remove it From Server
sp_trace_setstatus @traceid = <TRACE NUM HERE> , @status = 0 --Stop
GO
sp_trace_setstatus @traceid = <TRACE NUM HERE> , @status = 2 --Remove
d) Clean up Trace File
Even after you remove the trace definition from the server the physical file remains. I like to delete it in without leaving SSMS.
sp_configure 'xp_cmd',1
GO
RECONFIGURE
GO
xp_cmdshell 'del "D:\SQLTRACE.trc"'
GO
sp_configure 'xp_cmd',0
GO
RECONFIGURE
GO
Comments