监视和优化性能Monitor and Tune for Performance

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

监视数据库的目的是评估服务器的性能。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.

日常数据库性能评估有助于使响应时间最小化并使吞吐量最大化,从而实现最佳性能。Ongoing evaluation of the database performance helps you minimize response times and maximize throughput, yielding optimal performance. 有效网络流量、磁盘 I/O 和 CPU 使用率是实现最佳性能的关键。Efficient network traffic, disk I/O, and CPU usage are key to peak performance. 您需要透彻地分析应用程序要求,了解数据的逻辑结构和物理结构,评估数据库使用情况,并协商使用 [如联机事务处理 (OLTP) 与决策支持] 冲突之间的平衡措施。You need to thoroughly analyze the application requirements, understand the logical and physical structure of the data, assess database usage, and negotiate tradeoffs between conflicting uses such as online transaction processing (OLTP) versus decision support.

监视和优化数据库性能Monitoring and tuning databases for performance

Microsoft SQL ServerSQL Server 和 Microsoft Windows 操作系统提供实用工具,以便查看数据库的当前状态并跟踪性能的状态变化。Microsoft SQL ServerSQL Server and the Microsoft Windows operating system provide utilities to view the current condition of the database and track performance as conditions change. 可使用多种工具和技术来监视 MicrosoftMicrosoft SQL ServerSQL ServerThere are a variety of tools and techniques you can use to monitor MicrosoftMicrosoft SQL ServerSQL Server. 监视 SQL ServerSQL Server 可帮助你实现以下操作:Monitoring SQL ServerSQL Server helps you:

  • 确定是否可以提高性能。Determine whether you can improve performance. 例如,通过监视常用查询的响应时间,可以确定是否需要更改表的查询或索引。For example, by monitoring the response times for frequently used queries, you can determine whether changes to the query or indexes on the tables are required.

  • 评估用户活动。Evaluate user activity. 例如,通过监视尝试连接到 SQL ServerSQL Server实例的用户,可以确定安全设置是否充分以及是否需要测试应用程序或开发系统。For example, by monitoring users trying to connect to an instance of SQL ServerSQL Server, you can determine whether security is set up adequately and test applications or development systems. 例如,通过在执行 SQL 查询时对其进行监视,可以确定这些查询是否编写正确并生成预期的结果。For example, by monitoring SQL queries as they are executed, you can determine whether they are written correctly and producing the expected results.

  • 解决问题或调试应用程序组件(如存储过程)。Troubleshoot problems or debug application components, such as stored procedures.

动态环境中的监视Monitoring in a dynamic environment

更改条件会导致性能发生变化。Changing conditions result in changing performance. 在评估中,您可以看到性能会随着用户数量增加、用户访问和连接方法改变、数据库内容增加、客户端应用程序改变、应用程序中的数据变化、查询变得更加复杂以及网络流量上升而变化。In your evaluations, you can see performance changes as the number of users increases, user access and connection methods change, database contents grow, client applications change, data in the applications changes, queries become more complex, and network traffic rises. 通过使用工具来监视性能,帮助你将性能的变化与条件和复杂查询的变化相关联。Using tools to monitor performance helps you associate changes in performance with changing conditions and complex queries. 示例:Examples:

  • 通过监视常用查询的响应时间,可以确定是否需要更改查询或执行查询的表上的索引。By monitoring the response times for frequently used queries, you can determine whether changes to the query or indexes on the tables where the queries execute are required.

  • 通过在执行 Transact-SQLTransact-SQL 查询时对其进行监视,可以确定这些查询是否编写正确并生成预期的结果。By monitoring Transact-SQLTransact-SQL queries as they are executed, you can determine whether the queries are written correctly and producing the expected results.

  • 通过监视试图连接到 SQL ServerSQL Server实例的用户,可以确定安全设置是否得当并测试应用程序或开发系统。By monitoring users that try to connect to an instance of SQL ServerSQL Server, you can determine whether security is set up adequately and test applications or development systems.

响应时间是指以可视确认信息(指出正在处理查询)的形式将结果集的首行返回给用户所需的时间。Response time is the length of time required for the first row of the result set to be returned to the user in the form of visual confirmation that a query is being processed. 吞吐量是指在一段给定时间内,服务器处理的查询总数。Throughput is the total number of queries handled by the server during a specified period of time.

随着用户数量的增加,对服务器资源的竞争也会更激烈,进而导致响应时间增加和总体吞吐量减少。As the number of users increases, so does the competition for a server's resources, which in turn increases response time and decreases overall throughput.

监视和性能优化任务Monitoring and performance tuning tasks

主题Topic 任务Task
监视 SQL Server 组件Monitor SQL Server Components 监视任何 SQL Server 组件所需的步骤,如活动监视器、扩展事件、动态管理视图和函数等。Required steps to monitor any SQL Server component, such as Activity Monitor, Extended Events, and Dynamic Management Views and Functions, etc.
性能监视和优化工具Performance Monitoring and Tuning Tools 列出随 SQL Server 提供的监视和优化工具,如实时查询统计信息和数据库引擎优化顾问。Lists the monitoring and tuning tools available with SQL Server, such as Live Query Statistics, and the Database Engine Tuning Advisor.
使用查询优化助手升级数据库Upgrading Databases by using the Query Tuning Assistant 升级到更新的数据库兼容级别期间保持工作负荷性能稳定性。Keep workload performance stability during the upgrade to newer database compatibility level.
使用查询存储来监视性能Monitoring Performance by Using the Query Store 使用查询存储将自动捕获查询、计划和运行时统计信息的历史记录,并保留它们以供查阅。Use Query Store to automatically capture a history of queries, plans, and runtime statistics, and retain these for your review.
建立性能基线Establish a Performance Baseline 如何建立性能基线。How to establish a performance baseline.
隔离性能问题Isolate Performance Problems 隔离数据库性能问题。Isolate database performance problems.
识别瓶颈Identify Bottlenecks 监视和跟踪服务器性能,以发现瓶颈。Monitor and track server performance to identify bottlenecks.
使用 DMV 来确定视图的使用情况统计信息和性能Use DMVs to Determine Usage Statistics and Performance of Views 本文介绍了一些方法和脚本,用于获取查询性能的相关信息。Covers methodology and scripts used to get information about the performance of queries.
服务器性能和活动监视Server Performance and Activity Monitoring 使用 SQL ServerSQL Server 和 Windows 性能和活动监视工具。Use SQL ServerSQL Server and Windows performance and activity monitoring tools.
监视资源使用情况Monitor Resource Usage 使用系统监视器(也称为 perfmon)通过性能计数器来测量 SQL ServerSQL Server 的性能。Using System Monitor (also known as perfmon) to measure the performance of SQL ServerSQL Server using performance counters.

另请参阅See also

企业范围的自动化管理 Automated Administration Across an Enterprise
比较和分析执行计划 Compare and Analyze Execution Plans
显示和保存执行计划Display and Save Execution Plans