MSSQL优化之——查看语句执行情况

MSSQL优化之——查看语句执行情况

在写SQL语句时,必须知道语句的执行情况才能对此作出优化。了解SQL语句的执行情况是每个写程序的人必不可少缺的能力。下面是对查询语句执行情况的方法介绍。

一、设置STATISTICS

STATISTICS选项有PROFILE,IO ,TIME。

SET STATISTICS PROFILE ON:显示每个查询执行后的结果集,代表查询执行的配置文件。

SET STATISTICS IO ON:报告与语句内引用的每个表的扫描数、逻辑读取数(在高速缓存中访问的页数)和物理读取数(访问磁盘的次数)有关的信息。

SET STATISTICS TIME ON:显示分析、编译和执行查询所需的时间(以毫秒为单位)。

 

使用方法:

SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO /*--你的SQL脚本开始*/
SELECT [TestCase] FROM [TestCaseSelect]
GO /*--你的SQL脚本结束*/
SET STATISTICS PROFILE OFF
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

注意:SET STATISTICS TIME ON 方法也可以用以下方法代替

declare @d datetime=getdate()

/*你的SQL脚本开始*/

SELECT [TestCase] FROM [TestCaseSelect] 

/*你的SQL脚本结束*/

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

测试语句:

select top 10 * from buyer_database where ID>

(select MAX(id) from (

select top 1000000 ID from buyer_database order by ID asc)

as tblTmp)order by ID asc

 

结果显示:

IO和 TIME 选项在输出窗口的消息窗口进行查看

MSSQL优化之——查看语句执行情况

PROFILE选项在输出窗口的结果窗口查看

MSSQL优化之——查看语句执行情况

分析方法:

SQLSERVER执行语句是分以下阶段:分析—>编译—>执行。根据表格的统计信息分析出比较合适的执行计划,然后编译语句,最后执行语句。

(1)SET STATISTICS TIME ON

1、CPU时间 :指在这一步,SQL所花的纯CPU时间是多少。也就是说,语句花了多少CPU资源。

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

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

4、执行时间: 语句真正运行的时间。由于语句不是第一次运行,SQLSERVER不需要把数据从磁盘读到内存里,这里语句的运行发生了很短的I/O等待。所以这里的CPU时间和占用时间差别就不大了,一个是438毫秒,而另一个是432毫秒。

总的来讲,这条语句花了3+432+438=873毫秒,其中CPU时间为438毫秒。语句的主要时间一半多是花在了I/O等待上。

(2)SET STATISTICS IO ON

这个开关能够输出语句做的物理读和逻辑读的数目。对分析语句的复杂度有很重要的作用。

1、表:表的名称。这里的表就是buy_database

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

3、逻辑读取:从数据缓存读取的页数。页数越多,说明查询要访问的数据量就越大,内存消耗量越大,查询也就越昂贵。可以检查是否应该调整索引,减少扫描的次数,缩小扫描范围。

4、物理读取:从磁盘读取的页数。

5、预读:为进行查询而预读入缓存的页数。

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

7、LOB逻辑读取:从数据缓存读取的text、ntext、image、大值类型(varchar(max)、nvarchar(max)、varbinary(max))页的数目。

8、LOB物理读取:从磁盘读取的text、ntext、image、大值类型页的数目

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

这里,逻辑读取26680页,物理读取和预读取都是0,说明数据已经缓存在内存里。

(3)SET STATISTICS PROFILE ON

这是三个设置中返回最复杂的一个,他返回语句的执行计划,以及语句运行在每一步的实际返回行数统计。

通过这个结果,不仅可以得到执行计划,理解语句执行过程,分析语句调优方向,也可以判断SQLSERVER是否选择了一个正确的执行计划。

注意:

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

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总和),下面介绍的cost说的都是这个字段值

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

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

通过这样的方法,用户可以了解到语句的执行计划、SQLSERVER预估的准确性、cost的分布

在对SQL语句进行优化比较时,通常我们都是对同一数据的同一结果集不同SQL语句进行比较,物理读取及预读次数一样。所以我们不需要对它们比较,而是查看逻辑读取数据。通常对于大数据来说,使用索引比不使用索引用的逻辑读取次数少,逻辑读越小查询速度越快。

二、查看执行计划

MSSQL优化之——查看语句执行情况

两种方法分别是:

1.选择SQL语句—>点击显示估计的执行计划—>输出窗口直接显示执行计划信息。

2.点击包括实际的执行计划—>执行SQL语句—>查看输出窗口中的执行计划信息。

分析方法:

1、分析查询计划

执行计划从右到左,从上到下的顺序阅读。每个步骤代表获得查询最终输出所执行的操作。执行计划有以下特征:

  • 如果查询由多个查询的批组成,每个查询的执行计划按照执行的顺序显示。批中的每个执行将有一个相对的估算开销,整个批的总开销为100%。
  • 执行计划中的每个图标代表一个操作符。有相对的估算开销,所有节点的总开销为100%。
  • 执行计划中的一个起始操作符通常表示一个数据库对象(表或索引)的数据检索机制。
  • 数据检索通常是一个表操作或索引操作。
  • 索引上的数据检索将是索引扫描或索引查找。
  • 索引上的数据检索的命名惯例是[表名].[索引名]。
  • 数据从右到左在两个操作之间流动,由一个连接箭头表示。
  • 操作符之间连接箭头的宽度是传输行数的图形表示。
  • 同一列的两个操作符之间的连接机制将是嵌套的循环连接,hash匹配连接或者合并连接。
  • 将光标放置在执行计划的一个节点上,显示一个具有一些细节的弹出窗口。
  • 在Properties(属性)窗口中有完整的一组关于操作符的细节。可以右键单击操作符并选择Properties。
  • 操作符细节在顶部显示物理和逻辑操作的类型。物理操作代表存储引擎实际使用的,而逻辑操作是优化器用于建立估算执行计划的结构。如果相同,只显示物理操作。还会显示其他信息:I/O、CPU等。

● 操作符细节弹出窗口的Argument(参数)部分在分析中特别有用,因为显示了优化器锁使用的过滤或连接条件。

2、识别执行计划中开销较大的步骤:

● 执行计划中每个节点显示整个计划中的相对开销,整个计划总开销为100%。关注最高相对开销的节点。

● 执行计划可能来自于一批语句,因此可能也需要查找开销最大的语句。

● 查看节点之间连接箭头的宽度。非常宽的连接箭头表示对应节点之间的传输大量的行。分析箭头左边的节点以理解需要这么多行的原因,还要检查箭头的属性。可能看到估计的行和实际的行不一样,这可能由过时的统计造成。

● 寻找hash连接操作。对于小的数据集,嵌套的循环连接通常是首选的连接技术。

● 寻找书签查找操作。对于大结果集的书签操作可能造成大量的逻辑读。

● 如果操作符上有一个叹号的警告,是需要立刻注意的领域。这些警告可能是由各种问题造成的,包括没有连接条件的连接或者丢失统计的索引和表。

● 需找执行排序操作的步骤,这表示数据没有以正确的排序进行检索。

三、清空内存中的缓存数据

在对同一结果集使用不SQL语句进行分析时,往往执行第一次就对结果集自动存入缓存当中,而不能作出客观的比较,因此我们需要对缓存数据进行清空。方法如下:

--清除buffer pool里的所有缓存元素
DBCC FREEPROCCACHE --清除buffer pool里的所有缓存元素
DBCC DROPCLEANBUFFERS --刷新针对Microsoft SQL Server 实例执行的分布式查询所使用的分布式查询连接缓存。
DBCC FREESESSIONCACHE --从所有缓存中释放所有未使用的缓存条目。SQL Server 数据库引擎会事先在后台清理未使--用的缓存条目,以使内存可用于当前条目。但是,可以使用此命令从所有缓存中或者从指定--的资源调控器池缓存中手动删除未使用的条目。
DBCC FREESYSTEMCACHE('All')
上一篇:将excel按照某一列拆分成多个文件(方案整理)


下一篇:三种方法实现Hadoop(MapReduce)全局排序(1)