欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Sql Server之旅第十一站 简单说说sqlserver的执行计划

程序员文章站 2024-01-16 09:04:22
...

我们知道sql在底层的执行给我们上层人员开了一个窗口,那就是执行计划,有了执行计划之后,我们就清楚了那些烂sql是怎么执行的,这样 就可以方便的找到sql的缺陷和优化点。 一:执行计划生成过程 说到执行计划,首先要知道的是执行计划大概生成的过程,这样

  我们知道sql在底层的执行给我们上层人员开了一个窗口,那就是执行计划,有了执行计划之后,我们就清楚了那些烂sql是怎么执行的,这样

就可以方便的找到sql的缺陷和优化点。

一:执行计划生成过程

  说到执行计划,首先要知道的是执行计划大概生成的过程,这样就可以做到就心中有数了,下面我画下简图:

Sql Server之旅第十一站 简单说说sqlserver的执行计划

1. 分析过程

  这三个比较容易理解,首先我们要保证sql的语法不能错误,select和join的表是必须存在的,以及你是有执行这个sql的权限,对不对。。。

这样我们就走完了执行计划生命周期的第一个流程。

2. 编译过程

保证了上面sql这三点的话,引擎就必须硬着头皮看你这么一大坨烂sql,该删的删,该改的改,该转换的转换,比如说你的“子查询”会转化为

“表连接”等等。。。其实也挺难为引擎的,举个例子吧。

子查询生成的sql:

Sql Server之旅第十一站 简单说说sqlserver的执行计划

join生成的sql:

Sql Server之旅第十一站 简单说说sqlserver的执行计划

从上面的两个结果中,你可以看到,大家都是玩join的,如果你仔细看的话,会发现一个是“哈希匹配”,一个是“嵌套循环”,为什么不一样,这

当然是引擎根据很多情况综合评选出来的,比如说:磁盘IO,逻辑读,资源占用,硬件环境等等。。。这也是所谓的“计划选优”操作。

3.执行过程

  既然执行计划都选出来了,理所当然就要执行了,执行完后会把sql和执行计划放入缓存,这样下次有同样的sql过来的时候就可以直接从

Cache中提取了,不需要再次生成计划了,你也看到,生成执行计划还是比较消耗CPU时间的。

二:看看sql和执行的计划的缓存

  刚才也说了,sql和plan都已经放入缓存了,那我的好奇心比较强,我就想看看sql和plan到底在哪,并且长的是个什么丑样子,刚好

sqlserver还是比较能够满足我们G点的。

1. 为了方便查看缓存,我需要先将所有的缓存清空,比如下面的语句。

DBCC freeproccache
SELECT c.* FROM dbo.Category AS c
JOIN dbo.Product AS p
ON c.CategoryId=p.CategoryId
WHERE c.CategoryId=23794

2. 通过sys.dm_exec_cached_plans拿到sql和plan的指针(plan_handle),如下图

SELECT * FROM sys.dm_exec_cached_plans

Sql Server之旅第十一站 简单说说sqlserver的执行计划

从图中你看到了两个adhoc(即时查询),分别是我在第一步执行的join查询和我在第二步执行的这个select。

3. 现在我们已经拿到了2个adhoc的plan_handle,然后通过dm_exec_sql_text查看他们的sql分别是怎样?

Sql Server之旅第十一站 简单说说sqlserver的执行计划

4. 看完text缓存,接下来我们继续看看sql的plan缓存在哪?可以通过dm_exec_query_plan来查看。

Sql Server之旅第十一站 简单说说sqlserver的执行计划

上面的query_plan字段就是所谓的执行计划,以xml的形式保存在字段中。。。所以说解析这个xml还是很费时间的。。。

Sql Server之旅第十一站 简单说说sqlserver的执行计划Sql Server之旅第十一站 简单说说sqlserver的执行计划

  1 xml version="1.0"?>
  2 ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.1600.22">
  3     BatchSequence>
  4         Batch>
  5             Statements>
  6                 StmtSimple StatementText="SELECT c.* FROM dbo.Category AS c
  7  JOIN dbo.Product AS p
  8  ON c.CategoryId=p.CategoryId
  9  WHERE c.CategoryId=23794" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="1.33278" StatementEstRows="1.03803" StatementOptmLevel="FULL" QueryHash="0xB10B821B9B5E6396" QueryPlanHash="0x8C7B3B1660E28D16">
 10                     StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
 11                     QueryPlan CachedPlanSize="16" CompileTime="2" CompileCPU="2" CompileMemory="168">
 12                         MissingIndexes>
 13                             MissingIndexGroup Impact="99.4633">
 14                                 MissingIndex Database="[MYPETSHOP]" Schema="[dbo]" Table="[Product]">
 15                                     ColumnGroup Usage="EQUALITY">
 16                                         Column Name="[CategoryId]" ColumnId="2" />
 17                                     ColumnGroup>
 18                                 MissingIndex>
 19                             MissingIndexGroup>
 20                             MissingIndexGroup Impact="99.4636">
 21                                 MissingIndex Database="[MYPETSHOP]" Schema="[dbo]" Table="[Product]">
 22                                     ColumnGroup Usage="EQUALITY">
 23                                         Column Name="[CategoryId]" ColumnId="2" />
 24                                     ColumnGroup>
 25                                 MissingIndex>
 26                             MissingIndexGroup>
 27                         MissingIndexes>
 28                         RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1.03803" EstimateIO="0" EstimateCPU="4.33898e-006" AvgRowSize="97" EstimatedTotalSubtreeCost="1.33278" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
 29                             OutputList>
 30                                 ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="CategoryId" />
 31                                 ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="Name" />
 32                                 ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="Image" />
 33                             OutputList>
 34                             NestedLoops Optimized="0">
 35                                 RelOp NodeId="1" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="97" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1.00001e+006" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
 36                                     OutputList>
 37                                         ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="CategoryId" />
 38                                         ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="Name" />
 39                                         ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="Image" />
 40                                     OutputList>
 41                                     IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" NoExpandHint="0">
 42                                         DefinedValues>
 43                                             DefinedValue>
 44                                                 ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="CategoryId" />
 45                                             DefinedValue>
 46                                             DefinedValue>
 47                                                 ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="Name" />
 48                                             DefinedValue>
 49                                             DefinedValue>
 50                                                 ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="Image" />
 51                                             DefinedValue>
 52                                         DefinedValues>
 53                                         Object Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Index="[PK_Category]" Alias="[c]" IndexKind="Clustered" />
 54                                         SeekPredicates>
 55                                             SeekPredicateNew>
 56                                                 SeekKeys>
 57                                                     Prefix ScanType="EQ">
 58                                                         RangeColumns>
 59                                                             ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="CategoryId" />
 60                                                         RangeColumns>
 61                                                         RangeExpressions>
 62                                                             ScalarOperator ScalarString="(23794)">
 63                                                                 Const ConstValue="(23794)" />
 64                                                             ScalarOperator>
 65                                                         RangeExpressions>
 66                                                     Prefix>
 67                                                 SeekKeys>
 68                                             SeekPredicateNew>
 69                                         SeekPredicates>
 70                                     IndexScan>
 71                                 RelOp>
 72                                 RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1.03803" EstimateIO="1.18831" EstimateCPU="0.0983419" AvgRowSize="11" EstimatedTotalSubtreeCost="1.28665" TableCardinality="89259" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
 73                                     OutputList />
 74                                     IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
 75                                         DefinedValues />
 76                                         Object Database="[MYPETSHOP]" Schema="[dbo]" Table="[Product]" Index="[PK_Product]" Alias="[p]" IndexKind="Clustered" />
 77                                         Predicate>
 78                                             ScalarOperator ScalarString="[MYPETSHOP].[dbo].[Product].[CategoryId] as [p].[CategoryId]=(23794)">
 79                                                 Compare CompareOp="EQ">
 80                                                     ScalarOperator>
 81                                                         Identifier>
 82                                                             ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Product]" Alias="[p]" Column="CategoryId" />
 83                                                         Identifier>
 84                                                     ScalarOperator>
 85                                                     ScalarOperator>
 86                                                         Const ConstValue="(23794)" />
 87                                                     ScalarOperator>
 88                                                 Compare>
 89                                             ScalarOperator>
 90                                         Predicate>
 91                                     IndexScan>
 92                                 RelOp>
 93                             NestedLoops>
 94                         RelOp>
 95                     QueryPlan>
 96                 StmtSimple>
 97             Statements>
 98         Batch>
 99     BatchSequence>
100 ShowPlanXML>
View Code