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

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)

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增加了缺失索引的提示。

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

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

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%'

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