SQL Server优化入门系列(五)—— SQL Server的执行计划

俊达2年前技术文章1401

定位到TOP SQL后,怎么优化呢?我们需要分析SQL的执行计划,制定相应的优化策略。这篇文章中,我们将介绍查看SQL Server执行计划的几种方法。本文测试案例中使用了AdventureWorks示例数据库。


获取执行计划

使用showplan

showplan并不会真正执行SQL。

使用SHOWPLAN_TEXT获取文本格式的执行计划

使用showplan_text可以获取到文本格式的执行计划。

USE AdventureWorks2012;
GO

SET SHOWPLAN_TEXT ON;
GO

SELECT soh.AccountNumber,
       sod.LineTotal,
       sod.OrderQty,
       sod.UnitPrice,
       p.Name
FROM Sales.SalesOrderHeader soh
    JOIN Sales.SalesOrderDetail sod
        ON soh.SalesOrderID = sod.SalesOrderID
    JOIN Production.Product p
        ON sod.ProductID = p.ProductID
WHERE sod.LineTotal > 20000;    
GO
|--Nested Loops(Inner Join, OUTER REFERENCES:([sod].[SalesOrderID], [Expr1006]) WITH UNORDERED PREFETCH)
|--Hash Match(Inner Join, HASH:([sod].[ProductID])=([p].[ProductID]))
|    |--Compute Scalar(DEFINE:([sod].[LineTotal]=[AdventureWorks2012].[Sales].[SalesOrderDetail].[LineTotal] as [sod].[LineTotal]))
|    |    |--Compute Scalar(DEFINE:([sod].[LineTotal]=isnull(CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2012].[Sales].[SalesOrderDetail].[UnitPrice] as [sod].[UnitPrice],0)*((1.0)-CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2012].[Sales].[SalesOrderDetail].[UnitPriceDiscount] as [sod].[UnitPriceDiscount],0))*CONVERT_IMPLICIT(numeric(5,0),[AdventureWorks2012].[Sales].[SalesOrderDetail].[OrderQty] as [sod].[OrderQty],0),(0.000000))))
|    |         |--Clustered Index Scan(OBJECT:([AdventureWorks2012].[Sales].[SalesOrderDetail].[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] AS [sod]), WHERE:(isnull(CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2012].[Sales].[SalesOrderDetail].[UnitPrice] as [sod].[UnitPrice],0)*((1.0)-CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2012].[Sales].[SalesOrderDetail].[UnitPriceDiscount] as [sod].[UnitPriceDiscount],0))*CONVERT_IMPLICIT(numeric(5,0),[AdventureWorks2012].[Sales].[SalesOrderDetail].[OrderQty] as [sod].[OrderQty],0),(0.000000))>(20000.000000)))
|    |--Index Scan(OBJECT:([AdventureWorks2012].[Production].[Product].[AK_Product_Name] AS [p]))
       |--Clustered Index Seek(OBJECT:([AdventureWorks2012].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID] AS [soh]), SEEK:([soh].[SalesOrderID]=[AdventureWorks2012].[Sales].[SalesOrderDetail].[SalesOrderID] as [sod].[SalesOrderID]) ORDERED FORWARD)


使用SHOWPLAN_XML获取图形化执行计划

USE AdventureWorks2012;
GO
SET SHOWPLAN_XML ON;
GO


SELECT soh.AccountNumber,
       sod.LineTotal,
       sod.OrderQty,
       sod.UnitPrice,
       p.Name
FROM Sales.SalesOrderHeader soh
    JOIN Sales.SalesOrderDetail sod
        ON soh.SalesOrderID = sod.SalesOrderID
    JOIN Production.Product p
        ON sod.ProductID = p.ProductID
WHERE sod.LineTotal > 20000;    
GO



plan-1.png



上述图形格式的执行计划,其内容和文本格式执行计划是一样的。不过这里SSMS增加了缺失索引的提示。


使用set statistics profile

USE AdventureWorks2012;
GO

set statistics profile on
go

SELECT soh.AccountNumber,
       sod.LineTotal,
       sod.OrderQty,
       sod.UnitPrice,
       p.Name
FROM Sales.SalesOrderHeader soh
    JOIN Sales.SalesOrderDetail sod
        ON soh.SalesOrderID = sod.SalesOrderID
    JOIN Production.Product p
        ON sod.ProductID = p.ProductID
WHERE sod.LineTotal > 20000;    
GO


plan-2.png


从DMV查询执行计划

SQL Server会缓存SQL执行计划,可以从相关DMV中获取的执行计划。


sys.dm_exec_query_plan

SELECT TOP 20
  SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
  ((CASE WHEN qs.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
      ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
  , qt.text AS [Parent Query]
  , DB_NAME(qt.dbid) AS DatabaseName
  , qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
  ((CASE WHEN qs.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1)
LIKE '%SalesOrderHeader%'





使用活动监视器获取执行计划

从SQL Server SSMS的活动监视器中,也可以获取TOP SQL的执行计划,

打开 “最近耗费大量资源的查询”中找到对应的SQL,右键,点击 “显示执行计划”


plan-6.png



查看SQL执行统计数据


如果我们对SQL进行优化后,想对比优化前后的效果,可以使用 set statistics io 和 set statistics time,对比优化前后的IO、CPU消耗。


USE AdventureWorks2012;
GO

set statistics io on
go

set statistics time on
go

SELECT soh.AccountNumber,
       sod.LineTotal,
       sod.OrderQty,
       sod.UnitPrice,
       p.Name
FROM Sales.SalesOrderHeader soh
    JOIN Sales.SalesOrderDetail sod
        ON soh.SalesOrderID = sod.SalesOrderID
    JOIN Production.Product p
        ON sod.ProductID = p.ProductID
WHERE sod.LineTotal > 20000;    
GO


执行后,可以看到详细的统计数据

(26 行受影响)
表 'SalesOrderHeader'。扫描计数 0,逻辑读取 87 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Product'。扫描计数 1,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'SalesOrderDetail'。扫描计数 1,逻辑读取 1246 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 62 毫秒,占用时间 = 57 毫秒。


相关文章

MongoDB 单机安装部署

MongoDB 单机安装部署

说明本篇文章介绍 MongoDB 二进制安装的步骤,整个过程还是比较简单。1. 下载安装包进入 MongoDB 官网,获取安装包的下载链接:https://www.mongodb.com/try/do...

centos6下时间修改

1、手动修改1)使用date命令修改的时间是系统时间临时生效的,重启系统后失效,需要将当前时间和硬件时间同步后才可以持久生效。date -s "2012-05-23 01:01:01&quo...

idea打包java可执行jar包

idea打包java可执行jar包

1,在项目上鼠标右键 --> Open Module SettingsArtifacts --> + --> JAR --> From modules with depende...

HDFS业务类型与相关架构优化措施

低延迟业务计算密集型业务,低延迟业务,这一类业务通常是对大量NameNode文件目录访问为主的(权限判断,文件存在性,文件信息查询等对NameNode元数据操作为主的)。因此需要针对NameNode的...

开源大数据集群部署(一)集群实施规划

1、集群规划1.1 本次集群规划信息本次实际生产业务体量存在巨大差异,但集群规划内容相同,因此建议实际生产环境按照按照一定比例扩展即可。主机操作系统要求软硬件信息参数配置8C16G操作系统版本Cent...

PG的pg_stat_statements插件

pg_stat_statements可追踪一个服务器所执行的所有 SQL 语句的执行统计信息,可以用于统计数据库的资源开销,分析TOP SQL。一、插件安装1、编译安装进入postgresql源码目录...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。