你的位置:首页 > 数据库

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


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

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

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

对于开发者来说,为了达到能够重用执行计划的目的,使用参数化的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.textfrom sys.dm_exec_cached_plans p cross apply sys.dm_exec_sql_text(p.plan_handle) twhere 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并没有生成一个自动参数化的执行计划。

--测试之前注意清空一下执行计划缓存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) twhere 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指令,提高执行效率的目的。