Monitoring Microsoft SQL Server Using SQL Server Profiler

Edit online

SQL Server Profiler is a graphical tool used to monitor an instance of Microsoft SQL Server.

This tool is a good troubleshooting tool, but should not be enabled for day-to-day operations because there is an inherent overhead in capturing this data daily. The data about each event can be captured to a file or a table for analysis at a later date.

The SQL Server Profiler can be used to:
  • Monitor the performance of an instance of the SQL Server Database Engine
  • Identify procedures and queries that are executing slowly
  • Replay traces of one or more users
  • Perform query analysis
  • Troubleshoot problems
  • Audit and review activity
  • Correlate performance counters
  • Configure trace problems

For more information about Using the SQL Server Profiler, refer to the Microsoft Developer Network Web site, which can be accessed from: https://technet.microsoft.com/en-us/library/ms181091%28v=sql.105%29.aspx.

Enable the following events in SQL Server Profiler to capture deadlock-related information:
  • Deadlock graph
  • Lock: Deadlock
  • Lock: Deadlock Chain
  • RPC:Completed
  • SP:StmtCompleted
  • SQL:BatchCompleted
  • SQL:BatchStarting

For more information about Analyzing Deadlocks with SQL Server Profiler, refer to the Microsoft Developer Network Web site, which can be accessed from: http://msdn.microsoft.com/en-us/library/ms188246(SQL.90).aspx.

For more information about Troubleshooting Deadlocks in Microsoft SQL Server, refer to the Microsoft Developer Network Web site, which can be accessed from: http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/05/617960.aspx.