原文:查询在应用程序运行得很慢， 但在SSMS运行得很快的原因探究
查询在应用程序运行得很慢，
但在SSMS运行得很快的原因探究
-理解性能疑点
1 引言
内容来自http://www.sommarskog.se/query-plan-mysteries.html（Slow in the Application, Fast in SSMS?）
【看国内没有人好好翻译过这篇。全部翻译实在太长了，就挑主要的意译了。如果要看完整版，还是建议读原文。先翻译了一半，先解决了眼前的问题。剩下的一般看以后有时间再继续翻译了。】
【另外贴图太麻烦了，带图版看下载 http://pan.baidu.com/s/1SMQy】
2 介绍
经常有困惑的发帖者到SQL Server的论坛上提问，为什么有些查询及存储过程在应用程序运行得超慢，但把SQL Batch放到SQL Server Management Studio运行就超快（根据我遇到的情况，运行速度相差千倍）。也有这样的情况：把存储过程中的某句查询分离出来单独运行，有可能比在存储过程中运行的速度快或慢很多。
下面会介绍SQL Server如何编译一段存储过程，什么是变量嗅探（parameter sniffering），SQL Server如何使用缓存。
本文的范例使用的是Northwind范例数据库。
2.1 假设
本文不是初学者向的。你可以不了解查询计划。
3 SQL Server编译存储过程的方式
本章说明了SQL Server如何编译一段存储过程，以及如何使用查询缓存。
适用于：没有使用存储过程，而是直接提交SQL语句的应用程序。
3.1 什么是存储过程
这个问题背后的隐含问题是“什么对象自带查询计划”。SQL Server对下列的对象构建查询计划：
l 存储过程
l 可扩展用户自定义函数
l 表变量函数
l 触发器
SQL Server不会给视图或表内嵌函数（inline-table function）建立查询计划。
如果存储过程有嵌套，例如：
CREATE PROCECURE Outer_sp AS
...
EXEC Inner_sp
...
该情况下Outer_sp和Inner_sp的执行计划是相互独立的，Outer_sp不包含Inner_sp的执行计划。动态SQL也是如此。
3.2 SQL Server如何生成查询计划
3.2.1 概述
当创建存储过程（函数，触发器），SQL Server首先会检查语法，没有用到不存在的列。在这个时候还没有建立任何查询计划。
当执行的时候，计划被创建。对于其中的每一句查询，SQL Server会根据收集到的数据来进行优化该句查询，但不会分析存储过程的整个过程。
3.2.2 参数和变量
给一个范例
CREATE PROCEDURE List_orders_1 AS
SELECT * FROM Orders WHERE OrderDate > '20000101'
go
CREATE PROCEDURE List_orders_2 @fromdate datetime AS
SELECT * FROM Orders WHERE OrderDate > @fromdate
go
CREATE PROCEDURE List_orders_3 @fromdate datetime AS
DECLARE @fromdate_copy datetime
SELECT @fromdate_copy = @fromdate
SELECT * FROM Orders WHERE OrderDate > @fromdate_copy
Go
然后执行
EXEC List_orders_1
EXEC List_orders_2 '20000101'
EXEC List_orders_3 '20000101'
允许“包含真实执行计划（Include Actual Execution Plan）”，就可以看到前两个存储过程执行计划是一样的：
而第三个存储过程是：
（Clustered Index Scan等同于Table Scan）
当查看详细情况时，可以发现“预估行数（Estimated Number of Rows）”，对于前两个查询来说是1，对于第三个查询是249。当返回少量数据时，Index Seek + Key Lookup很有效率；但符合条件的查询结果变多时，SQL Server很有可能需要多次访问同一数据页。在极端情况下，如果要返回某一数据页里的所有行，Table Scan比Index Seek + Key Lookup效率高得多。以刚才的范例而言，Orders表的总行数是830行，SQL Server预估会返回249行时，它判断在这种情况下Table scan是最好的选择。
3.2.3 预估行数是怎么来的？
第一个查询中日期是一个常数。SQL Server分析了Orders表，里面所有的数据都是来自于2000年之前。但毕竟统计只是统计，SQL Server并不能确信这点，所以会预估返回1条。
第二个查询中，针对的是一个变量，或者用更精确的表述：是一个参数。当执行优化时，SQL Server知道这次是以2000-1-1作为参数来调用的。由于不会进行整体过程分析，SQL Server并不能肯定执行的时候一定会用2000-1-1作为参数。所以它用输入值来做了个预估，得到了第一个查询相同的结果：1行。这种策略被称为参数嗅探（parameter sniffing）。
第三个查询中，输入值被复制到一个局部变量中。但当SQL Server建立计划时，它并不知道这个参数可能的值。因此它采用了通用的假设：30%命中率。830的30%命中率是249。
3.2.4 关键点
l SQL Server会完全信任查询中的常数。如果预计到不会返回结果，甚至会跳过某些表。
l 对于参数，SQL Server不知道运行时的值，会在编译查询时，对输入的值进行嗅探。
l 对于局部变量，SQL Server不知道运行时的值，会使用通用假设。
3.3 将查询计划放入缓存
如果SQL Server（限定OLTP）每次运行一次都要编译一次存储过程，很有可能要消耗大量CPU资源，特别是有很多用户同时执行简单的短时查询的时候。
因此SQL Server会为存储过程缓存查询计划，直到这些缓存被清除。会清除缓存的情况包括：
l 缓存满了，而且一段时间内没用过
l 执行了ALTER PROCEDURE
l 执行了sp_recompile
l 执行了DBCC FREEPROCCACHE
l SQL Server重启
l 改变了某些配置参数
重新建立查询计划时，会重新嗅探。如果这次遇到一个不同的值，会重新生成查询计划。
有些情况下会重新建立部分语句重新编译，包括：
l 改变涉及的表定义
l 涉及的表丢弃或新增或修改一个索引
l 新建或更新涉及的表的统计
l 对涉及的表运行了sp_recompile
3.4 不同配置下的不同计划
并不是所有人都可以使用缓存中的查询计划。看一个范例：
CREATE PROCEDURE List_orders_6 AS
SELECT *
FROM Orders
WHERE OrderDate > '12/01/1998'
go
SET DATEFORMAT dmy
go
EXEC List_orders_6
go
SET DATEFORMAT mdy
go
EXEC List_orders_6
go
12/01/1998这个日期在“日月年”和“月日年”下都说得通。第一次执行会返回很多订单，而第二次执行会返回空结果。两者的执行计划也不相同。第一次执行是Clustered Index Scan（返回很多结果下的最佳选择），第二次执行是Index Seek + Key Lookup（返回空结果时的最佳结果）。
是否SET DATEFORMAT导致了重编译？非也，SQL Server没有那么傻。执行的用户很可能是有不同的时间格式，而计划缓存中的存储过程并非针对会话或用户的，而是全局的针对所有连接的用户。所以SQL Server是创建了第二个缓存记录。
如果要更深入地看缓存这个主题，可以看Books Online的sys.dm_exec_paln_attributes这一主题。
在会对执行计划产生影响的列中，set_options是一个很关键的属性。只要set_options不同，就会编译不同的计划。
3.5 默认设置
SET ON/OFF选项的存在是由于一个历史原因。在很早之前的黑暗年代，SQL Server存在很多与ANSI标准冲突的行为。从SQL Server 6.5版开始，微软引入了SET选项，允许用户采用ANSI标准。从SQL Server 7开始，微软改变了对于ODBC和OLE DB API的默认选项。
|
Applications using |
SSMS, |
SQLCMD, |
ISQL, |
|
|
ANSI_NULL_DFLT_ON |
ON |
ON |
ON |
OFF |
|
ANSI_NULLS |
ON |
ON |
ON |
OFF |
|
ANSI_PADDING |
ON |
ON |
ON |
OFF |
|
ANSI_WARNINGS |
ON |
ON |
ON |
OFF |
|
CONACT_NULLS_YIELD_NULL |
ON |
ON |
ON |
OFF |
|
QUOTED_IDENTIFIER |
ON |
ON |
OFF |
OFF |
|
ARITHABORT |
OFF |
ON |
OFF |
OFF |
从上面的表格可以看到，你的应用程序是以ARITHABORT
OFF的选项来运行的。但在SSMS中运行时，ARITHABORT是ON。所以你无法重复使用应用程序用户所使用的缓存。所以应用程序和SSMS是用完全两套缓存。这回答了文章开头的那个问题：“为什么应用程序中很慢，而在SSMS中执行得很快”。当然还可能有一些其他理由导致，不过最常见的理由就是ARITHABORT这个设置。（如果你只是想知道原因，读到这里就可以结束了。但如果你还想知道如何解决性能问题，再继续读下去）
除了通过SET命令，ALTER DATABASE这句语句也可以让你设置某个数据库对某个SET选项始终保持为ON，以覆盖API的默认选项。但是，不能将某个SET选项始终设置为OFF，虽然从语法上是可行的。另外，如果你在SSMS中写查询时，明确指定了某个选项，也会覆盖数据库的默认选项。
那么多SET选项看上去有些让人头晕。但其实只要记住，这7个选项中，前六个只是用来做向后兼容，基本你不会需要把它们设置成OFF。
接下来我们看ARITHABORT。在SQL Server 2005和之后的版本，只要ANSI_WARNINGS是ON，ARITHABORT选项就没有影响。所以没必要将这个选项设置成ON。在SSMS中时，将SET ARITHABORT设置成OFF会比较方便。
这会改变你连接到SSMS时对于ARITHABORT的默认选项。这不会使你的应用程序运行得更快，但你至少不会因为在SSMS中的性能差异而感到困惑。
PS. 强烈建议不要改变ANSI页里的任何选项。
对于SQLCMD和OSQL，请习惯性地加个-I选项。
【我终于知道为什么我根据*上的回答胡乱运行了几句SQL后，.NET程序执行存储过程的速度也变快很多了：我先执行了ALTER PROCEDURE，导致执行计划重新编译，然后在查询中显式地SET ARITHABORT OFF。于是存储过程重新嗅探了新的参数，然后生成了新的执行计划。最后.NET程序以同样的参数执行存储过程，使用了SET ARITHABORT OFF对应的执行计划，所以执行的速度基本和我在SSMS中执行的速度相同了】
4
并非所有原因都是变量嗅探
在深入研究如何解决变量嗅探导致的性能问题之前，先介绍一下其他几种与变量嗅探无关，但也会导致SSMS和应用程序性能差异的情况。
4.1
变量与参数替换
有些人遇到过这种情况：同一句查询，在存储过程中运行得很慢，但是单独拎出来跑就执行得很快。关键问题是局部变量和参数。当查找问题原因时，他们把变量替换成了常数。但如我们之前所述，如果是常数而非变量的话，SQL Server能给出更精准的预估，生成一个更好的查询计划。
类似的情况还有将参数改成变量。比如下面这句查询：
CREATE
PROCEDURE some_sp @par1 int AS
...
-- Some query that refers to @par1
如果改成了
DECLARE @par1
int
SELECT @par1 =
4711
-- query goes
here
当@par1是局部变量时，SQL Server不知道@par1的值，会做一个标准值的假设。
理论的情况我们都清楚了。但如果你有一个1000行的存储过程，其中一句查询很慢。你如何判定？
有一个方法是将查询放到sp_executesql里，例如：
EXEC
sp_executesql N'-- Some query that refers to @par1', N'@par1 int', 4711
（需要注意：如果有单引号的话要变成两个单引号。如果有局部变量的话需要赋值）
可以创建一个临时的存储过程，在存储过程中使用如上的方法替换有嫌疑的语句，运行后测试。
4.2
阻塞
别忘记有时候运行缓慢只是因为阻塞。可以再过3小时等阻塞结束之后再运行一次。如果你发现无论你加不加ARITHABORT，存储过程运行得都很快，就很可能是由于阻塞这个原因。
4.3
链接服务器（Linked Server）相关
本节的内容与SQL 2012 SP1之前的链接服务器有关。例如如下查询：
SELECT C.*
FROM SOME_SERVER.Northwind.dbo.Orders O
JOIN Customers C ON O.CustomerID = C.CustomerID
WHERE O.OrderID > 20000
我以两个不同的用户登录分别运行过。第一个用户在两台服务器上都是sysadmin，第二个用户是只有SELECT权限的普通用户。
当以sysadmin来运行时的计划如下：
当以普通用户运行时，计划是不同的：
由于没有参数，显然不是参数嗅探的原因。可以看一下预计行数的差异：
当以sysadmin运行时，预计是1行，这个预估符合实际，因为实际上没有ID超过20000。但当以普通用户运行时，预估是249行。这是由于统计丢失了。
当查询在本地服务器上运行时，查询优化器可以访问查询里涉及的所有表，没有权限检查。但对于链接服务器，由于没有跨服务器通信的安全协议，SQL Server只会使用标准的OLE DB接口。SQL Server对于其它实例，Oracle，文本文件都是如此。在上述的范例中，SQL Server实际进行了两个步骤：首先运行sp_table_statistics2_rowset来获取数据基数和数据密度信息。直到SQL Server 2012 RTM，运行DBCC
SHOW_STATISTICS，你必须是一个sysadmin，db_owner或db_ddladmin的角色。
当以普通用户运行时，会产生一个权限错误。虽然这个错误没有传染性，但查询优化器因此没法获得统计信息，只能用默认假设。
所以当问题涉及链接服务器时，需要注意权限问题。可以采取的措施有：
l 添加db_ddladmin权限。但这个权限可以增删表，所以不推荐。
l 将登录账号映射到sp_adlinkedsvrlogin
l 把查询用OPENQUERY重写，强制在远程服务器上重新评估。
l 使用HINT。
l 重新考虑为什么一定要用链接服务器的方式，换复制等其他方法。
从SQL Server 2012 SP1开始，这个权限标准被放宽了。
5
收集信息以解决变量嗅探问题
在紧急情况下，有一个快速解决的方法可以搞定性能问题：
EXEC
sp_recompile problem_sp
这可以清除查询缓存。下次运行的时候生成一条新的查询计划。如果问题不再发生就万事大吉了。
但更常见的展开是问题反复发生。在这种情况下，你不应该使用sp_recompile。
5.1
获得必要的依据
所有的性能调试都需要依据。你可以用来判断的依据有：
- 哪条语句很慢？
- 查询计划的差异在哪里？
- SQL Server嗅探了什么参数？
- 表定义和索引定义是什么？
- 统计是什么样的？有更新了么？
上面5个问题中，只有第三个是仅针对参数嗅探。其它的4个都是泛用的。
5.2
哪条语句很慢？
大多数情况下，问题只和一条语句有关。可以用SQL
Profiler来看语句运行的持续时间。或者也可以用这篇（http://www.sommarskog.se/sqlutil/sqltrace.html）中提到的sqltrace存储过程。
5.3
通过SSMS获得查询计划和参数
典型步骤如下：
SET ARITHABORT
ON
go
EXEC
that_very_sp 4711, 123, 1
go
SET ARITHABORT
OFF
go
EXEC
that_very_sp 4711, 123, 1
可以在查询计划中查看属性，发现嗅探的参数
上图中的Parameter
Compiled Value就是嗅探的参数。
此外还有SQL Sentry
Plan Explorer（http://www.sqlsentry.net/plan-explorer/sql-server-query-view.asp）工具。