用于磁盘I / O性能SQL Server监视工具

The goal of this article is to get familiar with SQL Server monitoring tools and identify what some of the most common SQL Server performance problems are.

本文的目的是熟悉SQL Server监视工具，并确定哪些是最常见SQL Server性能问题。

Dealing with performance problems is not an easy task but it all comes down to I/O, CPU, memory, or concurrency related stuff. SQL Server monitoring tools can help us paint the big picture. It’s all about identifying which one of these is causing the problem. A lot of times it can be a combination of two or more, or it could look like one but it’s actually other.

处理性能问题并不是一件容易的事，但这全归结于I / O，CPU，内存或并发相关的内容。 SQL Server监视工具可以帮助我们大致了解。 这完全是要确定其中哪一个导致了问题。 很多时候，它可以是两个或多个的组合，或者看起来像一个，但实际上是另一个。

Let’s start by mentioning some of the common performance issues:

让我们从提及一些常见的性能问题开始：

• High I/O during peak or maintenance times
  高峰或维护时间内的高I / O
• Memory pressure slowing down queries
  内存压力使查询变慢
• CPU pressure slowing down database server
  CPU压力降低了数据库服务器的速度
• Concurrency issue from locking/blocking
  锁定/阻止引起的并发问题

High input/output represents disk subsystems doing too much work. Particularly during peak or maintenance times. Input/output bottlenecks can be the hardest to troubleshoot because they are usually manifested from memory or CPU issues. Furthermore, they can be also manifested from poorly written queries and not having a good indexing strategy.

高输入/输出表示磁盘子系统做了太多的工作。 特别是在高峰或维护时间。 输入/输出瓶颈可能最难解决，因为它们通常从内存或CPU问题中体现出来。 此外，它们还可以通过书写不佳的查询以及没有好的索引策略来体现。

How that we’ve mentioned indexing strategy, this is a big piece of the puzzle that cannot be achieved easily. But here’s an article that you will hopefully find informative and useful: SQL index maintenance

我们如何提到索引策略，这是一个不容易实现的难题。 但是，本文希望您能从中找到有用的信息： SQL索引维护

Bear in mind that proper indexing and query tuning can have a significant impact on all areas of SQL Server performance.

请记住，正确的索引编制和查询调整可能会对SQL Server性能的所有方面产生重大影响。

All these factors are a good reason why it’s a good idea to look at I/O last, even though it first in the list, just to rule out any other things that can cause I/O problems.

所有这些因素都是一个很好的理由，尽管在列表中排在第一位，但最好还是先看一下I / O，以便排除可能导致I / O问题的所有其他因素。

Memory pressure can simply become an issue when the system doesn’t have enough RAM of SQL Server cannot allocate enough memory or more commonly poor database and query design. Lack of memory can also be caused by high I/O. Yeah right, it goes both ways. This is, again, why it can get tricky when troubleshooting SQL Server performance.

当系统没有足够SQL Server RAM不能分配足够的内存，或更常见的是数据库和查询设计较差时，内存压力可能只是一个问题。 高I ​​/ O也可能导致内存不足。 是的，这是双向的。 同样，这就是为什么在对SQL Server性能进行故障排除时会变得棘手的原因。

The general rule of thumb between these two is that memory pressure happens randomly for example when a raw query is pulling out all the data in a table and sucks up all the SQL Server memory. High I/O is happening at specific times. You can usually tell for example if every day at 3 PM the SQL Server comes to its knees, then you most likely have an I/O issue rather than a memory issue.

这两者之间的一般经验法则是，内存压力随机发生，例如当原始查询提取表中的所有数据并占用所有SQL Server内存时。 高I ​​/ O在特定时间发生。 例如，通常可以说出，如果每天下午3点SQL Server瘫痪，那么很可能是I / O问题而不是内存问题。

CPU pressure can come from a variety of things but generally, it’s come from expensive queries or external factors. First, you should deal with external stuff. This can be an application running on the database server that is sucking up all resources. But, as before, CPU pressures can also cause high I/O.

CPU压力可能来自多种因素，但总的来说，它来自昂贵的查询或外部因素。 首先，您应该处理外部事物。 这可能是在数据库服务器上运行的应用程序正在消耗所有资源。 但是，像以前一样，CPU压力也会导致高I / O。

Last but not least, concurrency can cause performance issues but this is a tough nut to crack. Because even if the I/O, memory, and CPU levels are normal, you can still have people complaining about waiting and slowdowns in an application, then it’s probably coming from locking and blocking issue.

最后但并非最不重要的一点是，并发会导致性能问题，但这是一个难以克服的难题。 因为即使I / O，内存和CPU级别正常，您仍然可以让人们抱怨应用程序中的等待和速度降低，所以它可能来自锁定和阻止问题。

Bottom line is to get the big picture, we need to take all the above factors into account when troubleshooting performance issues. To achieve this, we got a lot of SQL Server monitoring tools.

最重要的是要了解全局，在对性能问题进行故障排除时，我们需要考虑上述所有因素。 为此，我们获得了许多SQL Server监视工具。

Some of the common tools for performance monitoring are:

用于性能监视的一些常用工具是：

• Dynamic Management Views
  动态管理视图
• Performance monitor
  性能监控器
• Extended events
  扩展事件
• Execution plans
  执行计划
• Data collector
  资料收集器
• Resource governor
  资源调控器

We got a lot of DMVs as part of SQL Server monitoring tools for troubleshooting all common performance issues and we will be looking into some of them later in this article. We will also look at Performance monitor and the most popular counters used for identifying common I/O performance issues. Extended events profile is used for logging all the information within SQL Server. Execution plans are also very imported along with the database engine tuning advisor to help us out with query optimization and fine-tuning. The data collector can be used to monitor performance and SQL Server has its own data collector as well that compiles performance statistics. Lastly, we got a Resource governor which is good for controlling resources at the SQL Server level.

作为SQL Server监视工具的一部分，我们提供了许多DMV，用于对所有常见的性能问题进行故障排除，我们将在本文的后面部分研究它们。 我们还将介绍性能监视器和用于识别常见I / O性能问题的最受欢迎的计数器。 扩展事件配置文件用于记录SQL Server中的所有信息。 执行计划也与数据库引擎调整顾问一起非常重要地导入，以帮助我们进行查询优化和微调。 数据收集器可用于监视性能，SQL Server具有自己的数据收集器，该数据收集器还可以编译性能统计信息。 最后，我们获得了一个资源调控器，它非常适合在SQL Server级别上控制资源。

• For more information about Query optimization, see the SQL Query Optimization — How to Determine When and If It’s Needed article

  有关查询优化的更多信息，请参见“ SQL查询优化-如何确定何时以及是否需要”一文。

A lot of the times when troubleshooting performance problems, we can’t just use one of the SQL Server monitoring tools. Those tools represent an