性能监视和优化工具Performance Monitoring and Tuning Tools

适用对象:yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

MicrosoftMicrosoft SQL ServerSQL Server 提供了一套综合的工具,用于监视 SQL ServerSQL Server 中的事件和优化物理数据库的设计。SQL ServerSQL Server provides a comprehensive set of tools for monitoring events in SQL ServerSQL 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.

以下是 SQL ServerSQL Server 监视和优化工具:Following are the SQL ServerSQL Server monitoring and tuning tools:

工具Tool 描述Description
内置函数 (Transact-SQL)Built-in Functions (Transact-SQL) 内置函数可显示自启动服务器以来有关 SQL ServerSQL Server 活动的快照统计信息,这些统计信息存储在预定义的 SQL ServerSQL Server 计数器中。Built-in functions display snapshot statistics about SQL ServerSQL Server activity since the server was started; these statistics are stored in predefined SQL ServerSQL Server counters. 例如,@@CPU_BUSY** 包含 CPU 执行 SQL ServerSQL Server 代码所持续的时间;@@CONNECTIONS** 包含 SQL ServerSQL Server 连接或尝试连接的次数;**@@PACKET_ERRORS** 包含 SQL ServerSQL Server 连接上出现的网络数据包数。For example, **@@CPU_BUSY** contains the amount of time the CPU has been executing SQL ServerSQL Server code; **@@CONNECTIONS** contains the number of SQL ServerSQL Server connections or attempted connections; and **@@PACKET_ERRORS** contains the number of network packets occurring on SQL ServerSQL Server connections.
DBCC (Transact-SQL)DBCC (Transact-SQL) DBCC(数据库控制台命令)语句使您能够检查性能统计信息以及数据库的逻辑与物理一致性。DBCC (Database Console Command) statements enable you to check performance statistics and the logical and physical consistency of a database.
数据库引擎优化顾问 (DTA)Database Engine Tuning Advisor (DTA) 数据库引擎优化顾问可分析所执行的 Transact-SQLTransact-SQL 语句对要优化的数据库性能的影响。Database Engine Tuning Advisor analyzes the performance effects of Transact-SQLTransact-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.
数据库实验助手 (DEA)Database Experimentation Assistant (DEA) 数据库实验助手 (DEA) 是 SQL Server 的新的 A/B 测试解决方案。Database Experimentation Assistant (DEA) is a new A/B testing solution for SQL Server. 它会帮助评估给定工作负荷的 SQL Server 数据库引擎SQL Server Database Engine 的目标版本。It will assist in evaluating a targeted version of the SQL Server 数据库引擎SQL Server Database Engine for a given workload. 从上一 SQL ServerSQL Server 版本(以 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 开始)升级到任何更新的 SQL ServerSQL Server 版本时,DEA 将能够提供比较性的分析指标。When upgrading from a previous SQL ServerSQL Server versions (Starting with SQL Server 2005 (9.x)SQL Server 2005 (9.x)) to any newer version of SQL ServerSQL Server, DEA will be able to provide comparative analysis metrics.
错误日志Error Logs Windows 应用程序事件日志全面描述了 Windows Server 和 Windows 操作系统上发生的事件,以及 SQL ServerSQL ServerSQL ServerSQL Server 代理和全文搜索中的事件。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 ServerSQL Server, SQL ServerSQL Server Agent, and full-text search. 它包含有关 SQL ServerSQL Server 中独有的事件的信息。It contains information about events in SQL ServerSQL Server that is not available elsewhere. 您可以利用错误日志中的信息来解决与 SQL ServerSQL Server有关的问题。You can use the information in the error log to troubleshoot SQL ServerSQL Server-related problems.
扩展事件Extended Events 扩展事件是使用非常少的性能资源的轻型性能监视系统。Extended Events is a light weight performance monitoring system that uses very few performance resources. 扩展事件提供三个图形用户界面(新建会话向导、新建会话和 XE Profiler),用于创建、修改、显示和分析会话数据。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.
与执行相关的动态管理视图和函数 (Transact-SQL)Execution Related Dynamic Management Views and Functions (Transact-SQL) 通过与执行相关的 DMV 可查看与执行相关的信息。Execution related DMVs enable you to check execution related information.
实时查询统计信息 (LQS)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. 系统监视器(Microsoft Windows NT 4.0 中的性能监视器)将收集计数和比率而不是与事件相关的数据(例如,内存使用量、活动的事务数、阻塞的锁数或 CPU 活动)。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.

系统监视器在 Microsoft Windows Server 和 Windows 操作系统上运行。System Monitor works on Microsoft Windows Server and Windows operating systems. 它可以从远程或本地监视 Windows NT 4.0 或更高版本上的 SQL ServerSQL Server 实例。It can monitor (remotely or locally) an instance of SQL ServerSQL Server on Windows NT 4.0 or later.

SQL Server ProfilerSQL Server Profiler 与系统监视器之间的主要差别在于 SQL Server ProfilerSQL Server Profiler 用于监视数据库引擎事件,而系统监视器用于监视与服务器进程相关的资源使用情况。The key difference between SQL Server ProfilerSQL Server Profiler and System Monitor is that SQL Server ProfilerSQL Server Profiler monitors Database Engine events, whereas System Monitor monitors resource usage associated with server processes.
打开活动监视器 (SQL Server Management Studio)Open Activity Monitor (SQL Server Management Studio) SQL Server Management StudioSQL Server Management Studio 中的活动监视器对于当前活动的特别视图很有用,并以图形方式显示有关信息︰The Activity Monitor in SQL Server Management StudioSQL Server Management Studio is useful for ad hoc views of current activity and graphically displays information about:

- 在 SQL ServerSQL Server 的实例上运行的进程- Processes running on an instance of SQL ServerSQL Server
- 被阻塞的进程- Blocked processes
- 锁- Locks
- 用户活动- User activity
性能仪表板Performance Dashboard SQL Server Management StudioSQL Server Management Studio 中的性能仪表板有助于快速确定 SQL ServerSQL Server 中是否存在任何当前性能瓶颈。The Performance Dashboard in SQL Server Management StudioSQL Server Management Studio helps to quickly identify whether there is any current performance bottleneck in SQL ServerSQL Server.
查询优化助手 (QTA)Query Tuning Assistant (QTA) 查询优化助手 (QTA) 功能将指导用户完成推荐的工作流,以便在升级到更新的 SQL ServerSQL Server 版本时保持性能稳定,正如查询存储使用方案中的“在升级到新版 SQL Server 期间保持性能稳定”部分所述。The Query Tuning Assistant (QTA) feature will guide users through the recommended workflow to keep performance stability during upgrades to newer SQL ServerSQL Server 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 跟踪SQL Trace Transact-SQLTransact-SQL 存储过程:stored procedures that create, filter, and define tracing:

sp_trace_create (Transact-SQL)sp_trace_create (Transact-SQL)
sp_trace_generateevent (Transact-SQL)sp_trace_generateevent (Transact-SQL)
sp_trace_setevent (Transact-SQL)sp_trace_setevent (Transact-SQL)
sp_trace_setfilter (Transact-SQL)sp_trace_setfilter (Transact-SQL)
sp_trace_setstatus (Transact-SQL)sp_trace_setstatus (Transact-SQL)
SQL Server 分布式重播SQL Server Distributed Replay MicrosoftMicrosoft SQL ServerSQL Server 分布式重播可以使用多台计算机重播跟踪数据,并模拟任务关键型工作负荷。SQL ServerSQL Server Distributed Replay can use multiple computers to replay trace data, simulating a mission-critical workload.
sp_trace_setfilter (Transact-SQL)sp_trace_setfilter (Transact-SQL) SQL Server ProfilerSQL 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). 您可以将 SQL Server ProfilerSQL Server Profiler 数据捕获到 SQL ServerSQL Server 表或文件中供以后分析,还可以逐步重播在 SQL ServerSQL Server 上捕获的事件以确切了解所发生的事件。You can capture SQL Server ProfilerSQL Server Profiler data to a SQL ServerSQL Server table or a file for later analysis, and you can also replay the events captured on SQL ServerSQL Server step by step, to see exactly what happened.
系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL) 下列 SQL ServerSQL Server 系统存储过程可以作为许多监视任务的一种功能强大的备选方法:The following SQL ServerSQL Server system stored procedures provide a powerful alternative for many monitoring tasks:

sp_who (Transact-SQL)sp_who (Transact-SQL):
报告有关当前 SQL ServerSQL Server 用户和进程的快照信息,包括当前正在执行的语句以及该语句是否被阻塞。Reports snapshot information about current SQL ServerSQL Server users and processes, including the currently executing statement and whether the statement is blocked.

sp_lock (Transact-SQL)sp_lock (Transact-SQL):
报告有关锁的快照信息,包括对象 ID、索引 ID、锁的类型以及锁应用于的类型或资源。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)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)sp_monitor (Transact-SQL):
显示统计信息,包括 CPU 使用率、I/O 使用率以及自上次执行 sp_monitor 以来的空闲时间。Displays statistics, including CPU usage, I/O usage, and the amount of time idle since sp_monitor was last executed.
跟踪标志 (Transact-SQL)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).

选择监视工具Choosing a Monitoring Tool

监视工具的选择取决于要监视的事件或活动。The choice of a monitoring tool depends on the event or activity to be monitored.

事件或活动Event or activity 扩展事件Extended Events SQL Server 事件探查器SQL Server Profiler 分布式重播Distributed Replay 系统监视器System Monitor 活动监视器Activity Monitor Transact-SQLTransact-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 1Yes1 Yes Yes Yes Yes Yes
生成警报Generating alerts Yes
图形界面Graphical interface Yes Yes Yes Yes Yes Yes
在自定义应用程序内使用Using within custom application Yes 2Yes2 Yes

1 使用 SQL Server Management Studio XEvent Profiler 1 Using SQL Server Management Studio XEvent Profiler
2 使用 SQL Server ProfilerSQL Server Profiler 系统存储过程。2 Using SQL Server ProfilerSQL Server Profiler system stored procedures.

Windows 监视工具Windows Monitoring Tools

Windows 操作系统和 Windows Server 2003 也提供这些监视工具。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.

有关 Windows 操作系统或 Windows Server 工具的详细信息,请参阅 Windows 文档。For more information about Windows operating systems or Windows Server tools, see the Windows documentation.