笔记59-徐 问题定位与解决方法

 笔记59-徐  问题定位与解决方法  

--问题定位与解决方法     第13章 2013-2-6
--思路：
--1、确认是否因为做了物理I/O而导致性能不佳
--如果一条语句所要访问的数据页面没有事先缓存在内存里，那么数据
--页面要在语句运行的过程中，从磁盘读到内存里，这是一个很昂贵的动作。
--如果语句要访问的数据页面比较多，就会产生比较长的时间消耗。
--多数情况下，有数据缓存时的语句运行时间，会比没有数据缓存的时间，
--相差一个到几个数量级


--所以语句调优的前提，是要确认数据页面能够事先缓存在内存里。如果这个
--问题得到解决后，性能还不能达到要求，才有后续调优必要。如果这个
--问题解决后就能跑得足够快，那说明这个问题更是一个系统资源瓶颈问题
--而不是语句本身问题。


--2、确认是否是因为编译时间长而导致的性能不佳
--语句的执行总时间，由编译时间和运行时间两部分组成。大部分情况下，
--编译时间会远小于运行时间。而且很多时候SQL会重用执行计划，所以
--语句的第一次执行有编译时间，后面的执行常常就只有运行时间了。因此，
--调优的大部分精力，会花在降低运行时间上。
--但是，有些语句处理的数据量比较小，可以很快做完，但是语句本身并不很
--简单。SQL还是要“想一想”才能找到合适的执行计划。这样的语句，其编译
--时间可能会占总时间的50%甚至更高

--所以对于这类执行总时间比较短，但是在应用程序里会被反复调用的语句，就有
--必要检查他们编译时间占总时间的比重。如果编译相对比较昂贵，而运行速度
--又很快，调优的重点会转向如何避免重编译，或者降低编译时间。语句运行
--调优的空间，往往不是很大

--如果语句要访问的数据都已经缓存在内存里，而且编译时间很短，那么调优就进入
--如何降低语句运行时间这个比较纯粹的课题了。而这个工作，也要分两步进行

--3、确认SQLSERVER是否正确地预估了每一步的cost，选择了正确的执行计划
--语句要能高效运行，前提是SQL选择的执行计划是正确的。而选择正确的执行计划
--的前提，又是SQL正确地预估了每一个执行子句的cost，SQL是根据EstimateRows
--的大小来预估cost的。所以简单来讲，要判断一个执行计划是否合适，最重要
--的就是要判定SQL是不是正确预估了执行计划每一步返回的结果集的大小。
--如果预估值和实际值相差很多（这倒是经常发生的），就要判定他是否造成
--SQL选择了一个错误的执行计划。这里的判断需要一些经验，有时候也要做一些
--测试，才能下结论

--如果这一步SQL也没问题，就说明以现有的表结构和索引，SQL无法做到在预期
--时间内完成语句运行。这时就进入最后一步，也就是纯粹的语句调优步骤

--4、检查表结构和语句逻辑，确认是否有调优空间，提高语句的运行速度
--调整索引，当然是语句调优的重要途径。可以通过执行计划里的每个子句的cost
--找到最花时间的部分，看看是否可以通过调整索引的方法，提高运行效率。
--但是对于一些语句，可能天生就比较复杂，很难降低他的运行时间，有可能
--无法通过调整索引来达到目的。但是，做同样的业务逻辑可能会有不同的做法
--调整表格结构或者语句的写法，往往能起到事半功倍的效果。所以要提醒DBA和
--开发者，有时候要灵活解决问题。修改数据库或程序设计虽然比较麻烦，但是
--可能会比光调SQL设置效果好很多



--1、是否是因为做了物理I/O而导致性能不佳
--用什么办法能知道一条语句的运行有没有做物理I/O呢？怎样才知道，一条语句
--在数据提前缓存在内存里的前提下，要运行多久呢？这个信息DBA要打开
SET STATISTICS IO ON
--和
SET STATISTICS TIME ON
--开关以后，运行语句，才能得到
--例如，运行下面这段话，因为他在运行之前使用
DBCC DROPCLEANBUFFERS
--指令清除了缓冲池里所有缓存的页面，所以一定会做物理I/O动作。开启
SET STATISTICS IO ON
--和
SET STATISTICS TIME ON --就能知道问题的答案

DBCC DROPCLEANBUFFERS
--清除buffer pool里的所有缓存的数据页面
GO

SET STATISTICS IO ON
GO

SET STATISTICS TIME ON
GO

USE [AdventureWorks]
GO

SELECT
DISTINCT [ProductID],
[UnitPrice]
FROM [dbo].[SalesOrderDetail_test]
WHERE [ProductID]=777
GO

--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
--SQL Server 分析和编译时间:
--   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--
--SQL Server 执行时间:
--   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--SQL Server 分析和编译时间:
--   CPU 时间 = 16 毫秒，占用时间 = 188 毫秒。
--SQL Server 分析和编译时间:
--   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--
--(4 行受影响)
--表 'SalesOrderDetail_test'。扫描计数 5，逻辑读取 15064 次，物理读取 0 次，预读 15064 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
--
--SQL Server 执行时间:
--   CPU 时间 = 187 毫秒，占用时间 = 2108 毫秒。

--检查SET STATISTICS IO ON的输出，会发现表[SalesOrderDetail_test]的
--“物理读取”和“预读”不为0.这意味着语句做了物理I/O。运行这个例子的机器是台
--I/O很慢的机器，所以语句花了11009ms才跑完


--立刻再运行一次查询，不清空缓存区
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
USE [AdventureWorks]
GO
SELECT DISTINCT [ProductID],[UnitPrice]
FROM [dbo].[SalesOrderDetail_test]
WHERE [ProductID]=777
GO

--这次由于语句刚刚跑过，内存里的数据页还在，可以感觉到速度明显不同
--SQL Server 分析和编译时间:
--   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--
--SQL Server 执行时间:
--   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--SQL Server 分析和编译时间:
--   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--
--SQL Server 执行时间:
--   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--SQL Server 分析和编译时间:
--   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--
--SQL Server 执行时间:
--   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--SQL Server 分析和编译时间:
--   CPU 时间 = 16 毫秒，占用时间 = 125 毫秒。
--SQL Server 分析和编译时间:
--   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--
--(4 行受影响)
--表 'SalesOrderDetail_test'。扫描计数 5，逻辑读取 15064 次，物理读取 0 次，预读 15064 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
--
--SQL Server 执行时间:
--   CPU 时间 = 218 毫秒，占用时间 = 1743 毫秒。



--检查SET STATISTICS IO ON的输出，会发现表[SalesOrderDetail_test]的
--“物理读取”和“预读”都是0。这意味着语句完全没有做物理I/O
--语句花了262ms就跑完。可见在这台服务器上，做不做物理I/O的差别有多大

--如果你在单个语句调优时发现性能问题，只在物理I/O的时候才出现，那需要做的事情
--有下面几件：

--（1）检查生产服务器是否有内存瓶颈，是否存在经常换页的现象
--如果生产环境下，内存没有瓶颈，或者很少有page out/page in的动作，那说明
--SQL能够把数据页面维护在内存里，你看到的性能问题就不太会发生，所以无须太过忧虑
--如果有明显的内存瓶颈，那首先要解决的是SQL服务器层面的内存瓶颈。因为这个
--瓶颈不但会造成这句话运行慢，也会影响其他语句的性能

--（2）检查这句话，和他访问的数据，是被经常使用的，还是偶尔使用的
--如果问题语句经常会被不同的用户调用到，那按道理他说访问的数据应该缓存在
--内存中。如果这句话只是被某个特定的用户或任务偶尔调用到，而他访问的数据量既大
--其他人又不经常使用，那SQL没有把他们放在内存里也是正常的。对这样的语句，
--其运行时间里有物理I/O时间是合理的。这个可能要和最终用户做一下沟通，设定
--他们合理的性能期望值。并且安排他们在非业务高峰期时运行，以避免影响
--其他关键业务


--（3）检查语句执行计划，是否能减少其访问的数据量
--语句访问的数据量越小，要做的物理I/O就越少，当然能够越快。从这个角度
--做语句本身的调优也是有用的。

--（4）检查磁盘子系统的性能
--如果语句访问的数据很可能就不在内存里，而其数据量也很大，还一定要提高其性能，
--那唯一的出路就是提高磁盘子系统的性能了。这种情况，SQL内部的设置可能帮不了
--什么忙。要做的工作可能更多地会在硬件层面




--2、是否是因为编译时间长而导致性能不佳
--一般DBA需要对两类语句重点检查编译时间。一种是比较简单，长度比较短
--涉及表格比较少，但是在应用或任务里反复调用的语句。比较简单的语句执行
--起来会比较快。如果一句话执行只要100毫秒，编译也要100毫秒，那SQL
--花这麽多时间编译似乎有点不划算。如果能够通过执行计划重用来去除
--编译时间，或者通过调整数据库设计来降低编译时间，那整体的效率就能够
--提高40%~50%。如果语句只执行一次，从200毫秒降低到100毫秒似乎意义不大
--但是，如果一个任务要调用同样的语句成千上百次，效果就很明显了。还有
--一类，是语句本身比较复杂，或者其所基于的表格上有太多的索引可供
--选择，使得编译时间超过1秒，甚至更长

--比较好的是，用户可以有两种方法检查语句的编译时间。SET STATISTICS TIME ON
--当然是一种方法，但是其缺点是只能在测试连接里收集，不能收集到应用发过来
--的语句信息。使用SQL Trace，也可以收集到语句的编译时间，而且没有限制，
--可以收集到任何一个连接的信息

--下面用一个存储过程，来模拟一句会有比较长的编译时间的语句。存储过程
--的主体是一个三张表的查询，其中有一个“in”子句。根据代入参数@i的值，
--脚本会为其代入相应个数的SaleOrderID值。如果代入的值很多，“in”子句很长，
--语句本身就变得很长，SQL就要花比较多的时间和资源做编译
USE [AdventureWorks]
GO
DROP PROC LongCompile
GO

CREATE PROC LongCompile(@i INT)
AS
DECLARE @cmd VARCHAR(MAX)
DECLARE @j INT
SET @j =0
SET @cmd='
select count(b.SalesOrderID),sum(p.Weight)
from dbo.SalesOrderHeader_test a
inner join dbo.SalesOrderDetail_test b
on a.SalesOrderID=b.SalesOrderID
inner join Production.Product p
on b.ProductID=p.ProductID
where a.SalesOrderID in(43659'
WHILE @j<@i
BEGIN
SET @cmd=@cmd+','+STR(@j+43659)
SET @j=@j+1
END
SET @cmd=@cmd +')'
EXEC(@cmd)
GO

--SET STATISTICS TIME ON的检查方法前一章已经介绍过，这里不重复了。现在使用
--SQL Trace里的下面事件来跟踪语句的编译时间。这种方法得到的结果，虽然没有
--SET STATISTICS TIME ON的那么精确，但还是可以拿来参考的

--编译会发生下面情况：
--（1）一个新的批处理batch开始运行前
--（2）一个新的存储过程stored procedure开始运行前
--（3）语句发生重编译recompile的时候

--SQL Trace的事件不会直接打印出编译时间，但是你可以比较某些事件开始时间点starttime
--之间的间隔，算出当时的编译时间

--如果你发现语句性能问题和编译有关系，必须考虑的方向有：
--（1）检查语句本身是否过于复杂，长度太长
--如果是，可以考虑把一句话拆成几句更简单的语句，或者用temp table来替代大的
--“in”子句。
--因为SQL是先编译整个batch，然后再开始运行第一句话
--因为SQL是先编译整个SP，然后再运行第一句话

--（2）检查语句使用的表格上是不是有太多的索引
--索引越多，SQL要评估的执行计划就越多，花的时间越长。作为一个设计严谨的数据库，
--要把没有用的索引及时删除

--（3）引导SQL尽量多重用执行计划，减少编译


--3、判断执行计划是否合适
--如果SQL选错了执行计划，那当然会直接影响到语句的性能。在排除了数据缓存和
--编译这两大因素以后，用户就要来检查SQL现在选择的执行计划是否正确。但是，
--什么是“正确”的执行计划呢？大部分情况下SQL计算得会比人脑要准确，他选择
--的执行计划是有道理的。要找出一个比他选的更好的执行计划，不是那么容易

--但是SQL还是会犯错误。通常可以从以下这几个角度，判断现在得到的执行计划是否
--准确，以及有没有提高的空间

---------------------------------------------------------------------------------------------------
--预估cost的准确性
--之前介绍过SQL计算一个候选的执行计划开销的算法，他是根据EstimateRows、AvgRowSize
--和每一步要做的事情，估算出EstimateIO和EstimateCPU，然后再根据这两个值算出
--TotalSubtreeCost。SQL在候选的执行计划中，挑一个他算出来TotalSubtreeCost
--最低的。而一般AvgRowSize是不会估错的，所以，如果SQL选择的执行计划有问题，
--常常是因为EstimateRows估错。

--因此判断每一个子句的TotalSubtreeCost是否准确，常常就转化为检查EstimateRows和
--真实Rows的差别。这两个数据，可以通过
SET STATISTICS PROFILE ON
--的输出获得，也可以在SQL Trace里showplan statistics profile事件里得到

--需要说明的是，当SQL预估某一步不会记录返回时，他不是把EstimateRows设置为0，
--而是设置为1。所以EstimateRows等于1，常常意味着SQL认为该步骤不会有记录返回。
--如果实际的Rows不是0，也不是1，而EstimateRows等于1，那就要好好检查SQL在
--这里的预估开销是否准确，是否会影响到执行计划的准确性

--之前讲过一个案例突然很慢，重建索引以后性能就会恢复正常的查询。当时说，通过
--分析语句执行计划和统计信息，作者将问题定位到了两个表的联接动作上。是怎麽
--定位到这一点呢？

--查询的执行计划很复杂，下面的拷屏是其中的主体部分

--可以看到查询做了很多联接动作，而且都是用nested loops完成的。在拿到执行计划
--以后，第一步要做的事情，不是去看哪一部分的TotalSubtreeCost最高，而是要
--比较Rows和EstimateRows这两列的值差别大不大。我们把EstimateRow这一列，拷贝
--到执行计划的开头。这样比较起来就方便多了。


--在这个执行计划里，Rows的值和EstimateRows的值有明显差距。根据SQL的预估，
--联接最里面的几层应该不返回记录（EstimateRows=1.0）。而实际上，他们返回
--了9653条记录。这麽大的结果集，用nested loops是不太合适的。直接的结果，
--是Executes这一列的值在很多步骤上很高。从这种现象，笔者开始怀疑他是不是
--问题的直接原因

--很有利的是，还有一份查询速度正常的时候的执行计划。从这份执行计划里，可以
--看到正确的做法和那时的EstimateRows值

--很明显，在运行正常的时候，SQL预估的值要高得多，所以SQL选择的是HASH MATCH JOIN
--和MERGE JOIN的方式。这证明了前面的猜想。在查询有性能问题的时候，SQL没有正确
--地估计联接返回记录的多少，所以错误地估计了nested loops联接动作的开销，选择了
--一个错误的执行计划

--要强调的是，执行计划没有绝对的对错。只是一种执行计划在某种上下文，是合适的。
--但是在另一种上下文，可能又是不合适的。如果能够拿到一份既有EstimateRows列，
--又有Rows列的执行计划，那比较一下两列的差别，问题就能比较清楚。如果因为
--种种限制而拿不到两列的值，可能就要靠经验来猜测了

--现在再来做一个案例。在这个案例里，读者不但要用到读取执行计划的知识，还要
--用到编译和重编译的知识

--有一个查询，会返回大约12万条记录。请运行他们三次。第一次是正常运行，
--第二次运行之前，会先清空执行计划，然后设置语句只返回一条记录（set rowcount 1）
--第三次运行之前，不清空执行计划，但是设置语句返回所有记录（set rowcount 0）
--按道理，第一次运行和第三次运行应该速度相当，因为他们都要返回12万条记录。
--第二次会最快，因为他只返回一条记录

--第一次
USE [AdventureWorks]
GO
DBCC DROPCLEANBUFFERS --清空缓存数据页面 buffer pool
GO

DBCC freeproccache  --清空执行计划
GO

SET ROWCOUNT 0
GO
SET STATISTICS PROFILE ON
SELECT p.[ProductID],p.[Weight]
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
INNER JOIN [Production].[Product] p
ON b.[ProductID]=p.[ProductID]
WHERE a.[SalesOrderID]=75124

GO
---------------------------------------------------------
--第二次
USE [AdventureWorks]
GO
DBCC freeproccache
GO
SET ROWCOUNT 1
GO
SET STATISTICS PROFILE ON
SELECT p.[ProductID],p.[Weight]
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
INNER JOIN [Production].[Product] p
ON b.[ProductID]=p.[ProductID]
WHERE a.[SalesOrderID]=75124

GO

-----------------------------------------------------
--第三次
USE [AdventureWorks]
GO
SET ROWCOUNT 0
GO
SET STATISTICS PROFILE ON
SELECT p.[ProductID],p.[Weight]
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
INNER JOIN [Production].[Product] p
ON b.[ProductID]=p.[ProductID]
WHERE a.[SalesOrderID]=75124

GO


--第三次运行代价比第一次高的原因是,他重用了set rowcount1时的执行计划，而在
--set rowcount 0这个执行计划是不合适的。SQL在重用执行计划的时候没有考虑到
--set rowcount的情况，是他不够聪明的地方。希望在将来版本里，SQL能够有所改进

-----------------------------------------------------------------------------------------------------
--是Index Seek还是Table Scan
--检查执行计划的第二个重点，是要检查SQL从表格里检索数据的时候，是否选择了
--合适的方法。前面介绍过SQL检索数据的方法。在不同的数据结果上，SQL会根据
--检索数据的多少，以及索引的结构，选择是用Index Seek还是Table Scan

--不同结构上的数据检索方法          扫描                查找
--堆（没有聚集索引的表格数据页）  表扫描                 无
--聚集索引                        聚集索引扫描         聚集索引查找
--非聚集索引                      索引扫描             索引查找


--一般来说，如果检索返回的数据量，占整个表格的数据量比较小，那么用seek还是划算的
--所以常常说，seek会比scan要好。但是，如果检索返回的数据量差不多是整个表格的数据
--的一大部分，那么索引上的seek不会有什么帮助，甚至直接用scan可能会更快一些。
--所以也不是scan就绝对地比seek要差，不能看到scan就要想办法变成seek。关键还是要
--看EstimateRows和实际的Rows的大小

--如果某个数据检索动作实际返回的行数不多，但是SQL选择了scan方法，那就要重视了，
--因为这种scan，会带来比较大的性能影响，会慢几十倍，几百倍，甚至上千倍。
--下面是一个例子
--第一个查询
USE [AdventureWorks]
GO
SET STATISTICS PROFILE ON
GO
SELECT COUNT(b.[CarrierTrackingNumber])
FROM dbo.[SalesOrderDetail_test] b
WHERE b.[SalesOrderDetailID]>10000 AND
b.[SalesOrderDetailID]<=10100
GO
-------------------------------------
--第二个查询
USE [AdventureWorks]
GO
SET STATISTICS PROFILE ON
GO
SELECT COUNT(b.[CarrierTrackingNumber])
FROM [dbo].[SalesOrderDetail_test] b
WHERE CONVERT(NUMERIC(9,3),b.[SalesOrderDetailID]/100)=100
GO

--第一个查询很简单，[SalesOrderDetailID]字段是表格的聚集索引所在字段
--所以很自然地，SQL使用的是clustered index seek

--第二个查询的意思，和第一个一样，但是写法不同，在[SalesOrderDetailID]
--字段上做了计算。其结果是直接妨碍了SQL在这个字段的索引上使用seek
--SQL将不得不用scan的方法，找出所有符合条件的记录。但是这里，
--SQL做得还是比较聪明的

--SQL知道,如果要去scan整个表,是一件非常浩大的工程.所以他找找自己
--有没有其他索引覆盖了[SalesOrderDetailID]这个字段。因为索引只
--包含了表格的一小部分字段，占用的页面数量会比表格本身要小很多。
--去scan这样的索引，可以大大降低scan的消耗。非聚集索引SalesOrderDetail_test_NCL
--就是这样的一个索引。SQL选择了在他身上做scan。这是上面这个
--index scan的来源。美中不足的是，这个非聚集索引没有覆盖[CarrierTrackingNumber]
--这个字段。所以SQL还要根据挑出来的记录的[SalesOrderDetailID]
--值，到聚集索引SalesOrderDetail_test_CL上去找[CarrierTrackingNumber]
--这是第二个clustered index seek的来源

--这样做，显然比第一种做法要费事很多，所以两句话的性能是有很大差别的
--第一句话只要3个毫秒就做完了，而第二句话用953毫秒。两者相差300多倍，
--CPU和Reads的消耗也有很大差别

--那scan就一定比seek差麽？还是用这张表，再试一个例子。先创建一个存储过程，
--在[SalesOrderDetail_test]表上返回指定的6个SalesOrderID的所有记录的
--COUNT(b.[CarrierTrackingNumber])值

USE [AdventureWorks]
GO
DROP PROC Scan_Seek
GO
CREATE PROC Scan_Seek(@i INT)
AS
SELECT COUNT(b.[CarrierTrackingNumber])
FROM [dbo].[SalesOrderDetail_test] b
WHERE b.[SalesOrderDetailID]>@i AND
b.[SalesOrderID]<@i+7
GO


--在[SalesOrderID]等于43659到75123之间的记录里，每个[SalesOrderID]
--平均有三四条记录。但是在75124到75132之间，每个[SalesOrderID]都有
--12万条记录。所以对于这个查询，如果@i的值小于75115，那么检索条件
--只会返回几十条记录，占表格的很小一部分。如果@i的值等于75124，那
--这个查询就会返回表格的绝大多数记录。在这两种情况下，SQL会选择
--同样的执行计划麽？如果是的，结果会怎样？

--用下面的脚本测试一下
USE [AdventureWorks]
GO
[sys].[sp_recompile] @objname = N'Scan_Seek' -- nvarchar(776)
GO
SET STATISTICS PROFILE ON
EXEC [dbo].[Scan_Seek]  @i = 75124 -- int
GO
--------------------------------------------------------------
[sys].[sp_recompile] @objname = N'Scan_Seek' -- nvarchar(776)
GO
SET STATISTICS PROFILE ON
EXEC [dbo].[Scan_Seek]  @i = 43659 -- int
GO
-------------------------------------------------------------------
SET STATISTICS PROFILE ON
EXEC [dbo].[Scan_Seek]  @i = 75124 -- int
GO

--第一次运行和第二次运行前，因为都运行了[sp_recompile]，存储过程
--都发生了编译。第一次要计算的数据量比较大，用了808毫秒。第二次
--数据量小，只用了6毫秒。这个差异是数据量的差异造成的。

--但是第三次运行，代入的参数和第一次一样，却用了5391毫秒。CPU时间
--和Reads要高很多。这是为什麽呢？分析一下两次的执行计划差异

--第一次运行的时候，SQL选择的是一个clustered index scan的检索方法，
--直接scan整个表格，把要取的数据都找出来，最后返回727902行。相对于
--数据量，这是一个比较合适的做法

--第二次运行和第三次运行的执行计划是一样的，SQL选择SalesOrderDetail_test_NCL
--这个索引，找出所有 b.[SalesOrderDetailID]>@i AND b.[SalesOrderID]<@i+7
--的记录。索引能够返回SalesOrderID的值，但不能返回CarrierTrackingNumber
--的值。所以光seek这个索引不能完成查询。SQL还要根据挑出来的记录的
--SalesOrderDetailID值，到聚集索引SalesOrderDetail_test_CL上去找
--CarrierTrackingNumber。所以这里有一个clustered index seek，也有
--一个nested loops（其实这个nested loops就是一个bookmark lookup）

--这样的方法当b.[SalesOrderDetailID]>@i AND b.[SalesOrderID]<@i+7
--的记录比较少的时候，是比较有效的。但是像现在这种情况，符号条件
--的记录有727902行，SQL在聚集索引上就不得不做727902次lookup。所以
--最后的代价，反而比第一次运行，做整个表格的scan还要慢。这是一个
--很好的seek反而比scan要慢的例子

--因此，用户还是要比较实际返回行数和表格的整体行数，具体看，到底
--在语句的上下文，是scan好，还是seek好


--------------------------------------------------------------------------------
--是nested loops还是hash (merge) join


--之前详细介绍了SQL的三种join方式，也提到了，nested loops比较合适于
--联接的双方结果集比较小的情况，而hash（merge）join 适合结果集比较大
--的情况。做这麽详细的介绍，是因为很多SQL没有选对执行计划，而导致
--性能问题，都是因为误选了nested loops导致的。当outer table比较大
--时，使用nested loops方法，inner table会被loop很多次，导致执行
--复杂度急剧增加

--前面的例子，已经对这个问题有所体现。现在再来写一个例子，加深一下印象
--同样还是一个存储过程
USE [AdventureWorks]
GO
DROP PROC Sniff
GO
CREATE PROC Sniff(@i INT)
AS
SELECT *
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
INNER JOIN [Production].[Product] p
ON b.[ProductID]=p.[ProductID]
WHERE a.[SalesOrderID]=@i
GO

--然后运行一下
USE [AdventureWorks]
GO
DBCC freeproccache
GO
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff] @i = 50000 -- int
GO
----------------------------------------------------
USE [AdventureWorks]
GO
DBCC freeproccache
GO
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff] @i = 75124 -- int
GO


--第二次以75124为参数的运行要花3125毫秒。执行计划里都是用nested loops

--一般来说，如果看到一个执行计划的Executes值很大，又和一个nested loops
--相关，那就应该好好研究一下这个nested loops是否合适


------------------------------------------------------------------------------
--Filter运算的位置
--在一句查询里，常见的是几个表格做联接，同时又有一些where子句filter掉
--一些记录。那么是先filter掉记录，再做联接好呢，还是先做联接，再filter呢？
--两种方法都能计算出正确结果。但一般来讲，先filter掉一些记录，使得做联接
--的记录集小一点，会大大降低联接的消耗。所以filter先做，会提高查询效率
--在检查执行计划的时候，用户也要看看，是不是SQL及时做了filter

--现在有两个查询。对用户来讲，他们是一样的，只不过第二个查询把p.ProductID
--加了个1以后再做比较。返回的结果集也会一样
--查询一
USE [AdventureWorks]
GO
SET STATISTICS PROFILE ON
GO
SELECT COUNT(b.[ProductID])
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
INNER JOIN  [Production].[Product] p
ON b.[ProductID]=p.[ProductID]
WHERE p.[ProductID] BETWEEN 758 AND 800
GO
-----------------------------------------------
--查询二
USE [AdventureWorks]
GO
SET STATISTICS PROFILE ON
GO
SELECT COUNT(b.[ProductID])
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
INNER JOIN  [Production].[Product] p
ON b.[ProductID]=p.[ProductID]
WHERE (p.[ProductID]+1) BETWEEN 759 AND 801
GO


--但是两条语句的执行效率却有很大差别，第二条比第一条慢一倍还要多，
--CPU时间也高。这是为什麽呢？
--这里要分析这两句话的执行计划。先大致看一下，SQL在做返回结果集大小预估
--的时候，做得是不是准确。为了方便比较，我把EstimateRows和TotalSubtreeCost
--这两列拷贝到了前面

--两个查询的执行计划区别
--不管是第一句还是第二句，他们的EstimateRows和Rows的值都比较接近，所以SQL
--在cost预估上是准确的。现在可以通过分析TotalSubtreeCost，来找到语句最花
--资源的地方。
--这两句的主要消耗，都在一个hash match的联接上。但是两句的消耗又不一样。
--第一句的hash match，是一个31474的结果集和一个225990的结果集联接，cost是
--15.59，结果集大小是225990行。第二句的hash match，是一个31474的结果集和
--一个1213170的结果集联接，cost是19.09，大小是1213170行。他们的子步骤的
--cost是一样的，0.63和12.49。所以cost的差别就在hash match这个动作上，
--而原因就是联接双方的结果集，第二句的要比第一句大得多

--让我们仔细研究这两个执行计划，看看是什么导致了他们的差别

--第一句的做法，是dbo.SalesOrderDetail_test和dbo.SalesOrderHeader_test
--这两张表先做join，再和product表做join。有意思的是，虽然语句里只有一个where
--子句：WHERE p.[ProductID] BETWEEN 758 AND 800，但是执行计划里可以看到
--两个filter动作。一个在dbo.SalesOrderDetail_test上，一个在Product上。
--这是因为SQL发现这两张表将要通过ON b.[ProductID]=p.[ProductID]
--做联接，所以在Product上的条件，同样适合在dbo.SalesOrderDetail_test上
--这样，SQL先在dbo.SalesOrderDetail_test上做一个filter，结果集就小得多
--再做join，花费就能节省不少


--第二句的做法，也是SalesOrderHeader_test和SalesOrderDetail_test
--这两张表先做join，再和Product做join。但是filter动作只发生在Product上
--没有发生在SalesOrderDetail_test上。所以
--SalesOrderHeader_test和SalesOrderDetail_test做联接的时候，结果集会大一些
--产生这种现象的原因，是语句的where子句的写法：
--WHERE (p.[ProductID]+1) BETWEEN 759 AND 801
--SQL没有办法把这样的filter也适用在SalesOrderDetail_test这张表上

--从这个例子，可以看出filter动作的位置对语句性能的影响

---------------------------------------------------------------------------------------------
--确认问题产生的原因
--现在总结一下产生问题的原因有哪些：
--（1）预估返回结果集大小（EstimateRows）不准确，导致执行计划实际TotalSubtreeCost
--比预估的高很多
--统计信息不存在，或者没有及时更新，是产生这个问题的主要原因。
--应对办法：开启“自动创建统计信息”，“自动更新统计信息”
--如果这样还不能保证统计信息的精确性，可以定义一个任务，定期更新统计信息


--子句太过复杂，也可能使SQL猜不出一个准确的值，只好猜一个平均数。比如
--where子句里对字段做计算，代入函数等行为，都可能会影响SQL预估的准确性。
--如果发现这种情况，就要想办法简化语句，降低复杂度，提高效率
--当语句代入的变量值是一个参数，而SQL在编译的时候可能不知道这个参数的值，
--只好根据某些规则，“猜”一个预估值，这也会影响到预估的准确性。这个话题
--就是“参数嗅探”


--（2）语句重用了一个不合适的执行计划
--SQL的执行计划重用机理，是一次编译，多次重用。根据代入的第一个参数值进行编译
--以后不管参数值是多少，都重用根据前面的那个值编译出来的执行计划。这对一些
--数据分布比较均匀的表格是没有问题的，例如我们的dbo.SalesOrderHeader_test
--不管你代入什么值，返回的结果集数量都差不多

--但是，有些表格的数据分布不均匀，例如前面使用的dbo.SalesOrderDetail_test。
--他在某些值上，重复的记录很少，但是在另外一些值上，重复的记录又很多。这
--导致了对于不同的值，SQL必须使用不同的执行计划才能达到最优效率。如果
--重用的执行计划不合适，就会出现性能问题。

--关于这个话题：参数嗅探会说到


--（3）筛选子句写得不太合适，妨碍SQL选取更优的执行计划
--当语句要筛选掉一些记录时，索引会帮上忙。一般来讲，筛选动作做得越早，越能
--提高效率。SQL对筛选条件（search argument/SARG）的写法有一定建议
--筛选条件应该采用以下格式之一：
--列名 运算符 <常量或变量>
--<常量或变量>  运算符 列名

--例如：
name='annie'
amount>4000
6000<amount
department='hr'

--SARG运算符包括：=、>、<、>=、<=、in、between、like（在进行前缀匹配时，
--如like'john%'）
--SARG可以包括由AND联接的多个条件。

--SARG不但可以是匹配特定值的查询，
--例如：
name='annie'

--还可以是匹配一定范围的值的查询，例如：
amount>4000 and amount<6000
customerid IN('action','about')

--对于不是有SARG运算符的表达式，索引是没有用的，SQL对它们很难使用
--比较优化的做法。非SARG运算符包括NOT、<>、NOT EXISTS、NOT IN、NOT LIKE
--和内部函数，例如：
CONVERT(),UPPER()等

--当确认了执行计划出问题的原因，就可以对症下药，引导SQL总是选择一个
--好的执行计划，当然在有些情况下，基于现有语句和数据库结构，很难
--选择更好的执行计划。那就要通过修改数据库结构，或者修改语句设计
--来解决问题，提高效率