As a SQL Server database administrator (DBA), it’s important to have a clear understanding of server side and client side traces. These terms are often misunderstood, leading to confusion among DBAs. In this blog post, we will explore the differences between server side and client side traces to help clarify any misconceptions.
Client Side Trace
SQL Server Profiler provides a user interface that allows DBAs to easily choose the required events and add filters to capture specific data. When SQL Server Profiler is run to connect to a SQL instance and capture a trace, it is referred to as a client side trace. Even if the profiler is running on the server itself, it is still considered a client side trace.
During a client side trace, whenever an event is raised in the SQL Engine (such as a statement completed or an RPC completed), the data is sent to the client running the profiler and displayed in the user interface. This type of trace is also known as a rowset trace.
Server Side Trace
A server side trace, on the other hand, is a trace process that runs directly on the SQL Server itself. It collects trace data in a similar fashion as the profiler, but it is captured using T-SQL scripts. There are system level extended stored procedures, such as sp_trace_create, sp_trace_setstatus, sp_trace_setevent, and sp_trace_setfilter, that can be used to start, stop, pause, and filter the trace.
To generate a server side trace script, you can use the profiler itself. Start a profiler trace on any server, choose the required events and filters, and then use the “Script Trace Definition” option. This will generate a script that can be executed on any SQL instance using SQL Server Management Studio (SSMS). The script will create a server side trace and capture the data in the location specified in the script.
Disadvantages of Client Side Trace
While client side traces can be useful in certain scenarios, there are a few disadvantages to consider, especially when capturing traces on a busy production server:
- Performance hit: Running a client side trace can impact the performance of the server, especially if the trace captures a large amount of data.
- Risk of dropping events: There is a risk of dropping events and missing important data that could have been helpful in troubleshooting performance issues.
In summary, when trying to solve a performance issue on a busy production SQL server, it is generally recommended to avoid using a profiler trace altogether. Instead, consider using other methods such as server side traces or other performance monitoring tools.
We hope this blog post has helped clarify the differences between server side and client side traces in SQL Server. Understanding these concepts will enable DBAs to make informed decisions when it comes to capturing and analyzing trace data.
Have you had any experiences with SQL Server traces? We would love to hear about them and learn from your insights. Share your thoughts and experiences in the comments below!