Devart Blog

Analyzing SQL Server Events with help of Events Profiler

Posted by on January 12th, 2015

dbForge Studio for SQL Server provides the SQL Events Profiler tool that captures SQL Server events from a server. The events are stored in a trace file. The trace file can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem. Events Profiler shows how SQL Server executes queries internally. This allows you to see exactly what T-SQL statements are submitted to the server and how the server accesses the database to return result sets.
You can use this tool for:

  • Stepping through problem queries to find the root of the problem.
  • Finding and diagnosing slow-running queries.
  • Capturing the series of T-SQL statements that lead to a problem.
  • Monitoring the performance of SQL Server to tune workloads.
  • Correlating performance counters to diagnose problems.

Events Profiler Templates

The Events Profiler tool provides predefined trace templates that allow you to easily configure the event classes that you need for specific traces. Once you selected the template, you can run a trace that records the data for each event class you selected. You can use a template on many traces; the template is not itself executed.

The Standard template, for example, helps you to create a generic trace for recording logins, logouts, completed batches, and connection information. You can use this template to run traces without modification or as a starting point for additional templates with different event configurations.

Permissions Required to Run Events Profiler

To run Events Profiler you need to have the same user permissions as the T-SQL stored procedures that are used to create traces. To run Events Profiler, users must be granted the ALTER TRACE permission.

Specify Events and Data Columns for a Trace File

The Events to Capture tab provides the table that contains each of the traceable event classes.

Also you can select Actions that you want to capture in the current session.

If required, apply filter to a specific event to limit the tracing data.

Finally, you can select columns and set it’s order

Here is the example of the trace window

By using these windows, you can get the time duration of a query and all other events information that you have selected. You can save this result and use it in future. Or you can extract a particular query from the trace, and save it as an SQL script.

See also
Leave a Reply