| title | Performance Monitoring and Tuning Tools | Microsoft Docs | ||||||||
|---|---|---|---|---|---|---|---|---|---|
| ms.custom | |||||||||
| ms.date | 03/14/2017 | ||||||||
| ms.prod | sql-server-2016 | ||||||||
| ms.reviewer | |||||||||
| ms.suite | |||||||||
| ms.technology |
|
||||||||
| ms.tgt_pltfrm | |||||||||
| ms.topic | article | ||||||||
| helpviewer_keywords |
|
||||||||
| ms.assetid | 31529dfe-68e7-49f7-b3c2-39fcecf33a95 | ||||||||
| caps.latest.revision | 37 | ||||||||
| author | JennieHubbard | ||||||||
| ms.author | jhubbard | ||||||||
| manager | jhubbard |
[!INCLUDEmsCoName] [!INCLUDEssNoVersion] provides a comprehensive set of tools for monitoring events in [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] monitoring and tuning tools:
| Tool | Description |
|---|---|
| sp_trace_setfilter (Transact-SQL) | [!INCLUDEssSqlProfiler] 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 [!INCLUDEssSqlProfiler] data to a [!INCLUDEssNoVersion] table or a file for later analysis, and you can also replay the events captured on [!INCLUDEssNoVersion] step by step, to see exactly what happened. |
| SQL Server Distributed Replay | [!INCLUDEmsCoName] [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] on Windows NT 4.0 or later. The key difference between [!INCLUDEssSqlProfiler] and System Monitor is that [!INCLUDEssSqlProfiler] 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 [!INCLUDEssManStudioFull] is useful for ad hoc views of current activity and graphically displays information about: Processes running on an instance of [!INCLUDEssNoVersion]. Blocked processes. Locks. User activity. |
| Live Query Statistics | Displays real-time statistics about query execution steps. Because this data is available while the query is executing, these execution statistics are extremely useful for debugging query performance issues. |
| SQL Trace | [!INCLUDEtsql] stored procedures that create, filter, and define tracing: sp_trace_create (Transact-SQL) sp_trace_generateevent (Transact-SQL) sp_trace_setevent (Transact-SQL) sp_trace_setfilter (Transact-SQL) sp_trace_setstatus (Transact-SQL) |
| 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 [!INCLUDEssNoVersion], [!INCLUDEssNoVersion] Agent, and full-text search. It contains information about events in [!INCLUDEssNoVersion] that is not available elsewhere. You can use the information in the error log to troubleshoot [!INCLUDEssNoVersion]-related problems. |
| System Stored Procedures (Transact-SQL) | The following [!INCLUDEssNoVersion] system stored procedures provide a powerful alternative for many monitoring tasks: sp_who (Transact-SQL): Reports snapshot information about current [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] activity since the server was started; these statistics are stored in predefined [!INCLUDEssNoVersion] counters. For example, @@CPU_BUSY contains the amount of time the CPU has been executing [!INCLUDEssNoVersion] code; @@CONNECTIONS contains the number of [!INCLUDEssNoVersion] connections or attempted connections; and @@PACKET_ERRORS contains the number of network packets occurring on [!INCLUDEssNoVersion] 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 [!INCLUDEtsql] statements executed against databases you want to tune. Database Engine Tuning Advisor provides recommendations to add, remove, or modify indexes, indexed views, and partitioning. |
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* | Yes |
*Using [!INCLUDEssSqlProfiler] system stored procedures.
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.