| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Performance monitoring and tuning tools |
Learn about SQL Server monitoring and tuning tools and how to choose the right one depending on the type of monitoring and the events to monitor. |
MikeRayMSFT |
mikeray |
07/19/2024 |
sql |
performance |
conceptual |
|
[!INCLUDE SQL Server]
[!INCLUDE msCoName] [!INCLUDE ssNoVersion] provides a comprehensive set of tools for monitoring events in [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion] monitoring and tuning tools:
| Tool | Description |
|---|---|
| Built-in Functions | Built-in functions display snapshot statistics about [!INCLUDE ssNoVersion] activity since the server was started; these statistics are stored in predefined [!INCLUDE ssNoVersion] counters. For example, @@CPU_BUSY contains the amount of time the CPU has been executing [!INCLUDE ssNoVersion] code; @@CONNECTIONS contains the number of [!INCLUDE ssNoVersion] connections or attempted connections; and @@PACKET_ERRORS contains the number of network packets occurring on [!INCLUDE ssNoVersion] connections. |
| DBCC statements | DBCC (Database Console Command) statements enable you to check performance statistics and the logical and physical consistency of a database. |
| Database Engine Tuning Advisor (DTA) | Database Engine Tuning Advisor analyzes the performance effects of [!INCLUDE tsql] statements executed against databases you want to tune. Database Engine Tuning Advisor provides recommendations to add, remove, or modify indexes, indexed views, and partitioning. |
| Database Experimentation Assistant (DEA) | Database Experimentation Assistant (DEA) is a new A/B testing solution for SQL Server. You can compare two versions of the [!INCLUDE ssDEnoversion] for a given workload. When upgrading from a previous [!INCLUDE ssNoVersion] versions (Starting with [!INCLUDE ssVersion2005]) to any newer version of [!INCLUDE ssNoVersion], DEA will be able to provide comparative analysis metrics. |
| 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 [!INCLUDE ssNoVersion], [!INCLUDE ssNoVersion] Agent, and full-text search. It contains information about events in [!INCLUDE ssNoVersion] that is not available elsewhere. You can use the information in the error log to troubleshoot [!INCLUDE ssNoVersion]-related problems. |
| Extended Events | Extended Events is a lightweight performance monitoring system that uses very few performance resources. Extended Events provides three graphical user interfaces (New Session Wizard, New Session and the XE Profiler) to create, modify, display, and analyze your session data. |
| Execution Related Dynamic Management Views and Functions (Transact-SQL) | Execution related DMVs enable you to check execution related information. |
| Live Query Statistics (LQS) | 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. |
| 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 [!INCLUDE ssNoVersion] on Windows NT 4.0 or later. The key difference between [!INCLUDE ssSqlProfiler] and System Monitor is that [!INCLUDE ssSqlProfiler] 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 [!INCLUDE ssManStudioFull] is useful for ad hoc views of current activity and graphically displays information about: - Processes running on an instance of [!INCLUDE ssNoVersion] - Blocked processes - Locks - User activity |
| Performance Dashboard | The Performance Dashboard in [!INCLUDE ssManStudioFull] helps to quickly identify whether there is any current performance bottleneck in [!INCLUDE ssNoVersion]. |
| Query Tuning Assistant (QTA) | The Query Tuning Assistant (QTA) feature will guide users through the recommended workflow to keep performance stability during upgrades to newer [!INCLUDE ssNoVersion] versions, as documented in the section Keep performance stability during the upgrade to newer SQL Server of Query Store Usage Scenarios. |
| Query Store | The Query Store feature provides you with insight on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. It separates data by time windows so you can see database usage patterns and understand when query plan changes happened on the server. |
| SQL Trace | [!INCLUDE tsql] 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) |
| SQL Server Distributed Replay | [!INCLUDE msCoName] [!INCLUDE ssNoVersion] Distributed Replay can use multiple computers to replay trace data, simulating a mission-critical workload. |
| sp_trace_setfilter (Transact-SQL) | [!INCLUDE ssSqlProfiler] 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 [!INCLUDE ssSqlProfiler] data to a [!INCLUDE ssNoVersion] table or a file for later analysis, and you can also replay the events captured on [!INCLUDE ssNoVersion] step by step, to see exactly what happened. |
| System Stored Procedures (Transact-SQL) | The following [!INCLUDE ssNoVersion] system stored procedures provide a powerful alternative for many monitoring tasks: sp_who (Transact-SQL): Reports snapshot information about current [!INCLUDE ssNoVersion] 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. |
| 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). |
The choice of a monitoring tool depends on the event or activity to be monitored.
| Event or activity | Extended Events | SQL Server Profiler | Distributed Replay | System Monitor | Activity Monitor | Transact-SQL | Error logs | Performance Dashboard |
|---|---|---|---|---|---|---|---|---|
| Trend analysis | Yes | Yes | Yes | |||||
| Replaying captured events | Yes (From a single computer) | Yes (From multiple computers) | ||||||
| Ad hoc monitoring | Yes1 | Yes | Yes | Yes | Yes | Yes | ||
| Generating alerts | Yes | |||||||
| Graphical interface | Yes | Yes | Yes | Yes | Yes | Yes | ||
| Using within custom application | Yes | Yes2 | Yes |
1 Using SQL Server Management Studio XEvent Profiler
2 Using [!INCLUDE ssSqlProfiler] 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. |
| Performance monitor | Monitors system resources. |
| Windows Application event log | View application events generated by SQL Server and other applications. |
| Windows Firewall | The Windows Firewall has monitoring capabilities of blocked and allowed traffic. |
For more information about Windows operating systems or Windows Server tools, see the Windows documentation.