SQL调优

SET STATISTICS XX ON

收集语句运行的统计信息

SET STATISTICS TIME ON

输出语句做的物理读和逻辑读的数目

DBCC DROPCLEANBUFFERS
--清除buffer pool里的所有缓存数据
DBCC FREEPROCCACHE
GO
--清除buffer pool里的所有缓存的执行计划
SET STATISTICS TIME ON;

SELECT
	a.Name,
	b.new_level 
FROM
	Account a
	LEFT JOIN new_accountclassification b ON a.AccountId = b.new_account_id 
WHERE
	a.AccountId = '7FEC00BA-A4BA-EA11-A13F-000C29B5B96B'

SET STATISTICS TIME OFF

执行上面代码获取以下信息：

DBCC DROPCLEANBUFFERS
--清除buffer pool里的所有缓存数据
DBCC FREEPROCCACHE
> SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
> DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
> 
 SQL Server 执行时间:
   CPU 时间 = 328 毫秒，占用时间 = 332 毫秒。
> DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
> 
 SQL Server 执行时间:
   CPU 时间 = 15 毫秒，占用时间 = 10 毫秒。
> OK
> 时间: 0.35s


--清除buffer pool里的所有缓存的执行计划
SET STATISTICS TIME ON;

SELECT
	a.Name,
	b.new_level 
FROM
	Account a
	LEFT JOIN new_accountclassification b ON a.AccountId = b.new_account_id 
WHERE
	a.AccountId = '7FEC00BA-A4BA-EA11-A13F-000C29B5B96B'

SET STATISTICS TIME OFF
> SQL Server 分析和编译时间: 
   CPU 时间 = 203 毫秒，占用时间 = 278 毫秒。
> 
 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
> 
 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 5 毫秒。
> OK
> 时间: 0.295s

CPU时间

这个值的含义指的是在这一步，SQLSERVER所花的纯CPU时间是多少。也就是说，语句花了多少CPU资源。

占用时间

此值指这一步一共用了多少时间。也就是说，这是语句运行的时间长短，有些动作会发生I/O操作，产生了I/O等待，或者是遇到阻塞、产生了阻塞等待。总之时间用掉了，但是没有用CPU资源。所以占用时间比CPU时间长是很正常的 ，但是CPU时间是语句在所有CPU上的时间总和。如果语句使用了多颗CPU，而其他等待几乎没有，那么CPU时间大于占用时间也是正常的。

分析和编译时间

这一步，就是语句的编译时间。由于语句运行之前清空了所有执行计划，SQLSERVER必须要对他编译。这里的编译时间就不为0了。由于编译主要是CPU的运算，所以一般CPU时间和占用时间是差不多的。如果这里相差比较大，就有必要看看SQLSERVER在系统资源上有没有瓶颈了。这里他们是一个15毫秒，一个是104毫秒。

SQLSERVER执行时间

语句真正运行的时间。由于语句是第一次运行，SQLSERVER需要把数据从磁盘读到内存里，这里语句的运行发生了5毫秒的I/O等待。

关键字go

go 向 SQL Server 实用工具发出一批 Transact-SQL 语句结束的信号。
例如当某一语句要求必须是查询批次中的第一个语句，则可以在前面加上go来终结上一个批处理，使其成为下一个批处理的开头。
CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, CREATE VIEW

SET STATISTICS IO ON

SET STATISTICS IO ON;

SELECT
	a.Name,
	b.new_level 
FROM
	Account a
	LEFT JOIN new_accountclassification b ON a.AccountId = b.new_account_id 
WHERE
	a.AccountId = '7FEC00BA-A4BA-EA11-A13F-000C29B5B96B'
SET STATISTICS IO OFF;

执行上面代码获取以下信息：

> 表 'new_accountclassificationBase'。扫描计数 1，逻辑读取 6 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
> 表 'AccountBase'。扫描计数 0，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

表

表的名称。

扫描计数

执行的扫描次数。按照执行计划，表格被扫描了几次。一般来讲大表扫描的次数越多越不好。唯一的例外是如果执行计划选择了并发运行，由多个thread线程同时做一个表的读取，每个thread读其中的一部分，但是这里会显示所有thread的数目。也就是有几个thread在并发做，就会有几个扫描。这时数目大一点没问题的。

逻辑读取

从数据缓存读取的页数。页数越多，说明查询要访问的数据量就越大，内存消耗量越大，查询也就越昂贵。可以检查是否应该调整索引，减少扫描的次数，缩小扫描范围。
顺便说一下这个逻辑读取的统计原理：为什麽显示出来的结果的单位不是Page，也不是K或KB。SQLSERVER里在做读和写的时候，会运行到某一段特定的代码。每调用一次这个代码，Reads/Write就会加1。所以这个值比较大那语句一定做了比较多的I/O，但是不能通过这个值计算出I/O的绝对数量，这个值反映的是逻辑读写量不是物理读写量。

物理读取

从磁盘读取的页数

预读

为进行查询而预读入缓存的页数

物理读取+预读

就是SQLSERVER为了完成这句查询而从磁盘上读取的页数。如果不为0，说明数据没有缓存在内存里。运行速度一定会受到影响

LOB逻辑读取

从数据缓存读取的text、ntext、image、大值类型（varchar(max)、nvarchar(max)、varbinary(max)）页的数目

LOB物理读取

从磁盘读取的text、ntext、image、大值类型页的数目

LOB预读

为进行查询而放入缓存的text、ntext、image、大值类型页的数目

SET STATISTICS PROFILE ON

返回语句的执行计划，以及语句运行在每一步的实际返回行数统计

SET STATISTICS PROFILE ON;

SELECT
	a.Name,
	b.new_level 
FROM
	Account a
	LEFT JOIN new_accountclassification b ON a.AccountId = b.new_account_id 
WHERE
	a.AccountId = '7FEC00BA-A4BA-EA11-A13F-000C29B5B96B'
	
SET STATISTICS PROFILE OFF

会返回另外的一个结果如下图：

注意：这里是从最下面开始向上看的，也就是说从最下面开始一直执行直到得到结果集所以（行1）里的rows字段显示的值就是这个查询返回的结果集。而且有多少行表明SQLSERVER执行了多少个步骤,这里有6行，表明SQLSRVER执行了6个步骤！！

Rows

执行计划的每一步返回的实际行数

Executes

执行计划的每一步被运行了多少次

StmtText

执行计划的具体内容。执行计划以一棵树的形式显示。每一行都是运行的一步，都会有结果集返回，也都会有自己的cost

EstimateRows

SQLSERVER根据表格上的统计信息，预估的每一步的返回行数。在分析执行计划时，我们会经常将Rows和EstimateRows这两列做对比，先确认SQLSERVER预估得是否正确，以判断统计信息是否有更新

EstimateIO

SQLSERVER根据EstimateRows和统计信息里记录的字段长度，预估的每一步会产生的I/O cost

EstimateCPU

SQLSERVR根据EstimateRows和统计信息里记录的字段长度，以及要做的事情的复杂度，预估每一步会产生的CPU cost

TotalSubtreeCost

SQLSERVER根据EstimateIO和EstimateCPU通过某种计算公式，计算出每一步执行计划子树的cost(包括这一步自己的cost和他的所有下层步骤的cost总和)

Warnings

SQLSERVER在运行每一步时遇到的警告，例如，某一步没有统计信息支持cost预估等。

Parallel

执行计划的这一步是不是使用了并行的执行计划

具体步骤分析

执行计划的具体内容如下图：