Client Side and Server Side Tracing in SQL Server

What comes to your mind first when someone says or talks about the performance tuning?
Obviously, it will be Profiler and Tracing.
Profiler is a tool which is used to trace and store the events that are happening on a particular database or on a server overall. The result of this tool can be stored either as a file or in a table.
When you open the profiler and run the trace against a database it is referred to Client side tracing, this is because the profiler being a client tool and even when the profiler is run within the server, it is also referred as Client Side Tracing.

The Profiler gives a very beautiful GUI with the easy options of selecting only the required events and filtering data. This can reduce the load and impact on the server up to an extent and at the same time, more system resources are utilized when the profiler is run for long hours.

So the next question is, what is the alternative?
One of the alternative is to run the trace on the server side. Server Side trace process runs on the server and collects the trace data in the similar fashion as profiler but involves running T-SQL scripts to start, stop, mention the events to be collected and data to be filtered. This process has less impact on the server when compared to client side tracing and will store the data into a trace file which can be later imported to a table or can be read using simple T-SQL queries.

To set up a server side trace we have to

• Create the trace
  Use "sp_trace_create" to create the trace and to set the trace file location and its size
• Set the events
  Use "sp_trace_setevent" to set the required trace events. The below link gives the list of events that can be added to the trace.
  http://msdn.microsoft.com/en-in/library/ms186265.aspx 
• Filter the data
  Use "sp_trace_setfilter" to filter the tracing data
• Set the status
  Use "sp_trace_setstatus" to set the status to the trace.
  1 = Start the Trace
  0 = Stop the Trace
  2 = Delete the Trace. To delete the trace, the trace should be stopped first.

You can also use profiler to generate the T-SQL statements for running the server side trace.
To do this,

1. Open the profiler and click "New Trace"
2. Connect to the server
3. Provide the Trace name and the trace file location. Also choose "Enable file rollover"
4. Choose the required events and filters
5. Click "Run" and stop it immediately
6. Choose "File" >> "Export" >> "Script Trace Definition" >> Select the version and save the file.

The saved .sql file will contain the required T-SQL scripts to run the server side trace.

Once the trace data is collected you can analyse the data by importing it into a table or by reading the data from the file. To read the data directly from the file use "fn_trace_gettable" function.