合 MSSQL存储过程中的执行计划
Tags： MSSQLSQL Server执行计划存储过程
简介
存储过程可能包含单个查询，也可能包含整个查询系列。
在后一种情况下，您将看到多个执行计划，但是处理每个计划的方式与其他任何执行计划没有区别。
在运行存储过程的时候，勾选“包括实际的执行计划（Ctrl+M）”，在执行完后就会显示存储过程中的所有语句的执行计划和花费的时间比例，这在调试慢的存储过程时非常有用。 也可以不执行存储过程而使用“显示预估的执行计划（Ctrl+L）”来预估一下存储过程中的所有语句的执行计划
示例1
下面是 TaxRateByState 存储过程的创建脚本，目的是返回税率低于某个值的信息（这个例子中是低于7.5）。这是一个典型的存储过程的例子，这可能是随着时间的推移而形成的，由非专业的T-SQL人员创建。它涉及一系列步骤，将一些数据整合在一起，对这些数据进行操作，然后返回一个结果集。在某些情况下，这种方法是合理的，但在其他情况下，它不是最佳解决方案。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | CREATE OR ALTER PROCEDURE Sales.TaxRateByState @CountryRegionCode NVARCHAR(3) AS BEGIN SET NOCOUNT ON; CREATE TABLE #TaxRateByState ( SalesTaxRateID INT NOT NULL, TaxRateName NVARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL, TaxRate SMALLMONEY NOT NULL, TaxType TINYINT NOT NULL, StateName NVARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL ); INSERT INTO #TaxRateByState ( SalesTaxRateID, TaxRateName, TaxRate, TaxType, StateName ) SELECT st.SalesTaxRateID, st.Name, st.TaxRate, st.TaxType, sp.Name AS StateName FROM Sales.SalesTaxRate AS st JOIN Person.StateProvince AS sp ON st.StateProvinceID = sp.StateProvinceID WHERE sp.CountryRegionCode = @CountryRegionCode; DELETE #TaxRateByState WHERE TaxRate < 7.5; SELECT soh.SubTotal, soh.TaxAmt, trbs.TaxRate, trbs.TaxRateName FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesTerritory AS st ON st.TerritoryID = soh.TerritoryID JOIN Person.StateProvince AS sp ON sp.TerritoryID = st.TerritoryID JOIN #TaxRateByState AS trbs ON trbs.StateName = sp.Name; DROP TABLE #TaxRateByState; END GO |
可能仅仅需要一个查询就实现了相同的逻辑，而不需要临时表。但是，这种类型的代码你在实际系统中是经常遇到的，有时，你仅仅需要了解影响性能的问题，通过计划，并决定一个修复方案，而不一定有时间，甚至没有机会，来做一个完整的重写。同时，注意到@CountryRegionCode 参数 NVARCHAR(3) 不是最好的数据类型，CHAR(3) 可能更有效、合理。然而，NVARCHAR(3) 是表中对应列使用的数据类型，存储过程也遵循这个类型，避免数据转换问题。
我们可以通过传递一个值给存储过程，并执行存储：
1 2 3 | EXEC Sales.TaxRateByState @CountryRegionCode = N'US'; GO |
捕获其实际执行计划如下：
有趣的是，我们看到的不是存储过程。而是，优化器以与我们通过查询窗口编写和运行SELECT语句相同的方式处理存储过程中的T-SQL。
给定的存储过程，语句增加越多，你将看到的执行计划越多。在一些循环查询的事件中，你可能看到数百个执行计划。这种情形下，使用SSMS捕获所有的执行计划将造成性能问题。如果是处理那种情形的话，你可能的方法是捕获估计执行计划。如果必须查看实际执行计划，使用过滤的扩展事件捕获单个语句的执行计划，或者，如果可以修改代码的话，使用 SET STATISTICS XML ON和 OFF语句。
存储过程中有5个语句，但是我们仅仅只能看到3个执行计划。创建临时表#TaxRateByState 的 DDL语句，没有执行计划。DDL语句只有一种解析方式，因此它不经过优化器过程，因此，没有执行计划。同时，我们也不能看到 SET NOCOUNT 语句的执行计划。估计的执行计划将展示这些 T-SQL语句的，但也不是所有类型的完整执行计划。
就像我们执行包含两个或更多查询的批处理一样，对于包含两个或更多语句的存储过程，执行计划显示每个查询相对于批处理的估计成本。这些值出现在每个执行计划的顶部，以“查询开销（占总批）”的形式出现，我们可以使用这些值来确定需要性能优化的的计划。但是，像往常一样，要谨慎地对待这些估计的成本，并且只有在估计的行计数和实际行计数之间没有很大差异时才使用它们。
查询1 的估计消耗占总的估计消耗的 3%，它是用所提供国家(这里是美国)的每个州的税率信息填充临时表的计划。我们不会详细讨论这个计划，但是有必要看看INSERT操作符的属性。
