SQL Server Profiler Examples

Overview

Now that we have covered the functionality of SQL Profiler let’s look a some examples of things we can monitor on our SQL instance using the tool.

Explanation

In this topic I am not going to go through the details of actually setting up each trace using SQL Profiler as you’ve seen enough of that in previous sections of this tutorial. What we will do here is just look at some examples of questions you might be asked as a DBA to investigate when it comes to the performance of a SQL Server instance and how you could answer these questions using SQL Profiler. For each question below we will list the events, data columns and any filters that should be applied as well as any other configuration that may be required.

How to find your longest running SQL Server queries with Profiler?

• Events: RPC:Completed, SQL:BatchCompleted, SP:StmtCompleted: SQL:StmtCompleted
• Data Columns: ApplicationName, ClientProcessID, HostName, LoginName, DatabaseName, StartTime, CPU, Duration, Reads, Writes, RowCounts, TextData
• Filters: Duration > 30000

How to find SQL Server queries with high read, high IO, high CPU, etc. with Profiler?

• Events: RPC:Completed, SQL:BatchCompleted
• Data Columns: ApplicationName, ClientProcessID, HostName, LoginName, DatabaseName, StartTime, CPU, Duration, Reads, Writes, RowCounts, TextData
• Filters: Reads > 5000 or Writes > 1000 or CPU > 10000 etc

How to find SQL Server blocking queries in Profiler?

• Events: Blocked process report
• Data Columns: All
• Filters: None

While there isn’t much to configuring this trace in SQL Profiler there is some additional configuration that needs to be done on the SQL instance side in order for blocked process reports to be generated as by default they are disabled. The blocked process threshold option needs to be enabled on your instance and set to an interval (in seconds) when the report should be generated. Below is the T-SQL code to enable this option. Remember when you are done tracing to disable this option by setting it back to zero.

sp_configure 'show advanced options', 1;
GO
 
RECONFIGURE;
GO
 
sp_configure 'blocked process threshold', 10;
GO
 
RECONFIGURE;
GO

How to find SQL Server deadlocks with Profiler?

• Events: Deadlock graph
• Data Columns: All
• Filters: None

How to capture/audit SQL Server security information with Profiler?

• Events: Audit Login, Audit Logout
• Data Columns: EventClass, ApplicationName, LoginName, ClientProcessID, StartTime, EndTime, TextData, BinaryData, HostName, DatabaseName
• Filters: None

How to capture/audit SQL Server database schema changes with Profiler?

• Events: Audit Login, Audit Logout
• Data Columns: EventClass, ApplicationName, HostName, DatabaseName, LoginName, ObjectID, ObjectName, StartTime
• Filters: DatabaseName not like ‘tempdb’

How to capture a SQL Server trace to be used for replay?

There are a specific set of events and data columns that need to be configured in order for a trace to be used for a replay. You can read more details on exactly what events and data columns are required here, but luckily for us there is a template provided that defines this configuration called “TSQL_Replay”. Simply select this template and start your trace.

Additional Information

• Complete Event Class Reference