博客 SQL Server优化入门系列—— SQL Server的执行计划

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

   数栈君   发表于 2024-02-19 11:16  101  0

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

获取执行计划

使用showplan

showplan并不会真正执行SQL。

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

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

SQL
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
SQL
|--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获取图形化执行计划

SQL
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

http://dtstack-static.oss-cn-hangzhou.aliyuncs.com/2021bbs/files_user1/article/ac006d72417bad79e540d3f5b7da45d0..png

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

使用set statistics profile

SQL
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

http://dtstack-static.oss-cn-hangzhou.aliyuncs.com/2021bbs/files_user1/article/3fb3c1606013dd72dce429c546dcdd97..png

从DMV查询执行计划

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

sys.dm_exec_query_plan

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

http://dtstack-static.oss-cn-hangzhou.aliyuncs.com/2021bbs/files_user1/article/6254cfe9d0d049f8ff8c30f90c6dd696..png

http://dtstack-static.oss-cn-hangzhou.aliyuncs.com/2021bbs/files_user1/article/2262d112b96a6db60e854a98e3c10324..png

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

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

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

http://dtstack-static.oss-cn-hangzhou.aliyuncs.com/2021bbs/files_user1/article/aed093710b298de76bab1eb33f7601a1..png

查看SQL执行统计数据

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


SQL
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


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

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




《数栈V6.0产品白皮书》下载地址:https://fs80.cn/cw0iw1

想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友,浏览袋鼠云官网:https://www.dtstack.com/?src=bbs

同时,欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」,交流最新开源技术信息,群号码:30537511,项目地址:https://github.com/DTStack  
0条评论
社区公告
  • 大数据领域最专业的产品&技术交流社区,专注于探讨与分享大数据领域有趣又火热的信息,专业又专注的数据人园地

最新活动更多
微信扫码获取数字化转型资料
钉钉扫码加入技术交流群