Analyzing SQL Server Events with help of Events Profiler

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.

Select the Standard (default) template from the list

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.

Specify the events that you want to capture

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

Select the actions to capture in the current event session

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

Filter profile server events

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

Select the columns you want to capture in the session

Here is the example of the trace window

The example of the trace window with the server events

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 the future. Or you can extract a particular query from the trace, and save it as an SQL script.

Overview the main features and capabilities available in dbForge Studio for SQL Server

Comments are closed.