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

最新推荐文章于 2024-08-26 17:00:45 发布

imJaron

最新推荐文章于 2024-08-26 17:00:45 发布

阅读量922

点赞数

分类专栏：数据库

数据库专栏收录该内容

35 篇文章 1 订阅

订阅专栏

我们知道，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并没有生成一个自动参数化的执行计划。

 
        --测试之前注意清空一下执行计划缓存 
       
        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%'