There are times when you would want to capture database activity and the most popular tool that database engineers use is SQL Server Profiler. Note: SQL Server profiler has been deprecated and it is recommended to use Extended Events for tracing any database related activity. However, in this article, we will look into how we can use SQL Server profiler without the need to run from the SQL Server Profiler tool and how we can minimize the performance impact by creating a server side trace.

SQL Server Profiler Impacts Performance

Anytime SQL Server profiler is started, it adds to the performance of the database server. It also impacts the client machine from where it is being run. Then there’s network data round trip between the client and the server. It is recommended that if you MUST run profiler continuously on lower level environments or frequently on-demand, create a server side trace and start and stop the profiler using T-SQL.

In this example, we will look at creating a stored procedure that will help you:

  1. Create and start new trace
  2. Stop the trace
  3. Read the trace data without the need of SQL Server Profiler tool

Since this is just an example, we will capture only two events:

  1. RPC:Completed (Under Stored Procedures)
  2.  SQL:BatchCompleted (Under TSQL)

If you need to start this trace automatically every time SQL Server starts, you can schedule a SQL Agent job and select the schedule type from the job schedule option as “Start automatically when SQL Server Agent starts”. This will make sure that the trace starts as soon SQL Server agent service is started.

Note: Make sure to delete or move any old trace files from F:\DBA_TraceManager_TraceFiles\ folder before starting a new trace.

DBA_TraceManager script will help with:

  1. Creating a server side lightweight trace
  2. Can be started and stopped on-demand
  3. Can be started automatically on server reboot (as long as SQL Agent service is set to auto start)
  4. Trace data will be processed at the server side rather than at a client side
  5. Auto rollover the trace file (.trc) when it reaches 200 MB
  6. Has the ability to read the trace data by simply calling the stored procedure and passing @action parameter value as ‘Read’

DBA_TraceManager accepts a parameter and here are the different values it can accept:

  1. @Action=’Start’ – This will allow you to start a new trace. However, make sure the trace file do not exist at the destination folder.
  2. @Action=’Stop’ – Stops the trace that was created and started using the above stored procedure (i.e. DBA_TraceManager)
  3. @Action=’Read’ – Allows you to read trace data while the trace is in-flight. The helps prevent the need of using SQL Server profiler tool to read profiler data.