sqlserver手动执行计划(sqlserver的执行计划)

如何分析SQL Server中计划执行的相关机制和常见问题？我相信很多没有经验的人都不知所措。为此，本文总结了问题产生的原因及解决方法。通过这篇文章，希望你能解决这个问题。

简介

我们通常写的SQL语句的本质只是数据获取的逻辑，而不是数据获取的物理路径。当我们编写的SQL语句被传输到SQL Server时，查询分析器将依次解析、绑定、优化(有时称为简化)和执行这些语句。除了执行步骤之外，执行计划是在前三个步骤之后生成的，即SQL Server根据计划获取物理数据的方式，最后执行步骤根据执行计划执行查询以获取结果。然而，查询优化器不是本文的重点。下面主要描述查询优化器生成执行计划后缓存执行计划的相关机制和常见问题。

为什么需要执行计划缓存

从简介中我们知道生成执行计划的流程步骤占了很大的比重，会消耗CPU和内存资源。实际上，查询优化器需要做更多的工作来生成执行计划，大致分为三个部分：

首先，根据传入的查询语句文本，分析表名、存储过程名、视图名等。然后，基于逻辑数据操作生成表示查询文本的树。

第二步是优化和简化，例如将子查询转换为对等连接，优先应用过滤条件，删除不必要的连接(例如，如果有索引，可能不需要引用原始表)。

第三步是根据数据库中的统计信息进行基于成本的评估。

以上三个步骤完成后，将生成多个候选执行计划。虽然逻辑中只有一条SQL语句，但是可以有多个符合这个逻辑顺序的物理数据采集顺序。比如你想从北京去上海，也就是可以坐高铁或者飞机，但是从北京到上海的描述是一个逻辑描述，有多种实现方式。让我们看看SQL Server中的另一个例子，如清单1中的查询。

选择* from mainer joinbona . a=b . binner joincc . c=a . a .代码清单1。

对于这个查询，不管是A先内联B还是B先内联C，结果都是一样的，所以可以生成多个执行计划。但是，一个基本原则是，SQL Server不一定选择最佳的执行计划，而是选择足够好的计划，因为评估所有执行计划的成本所消耗的成本不应该太大。最后，SQL Server会根据数据库和每一步消耗的CPU和IO成本来评估执行计划的成本，所以执行计划的选择很大程度上取决于统计信息，所以我就不详细阐述统计信息的相关内容了。

从前面查询分析器生成执行计划的过程中不难看出，这一步消耗的资源成本也是惊人的。因此，当同一个查询执行一次时，缓存它会大大减少执行计划的编译，从而提高效率，这也是执行计划缓存的初衷。

执行计划所缓存的对象

执行计划缓存的对象分为四类，即：

编译计划：编译执行计划与执行计划的关系同MSIL与C#的关系。

执行上下文：执行编译好的计划时会有上下文。因为编译后的计划可以被多个用户共享，所以查询需要存储SET信息和局部变量的值，所以上下文需要与对应的执行计划关联。执行上下文也称为可执行计划。

游标：存储的游标状态类似于执行上下文和编译计划之间的关系。游标本身只能由一个连接使用，但是与游标关联的执行计划可以由多个用户共享。

代数：代数树(也称为解析树)表示查询文本。如前所述，查询分析器并不直接引用查询文本，而是一个代数树。也许你在这里有疑问。代数树用于生成执行计划，但它也在这里缓存代数主干。这是因为视图、默认值和约束可以被不同的查询重用，缓存这些对象的代数树可以节省解析过程。

例如，我们可以通过dm_exec_cached_plans找到缓存的执行计划，如图1所示。

图1。缓存的执行计划

你如何看待这类对象缓存占用的内存相关信息？我们可以从DMV dm_os_memory_cache_counters中看到，上面提到的缓存对象如图2所示。

图2。内存中这类对象的缓存占用的内存

此外，执行计划缓存是一个缓存。缓存中的对象将根据算法被替换。对于执行计划缓存，替换的算法主要基于内存压力。记忆压力可以分为两种：内部压力和外部压力。外部压力是由于缓冲池的可用空间下降到某个临界值(临界值将根据物理内存的大小而不同，或者如果设置了最大内存，则根据最大内存而不同)。内部压力是由执行计划缓存中的对象超过某个阈值引起的，例如，32位SQL Server的阈值为40，000，而64位SQL Server的值提升到160，000。

这里需要注意的是，缓存的标识符是查询语句本身，所以从schemaname.tablename中选择*并从Table中选择*。

Name虽然效果一致，但需要缓存两份执行计划，所以一个Best Practice是在引用表名称和以及其他对象的名称时，请带上架构名称。基于被缓存的执行计划对语句进行调优

被缓存的执行计划所存储的内容非常丰富，不仅仅包括被缓存的执行计划、语句，还包括被缓存执行计划的统计信息，比如说CPU的使用、等待时间等。但这里值得注意的是，这里的统计只算执行时间，而不算编译时间。比如说我们可以利用代码清单2中的代码根据被缓存的执行计划找到数据库中耗时最长的20个查询语句。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT TOP 20  CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))                    AS [Total Duration (s)]  , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time                 AS DECIMAL(28, 2)) AS [% CPU]  , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /     qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]  , qs.execution_count  , CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count         AS DECIMAL(28, 2)) AS [Average Duration (s)]  , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,     ((CASE WHEN qs.statement_end_offset = -1    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2    ELSE qs.statement_end_offset    END - qs.statement_start_offset)/2) + 1) AS [Individual Query  , qt.text AS [Parent Query]  , DB_NAME(qt.dbid) AS DatabaseName  , qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qs.total_elapsed_time > 0 ORDER BY qs.total_elapsed_time DESC

代码清单2.通过执行计划缓存找到数据库总耗时最长的20个查询语句

上面的语句您可以修改Order By来根据不同的条件找到你希望找到的语句，这里就不再细说了。

相比较于无论是服务端Trace还是客户端的Profiler，该方法有一定优势，如果通过捕捉Trace再分析的话，不仅费时费力，还会给服务器带来额外的开销，通过该方法找到耗时的查询语句就会简单很多。但是该统计仅仅基于上次实例重启或者没有运行DBCC FreeProcCache之后。但该方法也有一些弊端，比如说：

类似索引重建、更新统计信息这类语句是不缓存的，而这些语句成本会非常高。缓存可能随时会被替换掉，因此该方法无法看到不再缓存中的语句。该统计信息只能看到执行成本，无法看到编译成本。没有参数化的缓存可能同一个语句呈现不同的执行计划，因此出现不同的缓存，在这种情况下统计信息无法累计，可能造成不是很准确。

执行计划缓存和查询优化器的矛盾

还记得我们之前所说的吗，执行计划的编译和选择分为三步，其中前两步仅仅根据查询语句和表等对象的metadata，在执行计划选择的阶段要重度依赖于统计信息，因此同一个语句仅仅是参数的不同，查询优化器就会产生不同的执行计划，比如说我们来看一个简单的例子，如图3所示。

图3.仅仅是由于不同的参数，查询优化器选择不同的执行计划

大家可能会觉得，这不是挺好的嘛，根据参数产生不同的执行计划。那让我们再考虑一个问题，如果将上面的查询放到一个存储过程中，参数不能被直接嗅探到，当第一个执行计划被缓存后，第二次执行会复用第一次的执行计划！虽然免去了编译时间，但不好的执行计划所消耗的成本会更高！让我们来看这个例子，如图4所示。

图4.不同的参数，却是完全一样的执行计划！

再让我们看同一个例子，把执行顺序颠倒后，如图5所示。

图5.执行计划完全变了

我们看到，第二次执行的语句，完全复用了第一次的执行计划。那总会有一个查询牺牲。比如说当参数为4时会有5000多条，此时索引扫描应该最高效，但图4却复用了上一个执行计划，使用了5000多次查找！！！这无疑是低效率的。而且这种情况出现会非常让DBA迷茫，因为在缓存中的执行计划不可控，缓存中的对象随时可能被删除，谁先执行谁后执行产生的性能问题往往也让DBA头疼。

由这个例子我们看出，查询优化器希望尽可能选择高效的执行计划，而执行计划缓存却希望尽可能的重用缓存，这两种机制在某些情况会产生冲突。

看完上述内容，你们掌握怎样浅析SQL Server中执行计划的相关机制以及常见问题的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注行业资讯频道，感谢各位的阅读！