Devart Blog

Capturing SQL Server Trace Data

Posted by on March 16th, 2015

One of the best ways to gather information about SQL Server workloads is to capture trace information. In this article, we will look at how to use SQL Trace and Server Events Profiler for tracing SQL Server activity. You can use this information for auditing, monitoring and troubleshooting SQL Server performance. This article is intended for DBAs who need to collect information about workloads running on SQL Server for analytic purposes.

Gathering Trace Information

Workload information gathered from SQL Server may be used for many purposes:

  • Troubleshooting performance
  • Auditing activity
  • Collecting sample data for testing and performance analysis
  • Debugging T-SQL statements and stored procedures that fail to execute correctly on a production server.

For production server performance is usually critical, and incorrect workload collection using SQL Trace can affect server throughput negatively. Therefore, you need to be careful about how you collect trace data.

Workload Analysis

Collecting workload information from SQL Server is only the half of the work that you need to do. You also need to be able to analyze the workload information that has been collected. Identifying key events and trends is the second half of the work to be done. Such analysis will allow you to answer the following questions:

  • Which queries execute most frequently?
  • Which queries have the highest accumulated duration?
  • Which queries have the highest accumulated reads?
  • Why errors or exceptions occur?

SQL Trace Architecture

What is SQL Trace?

SQL Trace is a low-level, server-side event implementation in SQL Server that can be used for real-time or offline analysis of event data through the use of a streaming row set destination or file destination:

What are Events?

An event defines a known point in code execution in SQL Server. For example, you might have the SQL statement completed or SP statement completed and an event fires when its point is reached and the event is enabled for capture by an active trace. Events provide specific information about the conditions for the event firing using standardized set of data columns. Events themselves are divided into categories that specify the origin of the event inside of the Engine. Form SQL Server 2008 onwards there are 180 events in 21 different categories.

Trace Controller

The first component in SQL Server Trace architecture is the Trace Controller. The trace controller is the central component that manages all of the traces created inside of the instance. The trace controller provides a synchronization queue for event data being generated for distribution to the active traces. It also provides a global event bitmap for tracking which events have been enabled by active trace on the server.

Trace-Controller

Event Producers

The second component in SQL Server Trace architecture is the Event Producers. The Event Producers generate data associated with the events that have been enabled by an active trace in the server. By default, all of the events are disabled in SQL Server. When the trace starts, trace controller updates the bitmap to set the bits for the events that are active in the trace, enabling events for collection. All of the event data is provided to trace controller, which synchronizes and distributes the data to the appropriate traces.

Traces

Traces track a list of events and columns based on the trace definition as well as the filtering criteria, if specified. As the data is distributed by the trace controller, each trace filters the data and discards unneeded events, then trims down to the data columns before sending the data to the I/O provider for the trace.

 

Traces-2

I/O Providers

Two I/O providers exist for the SQL Trace — the server side file provider and the client roe set provider for streaming data to a client application. The file provider is designed to guarantee that no event data is lost. Slow writes will cause events to be buffered and lead to threads waiting in SQL. The row set provider does not guarantee no event data loss. Events not consumed within 20 seconds of being buffered are lost to free buffers.

IO-1

 

Using Server Events Profiler

dbForge Studio for SQL Server v4.5 provides the new Server Events Profiler feature. Server Events Profiler simplifies the task of creating new traces and reading the trace data. It uses the rowset provider to read event data generated by the server-side trace feature. Tracing live data using Server Events Profiler can create overhead that reduces the performance of SQL Server due to event buffering in memory. Capturing trace data with profiler can result in lost events if the events are not consumed fast enough from the server.

Creating New Traces

Server Events Profiler can be used to create new traces using many different methods. You can:

  • Create a new trace from scratch by selecting the appropriate events and data columns using the UI
  • Create a new trace based on an existing template that contains the events and data column definitions
  • Create SQL traces using T-SQL stored procedures.

Creating a New Trace from Scratch

  1. Start dbForge Studio for SQL Server.
  2. On the Start page, navigate to Administration, and then click Profile Server Events. The Profile Server Events wizard appears.
  3. Make sure that the Use template checkbox is enabled and the Standard template is selected. Click Next.Trace-From-Scratch-1
  4. On the Events to Capture page, select the Show all checkbox.Trace-From-Scratch_Show_All_Events
  5. To see what events are exposed, expand a required event category.
  6. Once you selected all events to trace, click Execute. The Trace will start and you can see specific events.Trace-Start - 2

Filtering Trace Events

Each data column has different filter criteria:

  • Numeric and datetime columns have =,<>,>=, and <= filters
  • String columns have LIKE and NOT LIKE filters
  • String columns can use % character as a wildcard
  • Datetime filters must use format YYY/MM/DD hh:mm:ss

Not all data columns can be filtered in Trace: data has to exist for the filter to be applied. Events Profiler only displays the available filter criteria for data columns. Filters are applied at the trace level to all events in the trace. Not all events populate the same kind of data in the same columns. Many columns have overloaded values based on the event being collected.

Server Profiler Templates

Server Events Profiler provides preset trace templates that allow you to easily configure the event classes that you will most likely need for specific traces. Below we will review most useful templates.

Standard

The Standard template is the default template for Server Events Profiler. This template is used whenever you create a new trace against SQL Server unless you change the template. This template helps you to create a generic trace for recording logins, logouts, batches completed, and connection information, and can be used for common troubleshooting of normal database activity.

T-SQL

Captures all Transact-SQL statements that are submitted to SQL Server. This template can be used for debugging client applications because it itracks Audit Login, Audit Logout, Existing Connection, PRC:Starting, SQL:BatchStarting. Notice that this trace does not capure any of the completed events, it is only tracking the statements that have been started by the application against the SQL Server. This template therefore cannot be used for performance diagnostics, since  the starting events does not contain the CPU duration, reads or writes information for the events ehn they fire.

T-SQL Duration

Capturea all Transact-SQL statements executed by SQL Server and groups them by duration. This template can be used to identify slow queries by tracking the frequency of frequency of long duration executions occurring, using PRC:Completed, SQL:BatchCompleted events.

SP Counts

Captures stored-procedure executions over time. It can be used for tracking stored-procedure frequency of usage. This template may also be useful for trying to identify objects that are not being used inside of the database. The template captures SP:Starting events only.

Saving Captured Data

Trace data that is captured using Server Events Profiler has to be saved if you want to make use of that data after you close the profiler tool.

Trace data captured may be saved for a number of different reasons. For example, you may save it for future analysis for benchmarking and baseline purposes. You might want to use it for replaying workloads or to apply tuning input for the Database Tuning Advisor.

Conclusion

In this article we reviewed how to use the Server Events Profiler tool provided by dbForge Studio for SQL Server to collecting SQL Server Trace Data.

 

2 Responses to “Capturing SQL Server Trace Data”

  1. Alex Says:

    Hi Guys,
    We own licences for dbforge studio for MySQL.
    is there a way to use this tool to trace audit logins or connection pooling?

    I assume we can do it with this tool, but it does not support mysql.

  2. Andrey Langovoy Says:

    Hi Alex,
    This tool is designed for SQL Server. You can use is absolutely for FREE. It doesn’t support MySQL.

    Regards,
    Andrey Langovoy
    dbForge Team

Leave a Reply