Server Performance and Activity Monitoring

Applies to: SQL Server

The goal of monitoring databases is to assess how a server is performing. Effective monitoring involves taking periodic snapshots of current performance to isolate processes that are causing problems, and gathering data continuously over time to track performance trends. Microsoft SQL Server and the Microsoft Windows operating system provide utilities that let you view the current condition of the database and to track performance as conditions change.

The following section contains topics that describe how to use SQL Server and Windows performance and activity monitoring tools. It contains the following topics:

To perform monitoring tasks with Windows tools

• Start System Monitor (Windows)

• View the Windows Application Log (Windows)

To create SQL Server database alerts with Windows tools

• Set Up a SQL Server Database Alert (Windows)

To perform monitoring tasks with Extended Events

• Extended Events

• Quick Start: Extended events in SQL Server

• Manage Event Sessions in the Object Explorer

• Alter an Extended Events Session

• Convert an Existing SQL Trace Script to an Extended Events Session

• View the Extended Events Equivalents to SQL Trace Event Classes

To perform monitoring tasks with SQL Server Management Studio

• View the SQL Server Error Log (SQL Server Management Studio)

• Open Activity Monitor (SQL Server Management Studio)

• Monitoring performance by using the Query Store

To perform monitoring tasks with SQL Trace and SQL Server Profiler

Important

The next sections describe methods of using SQL Trace and SQL Server Profiler.
SQL Trace and SQL Server Profiler are deprecated. The Microsoft.SqlServer.Management.Trace namespace that contains the Microsoft SQL Server Trace and Replay objects are also deprecated.

This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Use Extended Events instead. For more information on Extended Events, see Quick Start: Extended events in SQL Server and SSMS XEvent Profiler.

Note

SQL Server Profiler for Analysis Services workloads is NOT deprecated, and will continue to be supported.

To perform monitoring tasks with SQL Trace by using Transact-SQL stored procedures

• Create a Trace (Transact-SQL)

• Set a Trace Filter (Transact-SQL)

• Modify an Existing Trace (Transact-SQL)

• View a Saved Trace (Transact-SQL)

• View Filter Information (Transact-SQL)

• Delete a Trace (Transact-SQL)

To create and modify traces by using SQL Server Profiler

• Create a Trace (SQL Server Profiler)

• Set Global Trace Options (SQL Server Profiler)

• Specify Events and Data Columns for a Trace File (SQL Server Profiler)

• Create a Transact-SQL Script for Running a Trace (SQL Server Profiler)

• Save Trace Results to a File (SQL Server Profiler)

• Set a Maximum File Size for a Trace File (SQL Server Profiler)

• Save Trace Results to a Table (SQL Server Profiler)

• Set a Maximum Table Size for a Trace Table (SQL Server Profiler)

• Filter Events in a Trace (SQL Server Profiler)

• View Filter Information (SQL Server Profiler)

• Modify a Filter (SQL Server Profiler)

• Filter Events Based on the Event Start Time (SQL Server Profiler)

• Filter Events Based on the Event End Time (SQL Server Profiler)

• Filter Server Process IDs (SPIDs) in a Trace (SQL Server Profiler)

• Organize Columns Displayed in a Trace (SQL Server Profiler)

To start, pause, and stop traces by using SQL Server Profiler

• Start a Trace Automatically after Connecting to a Server (SQL Server Profiler)

• Pause a Trace (SQL Server Profiler)

• Stop a Trace (SQL Server Profiler)

• Run a Trace After It Has Been Paused or Stopped (SQL Server Profiler)

To open traces and configure how traces are displayed by using SQL Server Profiler

• Open a Trace File (SQL Server Profiler)

• Open a Trace Table (SQL Server Profiler)

• Clear a Trace Window (SQL Server Profiler)

• Close a Trace Window (SQL Server Profiler)

• Set Trace Definition Defaults (SQL Server Profiler)

• Set Trace Display Defaults (SQL Server Profiler)

To replay traces by using SQL Server Profiler

• Replay a Trace File (SQL Server Profiler)

• Replay a Trace Table (SQL Server Profiler)

• Replay a Single Event at a Time (SQL Server Profiler)

• Replay to a Breakpoint (SQL Server Profiler)

• Replay to a Cursor (SQL Server Profiler)

• Replay a Transact-SQL Script (SQL Server Profiler)

To create, modify, and use trace templates by using SQL Server Profiler

• Create a Trace Template (SQL Server Profiler)

• Modify a Trace Template (SQL Server Profiler)

• Derive a Template from a Running Trace (SQL Server Profiler)

• Derive a Template from a Trace File or Trace Table (SQL Server Profiler)

• Export a Trace Template (SQL Server Profiler)

• Import a Trace Template (SQL Server Profiler)

To use SQL Server Profiler traces to collect and monitor server performance

• Find a Value or Data Column While Tracing (SQL Server Profiler)

• Save Deadlock Graphs (SQL Server Profiler)

• Save Showplan XML Events Separately (SQL Server Profiler)

• Save Showplan XML Statistics Profile Events Separately (SQL Server Profiler)

• Extract a Script from a Trace (SQL Server Profiler)

• Correlate a Trace with Windows Performance Log Data (SQL Server Profiler)