SQL Server查询执行计划–基础

为什么查询执行对SQL Server性能很重要？ (Why is query execution important for SQL Server performance?)

SQL Server性能监视取决于性能目标。 监视的第一步是确定服务器的最佳性能，并根据捕获的指标信息创建性能趋势。 如果性能不令人满意，则有必要诊断性能问题并找到其根源。 这涉及测试不同的查询和应用程序如何影响性能

Whatever the performance goals are, they all have one thing in common – to ensure optimal performance. It doesn’t have to be the best performance possible, sometimes you have to tradeoff between needs and cost. To achieve this, analyze available hardware resources and their performance first. Then, analyze how the resources are used. Poor designed databases, inefficient queries, bad indexing, etc. can significantly slow down the whole system

无论性能目标是什么，它们都有一个共同点–确保最佳性能。 它不一定是最佳性能，有时您必须在需求和成本之间进行权衡。 为此，请首先分析可用的硬件资源及其性能。 然后，分析如何使用资源。 设计不良的数据库，查询效率低下，索引编制不正确等会大大降低整个系统的速度

One of the first issues you notice when working with a SQL Server database is the response time. If you execute a stored procedure, query, or search for specific data, you’d like the results to appear quickly. If not, is there anything that can be done to make the query run faster?

使用SQL Server数据库时，您注意到的第一个问题是响应时间。 如果执行存储过程，查询或搜索特定数据，则希望结果快速显示。 如果没有，是否可以做些什么来使查询运行更快？

The SQL Server query execution plan can answer this question and help you diagnose the problem, if it exists. The execution plan can also help you write efficient queries, create the right indexes that quickly provide only the rows you need, instead of searching through millions of records, etc.

SQL Server查询执行计划可以回答此问题并帮助您诊断问题（如果存在）。 执行计划还可以帮助您编写有效的查询，创建正确的索引以快速仅提供所需的行，而不是搜索数百万条记录等。

什么是SQL Server查询执行计划？ (What is a SQL Server query execution plan?)

A query plan, execution plan, or query execution plan is an algorithm showing a set of steps in a specific order that is executed to access data in a database

查询计划，执行计划或查询执行计划是一种算法，该算法以特定顺序显示一组步骤，这些步骤被执行以访问数据库中的数据

A query plan shows how a query was executed, or how it will be executed which is significant for troubleshooting query performance issues. Executing a SELECT statement to find out its query plan and effect on SQL Server performance can be acceptable, but executing UPDATEs to find that out is not an option. The plan is calculated by a SQL Server component Query Optimizer using minimum of server resources. When creating the SQL Server query execution plan, the number of database objects involved, joins, indexes and their availability, number of output columns, and more is considered

查询计划显示查询的执行方式或执行方式，这对于解决查询性能问题非常重要。 执行SELECT语句以了解其查询计划和对SQL Server性能的影响是可以接受的，但是执行UPDATE来找出该查询不是一种选择。 该计划由SQL Server组件查询优化器使用最少的服务器资源来计算。 在创建SQL Server查询执行计划时，要考虑所涉及的数据库对象的数量，联接，索引及其可用性，输出列的数量等等。

When a new query is executed, Query Optimizer evaluates the query plan, optimizes and compiles it, and stores it in the plan cache. The plan cache is a part of SQL Server buffer where data and query plans are stored (buffered), so they can be reused later

执行新查询时，Query Optimizer会评估查询计划，对其进行优化和编译，然后将其存储在计划缓存中。 计划缓存是存储和缓冲数据和查询计划SQL Server缓冲区的一部分，因此以后可以重用

When a query is executed, Query Optimizer first searches the plan cache looking for a query plan that can be reused, thus making the execution faster. If there’s no query plan that can be reused, a new one has to be created, which takes time and therefore makes query execution last longer

执行查询时，查询优化器首先搜索计划缓存，以查找可重复使用的查询计划，从而使执行速度更快。 如果没有可重复使用的查询计划，则必须创建一个新计划，这需要时间，因此会使查询执行的时间更长

A very useful characteristic of query plans is that when a stored procedure is executed, the query plan is created for the stored procedure name and the same query plan will be reused whenever the stored procedure is executed, despite the values specified for procedure parameters. When it comes to executing ad hoc queries, query plans are created based on complete code, so different parameters or any change in code will prevent reuse of the existing plan. This clearly indicates what should be done to make your code run faster – wrap it up as stored procedures or functions, and the existing query plans will be reused and therefore code will be executed much faster

查询计划的一个非常有用的特征是，当执行存储过程时，将为存储过程名称创建查询计划，并且无论执行过程参数的值如何，只要执行存储过程，相同的查询计划都将被重用。 在执行临时查询时，查询计划是基于完整的代码创建的，因此不同的参数或代码中的任何更改都将阻止现有计划的重用。 这清楚地表明应该采取哪些措施来使您的代码更快地运行–将其包装为存储过程或函数，并且现有的查询计划将被重用，因此代码的执行速度将大大提高

The slow execution of ad hoc queries can be mitigated by using the Optimize for ad hoc workloads option, introduced in SQL Server 2008. The option optimizes the plan cache use, as it solves the situation when query plans executed only once fill up the plan cache. As the buffer cache is used for both data and plan buffering, and the percentage of cache used for each changes in time depending on the current situation, it’s necessary to use the cache wisely. Instead of buffering the whole plan, when the option is set to “True”, only a fragment of the plan is buffered when the query is executed for the first time. When an ad hoc query is executed for the second time, its complete plan is buffered in the cache

可以使用SQL Server 2008中引入的“ 优化临时工作负载”选项来缓解临时查询的缓慢执行。该选项可以优化计划缓存的使用，因为它可以解决仅在一次查询计划填满计划缓存后才执行查询计划的情况。 。 由于缓冲区高速缓存同时用于数据和计划缓冲，并且根据当前情况，每次更改所使用的高速缓存百分比会根据当前情况而变化，因此有必要明智地使用高速缓存。 当选项设置为“ True”时，不是缓冲整个计划，而是在第一次执行查询时仅缓冲计划的一部分。 当第二次执行临时查询时，其完整计划将缓存在缓存中

The default value is False. The option in available in the SQL Server instance properties

默认值为False。 SQL Server实例属性中可用的选项

1. Right-click the SQL Server instance in the SQL Server Management Studio Object Explorer
2. 右键单击“ SQL Server Management Studio 对象资源管理器”中SQL Server实例
3. Advanced tab高级选项卡

4. In the Miscellaneous options list, set the Optimize for ad hoc workloads option to True

   在“ 其他选项”列表中，将“ 优化临时工作负载”选项设置为“ True”。

   

Another way to change this option is to use T-SQL

更改此选项的另一种方法是使用T-SQL

 
sp_CONFIGURE 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'optimize for ad hoc workloads', 1
GO
RECONFIGURE
GO
    

However, keep in mind that this is an advanced option, therefore recommended to be modified only by advanced users

但是，请记住，这是一个高级选项，因此建议仅由高级用户进行修改

如何在SQL Server中删除查询计划？ (How to remove query plans in SQL Server?)

Query plans are automatically removed from the plan cache when the SQL Server instance is restarted or memory pressure appears. Not reused plans and the ones that can be easily recompiled are first removed to resolve the memory pressure situation

重新启动SQL Server实例或出现内存压力时，查询计划将从计划缓存中自动删除。 首先删除未重用的计划，并先删除那些易于重新编译的计划，以解决内存不足的情况

To remove all query plans from cache manually, use the following statement

要手动从缓存中删除所有查询计划，请使用以下语句

 
DBCC FREEPROCCACHE WITH NO_INFOMSGS
    

Using this statement is recommended for advanced users only and it should be never be used on a production server before previous analysis of the instance state, as it will cause recompilation of stored procedures and thus slowdown the system performance

建议仅对高级用户使用此语句，并且在对实例状态进行先前的分析之前，切勿在生产服务器上使用该语句，因为它会导致重新编译存储过程，从而降低系统性能

如何在SQL Server中重新编译查询计划？ (How to recompile query plans in SQL Server?)

Query plans are automatically recompiled whenever a cached plan becomes invalid. A plan can become invalid (or obsolete) if there is a database change. The most common causes are changes of the objects used by the stored procedure: the stored procedure itself, the referenced table or view, indexes used by the plan, statistics, significant key data changes, etc. Next time the same stored procedure is executed, Query Optimizer will find an invalid plan in the cache, and will recompile a new one. The good news is that starting with SQL Server 2005, only the statements that need recompilation are recompiled, not the whole batch

只要缓存的计划变为无效，查询计划就会自动重新编译。 如果数据库发生更改，则计划可能无效（或作废）。 最常见的原因是存储过程使用的对象发生了变化：存储过程本身，所引用的表或视图，计划所使用的索引，统计信息，重要的关键数据更改等。下次执行同一存储过程时，查询优化器将在缓存中找到无效的计划，并将重新编译一个新的计划。 好消息是，从SQL Server 2005开始，仅重新编译需要重新编译的语句，而不重新编译整个批处理

A SQL Server query plan can also be recompiled explicitly when the stored procedure is executed using the WITH RECOMPILE clause, or the sp_recompile stored procedure is executed

当使用WITH RECOMPILE子句执行存储过程或执行sp_recompile存储过程时，也可以显式重新编译SQL Server查询计划。

In this article we introduced SQL Server query execution plans – explained what they are, how they are created, and how they affect SQL Server performance. As shown, a query plan reuse makes the time needed for execution shorter, therefore it’s recommended to save the frequently used queries as stored procedures. In next part of this series, we’ll present various methods for viewing the query execution plans

在本文中，我们介绍了SQL Server查询执行计划-解释了它们是什么，如何创建它们以及如何影响SQL Server性能。 如图所示，查询计划的重用可以缩短执行所需的时间，因此建议将常用查询保存为存储过程。 在本系列的下一部分中，我们将介绍用于查看查询执行计划的各种方法。

翻译自: https://www.sqlshack.com/sql-server-query-execution-plans-basics/