找回密码
当前位置：Mikel > 开发笔记 > Debug > 正文

SqlServer性能优化 即席查询（十三） - 孙丽媛 - 博客园

2020-07-10 分类：Debug / 数据库 阅读(616) 评论(0)

来源： SqlServer性能优化 即席查询（十三） – 孙丽媛 – 博客园

执行计划，查询类别：

1.即席查询     2.预定义查询

1
2
3
4
5
6
7
8
9
10
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Size
from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=
c.ProductCategoryKey where p.Size>'1'
<br>--查询执行计划是否被缓存
 select c.usecounts,c.size_in_bytes,c.objtype,t.text from sys.dm_exec_cached_plans as c
cross apply sys.dm_exec_SQL_text(c.plan_handle) as t
dbcc freeproccache--清空执行计划
<br>--没有join 的形式会生成简单参数化
select EnglishProductName,Color,Size from Product where size>'1'--简单参数化<br><br>select EnglishProductName,Color,Size from Product where size>'2'--简单参数化

 

1
2
3
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Size
from Product as p inner join ProductCategory as c on p.aProductSubcategoryKey=
c.ProductCategoryKey where p.Size>'2'

语句一样时即席查询才会重用执行计划。

 

优化：打开开关

1
2
exec sp_configure 'show advanced options',1
reconfigure with override

为ad hoc的查询优化：

1
2
exec sp_configure 'Optimize for ad hoc workloads',1
reconfigure with override

 

1
2
3
--使用参数化
alter database HRDB
set Parameterization forced

set Parameterization forced 强制参数化（like无法识别 ）

1
2
3
4
5
6
7
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Size
from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=
c.ProductCategoryKey where p.Size>'2'
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Size
from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=
c.ProductCategoryKey where p.Size like '2%'

 

 

预定义查询：

预定义查询–参数化执行计划：

     存储过程：

           1.创建时延时检查

           2.第一次执行时编译并生成执行计划

           3.减少网络传输量

           4.封装变化点

           5.增强安全性，隔离访问控制

创建存储过程：

1
2
3
4
5
create procedure p_querycp @size varchar(500)
as
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.size
from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey
where p.Size>@size

做跟踪(以前有对应得截图)：

执行存储过程：

1
2
3
4
5
6
7
8
9
10
create procedure p_querycp @size varchar(500)
as
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.size
from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey
where p.Size>@size
--清空执行计划
dbcc freeproccache
--执行
exec p_querycp '1'

执行重复的语句：

1
2
3
4
5
dbcc freeproccache
exec p_querycp @size='1'
exec p_querycp @size='2'

查看缓存计划：

1
2
select c.usecounts,c.size_in_bytes,c.objtype,t.text from sys.dm_exec_cached_plans as c
cross apply sys.dm_exec_SQL_text(c.plan_handle) as t

 

预定义查询—参数化执行计划：

SP_ExecuteSQL

避免了自己维护存储过程管理成本

可重用执行计划

Unicode字符串作为参数值与类型

大小写敏感

 

把存储过程定义成传递参数的：

1
2
3
4
5
6
7
8
9
declare @sqltext nvarchar(500)
set @sqltext=N'
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.size
from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey
where p.Size>@size
'
declare @params nvarchar(500)
set @params=N'@size varchar(500)'
 exec sp_executesql @sqltext,@params,@size='1'

 

1
把size 的大小换成  2

 

在.net中调用：（两种写法）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
public object getCp(string size)
{
    HRUser dbcontext = new HRUser();
    var cps = from p in dbcontext.Product
              join c in dbcontext.ProductCategory
              on p.ProductSubcategoryKey equals c.ProductCategoryKey
              where p.Size == size
              //返回匿名对象
              select new
              {
                  CName = c.EnglishProductCategoryName,
                  PName = p.EnglishProductName,
                  Color = p.Color,
                  Size = p.Size
              };
    return cps.ToList();
}<br>
public object  getcp(string size)
{
    HRUser dbcontext = new HRUser();
    var cps = dbcontext.Product.Join(dbcontext.ProductCategory, a => a.ProductSubcategoryKey, ar => ar.ProductCategoryKey, (a, ar) => new
    {
        CName = ar.EnglishProductCategoryName,
        PName = a.EnglishProductName,
        Color = a.Color,
        Size = a.Size
    }).Where(p => p.Size == size);
    return cps.ToList();
}

页面：

1
2
3
4
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
  <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="显示产品" />
  <asp:GridView ID="GridView1" runat="server">
  </asp:GridView>

点击后的事件：

1
2
3
4
5
6
7
protected void Button2_Click(object sender, EventArgs e)
      {
          Product p = new Product();
          var cps = p.getCp(TextBox1.Text.Trim());
          GridView1.DataSource = cps;
          GridView1.DataBind();
      }

 

 

1
2
3
4
5
6
7
--动态构建语句（执行带参数的方法）
 declare @size varchar(500)
set @size='2'
execute('select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.size
from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey
where p.Size>'+''''+@size+'''')
   

 

1
2
3
4
5
dbcc freeproccache
--执行计划 缓存
select c.usecounts,c.size_in_bytes,c.objtype,t.text from sys.dm_exec_cached_plans as c
cross apply sys.dm_exec_sql_text(c.plan_handle) as t

形成两个缓存计划：

 

创建存储过程：

1
2
3
create  procedure p_querye @vacationhours  int
as
select e.LoginID,e.JobTitle from EmployeeOp as e where VacationHours>@vacationhours
1
2
3
exec p_querye 2--实际执行计划 表扫描
exec p_querye 99--实际执行计划 表扫描 应用用索引更好

 

1
2
--重新编译的执行计划
exec p_querycp 99 with recompile

 

手工的指定执行几乎：

1
2
3
4
5
6
7
8
9
10
--手工的指定执行计划
exec  sp_create_plan_guide @name='执行任务计划指南之EmployeeOp Vacation',
--转成Unicode编码格式
@stmt=N'select e.LoginID,e.JobTitle from EmployeeOp as e where VacationHours>@vacationhours',
@type=N'Object',
--执行计划的名字
@module_or_batch ='p_querye',
@params =null,
--提示
@hints =N'OPTION(OPTIMIZE FOR(@vacationhours=''99''))'

 

清除执行计划：执行（会参考上面指定的执行计划）

1
exec p_querye 2

存储过程重编译：

 

临时结果集：

定义跟踪的模板：

一：使用物理表进行临时结果集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--1.做一张物理表
create procedure p_physicaltb
as
CREATE TABLE PhysicalTB(
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int]  NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal] [numeric](38, 6) NOT NULL,
    [rowguid] [uniqueidentifier] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)
insert into PhysicalTB select * from OrderDetail
select * from PhysicalTB
set statistics time on
exec p_physicaltb   --cpu:391  total:1762
set statistics time off

跟踪的情况：

 

删除之后再次创建执行。

物理表每次执行都会有重编译的过程（不建议使用物理表来存储临时结果集）

 

第二种方式：

临时表存储临时结果集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
create procedure p_temptb
as
CREATE TABLE #PhysicalTB(
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int]  NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal] [numeric](38, 6) NOT NULL,
    [rowguid] [uniqueidentifier] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)
insert into #PhysicalTB select * from OrderDetail
select * from #PhysicalTB
drop table #PhysicalTB
set statistics time on
exec p_temptb  --cpu:110  total:1494
set statistics time off
sp_helpdb 'tempdb'--16064,768

第一次执行时重新编译，第二次就不会重新编译了。

用到了tempdb临时表：

 

第三种方式：表变量存储临时结果集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
--表变量存储临时结果集
create procedure p_tabletb
as
--申明表变量
declare @PhysicalTB table(
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int]  NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal] [numeric](38, 6) NOT NULL,
    [rowguid] [uniqueidentifier] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)
insert into @PhysicalTB select * from OrderDetail
select * from @PhysicalTB
set statistics time on
exec p_tabletb  --cpu:110  total:1494
set statistics time off
sp_helpdb 'tempdb'--17064,768

执行时不会重新编译

 

第四种方式：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--CTE(通用表表达式)存储临时结果集  完全放在内存中 不会操作任何数据库中的东西
create procedure p_ctetb
as
begin
--会自动推断数据类型
;with PhysicalTB(
    [SalesOrderID],
    [SalesOrderDetailID],
    [CarrierTrackingNumber],
    [OrderQty] ,
    [ProductID],
    [SpecialOfferID],
    [UnitPrice] ,
    [UnitPriceDiscount] ,
    [LineTotal] ,
    [rowguid] ,
    [ModifiedDate]
) as (select * from OrderDetail)
--访问通用表表达式
select * from PhysicalTB
end

跟踪的结果：

没有重新编译的过程，纯粹操作内存。tempdb数据库不会有任何的变化。

1
2
3
4
5
set statistics time on
exec p_ctetb  --cpu:100  total:1300
set statistics time off
sp_helpdb 'tempdb'--17064,768

 

高版本的通用表达式可以进行多次的使用：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
create procedure p_ctetb1
as
begin
;with PhysicalTB(
    [SalesOrderID],
    [SalesOrderDetailID],
    [CarrierTrackingNumber],
    [OrderQty] ,
    [ProductID],
    [SpecialOfferID],
    [UnitPrice] ,
    [UnitPriceDiscount] ,
    [LineTotal] ,
    [rowguid] ,
    [ModifiedDate]
) as (select * from OrderDetail)
select * from PhysicalTB
select * from PhysicalTB
end
exec p_ctetb1

08之前的数据库，只要把表达式在创建一次就可以了

 

临时数据集的优化处理：

 

 

优化查询：编译指南。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
--清空执行计划
dbcc freeproccache
select * from EmployeeOp where VacationHours>1 option(use plan N'
<ShowPlanXML xmlns="http://schemas.microsoft.com/SQLServer/2004/07/showplan" Version="1.2" Build="12.0.2000.8">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="select * from EmployeeOp where VacationHours>99" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00657038" StatementEstRows="1" StatementOptmLevel="FULL" QueryHash="0x7E06C77E90EB9FBB" QueryPlanHash="0x64478FC6152D2A83" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="120" ParameterizedText="(@1 tinyint)SELECT * FROM [EmployeeOp] WHERE [VacationHours]>@1">
          <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
          <QueryPlan CachedPlanSize="40" CompileTime="6" CompileCPU="6" CompileMemory="232">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="206857" EstimatedPagesCached="51714" EstimatedAvailableDegreeOfParallelism="2" />
            <RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="830" EstimatedTotalSubtreeCost="0.00657038" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
              <OutputList>
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BusinessEntityID" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="NationalIDNumber" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="LoginID" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationNode" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationLevel" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="JobTitle" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BirthDate" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="MaritalStatus" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="Gender" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="HireDate" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SalariedFlag" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="VacationHours" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SickLeaveHours" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="CurrentFlag" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="rowguid" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="ModifiedDate" />
              </OutputList>
              <NestedLoops Optimized="0">
                <OuterReferences>
                  <ColumnReference Column="Bmk1000" />
                </OuterReferences>
                <RelOp NodeId="1" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="290" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
                  <OutputList>
                    <ColumnReference Column="Bmk1000" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="VacationHours" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SickLeaveHours" />
                  </OutputList>
                  <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Bmk1000" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="VacationHours" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SickLeaveHours" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Index="[nc_Employee_vacationsickleave]" IndexKind="NonClustered" Storage="RowStore" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <StartRange ScanType="GT">
                            <RangeColumns>
                              <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="VacationHours" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="(99)">
                                <Const ConstValue="(99)" />
                              </ScalarOperator>
                            </RangeExpressions>
                          </StartRange>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
                <RelOp NodeId="3" PhysicalOp="RID Lookup" LogicalOp="RID Lookup" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="826" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="290" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
                  <OutputList>
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BusinessEntityID" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="NationalIDNumber" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="LoginID" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationNode" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationLevel" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="JobTitle" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BirthDate" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="MaritalStatus" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="Gender" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="HireDate" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SalariedFlag" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="CurrentFlag" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="rowguid" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="ModifiedDate" />
                  </OutputList>
                  <IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BusinessEntityID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="NationalIDNumber" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="LoginID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationNode" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationLevel" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="JobTitle" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BirthDate" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="MaritalStatus" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="Gender" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="HireDate" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SalariedFlag" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="CurrentFlag" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="rowguid" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="ModifiedDate" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" TableReferenceId="-1" IndexKind="Heap" Storage="RowStore" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <Prefix ScanType="EQ">
                            <RangeColumns>
                              <ColumnReference Column="Bmk1000" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="[Bmk1000]">
                                <Identifier>
                                  <ColumnReference Column="Bmk1000" />
                                </Identifier>
                              </ScalarOperator>
                            </RangeExpressions>
                          </Prefix>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
              </NestedLoops>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@1" ParameterCompiledValue="(99)" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
')  --表扫描
select * from sys.dm_exec_cached_plans
select * from sys.dm_exec_sql_text
select * from  sys.dm_exec_query_plan
select * from EmployeeOp where VacationHours>99  -- 索引
select c.plan_handle,p.text from sys.dm_exec_cached_plans as c cross apply  sys.dm_exec_sql_text(c.plan_handle) as p
select * from  sys.dm_exec_query_plan(0x06000A00CD253E14207CA0290200000001000000000000000000000000000000000000000000000000000000)

 

赞(0) 打赏
分享到： 更多 (0)
标签：

相关推荐

热门标签

C# (813)ASP.NET MVC (619)Android (587)ASP.NET (447)数据库 (299)JavaScript (285)JQuery (221)PHP (210)SQLServer (197)程序开发 (156)架构设计 (155)源价值 (155)SQL (128)Java (111)EasyUi (85)教程 (76)网站 (67)Debug (63)互联网 (63)MySQL (60)Flash (56)搜索优化 (54)创意 (53)SEO (51)ASP.NET MVC3 (51)电子商务 (50)建站经验 (49)实例代码 (49)源码下载 (47)原创 (45)

分类

链接表

<figure class="wp-block-image size-full"><a href="https://act.walk-live.com/acts/invite/registration?inviteid=hkXrmnvL" target="_blank"><img src="https://www.mikel.cn/wp-content/uploads/2024/11/wangxinyun.jpg"></a></figure>

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏