t–sql pl–sql

It doesn’t mean that every SQL Server slowdown is a performance problem. Some specific actions require many resources and put much stress on the server. If these actions are occasional, they should not be considered as a serious threat and can be ignored. Running your SQL Server under stress all the time should be thoroughly analyzed. It is good to know how the system behaves when there are no performance problems. This can be obtained by collecting baseline information about resource using

这并不意味着每个SQL Server速度下降都是性能问题。 一些特定的操作需要大量资源，并给服务器带来很大压力。 如果偶尔采取这些措施，则不应将其视为严重威胁，可以忽略不计。 应始终分析在无时无刻不在运行SQL Server的情况。 最好知道没有性能问题时系统的行为。 这可以通过以下方式收集有关资源的基准信息来获得：

To find out what’s happening with your SQL Server, start with monitoring databases, watch the performance metrics over time to create baselines and trend lines for normal operation, isolate the processes that use a lot of resources. Then you will be able to debug and repair the issues

若要了解SQL Server的情况，请从监视数据库开始，观察一段时间内的性能指标，以创建正常运行的基准和趋势线，隔离使用大量资源的流程。 然后，您将能够调试和修复问题

诊断问题 (Diagnose problems)

To start troubleshooting, you have to define the symptoms first. The most common SQL Server performance symptoms are CPU, memory, network, and I/O bottlenecks, and slow running queries

要开始故障排除，您必须先定义症状。 最常见SQL Server性能症状是CPU，内存，网络和I / O瓶颈以及运行缓慢的查询

CPU bottlenecks are caused by insufficient hardware resources. Troubleshooting starts with identifying the biggest CPU resource users. Occasional peaks in processor usage can be ignored, but if the processor is constantly under pressure, investigation is needed. Adding additional processors or using a more powerful one might not fix the problem, as badly designed processes can always use all CPU time. Query tuning, improving execution plans, and system reconfiguration can help. To avoid bottlenecks, it’s recommended to have a dedicated server that will run only SQL Server, and to remove all other software to another machine

CPU瓶颈是由硬件资源不足引起的。 故障排除始于确定最大的CPU资源用户。 可以忽略偶尔出现的处理器使用高峰，但是如果处理器一直处于压力之下，则需要进行调查。 添加其他处理器或使用功能更强大的处理器可能无法解决问题，因为设计不良的进程始终会占用所有CPU时间。 查询调优，改进执行计划和系统重新配置可以提供帮助。 为避免瓶颈，建议使用专用服务器仅运行SQL Server，并将所有其他软件删除到另一台计算机上。

Memory bottlenecks can result in slow application responsiveness, overall system slowdown, or even application crashing. It’s recommended to identify when the system runs with insufficient memory, what applications use most of memory resources, whether there are bottlenecks for other system resources. Reviewing and tuning queries, memory reconfiguration, and adding more physical memory can help

内存瓶颈可能导致应用程序响应速度变慢，整个系统变慢甚至崩溃。 建议确定何时在内存不足的情况下运行系统，哪些应用程序使用了大部分内存资源，其他系统资源是否存在瓶颈。 查看和调整查询，重新配置内存以及添加更多物理内存可以帮助您

Network bottlenecks might not be instantly recognized, as they can at a first glance be considered as SQL Server performance issues caused by other resources. For example, a delay of data sent over a network can look like SQL Server slow response

网络瓶颈可能不会立即被识别出来，因为乍一看可以将它们视为由其他资源引起SQL Server性能问题。 例如，通过网络发送的数据延迟可能看起来像SQL Server响应缓慢

I/O bottlenecks are caused by excessive reading and writing of database pages from and onto disk. A bottleneck is manifested through long response times, application slowdowns and tasks time-outs. If other applications use disk resources excessively, SQL Server might not get enough disk resources for its normal operation and would have to wait to be able to read and write to disk

I / O瓶颈是由于过多地在磁盘上读写数据库页面而引起的。 瓶颈体现在响应时间长，应用程序速度变慢和任务超时。 如果其他应用程序过度使用磁盘资源，则SQL Server可能无法获得足够的磁盘资源来正常运行，因此必须等待才能读取和写入磁盘

Slow running queries can be a result of missing indexes, poor execution plans, bad application and schema design, etc.

查询运行缓慢的原因可能是缺少索引，执行计划不佳，应用程序和架构设计不正确等。

性能问题的常见原因 (Common causes of performance issues)

The causes for performance problems can be various, but the most common are a poorly designed database, incorrectly configured system, insufficient disk space or other system resources, excessive query compilation and recompilation, bad execution plans due to missing or outdated statistics, and queries or stored procedures that have long execution times due to improper design

性能问题的原因可能多种多样，但最常见的是数据库设计不良，系统配置不正确，磁盘空间或其他系统资源不足，查询编译和重新编译过多，由于缺少统计信息或过时的统计信息而导致的执行计划不佳以及查询或由于设计不当而导致执行时间较长的存储过程

Memory bottlenecks are caused by limitations in available memory and memory pressure caused by SQL Server, system, or other application activity. Poor indexing requires table scans which in case of large tables means that a large number of rows is read from disk and handled in memory

内存瓶颈是由SQL Server，系统或其他应用程序活动导致的可用内存限制和内存压力引起的。 索引编制不良需要进行表扫描，如果表较大，则意味着从磁盘读取了大量行并在内存中进行处理

Network bottlenecks are caused by overload on a server or network, so the data cannot flow as expected

网络瓶颈是由服务器或网络上的过载造成的，因此数据无法按预期方式流动

I/O issues can be caused by slow hardware used, bad storage solution design, and configuration. Besides hardware components, such as disk types, disk array type, and RAID configuration that affect I/O performance, unnecessary requests made by a database also affect I/O traffic. Frequent index scans, inefficient queries, and out of date statistics can also cause I/O workload and bottlenecks

I / O问题可能是由于使用的硬件速度慢，存储解决方案设计和配置不正确引起的。 除了会影响I / O性能的硬件组件（例如磁盘类型，磁盘阵列类型和RAID配置）外，数据库发出的不必要请求也会影响I / O流量。 频繁的索引扫描，低效的查询和过时的统计信息也会导致I / O工作负载和瓶颈

要监视哪些指标 (What metrics to monitor)

To start troubleshooting the most common CPU performance issues, monitor % Processor Time. This counter is available in Performance Monitor. If its value is constantly higher than 80%, the processor is under pressure

要开始对最常见的CPU性能问题进行故障排除，请监视％Processor Time 。 该计数器可在Performance Monitor中使用 。 如果其值始终高于80％，则表明处理器承受压力

The counters that indicate most common causes for processor pressure are Batch Requests/sec, SQL Compilations/sec, and SQL Recompilations/sec. These counters are available in Performance Monitor and in the sys.dm_os_performance_counters view

指示造成处理器压力的最常见原因的计数器是“ 批请求/秒” ，“ SQL编译/秒”和“ SQL重新编译/秒”。 这些计数器在性能监视器和sys.dm_os_performance_counters视图中可用

select * from sys.dm_os_performance_counters where counter_name in ('Batch Requests/sec', 'SQL Compilations/sec' , 'SQL Re-Compilations/sec')    

Just note that the counter type for all three counters is 272696576 and that the values shown are cumulative since the last SQL Server start, so they have to be calculated. One of the methods is to take two samples with a 10-second delay

请注意，所有三个计数器的计数器类型均为272696576，并且显示的值自上次SQL Server启动以来是累积的，因此必须对其进行计算。 一种方法是延迟10秒获取两个样本

DECLARE @BatchRequests BIGINT; SELECT @BatchRequests = cntr_valueFROM sys.dm_os_performance_countersWHERE counter_name = 'Batch Requests/sec'; WAITFOR DELAY '00:00:10'; SELECT (cntr_value - @BatchRequests) / 10 AS 'Batch Requests/sec'FROM sys.dm_os_performance_countersWHERE counter_name = 'Batch Requests/sec';

The Batch Requests/sec value depends on hardware used, but it should be under 1000. The recommended value for SQL Compilations/sec is less than 10% of Batch Requests/sec and for SQL Re-Compilations/sec is less than 10% of SQL Compilations/sec

批处理请求/秒的值取决于所使用的硬件，但应小于1000。SQL 编译/秒的建议值小于批处理请求/秒的 10％， SQL重新编译/秒的建议值小于以下的10％ SQL编译/秒

For memory problems, monitor the Memory Available KB performance counter. The normal values should be over 200 MB. If the value of Memory Available KB counter is lower than 100 MB for long time, it’s a clear indication of insufficient memory on the server. This counter is available in Performance Monitor, and two more useful counters – Total Server Memory (KB) and Target Server Memory (KB) are available via the sys.dm_os_performance_counters view

对于内存问题，请监视“ 可用内存” KB性能计数器。 正常值应超过200 MB。 如果“ 内存可用KB”计数器的值长时间低于100 MB，则明确表明服务器上的内存不足。 此计数器在Performance Monitor中可用，另外两个有用的计数器– 总服务器内存（KB）和目标服务器内存（KB）可通过sys.dm_os_performance_counters视图使用。

Another counter to monitor is Pages/sec, it is available in Performance Monitor. It shows the rate at which the pages are written from disk to RAM and read from RAM to disk. The values higher than 50 show intensive memory activity and possible overhead and memory pressure that can lead to SQL Server performance degradation

另一个要监视的计数器是“ 页数/秒” ，在“ 性能监视器”中可用。 它显示页面从磁盘写入RAM以及从RAM读取到磁盘的速率。 高于50的值表示密集的内存活动以及可能导致SQL Server性能下降的开销和内存压力

Checkpoint pages/sec and Lazy writes/sec indicate whether dirty pages are flushed to disk too often. Dirty pages are automatically flushed to disk at a checkpoint. If the available free space in the buffer cache between two checkpoints is low, a lazy write will occur to flush the pages from buffer to disk and free up memory. The Lazy Writes/sec value should be below 20. Both counters are available in Performance Monitor and the sys.dm_os_performance_counters view, but as the counter type is 272696576, the values returned by the view should be calculated

检查点页面数/秒和延迟写入数/秒指示是否将脏页频繁地刷新到磁盘。 脏页将在检查点自动刷新到磁盘。 如果两个检查点之间的缓冲区高速缓存中的可用空间不足，则会发生延迟写入，以将页面从缓冲区刷新到磁盘并释放内存。 延迟写入/秒的值应低于20。这两个计数器在Performance Monitor和sys.dm_os_performance_counters视图中均可用，但是由于计数器类型为272696576，因此应计算视图返回的值

If the Lazy Writes/sec value is constantly above the threshold, check the Page Life Expectancy value. Values below 300 seconds indicate memory pressure. The counter is available in Performance Monitor and the sys.dm_os_performance_counters view, no additional calculation is needed

如果“ 延迟写入/秒”值始终高于阈值，请检查“ 页面预期寿命”值。 小于300秒的值表示记忆压力。 该计数器在性能监视器和sys.dm_os_performance_counters视图中可用，不需要其他计算

Buffer Cache Hit Ratio shows the ratio of the data pages found and read from the SQL Server buffer cache and all data page requests. If a page doesn’t exist in the buffer cache, it has to be read disk, which downgrades performance. The recommended value is over 90. The counter is available in Performance Monitor and the sys.dm_os_performance_counters view

缓冲区高速缓存命中率显示找到并从SQL Server缓冲区高速缓存读取的数据页与所有数据页请求的比率。 如果缓冲区高速缓存中不存在页面，则必须将其读取到磁盘，这会降低性能。 推荐值超过90。该计数器可在Performance Monitor和sys.dm_os_performance_counters视图中使用。

As the counter type is 537003264, the value returned by the view has to be calculated to get the current value. To do that, it’s necessary to use the Buffer Cache Hit Ratio Base value also

由于计数器类型为537003264，因此必须计算视图返回的值以获取当前值。 为此，还必须使用“ 缓冲区高速缓存命中率基准”值

Buffer Cache Hit Ratio % = 100 * Buffer Cache Hit Ratio / Buffer Cache Hit Ratio Base

                                        = 100 * 1,797 / 1,975                                         = 90.98%

缓冲区高速缓存命中率％= 100 *缓冲区高速缓存命中率/缓冲区高速缓存命中率基准

= 100 * 1,797 / 1,975 = 90.98％

Troubleshooting network problems should start with finding queries, functions, and stored procedures that have slow response time. If they are executed quickly, but with a large delay between two calls, it can be an indication of a network issue. SQL Server Profiler can be used to determine which queries, functions and stored procedures were executed

解决网络问题应从查找响应时间较慢的查询，函数和存储过程开始。 如果它们执行得很快，但是两次调用之间有很大的延迟，则可能表明存在网络问题。 SQL Server Profiler可用于确定执行了哪些查询，函数和存储过程

For I/O problems, monitor disk-related counters: Average Disk Queue Length, Average Disk Sec/Read, Average Disk Sec/Write, %Disk Time, Average Disk Reads/Sec, and Average Disk Writes/Sec. All counters are available in Performance Monitor

对于I / O问题，请监视与磁盘相关的计数器： 平均磁盘队列长度，平均磁盘秒/读取，平均磁盘秒/写入，％磁盘时间，平均磁盘读取/秒和平均磁盘写入/秒 。 所有计数器都可以在性能监视器中使用

Average Disk Queue Length shows the average number of I/O operations that are waiting to be written to or read from disk and the number of currently processed reads and writes. The recommended value is below 2 per individual disk, and higher values indicate I/O bottlenecks

平均磁盘队列长度显示了等待写入磁盘或从磁盘读取的I / O操作的平均数量，以及当前正在处理的读写数量。 每个磁盘的建议值均低于2，更高的值表示I / O瓶颈

Average Disk Sec/Read shows the average time in seconds needed to read data from disk. The recommended values are given for categories, where under 8ms is excellent performance, and higher than 20ms is a serious I/O issue

平均磁盘秒/读取显示从磁盘读取数据所需的平均时间（以秒为单位）。 推荐值是针对类别给出的，其中8ms以下是出色的性能，而20ms以上则是严重的I / O问题

Average Disk Sec/Write shows the average time in seconds needed to write data to disk. The performance is excellent if the value is below 1ms and bad if the counter value is higher than 4ms

“平均磁盘秒数/写入”显示将数据写入磁盘所需的平均时间（以秒为单位）。 如果该值小于1ms，则性能优异；如果计数器值大于4ms，则性能较差。

Average Disk Reads/Sec and Average Disk Writes/Sec show the rate of read and write operations on disk, respectively. Low values indicate slow disk I/O processing, and checking processor usage and disk-expensive queries is recommended. The normal values depend on disk specification and server configuration. These counters don’t have a specific threshold, so it’s recommended to monitor these metrics for a while and to determine trends and set a baseline

平均磁盘读取/秒和平均磁盘写入/秒分别显示磁盘上的读取和写入操作的速率。 较低的值表示磁盘I / O处理缓慢，建议检查处理器使用情况和磁盘昂贵的查询。 正常值取决于磁盘规格和服务器配置。 这些计数器没有特定的阈值，因此建议您一段时间内监视这些指标并确定趋势并设置基准

SQL Server performance can be affected by many factors. When troubleshooting problems, it’s necessary to know where to start, to know the normal values for the performance counters and to select a tool that will provide sufficient information for analyzing and troubleshooting the issues

SQL Server的性能可能受许多因素影响。 对问题进行故障排除时，有必要知道从何处开始，了解性能计数器的正常值并选择一种工具，该工具将提供足够的信息来分析和解决问题。

资源资源 (Resources)

目录 (Table of contents)

A DBA guide to SQL Server performance troubleshooting – Part 1 – Problems and performance metrics

SQL Server性能疑难解答的DBA指南–第1部分–问题和性能指标

翻译自:

t–sql pl–sql