SQL Server Trace gives us ability to see what is happening inside the SQL Server. There are many events that we can defined what information we like to see from the trace. The more we enable and the more it may cost SQL Server to split those information.
Server Side Trace Client Trace
SQL Server Profiler is the GUI tool to create the trace event. By default, it is running the Client side trace, unless you explicitly want the server side trace. After SQL Server generate the trace, it would pass the trace to the IO Provider. SQL server Support 2 types of IO Providers.
- Flat-file provider
- Rowset provider
Because the Profiler use the Rowset provider by default in the Client side trace mode. It add more load on SQL Server . Linchi Shea’s benchmarking also has elaborate more detail on this point.
The screenshot below show how to enable the Server Trace when use profiler.
How to see what Trace is currently running
select * from sys.traces
By default, you will at least see trace id 1 is enable. It is default trace that SQL Server defined.
How to load the trace to the table
Here is the SQL form James Rowland-Jones in the Book: “Professional SQL Server 2008: Internals and Performance Tuning”.
The SQL demo use fn_trace_gettable to load the trace file to the table. The fn_trace_gettable takes 2 parameters, filename and number of the files. Default means all the roll over traces files.
Find what Event is enable for the Trace
Find out What filter with the Trace
Create the trace
Alter trace permission is required to create the trace.
Here is the sample script to create the trace, add event and add the filter to the trace.
Use Profiler to create the Server side trace template
As you can see, use the sp_trace_setevent and sp_trace_setfilter to create the trace is tedious work. You need to know exactly what the event id to pass. We can use Profiler to simplify this work.
Start the new trace in the profiler
Configure the trace name, enable the stop time.
Click Run the start the Trace and click the STOP to stop the trace right away.
File –> Export –> Script Trace definition –> For SQL Server 2005 - 2008R2
Open the output SQL. It looks like below. We can then update it.
Option 2 is for TRACE_FILE_ROLLOVER when the trace file reach the maxFilesize.
DateTime is when the Trace would stop. ( How long the trace would run)
Then we need to decide where is the trace file output location.
We can then execute the script to create the trace. The script would also output the trace ID.
Start/stop/remove the trace
Here is the sample script to start/stop/remove the trace.
The default trace can not be stop by using sp_trace_setstatus
Even we use sp_trace_setstatus with status id 0 to remove the trace. The trace file still on the disk. It has to be removed manually. If we try to create another trace on the same location, we would get the Msg 19067 error.
Security Issue with Trace
- The one with the alter trace permission can have control on all the traces on the sql server. even it is not created by itself.
- The one has the alter trace permission can see all the object definition, parameter pass through the trace.
- If the trace file is write into the files. The files may contents the sensitive information pass to the trace. the file also needs to be secure.
Exploring server side trace
Unfortunately, once the trace is created in the SQL Server. It is difficult to script out the original definition. James Rowland-Jones in the Book: “Professional SQL Server 2008: Internals and Performance Tuning” has the handy script “CH10 Trace Hydrate.sql” that can do the job.
- SSIS Log analysis: http://www.sqlis.com/sqlis/post/New-SSIS-tool-on-Codeplex-SSIS-Log-Analyzer.aspx
- ClearTrace: http://www.scalesql.com/cleartrace/
- Automating Server Side Tracing in SQL Server: http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
- Performance Impact: Profiler Tracing vs. Server Side SQL Trace: http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx