第17章 语句调优 - 知识准备 719
·
“有问题”的语句“问题”究竟在哪里？也就是说，你要优化的目标是什么？常见的需求有：
o
语句需要访问大量的数据页面，造成内存压力，磁盘繁忙等 - 为什麽语句执行要访问这麽多数据页面？是语句的结果集本身就比较大，还是SQL没有办法有效地seek，还是因为数据页面有很多碎片，导致SQL读了很多页面，但是每个页面里的数据量不多？
o
在内存没有压力的前提下（语句所访问的页面都事先缓存在内存里），语句运行的时间还是很长 -编译和执行各花了多少时间，哪一段时间有优化空间，以及怎样优化？
o
单个语句执行时间可以接受，但是其CPU使用量比较大，多个语句并发执行会造成SQL CPU高 - 语句的CPU时间也分编译阶段和执行阶段。优化者要先搞清楚这两个阶段各用了多少CPU资源。然后，再看有没有优化降低CPU使用量的可能
o
语句单独执行看不出有大问题，但是并发执行就容易遇到阻塞或死锁 - 这种现象往往是由于应用在某个表或者索引上的并发度特别高，而问题语句申请的锁数量比较大造成的。有时候可以使用Query Hint（查询提示 WITH (ROWLOCK) ）来强制SQL使用粒度比较小的锁。最理想的方法，是通过调整语句运行的方式，引导它申请尽可能少的、粒度尽可能小的锁。
·
策略：有些语句本身比较简单，可以通过调整索引的方法迅速提高性能。有些语句非常复杂，或者返回的结果集很大，可以考虑语句本身是不是能够换一种方法实现，把一条大的语句拆分为若干条小的语句，
17.1.
索引与统计信息 723
17.1.1.
索引上的数据检索方法 723
·
如果一张表上没有聚集索引，只有非聚集索引，数据将会以随机的顺序存放在表格里。
·
在有聚集索引的表格上，数据是直接存放在索引的最底层的。如果一个查询要扫描整个表格里的数据, 就要把整个聚集索引扫描一遍。在这里，聚集索引扫描就相当于一个表扫描。所要用的时间和资源与表扫描没有什么差别。并不是说这里有了“Index”这个字样，就说明执行计划比表扫描的有多大进步。当然反过来讲，如果看到“Table Scan”的字样，就说明这个表格上没有聚集索引。
·
如果在在有聚集索引的表格上再有非聚集索引，SQL Server会为每条记录存储一份非聚集索引索引键的值和一份聚集索引索引键的值。按照非聚集索引的顺序存放数据。注意：在没有聚集索引的表格里，是RID值指向数据页面，有聚集索引的话，指向聚集索引的键(在不使用include时)。
·
本节小结：
o
表扫描表明正在处理的表格没有聚集索引，SQL正在扫描整张表。
ü No indexes at all - Where后面的字段没有建立索引, 并且表格上没有聚集索引和非聚集索引
ü There are nonclustered indexes (but no clustered index), but
SQL Server still chooses to use table scan instead of index scan.
o
聚集索引扫描表明SQL正在扫描一张有聚集索引的表，但是也是整张表扫描.
ü No indexes on the columns in the WHERE clause, but there is a
cluster index on the table - Where后面的字段没有建立索引, 但是表格上有聚集索引，不管表格上有没有非聚集索引。
ü There are indexes on the columns in the WHERE clause, and
there is a cluster index on the table, but the query need to return other
columns as well.
o
Index Scan表明SQL正在扫描一个非聚集索引。但由于非聚集索引上一般只会有一小部分字段，所以这里虽然也是扫描但是代价会比整表扫描会少很多，因为数据不存放在索引里面。
ü Where后面的字段是没有建立非聚集索引字段 ，但表格上有非聚集索引、没有聚集索引。
o
Clustered Index Seek和Index Seek说明SQL正在利用索引结果检索目标数据。如果结果集只占表格总数据量的一小部分并且结果集的字段都包含在索引里，Seek会比Scan便宜很多，索引就起到了提高性能的作用，否则还要使用bookmark
lookup。书签查找就是：where后面的字段建立了索引（不管是聚集索引还是非聚集索引），但是where前面返回的字段中有些没有建立索引（不管是聚集索引还是非聚集索引）
ü Index seek (i.e. nonclustered seek) - indexes on the columns
in the WHERE clause.
ü Indexing the columns in the WHERE clause and INCLUDE other
returned columns can make the query from clustered index scan to index seek,
significant improvement.
17.1.2.
统计信息的含义与作用 727
·
为了以尽可能快的速度完成语句，光有索引是不够的。索引只能帮助SQL找到符合条件的记录。但统计信息（statistics）可以进一步为你选取一个代价最小的执行计划。注意：对于同一个查询，SQL有很多种方法来完成它。有些方法适合于数据量比较小的时候，有些方法适合于数据量比较大的时候。同一种方法，在数据量不同的时候，复杂度会有非常大的差别。那Statistics是如何做到选择最佳方案的呢？- 通过了解数据的分布情况！
·
SQL会在每个索引上自动建立统计信息，也会根据运行指令的需要，动态地创建一些统计信息。统计信息的准确度，会直接影响SQL完成指令的速度。
·
可以通过运行DBCC SHOW_STATISTICS([TableName], IndexName)来了解一个索引上的统计信息。统计信息的内容分为3部分：统计信息头信息，数据字段的选择性, 和直方图（histogram）。通过统计信息，SQL能够使用有限的数据分组（即Steps）比较好地预测返回的结果集的行数和长度。注意2点：（1）如果一个统计信息是为一组字段建立的，例如一个复合索引建立在两个以上的字段上，SQL维护所有字段的选择性信息，但是只会维护第一个字段的直方图。（2）当表格比较大的时候，SQL在更新统计信息的时候为了降低消耗，只会取表格的一部分数据做抽样（rows sample），这时候统计信息里面的数据都是根据这些抽样数据估算出来的值，可能和真实值会有些差异。
17.1.3.
统计信息的维护和更新 732
·
SQL会在什么情形下创建统计信息呢？主要有3种情况：
o
（1）在索引创建时，SQL会自动在索引所在的列上创建统计信息。所以从某种角度讲，索引的作用是双重的，它自己能够帮助SQL快速找到数据，而它上面的统计信息，也能够告诉SQL数据的分布情况。
o
（2）DBA也可以通过之类的语句手动创建它认为需要的统计信息：CREATE STATISTICS。但如果打开了auto create statistics，一般来讲很少需要手动创建。
o
（3）当SQL想要使用某些列上的统计信息，发现没有的时候，“auto create statistics”会让SQL自动创建统计信息。例如，当语句要在某个（或者几个）字段上做过滤，或者要拿它们和另外一张表做联接（join），SQL要估算最后从这张表会返回多少记录。这时候就需要一个统计信息的支持。如果没有，SQL会自动创建一个。
·
数据的插入、删除、修改都可能会引起统计信息的更新。但是，如果有一点点小的修改，SQL都要去更新统计信息，会很消耗资源。SQL还是要在统计信息的准确度和资源合理消耗之间做一个平衡。触发统计信息自动更新的条件是：
o
（1）如果统计信息是定义在普通表格上，那么当发生下面变化之一后，统计信息就被认为是过时的了。下次使用到时，会自动触发一个更新动作。
ü 表格从没有数据变成有大于等于1条数据
ü 对于数据量小于500行的表格，当统计信息的第一个字段数据累计变化量大于500以后
ü 对于数据量大于500行的表格，当统计信息的第一个字段数据累计变化量大于500+( 20%*表格数据总量 ) 以后。所以对于比较大的表，只有1/5以上的数据发生变化后，SQL才会去重算统计信息。
o
（2）临时表（temp table）上可以有统计信息。其维护策略基本和普通表一致。但是表变量（table variable）上不能建立统计信息
·
在SQL2000的时候，如果SQL在编译一个语句时发现某个表的某个统计信息已经过时，它会暂停语句的编译，转去更新统计信息，等统计信息更新好以后，用新的信息来做执行计划。SQL2005以后，数据库属性多了一个“auto update statistics
asynchronously”。当SQL发现某个统计信息过时时，它会用老的统计信息继续现在的查询编译，但是会在后台启动一个任务，更新这个统计信息。这样下一次统计信息被使用到时，就已经是一个更新过的版本。这样做的缺点是，不能保证当前这句查询的执行计划准确性。
·
有一些例外情况。由于数据的特殊性，会使得SQL这种auto update statistics的算法不能满足确保执行计划准确性的需求。在实际使用中，有时候数据库的性能突然之间慢下来。有经验的DBA会安排做一次索引重建任务，常常对性能有所帮助。通常人们会解释为：因为索引重建消除了数据碎片，因而提高了性能。其实索引重建还做了另外一件很重要的工作。它使用full scan的方式，重新更新了表上的统计信息，使得统计信息非常精确。这对性能帮助作用会很大。
17.2.
编译与重编译 736
·
基本流程
SQL指令(包含Ad hoc query，usp，trigger，Prepared statement，Dynamic SQL）==è语法、语句解释 ==è编译（compile）==è execution plan
·
在编译的过程中，SQL会根据所涉及的对象的架构（schema）、统计信息以及指令的具体内容，估算可能的执行计划，以及它们的成本（cost），最后选择一个SQL认为成本最低的执行计划。执行计划生成之后，SQL通常会把它们缓存在内存里。以后同样的语句执行，SQL就可以使用同样的执行计划，而无须再做一次编译。
·
但是有时候，哪怕是一模一样的语句，SQL下次执行还是要重编译（recompile）。
·
注意：对不同的指令调用方法，SQL做执行计划缓存和重用机制也有所不同。下面介绍最常见的几种以及怎样通过跟踪Sys.dm_exec_cached_plans 或者sys. [Syscacheobjects] 视图和SQL Trace里的事件来分析是否有编译或者执行计划重用。
SELECT usecounts ,
cacheobjtype ,
objtype ,
text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
SELECT *
FROM Sys. [Syscacheobjects]
o
Ad hoc语句 - 包含select,
insert, update, delete的批处理指令。对这样的指令，只有前后完全一致包括字母的大小写，空格，甚至回车换行都一致，SQL才认为是两条一样的语句，才能够重用执行计划。
o
用EXEC()的方式运行动态SQL语句 - 在程序运行过程中，动态地拼接一个语句字符串，然后用exec（）的方式执行。这种 “动态SQL dynamic SQL”的好处是可以根据客户的选择，动态生成指令，而不仅限于预先定义的那几种。但是它的缺点也是太灵活，客户发过来的语句每次都不一样，或者语句主体部分是一样的，但是参数不一样，SQL都要做编译。这点和adhoc语句是一样的。所以ad hoc语句和dynamic SQL不是最佳的调用方法。
o
自动参数化的查询（auto-parameterized
query）- 对于一些比较简单的查询，SQL2005自己就可以做自动参数化，把语句里的参数用一个变量代替以提高执行计划的可重用性。
USE [AdventureWorks]
GO
DBCC FREEPROCCACHE
GO
SELECT [ProductID],
[SalesOrderID]
FROM Sales.[SalesOrderDetail]
WHERE [ProductID] >
1000
GO
SELECT [ProductID],
[SalesOrderID]
FROM Sales. [SalesOrderDetail]
WHERE [ProductID] >
2000
GO
SELECT *
FROM sys. [Syscacheobjects]
GO
两条查询语句一模一样，只是参数的值不同。当察看缓存里的执行计划时，就能发现SQL不但缓存了两条语句自己的执行计划，还缓存了一个参数化了的执行计划.
如果再运行一句类似的语句：在SQL Trace里，可以看到这句话在开始之前，先有一个SP:CacheHit的事件。语句在缓存中找到了一个参数化后的执行计划。然后它根据这个执行计划，做出了自己的adhoc执行计划（SP:CacheInsert）。这也能节省一部分编译时间。
SELECT [ProductID], [SalesOrderID]
FROM sales.
[SalesOrderDetail]
WHERE [ProductID]>3000
GO
o
用sp_executesql的方式调用的指令 - 上面的查询自动参数化在很多种条件下是不支持的，而且它还是要为每句查询生成一个ad hoc的执行计划。所以它并不是减少编译的最优手段。如果改用sp_executesql就能够更有效地增加执行计划重用。在执行计划缓存里，只会看到一份执行计划（prepared）,不再有ad hoc的那一份。
o
存储过程（stored procedure）- 对用户经常要调用的指令，把它们做成存储过程，既方便管理，规范脚本，又能够大大提高执行计划重用率，是值得推荐的一种做法。从SQL的角度，最好绝大多数指令都能够以存储过程的方式调用，尽量少使用Dynamic SQL的方式。例如把前面的那个查询做成一个存储过程，然后用EXEC加参数的方式调用。可以清楚地看见第二次调用重用了第一次执行计划生成的执行计划。
·
但是有些时候，SQL为了确保返回正确的值，或者有性能上的顾虑，有意不重用缓存在内存里的执行计划，而现场编译一份。这种行为，被称为重编译（recompile）。下面是比较常见的会发生重编译的情形：
o
当指令或者批处理所涉及的任何一个对象（表格或者视图）发生了架构（schema）变化。
例如，在表或者视图上添加或删除了一个字段，添加或者删除了一个索引，在表上添加或者删除了一个约束条件（constraint）等。定义发生了变化，原来的执行计划就不一定正确，当然要重编译。
o
运行过sp_recompile
当用户在某个存储过程或者触发器上运行过sp_recompile后，下一次运行它们就会发生一次重编译。如果用户在某个表或者视图上运行了sp_recompile，那么所有引用到这张表（或者视图）的存储过程在下一次运行前，都要做重编译。
o 有些动作会清除内存里的所有执行计划，迫使大家都要做重编译。例如，下列动作会清除整个SQL服务器缓存的所有执行计划：
ü Detach一个数据库
ü 对数据库做了升级，在新的服务器上，会发生执行计划清空
ü 运行了DBCC freeproccache
ü 运行了reconfigure语句
ü 运行了alter database..collate语句修改了某个数据库的字符集（collation）
下列动作会清除SQL服务器缓存的某个数据库的执行计划：
ü DBCC FLUSHPROCINDB (db_id) --清除SQL Server 2000服务器内存中的某个数据库的存储过程缓存内容
ü ALTER DATABASE ...MODIFY NAME语句
ü ALTER DATABASE ...SET ONLINE语句
ü ALTER DATABASE ...SET OFFLINE语句
ü ALTER DATABASE...SET EMERGENCY语句
ü DROP DATABASE 语句
ü 当一个数据库自动关闭时
ü DBCC CHECKDB语句结束时
o 当下面这些SET 开关值变化后，先前的那些执行计划都不能重用。这是因为这些SET开关会影响语句的执行的行为，甚至带来不同的结果。它们发生变化了，SQL就要根据新的设置重做执行计划
ü ansi_null_dflt_off,
ü ansi_null_dflt_on,
ü ansi_nulls,
ü ansi_padding
ü ansi_warnings,
ü arithabort,
ü concat_null_yields_null,
ü datefirst,
ü dateformat,
ü forceplan,
ü language,
ü no_browsetable,
ü numeric_roundabort,
ü quoted_identifier
o 当表格或者视图上的统计信息发生变化后 - 当统计信息被手动更新后，或者SQL发现某个统计信息需要自动更新时，SQL会对所涉及的语句都做重编译。
· 在SQL里，计划重用可以帮助SQL节省编译时间，对降低CPU使用率和减少阻塞都有好处。但是每次重用的计划并不一定是最合适的计划。编译和重编译当然能给当前运行的语句带来尽可能准确执行计划，但是对于经常运行的语句，尤其是一些执行速度比较快的语句，可能其编译时间占最后总时间的相当大比例。这对资源来讲是一个很大的浪费。一般来说，SQL能够很好地在编译与重编译之间做平衡，大部分情况下没什么问题。如果发现系统因为编译和重编译太多，或者重用了不准确的计划，导致了性能问题，那就要根据具体情况做调整。在SQL里，能对计划重用和编译/重编译产生影响的功能主要有：
o 使用存储过程，或者[sys].[sp_executesql]的方式调用会被重复使用的语句，而不要直接用adhoc语句或者dynamic SQL
o 在语句里引用对象（表，视图，存储过程等），要带上它的schema名字（full qualified object name）能够更好地“引导”SQL重用执行计划。
o 将数据库parameterization属性设置为forced - SQL2005以后，可以在单个数据库上开启强制参数化。如果应用经常用adhoc方式调用一样的语句，强制参数化可能会有所帮助。
o 统计信息更新 - 统计信息手工或者自动更新后，对和它有关的执行计划都不再能重用，而会产生重编译，这样能够保证下次运行的语句能够根据数据变化使用新的执行计划。
o Create procedure...with recompile选项和exec ....with recompile选项会强制SQL在调用这个存储过程的时候，永远都先编译，再运行。哪怕有合适的缓存执行计划，也不会重用。这个选项一般用在DBA已经确定了对某个特定的存储过程，或者是特定语句执行计划重用会带来负面影响时（例如parameter sniffing后果比较严重）。
o 用户调用了sp_recompile - Causes a stored procedure, trigger, table, view, or user-defined function in the current database to be recompiled the next time that they are run. 当DBA怀疑某个对象上现在缓存的计划不合适，已经造成性能问题时，可以运行sp_recompile，让SQL重新编译
EXEC [sys].[sp_recompile] @objname = N'xxx’
--The following example causes stored
procedures, triggers, and user-defined functions that act on the Customer table to be recompiled the next time that they are run.
USE AdventureWorks2012;
o 用户在调用语句的时候，使用了“keep plan”或者“keepfixed plan”这样的查询提示（query hints）。keep plan放宽了对临时表的重编译阀值(为什么只是临时表？)，使得SQL像对普通表一样对待临时表，不会在上面做额外的重编译。用户可以像下面这样使用它：
SELECT b.col4 ,
SUM(a.col1)
FROM [dbo].permtable
a
INNER JOIN #temptable
b ON a.col1 = b.col2
WHERE b.col3 < 100
GROUP BY
b.col4
OPTION ( KEEP PLAN )
KEEP PLAN - 强制查询优化器对查询放宽估计的重新编译阈值。 当通过运行 UPDATE、DELETE、MERGE 或 INSERT 语句对表进行的索引列更改数目达到估计数目时，会自动重新编译查询，该估计数目即为估计的重新编译阈值。 指定 KEEP PLAN 可确保当表有多个更新时不会频繁地对查询进行重新编译 (from msdn – 这里没提到只是临时表)。
keepfixed plan强制查询优化器不因统计信息的更改而重新编译查询。只有在基础表的结构发生变化后，或者有人运行过sp_recompile以后，才会发生重编译。用户可以像下面这样使用它：
USE [AdventureWorks]
GO
SELECT c.[TerritoryID]
,
COUNT(*) AS number ,
c.SalesPersonID
FROM Sales.[Store]
s
INNER JOIN Sales.[Customer] c ON s.[CustomerID] = c.[CustomerID]
WHERE s.[Name] LIKE '%Bike%'
AND [c].SalesPersonID
> 285
GROUP BY
c.[TerritoryID] ,
c.SalesPersonID
ORDER BY
number DESC
OPTION ( KEEPFIXED PLAN )
总之，DBA可以根据实际情况，选择某一种或某几种方法来影响SQL的编译/重编译行为。
· 在SQL里，也提供了很多跟踪和分析编译与执行计划重用行为的方法：
o 系统管理视图sys.syscacheobjects- 可以看到SQL缓存的所有执行计划。唯一再次强调的是，在一个生产环境里，SQL缓存的执行计划可能会很多。要去查询这张表最好带上一些过滤条件。
o 清除执行计划缓存的语句 -清除执行计划，顺便也释放内存。
DBCC FREEPROCCACHE
DECLARE @a INT
SELECT @a = DB_ID ('gposdb')
DBCC flushprocindb (@a)
o SQL Trace里的一些和编译有关的事件 - 在SQLTrace里有一些事件可用很有效地跟踪编译和重编译行为：
ü cursors -cursorrecompile:当游标所基于的对象发生架构变化，导致的TSQL游标做的重编译
ü performance -auto stats:发生自动创建或者更新统计信息的事件
ü SP:CacheHit:说明当前语句在缓存里找到了一个可用的执行计划
ü SP:CacheInsert:当前有一个新执行计划被插入到缓存里
ü SP:CacheMiss:说明当前语句在缓存里找不到一个可用的执行计划
ü SP: CacheRemove: 有执行计划被从缓存里移除。内存有压力的时候会发生这样的行为
ü SP: Recompile: 一个存储过程发生了重编译。这个事件有一个数据字段叫EventSubClass记录了重编译发生的原因。如果DBA要跟踪为什麽一条指令老是重编译，跟踪这个事件的这个字段很有用，它一共有11种可能的原因。所以在处理重编译问题时，这个事件很有帮助
Ø 1=架构已更改
Ø 2=统计已更改
Ø 3=重新编译DNR
Ø 4=所设置的选项已更改
Ø 5=临时表已更改
Ø 6=远程行集已更改
Ø 7=浏览Perm的方式已更改
Ø 8=查询通知环境已更改
Ø 9=MPI视图已更改
Ø 10=光标选项已更改
Ø 11=使用重编译选项
ü 在SQL2000中，只要批处理中的任何一句语句导致重新编译，就会重新编译整个批处理，无论此批处理是通过存储过程、触发器、Ad Hoc Queries，还是通过预定义的语句(SQLPrepare函数 – Introduced for ODBC 1.0 Standards Compliance: ISO 92)进行提交。而在SQL2005和更高版本中，只会重新编译批处理中导致重新编译的语句。因为这种变化，在SQL2005以后，要改用“TSQL-SQL:StmtRecompile”这个事件来跟踪重编译，因为现在的重编译都发生在语句一级。
o 一些性能监视器计数器
ü SQLSERVER:BUFFER MANAGER: buffer cache hit ratio,lazy writes/sec, procedure cache pages,total pages
ü SQLSERVER：Cache Manager: cache hit ratio,cache object counts,cache pages ,cache use counts/sec
ü SQLSERVER:MEMORY MANAGER: sql cache memory(kb)
ü SQLSERVER:SQL STATISTICS: auto-param attmpts/sec,batch request/sec,failed auto-params/sec,safe auto-param/sec, sql compilations/sec,sql re-compilations/sec,unsafe auto-params/sec
17.3.
读懂执行计划 750
·
得到执行计划的方式有两种：
o
一种是在指令的前面打开一些开关，让执行计划信息打在结果集里，这种方法比较适合在一个测试环境里对单个语句调优。这些开关最常用的有:
ü
SET SHOWPLAN_ALL ON
ü SET SHOWPLAN_XML ON （可以看到是否reuse plan，缺索引）
ü
SET STATISTICS PROFILE ON
o
另一种方法是使用SQL
Trace里的事件跟踪来跟踪语句的执行计划。这种方法的好处是无须打开任何开关，可以用来直接跟踪应用程序的语句执行。缺点是
它会把所有语句都抓下来常用的事件：
ü SHOWPLAN ALL、
ü SHOWPLAN STATISTICS PROFILE、
ü SHOWPLAN XML STATISTICS PROFILE
·
这两种方法收集的信息大部分是一样的。XML的方式会将结果以XML的结构返回，在SQL Trace里，这种结果可以用图形的方式显示执行计划以及其相关的信息。对执行计划比较简单的语句，这样的输出方式可能比较友善。但是对于复杂的执行计划，如果用图形的方式显示，一个屏幕很难全放得下，每一步的统计信息要鼠标点到才会显示出来，反而不如文本输出格式易读性好。所以下面将集中讨论SHOWPLAN ALL的方法和SHOWPLAN STATISTICS PROFILE方法。当然，有些信息（例如是不是reuse了一个执行计划，SQL有没有觉得缺少索引），只能在XML的输出里看到。
·
SHOWPLAN
ALL和SHOWPLAN STATISTICS PROFILE有两大区别：
o
SET
SHOWPLAN_ALL ON或者SQL Trace里的SHOWPLAN ALL输出执行计划是在语句执行前，而SET STATISTICS PROFILE ON或者SHOWPLAN STATISTICS PROFILE是在语句执行执行后才输出执行计划。换句话说：
ü SET SHOWPLAN_ALL ON或者SHOWPLAN ALL只返回执行计划，没有结果集返回，执行计划里只有“EstimateRows”，没有“Rows” (实际行数) 。
ü SET STATISTICS PROFILE ON或者SHOWPLAN STATISTICS PROFILE先返回结果集，再返回执行计划。执行计划里有“EstimateRows”，也有“Rows”(实际行数)
o
STATISTICS
profile的结果里，不但包含预估值，还会返回实际每一步的返回行数。比较这两行，就能看出SQL预估得是否准确。而Showplan
all的方法由于没有真正去运行这句查询，只能得到预估返回行数，得不到实际返回行数。但是STATISTICS profile的缺点是要真正去运行一遍语句。如果语句非常昂贵，要跑很久才能跑完（有时候甚至跑不完）。
·
SQL的执行计划是按照树状结构输出的，分成若干个层次，下一层分支隶属于上一层子句。
·
在执行计划里，会出现很多运算符，比如Parallelism,Nested Loops，Merge Join等。
17.3.1.
连接 754
·
联接是SQL语句里最常见的运算。一条语句会涉及若干个表格，这些表格是通过某些字段联接在一起的。SQL有三种Join方法：nested loops join,merge join ,hash join。SQL会根据两个结果集所基于的表格结构，以及结果集大小，选择最合适的联接方法。当然，用户也可以在语句里指定join方法如INNER
LOOP JOIN，INNER MERGE JOIN，或者INNER HASH JOIN 。SQL会尽力尊重你的选择。
·
Nested
loops join是一种最基本的联接方法，被SQL广泛使用。它的算法是对于两张要被join在一起的表格，SQL选择一张做Outer table ,另外一张做inner table
o
Nested
loops join是一种基本的联接方式。它不需要SQL为join建立另外的数据结构所以也比较节省内存空间，也无须用tempdb的空间。它适用join类型是非常广泛的。但是它的缺点也很明显：
ü 算法的复杂度等于inner table乘以outer table。所以如果是两张表比较大, 尤其是outer table比较大的情况，inner
table会被扫描很多次。所以nested loops join比较适合于两个比较小的结果集做联接，或者至少是outer table的结果集比较小。
ü Outer table的数据集最好能够事先排序好，以提高检索效率。
ü Inner table上最好有一个索引，能够支持检索 - 由于算法是拿着outer
table里的一个值，在inner table里找到所有符合条件的记录，所以在inner table里找得快慢也能很大程度上影响整体的速度。如果进行检索的字段上有一个索引，查找的速度会大大加快。
o
总之，nested
loops join对数据集比较小的联接，效率是最高的，因此在SQL里使用得很广泛。当SQL发现能够选择一个很小的数据集作为outer table的时候，它往往会选择nested loops，大的数据集做outer
table，性能会急剧下降。很多语句性能问题，都是由于这个造成的。
·
Merge Join
o
Merge
join的前提条件是，两个数据集必须要先排序好。
o
它的算法是：从两边的数据集里各取一个值，比较一下。
ü 如果相等，就把这两行联接起来返回。
ü 如果不相等，那就把小的那个值丢掉，按顺序取下一个更大的。
ü 两边的数据集有一边遍历结束，整个join的过程就结束。所以整个算法的复杂度最大就是大的那个数据集里的记录数量，
o
这个比起nested
loops join两个数据集相乘的复杂度，的确是小了很多。所以在数据集很大的情况下，merge join的优势是非常明显的。
o
但是merge
join的局限性也很强，所以在实际的语句里，使用得并不是那么普遍。它的局限性主要有：
ü 做联接的两个数据集必须要事先按照join的字段排好序。这个先决条件是Merge join算法的基础，而对大的数据集排序本来就是一件比较复杂的事情。有些数据集是基于join的那个字段上的索引得到的，所以能够不费额外的资源就排好了顺序，这时候用merge join可能就比较合适。
ü Merge join只能做以“值相等”为条件的联接，而且如果数据集可能有重复的数据，merge join要采用many-to-many这种很浪费资源的联接方式。在SQL扫描数据集时，如果数据集1有两个或者多个记录值相等，SQL必须得把数据集2里扫描过的数据暂时建立一个数据结构存放起来，万一数据集1里下一个记录还是这个值，那还有用。这个临时数据结构被称为“Worktable”，会被放在tempdb或者内存里。这样做很耗资源，
ü 上面这两个限制（排序，唯一（可以不唯一）），影响了merge join的使用范围。但是Merge join的一个独特好处是，返回的数据集也是按照顺序排好的。
o
这里顺便提一下结果集的顺序问题。有些用户会抱怨，同一个查询的结果集为什麽有时候是按它想要的顺序排列，有时候又不是。讲完了Merge join大家就能够理解，同样做join，merge就能够按顺序返回，但是nested loops 就不能。只要语句里没有指定“order by”, SQL选取哪一种join并不需要考虑结果集是否按顺序返回的。它更多考虑的是哪一种join算法代价最小。
·
Hash Join
o
Hash
join就是利用哈希算法做匹配的联接算法。哈希算法分成两步：“build”和“probe”。在“Build”阶段，SQL选择两个要做join的数据集中的一个，根据记录的值建立在一张在内存中的hash表。然后在“probe”阶段，SQL选择另外一个数据集，将里面的记录值依次带入，找出符号条件，返回可以做联接的行。
o
和其它两种join算法比，hash join的优点是很明显的
ü 这对于数据集比较大的join，其复杂度能够控制在合理的范围以内（即分别遍历两边的数据集各一遍）。虽然比Merge join要高一些，但是比nested loops要简单许多。
ü 它不需要数据集事先按照什么顺序排序，也不要求上面有索引，因为联接使用的是哈希算法，对输入没有限制，不需要SQL像为merge
join一样，事先准备好一个排过序的输入。由于做Hash join总是要把两边的数据集都要扫描一遍。所以有没有索引其实帮助也不大。没有索引，对性能也不会有太大影响。
ü 可以比较容易地升级成使用多处理器的并行执行计划 - 因为算法没有要求代入的数据有任何次序，所以用多个CPU并行完成是比较容易的。
ü 总之，hash join是一种适合于要join的数据集比较大，上面没有合适索引的情况。
o
但是，hash
join并不是一种最优的join算法，只是SQL在输入不优化（join的数据集比较大，或上面没有合适索引）的时候的一种不得已的选择。这是因为Hash join是一种最耗资源的join算法。它在做join之前，要先在内存里建立一张hash表。建立的过程需要CPU资源，hash表需要用内存或tempdb存放。而join的过程也要使用CPU资源来计算（“probe”）。如果同时有很多用户在用hash算法做join，对SQL的整体负担是比较重的。从降低SQL整体负荷的角度考虑，还是要尽量降低join输入的数据集的大小，配合合适的索引，引导SQL尽量使用nested
loops join或者merge join。
·
本节小结：在SQL做联接的时候，会按照输入数据集所基于的表格的结构，衡量可能利用的索引，也根据统计信息，预估两个输入数据集的大小，选择使用三种join方式其中的一种。如果选得不对，可能会造成join的速度非常慢。
17.3.2.
其它常见的运算操作 762
·
除了读取数据的运算操作（table scan/seek、clustered
index scan/seek、index scan/seek、bookmark lookup等）以及3个join运算，SQL常见的其它运算有：
·
Aggregation
- 主要用来计算sum()、count()、max()、min()、avg()、distinct()等运算函数。aggregation也分两种
o
Stream
Aggregation(将数据集排成一个队列以后做运算)和
o
Hash Aggregation
(类似于hash join，需要SQL先建立hash表，然后才能做运算) 。
·
Union就是把两个数据集合并起来。但是要注意的是，Union有两种：Union ALL和Union。Union ALL很简单，就是合并两个集合，不管里面是否有重复的数据。但是Union就不同了。它不但要合并两个数据集，还要把其中重合的数据删除。所以SQL要把合并以后的结果集进行排序。这是比较昂贵的。
·
Parallelism
- 当SQL发现某个要处理的数据集比较大，cost比较高时，如果SQL服务器有多颗CPU，SQL就会尝试使用并行的方法，把一个数据集分成若干个，由若干个线程同时处理，以提高总体效率。这就是通常说的“并行执行计划”
17.4.
读懂语句运行统计信息 765
·
如果DBA能够把问题语句单独测试运行，可以在运行前打开下面这三个开关，收集语句运行的统计信息包括语句的编译时间、执行时间、做了多少次磁盘读等。
SET STATISTICS TIME ON
SET STATISTICS IO ON
SET STATISTICS PROFILE ON
· 可能在测试前，需要先清理缓存
--清除buffer pool里的所有缓存数据
DBCC DROPCLEANBUFFERS
--清除buffer pool里的所有缓存的执行计划
DBCC freeproccache
17.4.1.
set statistics time on–先来看看SET
STATISTICS TIME ON会返回什么信息
·
分析和编译（parse
and compile）时间 - 由于语句运行之前清空了所有执行计划，SQL必须要对它编译。由于编译主要是CPU的运算，所以一般CPU时间和占用时间是差不多的。如果这里相差比较大，就有必要看看SQL在系统资源上有没有瓶颈了。
o
Parse and compile时间: CPU 时间 = 15
毫秒，elapsed时间 = 36
毫秒
这里它们是一个15毫秒，一个是36毫秒。其中,
ü
CPU时间 - 在这一步，SQL所花的纯CPU时间是多少。
ü
占用（elapsed）时间 - 这一步一共用了多少时间。这是语句运行的时间长短.
o
SQL执行时间 - 语句真正运行的时间。由于语句是第一次运行，SQL需要把数据从磁盘读到内存里，这里语句的运行发生了比较长的I/O等待。所以这里的CPU时间和占用时间差别就很大了，一个是32毫秒，而另一个是767毫秒。
o
总的来讲，这条语句花了36+767=803毫秒，其中CPU时间为15+32=47毫秒。语句的主要时间应该是都花在了I/O等待上。
· 现在再做一遍语句，但是不清除任何缓存。由于执行计划被重用，SQL分析和编译时间CPU时间是0，占用时间是1ms。另外，由于数据已经缓存在内存里，不需要从磁盘上读取，SQL执行时间 CPU时间是32，占用时间这次和CPU时间非常接近，是41。这里省下运行时间767-41=726毫秒。从这里可以再次看出，缓存对语句执行性能起着至关重要的作用。
17.4.2.
set statistics io on - 现在来看看SET
STATISTICS IO ON会返回什么信息
· 这个开关能够输出语句做的物理读和逻辑读的数目。 它的返回是：表 'SalesOrderDetail_test'。扫描计数 5，逻辑读取 15064 次，物理读取 0 次，预读 15064 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
· 注意预读是指为进行查询而预读入缓存的页数。所以，物理理读取+预读：就是SQL为了完成这句查询而从磁盘上读取的页数。如果不为0，说明数据没有缓存在内存里。运行速度一定会受到影响。
· LOB逻辑读取：从数据缓存读取的text、ntext、image大值类型或者（varchar(max)、nvarchar(max)、varbinary(max)）页的数目
17.4.3.
set statistics profile on - 最后来看看SET
STATISTICS PROFILE ON会返回什么信息
· 这是三个设置中返回最复杂的一个，它返回语句的执行计划，以及语句运行在每一步的实际返回行数统计。通过这个结果，不仅可以得到执行计划、理解语句执行过程、分析语句调优的方向，也可以判断SQL是否选择了一个正确的执行计划。如果不正确，原因又大概是什么。
· 注意：看返回的结果是从最下面开始向上看的，也就是说从最下面开始一直执行直到得到结果集。所以（行1）里的rows字段显示的值就是这个查询返回的结果集。而且有多少行表明SQL执行了多少个步骤。
· 返回的结果集中重要的字段有：
o rows:执行计划的每一步返回的实际行数
o executes:执行计划的每一步被运行了多少次
o Stmttext: 执行计划的具体内容。执行计划以一棵树的形式显示。每一行，都是运行的一步都会有结果集返回，也都会有自己的cost
o Estimaterows: SQL根据表格上的统计信息，预估的每一步的返回行数。在分析执行计划时，我们会经常将rows和estimaterows这两列做对比，先确认SQL预估得是否正确，以判断统计信息是否有更新。
o Estimateio: SQL根据estimaterows和统计信息里记录的字段长度，预估的每一步会产生的I/O cost。
o estimatecpu:SQL根据estimaterows和统计信息里记录的字段长度，以及要做的事情的复杂度，预估的每一步会产生的CPU cost
o totalsubtreecost:SQL根据estimateio和estimatecpu通过某种计算公式，计算出每一步执行计划子树的cost（包括这一步自己的cost和它的所有下层步骤的cost总和），下面介绍的cost说的都是这个字段值
o Warnings: SQL在运行每一步时遇到的警告，例如，某一步没有统计信息支持cost预估等。
o Parallel: 执行计划的这一步是不是使用了并行的执行计划.
· 执行计划里cost的值绝对大小问题
o 经常有人会问，两句查询的cost，是不是一句比另一句高，就一定是高的那一句比较耗时？简而言之，在同一个SQL SERVER里，可以说是的。
o SQL是根据预测结果集 (estimaterows) 大小和结果集里的数据类型推算出estimateio和estimatecpu，然后再根据estimateio和estimatecpu推算出totalsubtreecost。计算方法是所有SQL SERVER都一样的（只要SQL SERVER版本一样），所以在同一个SQL SERVER里，可以说，cost高的那一句一定会比较复杂。当然，前提是SQL要estimate得准确。
o 但是对于不同的SQL SERVER，由于硬件差异，SQL能够调动的内存，CPU，磁盘资源也会有所差异。同样cost的语句，执行的速度会有很大不同。所以一般不会去对比从不同SQL服务器上取得的执行计划里的cost。
17.5.
小结
775
·
Nested Loops Join对数据集比较小的连接效率是很高的，因此，在SQL Server里使用得很广泛。
·
Merge Join的前提是2个做连接的数据集必须要首先按照Join的字段排好序，其次，Merge Join只能做“值相等“的连接。如果数据集有重复的数据，它则要采用M-to-M这种很费资源的连接方式。
·
Hash Join利用哈希算法做匹配的连接算法。用于数据集比较大，上面又无合适索引的情况。
