SQL SERVER – Server Side and Client Side Trace

I don’t know where to start, I get to learn things from the most unusual of places. Most of the learnings is based on challenging the statuesque of our understanding. During my interactions with various DBAs one of the common misunderstanding is about server side and client side trace. One of the answer which I commonly hear is “When SQL server profiler is run directly on server, it is called server side trace. When we run on client computer, it’s called client side trace” – this is NOT true.

In this blog, I would try to explain them in little detail.

Client Side Trace

SQL Server Profiler provides user interface with the easy way to choose the required events and adding filters to capture less data. When SQL Server Profiler is run to connect to SQL instance and capture the trace – it is called as client side trace. Even if the profiler is running on server itself, it is client side tracing. Whenever the event is raised in SQL Engine (like statement completed, RPC completed etc.) the data is sent to client which is running profiler and data is shown in the UI. This is also called as rowset trace.

Server Side Trace

Server Side trace process runs on the server and collects the trace data in the similar fashion as profiler but captured using T-SQL scripts. There are system level extended stored procedures to start, stop, pause, filter and they are named as sp_trace*. Below are few of them.

Solarwinds
sp_trace_create
sp_trace_setstatus
sp_trace_setevent
sp_trace_setfilter

The easiest way to generate the script is to use a profiler itself. To do that, first start a profile on any server by choosing required events, filters etc. Then use “Script Trace Definition” option as shown below.

SQL SERVER - Server Side and Client Side Trace server-trace-01

Once script is generated, it can be used to any of SQL instance and executed in SSMS. This would create server side trace and capture the data in the location provided in the script.

/*Server Side*/
SELECT *
FROM   sys.traces
WHERE  is_rowset = 0
/*Client Side*/
SELECT *
FROM   sys.traces
WHERE  is_rowset = 1

SQL SERVER - Server Side and Client Side Trace server-trace-02

Below are few disadvantages on client side trace when captured on a busy production server.

  1. Performance hit.
  2. Risk of dropping event and missing the data which might have been helpful.

To summarize, if you are trying to solve a performance issue on a busy production SQL server, don’t use profiler trace at all. Do let me know about your experience and it can be a great learning for readers too.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Fix SQL Server Index Fragmentation with dbForge Index Manager
Next Post
SQL SERVER – Unable to Start SQL Server After Patching

Related Posts

No results found

Leave a Reply

Menu