Performance Monitoring and Tuning Tools

  • Article

Microsoft SQL Server provides a comprehensive set of tools for monitoring events in SQL Server and for tuning the physical database design. The choice of tool depends on the type of monitoring or tuning to be done and the particular events to be monitored.

Following are the SQL Server monitoring and tuning tools:

Tool

Description

sp_trace_setfilter (Transact-SQL)

SQL Server Profiler tracks engine process events, such as the start of a batch or a transaction, enabling you to monitor server and database activity (for example, deadlocks, fatal errors, or login activity). You can capture SQL Server Profiler data to a SQL Server table or a file for later analysis, and you can also replay the events captured on SQL Server step by step, to see exactly what happened.

SQL Server Distributed Replay

Microsoft SQL Server Distributed Replay can use multiple computers to replay trace data, simulating a mission-critical workload.

Monitor Resource Usage (System Monitor)

System Monitor primarily tracks resource usage, such as the number of buffer manager page requests in use, enabling you to monitor server performance and activity using predefined objects and counters or user-defined counters to monitor events. System Monitor (Performance Monitor in Microsoft Windows NT 4.0) collects counts and rates rather than data about the events (for example, memory usage, number of active transactions, number of blocked locks, or CPU activity). You can set thresholds on specific counters to generate alerts that notify operators.

System Monitor works on Microsoft Windows Server and Windows operating systems. It can monitor (remotely or locally) an instance of SQL Server on Windows NT 4.0 or later.

The key difference between SQL Server Profiler and System Monitor is that SQL Server Profiler monitors Database Engine events, whereas System Monitor monitors resource usage associated with server processes.

Open Activity Monitor (SQL Server Management Studio)

The Activity Monitor in SQL Server Management Studio graphically displays information about:

  • Processes running on an instance of SQL Server.

  • Blocked processes.

  • Locks.

  • User activity.

This is useful for ad hoc views of current activity.

SQL Trace

Transact-SQL stored procedures that create, filter, and define tracing:

Error Logs

The Windows application event log provides an overall picture of events occurring on the Windows Server and Windows operating systems as a whole, as well as events in SQL Server, SQL Server Agent, and full-text search. It contains information about events in SQL Server that is not available elsewhere. You can use the information in the error log to troubleshoot SQL Server-related problems.

System Stored Procedures (Transact-SQL)

The following SQL Server system stored procedures provide a powerful alternative for many monitoring tasks:

Stored procedure

Description

sp_who (Transact-SQL)

Reports snapshot information about current SQL Server users and processes, including the currently executing statement and whether the statement is blocked.

sp_lock (Transact-SQL)

Reports snapshot information about locks, including the object ID, index ID, type of lock, and type or resource to which the lock applies.

sp_spaceused (Transact-SQL)

Displays an estimate of the current amount of disk space used by a table (or a whole database).

sp_monitor (Transact-SQL)

Displays statistics, including CPU usage, I/O usage, and the amount of time idle since sp_monitor was last executed.

DBCC (Transact-SQL)

DBCC (Database Console Command) statements enable you to check performance statistics and the logical and physical consistency of a database.

Built-in Functions (Transact-SQL)

Built-in functions display snapshot statistics about SQL Server activity since the server was started; these statistics are stored in predefined SQL Server counters. For example, @@CPU_BUSY contains the amount of time the CPU has been executing SQL Server code; @@CONNECTIONS contains the number of SQL Server connections or attempted connections; and @@PACKET_ERRORS contains the number of network packets occurring on SQL Server connections.

Trace Flags (Transact-SQL)

Trace flags display information about a specific activity within the server and are used to diagnose problems or performance issues (for example, deadlock chains).

Database Engine Tuning Advisor

Database Engine Tuning Advisor analyzes the performance effects of Transact-SQL statements executed against databases you want to tune. Database Engine Tuning Advisor provides recommendations to add, remove, or modify indexes, indexed views, and partitioning.

Choosing a Monitoring Tool

The choice of a monitoring tool depends on the event or activity to be monitored.

Event or activity

SQL Server Profiler

Distributed Replay

System Monitor

Activity Monitor

Transact-SQL

Error logs

Trend analysis

Yes

Yes

 

 

 

Replaying captured events

Yes (From a single computer)

Yes (From multiple computers)

 

 

 

 

Ad hoc monitoring

Yes

 

Yes

Yes

Yes

Generating alerts

 

Yes

 

 

 

Graphical interface

Yes

Yes

Yes

 

Yes

Using within custom application

Yes 1

 

 

Yes

 

1 Using SQL Server Profiler system stored procedures.

Windows Monitoring Tools

Windows operating systems and Windows Server 2003 also provide these monitoring tools.

Tool

Description

Task Manager

Shows a synopsis of the processes and applications running on the system.

Network Monitor Agent

Monitors network traffic.

For more information about Windows operating systems or Windows Server tools, see the Windows documentation.