查询在应用程序运行得很慢, 但在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
ADO .Net, ODBC or OLE DB

SSMS
Query Analyzer

SQLCMD,
OSQL, BCP,
SQL
 Server Agent

ISQL
DB-Library

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选项。

【我终于知道为什么我根据Stackoverflow上的回答胡乱运行了几句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      获得必要的依据

所有的性能调试都需要依据。你可以用来判断的依据有:

  1. 哪条语句很慢?
  2. 查询计划的差异在哪里?
  3. SQL Server嗅探了什么参数?
  4. 表定义和索引定义是什么?
  5. 统计是什么样的?有更新了么?

上面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)工具。

 

posted @ 2014-05-15 18:18  Dem  阅读(2736)  评论(1编辑  收藏  举报