浅析SqlServer简单参数化模式下对sql语句自动参数化处理以及执行计划重用

我们知道,SqlServer执行sql语句的时候,有一步是对sql进行编译以生成执行计划,

 

在生成执行计划之前会去缓存中查找执行计划

 

如果执行计划缓存中有对应的执行计划缓存,那么SqlServer就会重用这个执行计划缓存,避免编译,从而提高效率,

 

对于开发者来说,为了达到能够重用执行计划的目的,使用参数化的sql是一个必要的条件。

 

除了参数化的sql,对于即席查询或者是动态生成的查询语句,也就是非参数化的sql语句,SqlServer本身也在对一些sql进行自动优化处理。

 

在SqlServer层面,分为简单参数化和强制参数化两种方式,SqlServer数据库中对sql的解析方式,默认是简单参数化,当然也可以设置为强制参数化。

 

在简单参数化模式下,SqlServer对查询有一些专门的优化,就是sql查询条件一些变量的值,不会影响到查询的执行计划,

 

那么SqlServer会自动地进行参数化处理,后续如果有类似的查询,可以使用自动参数化的sql生成的执行计划,避免重编译,从而提高sql的执行效率以及减少服务器资源的消耗。

 

参数化的设置是一个数据库级别的选项,如下是通过图形界面看到的参数化默认设置方式,默认是“简单”模式,也可以设置为“强制”模式,

也可以通过脚本的方式来设置

 

--查看数据库的参数化方式

select name,is_parameterization_forced from sys.databases where name='dbtest'

--默认是简单模式

alter database dbtest set parameterization simple

--可以设置为强制模式

alter database dbtest set parameterization forced

 

首选来看简单参数化模式下的处理方式

 

这种情况下,如果一个查询的常量值不影响执行计划的方式,那么SqlServer会对这个查询生成一个参数化的sql,随后的查询中的即便常量的值发生了变化,也会重用上面进行参数化后生成的执行计划,

 

create table t_1

(

    id int,

    name varchar(50)

)

 

insert into t_1 values (1,'A')

insert into t_1 values (2,'B')

insert into t_1 values (3,'C')

insert into t_1 values (4,'D')

insert into t_1 values (5,'E')

 

--测试之前注意清空一下执行计划缓存

dbcc freeproccache

 

首选执行第一个查询

 

select * from t_1 where id=1

 

执行完成后,我们查询执行计划中的信息

select usecounts,size_in_bytes,objtype,t.text

from sys.dm_exec_cached_plans p cross apply sys.dm_exec_sql_text(p.plan_handle) t

where p.cacheobjtype='Compiled Plan'

and t.text like '%t_1%'

and t.text not like '%dm_exec_cached_plans%'

 

此时会观察到,

 

select * from t1 where id=1

 

这一句sql本身也被缓存了,同时自动生成了一个objtype为prepared的执行计划,

 

此时再执行另外一个sql

 

select * from t1 where id=2

注意观察参数objtype为prepared的执行计划的usecounts,由第一次的1变为了2

 

说明第二句执行的sql重用了第一句sql自动参数化生成的执行计划,第一次自动参数化生成的执行计划得到了重用。

 

或许你会有一点疑问,第二个执行的sql也就是select * from t_1 where id=2

 

也生成了执行计划,为什么说这一句sql执行的时候重用了第一句sql生成的执行计划的缓存的呢?

 

这里会看到,执行的原始的sql语句生成的执行计划的类型都是Adhoc的,这种执行计划称之为壳查询(Shell Query),

 

(这里解释一下,Adhoc查询很多人称之为即席查询,但是这里类型是Adhoc查询,却不是即席查询,

 

即席查询时包含完整的执行计划的,这里的壳查询时不包含执行计划的,只能说所谓的即席查询是Adhoc类型的查询的一种

 

上面查询执行计划的sql再加一个DMV,如下,就会发现,生成的Adho执行计划缓存中q.query_plan为空,并不包含完整的执行计划

 

select usecounts,size_in_bytes,objtype,t.text ,q.query_plan

from sys.dm_exec_cached_plans p 

cross apply sys.dm_exec_sql_text(p.plan_handle) t

cross apply sys.dm_exec_query_plan(p.plan_handle) q

where p.cacheobjtype='Compiled Plan' 

and t.text like '%t_1%' 

and t.text not like '%dm_exec_cached_plans%')

 

这个壳查询也会缓存,但是其未包含完整的执行计划,只包含sql的文本以及执行真正执行计划的指针,

 

缓存壳查询的目的在于当执行与壳查询完全相同的sql时,查询语句可以快速地找到其对应的真正的执行计划

 

缓存壳查询的目的在于:

 

用户发送给SqlServer的是一个文本,因为可能每次查询条件不同,文本本身也是不同的,SqlServer缓存的是一个参数化的执行计划,如果用户发送过来的文本匹配上了壳查询,

 

壳查询又存储了一个指向真正执行计划的指针,那么就可以将用户发送过来的sql快速连接到其执行计划,是作为连接用户发送过来的sql和自动参数化生成的执行计划的一道桥梁,

 

另外,缓存壳查询因为没有包含完整的执行计划,所以其占用的内存也并没有一个完整的执行计划占用的内存大,

 

从size_in_bytes字段也可以看到,壳查询的缓存的空间是没有参数化的sql的占用空间大的。

 

在执行select * from t_1 where id=1之后,生成了参数化的执行计划,

 

随后如果多次执行select * from t_1 where id=2,会发现参数化后的执行计划usecounts并没有增加,一直为2,而壳查询的计划的usecounts在增加,

 

上面说了,“缓存壳查询的目的在于当执行与壳查询完全相同的sql时,查询语句可以快速地找到其对应的真正的执行计划”,

 

此时虽然真正使用的是参数化执行计划,但是DMV中只是将壳查询的usecounts计数器加1

 

那么回头再想一个问题,对于简单参数模式下,SqlServer什么情况下回对sql语句自动参数化?

 

简单模式下,SqlServer并不是所有的sql都进行参数化处理,只有对那些有且只有一种执行计划的sql语句,才自动参数化处理,

 

比如上面的查询,因为t_1表上的id列上没有任何索引,不管对于任何一个值,都只能是全表扫描的方式去执行这个查询,此时SqlServer会做到自动参数化,生成一个可重用的执行计划。

 

如果id列上有一个非唯一的索引,那么select * from t_1 where id=***这个查询就有可能存在不同的执行计划,

 

比如是索引查询或者是全表扫描,这个要依据列上的统计信息了(这个话题也比较大,就不展开说了),

 

也就是说,在不同的查询条件下,有可能生成不同的执行计划,那么SqlServer就不会为其生成参数化的sql。

 

比如在id列上见一个非唯一的索引,那么再次观察执行计划缓存,就发现SqlServer并没有生成一个自动参数化的执行计划。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--测试之前注意清空一下执行计划缓存
dbcc freeproccache
 
--在id列上创建一个非唯一的索引
create index idx_id on t_1(id)
 
--此时执行这个查询
select * from t_1 where id= 1
 
--查看执行计划的缓存
select usecounts,size_in_bytes,objtype,t.text
from sys.dm_exec_cached_plans p cross apply sys.dm_exec_sql_text(p.plan_handle) t
where p.cacheobjtype= 'Compiled Plan'
and t.text like '%t_1%'
and t.text not like '%dm_exec_cached_plans%'

 

 

此时会发现,SqlServer并没有自动地生成一个参数化的sql,就是因为对于select * from t_1 where id=1这个查询

 

可能索引查找是最优化的,但是如果把id的值换做另外其他的未知的值,可能表扫描是最后化的,也就是说,在这种情况下,当前查询的执行计划不具备通用性

 

所以此时SqlServer并不会生成自动参数化的sql执行计划。

 

总结:SqlServer在执行sql的的生成执行计划的时候,除了人为因素(比如参数化的sql)的影响,SqlServer本身也会对一些没有参数化的sql做自动参数化,以最优化的方式去运行sql指令,提高执行效率的目的。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值