SQL Server中的查询优化技术：基础

描述 (Description)

Fixing and preventing performance problems is critical to the success of any application. We will use a variety of tools and best practices to provide a set of techniques that can be used to analyze and speed up any performance problem!

修复和防止性能问题对于任何应用程序的成功都是至关重要的。 我们将使用各种工具和最佳实践来提供可用于分析和加速任何性能问题的技术！

This is one of my personal favorite areas of research and discussion as it is inherently satisfying. Taking a performance nightmare and tuning it into something fast and sleek feels great and will undoubtedly make others happy.

这是我个人最喜欢的研究和讨论领域之一，因为它本质上令人满意。 参加一场表演噩梦并将其调整为快速而时尚的感觉真是太好了，并且无疑会让其他人感到高兴。

I often view optimization as a detective mystery. Something terrible has happened and you need to follow clues to locate and apprehend the culprit! This series of articles is all about these clues, how to identify them, and how to use them in order to find the root cause of a performance problem.

我经常将优化视为侦探之谜。 发生了一件可怕的事情，您需要遵循线索来找到并逮捕罪魁祸首！ 本系列文章全部涉及这些线索，如何识别它们以及如何使用它们以查找性能问题的根本原因。

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

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

定义优化 (Defining Optimization)

What is “optimal”? The answer to this will also determine when we are done with a problem and can move onto the next one. Often, a query can be sped up through many different means, each of which has an associated time and resource cost.

什么是“最佳”？ 答案也将决定我们何时解决问题，并可以继续解决下一个问题。 通常，可以通过许多不同的方式加快查询的速度，每种方式都有相关的时间和资源成本。

We usually cannot spend the resources needed to make a script run as fast as possible, nor should we want to. For the sake of simplicity, we will define “optimal” as the point at which a query performs acceptably and will continue to do so for a reasonable amount of time in the future. This is as much as a business definition as it is a technical definition. With infinite money, time, and computing resources, anything is possible, but we do not have the luxury of unlimited resources, and therefore must define what “done” is whenever we chase any performance problem.

我们通常不能花费使脚本尽可能快地运行所需的资源，我们也不应该这样做。 为了简单起见，我们将“最佳”定义为查询可接受的执行点，并且在将来的合理时间内将继续执行该操作。 这既是业务定义，又是技术定义。 有了无限的金钱，时间和计算资源，一切皆有可能，但是我们没有无限资源的奢侈，因此，当我们追求任何性能问题时，必须定义“完成”是什么。

This provides us with several useful checkpoints that will force us to re-evaluate our progress as we optimize:

这为我们提供了几个有用的检查点，这些检查点将迫使我们在优化时重新评估我们的进度：

1. The query now performs adequately.
   查询现在可以正常执行。
2. The resources needed to optimize further are very expensive.
   进一步优化所需的资源非常昂贵。
3. We have reached a point of diminishing returns for any further optimization.
   对于任何进一步的优化，我们已经达到了收益递减的地步。
4. A completely different solution is discovered that renders this unneeded.
   发现了一个完全不同的解决方案，从而不需要此解决方案。

Over-optimization sounds good, but in the context of resource management is generally wasteful. A giant (but unnecessary) covering index will cost us computing resources whenever we write to a table for the rest of eternity (a long time). A project to rewrite code that was already acceptable might cost days or weeks of development and QA time. Trying to further tweak an already good query may net a gain of 3%, but take a week of sweating to get there.

过度优化听起来不错，但是在资源管理的情况下通常是浪费的。 每当我们在永恒的余下时间（很长一段时间）中向表写入数据时，一个庞大的（但不必要的）覆盖索引将使我们浪费计算资源。 重写已经可以接受的代码的项目可能会花费数天或数周的开发和质量检查时间。 尝试进一步调整本来不错的查询可能会获得3％的收益，但要花上一周的时间才能达到目标。

Our goal is to solve a problem and not over-solve it.

我们的目标是解决问题，而不是过度解决。

查询做什么？ (What Does the Query Do?)

Question #1 that we must always answer is: What is the purpose of a query?

我们必须始终回答的问题＃1：查询的目的是什么？

• What is its purpose?
  目的是什么？
• What should the result set look like?
  结果集应该是什么样？
• What sort of code, report, or UI is generating the query?
  什么样的代码，报告或UI会生成查询？

It is first-nature for us to want to dive in with a sword in hand and slay the dragon as quickly as humanly possible. We have a trace running, execution plans in hand, and a pile of IO and timing statistics collected before realizing that we have no idea what we are doing