SQL Server优化入门系列（五）—— SQL Server的执行计划

俊达3年前 (2022-10-17)技术文章1795

定位到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

上述图形格式的执行计划，其内容和文本格式执行计划是一样的。不过这里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

从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，右键，点击 “显示执行计划”

查看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 毫秒。

返回列表

上一篇：远程DEBUG HADOOP源码方法

下一篇：大数据监控系列（一）——Prometheus+Grafana监控概述