定位到TOP SQL后,怎么优化呢?我们需要分析SQL的执行计划,制定相应的优化策略。这篇文章中,我们将介绍查看SQL Server执行计划的几种方法。本文测试案例中使用了AdventureWorks示例数据库。
showplan并不会真正执行SQL。
使用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)
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%'
从SQL Server SSMS的活动监视器中,也可以获取TOP 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 毫秒。