SQL Server中语句的自动参数化

原文: SQL Server中语句的自动参数化


   
  
      use master   
         go   
           
         if exists( select * from sys.databases where name = ‘test‘)   
         drop database test   
         go   
           
         --创建数据库   
         create database test   
           
         if exists( select * from sys.tables where name = ‘t‘)   
         drop table t   
         go   
           
           
         --1.创建表t   
         create table t(i int);   
           
           
         --2.添加100000条记录,消耗56秒   
         declare @i int;   
         declare @ sql varchar( 1000);   
           
         set @i = 1   
         set @ sql = ‘‘;   
           
         while @i <= 100000   
         begin   
         set @ sql = ‘insert into t values(‘   cast(@i as varchar)   ‘)‘   
         begin tran   
           
         exec(@ sql)   
         commit tran   
           
         set @i = @i   1   
         end   
           
           
         --3.1查询的统计信息   
         --但发现没有返回记录   
         select st.text,   
         SUBSTRING(st.text, (qs.statement_start_offset/ 2)  1,   
         (( CASE qs.statement_end_offset   
         WHEN -1 THEN DATALENGTH(st.text)   
         ELSE qs.statement_end_offset   
         END - qs.statement_start_offset)/ 2)   1) AS statement_text,   
         qs.*   
         from sys.dm_exec_query_stats qs   
         cross apply sys.dm_exec_sql_text(qs.sql_handle) st   
         where cast(st.text as varchar) like ‘%insert into t values%‘   
           
           
           
         --3.2查询的统计信息   
         --有返回,说明SQL Server在执行动态生成的语句时,已经参数化了   
         --execution_count字段的值为100000   
         select st.text,   
         SUBSTRING(st.text, (qs.statement_start_offset/ 2)  1,   
         (( CASE qs.statement_end_offset   
         WHEN -1 THEN DATALENGTH(st.text)   
         ELSE qs.statement_end_offset   
         END - qs.statement_start_offset)/ 2)   1) AS statement_text,   
         qs.*   
         from sys.dm_exec_query_stats qs   
         cross apply sys.dm_exec_sql_text(qs.sql_handle) st   
         where cast(st.text as varchar) like ‘%insert into%‘   
           
           
         --3.3 查看数据库是否强制参数化   
         --并没有强制参数化,说明上面是系统自动对语句进行参数化了   
         select name,   
         is_parameterization_forced --返回0,说明并没有强制参数   
         from sys.databases   
         where name = ‘test‘   
           
           
         --3.4 再次执行后，再次执行3.2后发现，execution_count为100001   
         insert into t values( 100001)   
           
         --3.5 这次在语句中加了空格,execution_count为100002   
         insert into t values( 100002 )   
           
           
         --3.5 这次在语句中加了架构   
         --返回2条记录，一条为100002，另一个为1   
         --虽然2条数据的sql_handle、plan_handle都不相同，但是query_hash、query_plan_hash相同   
         insert into dbo.t values( 100003 )   
           
           
         --3.6 显示缓存的执行计划   
         --返回2条数据，一条为100002，一个为1   
         --缓存对象类型为编译计划，对象类型为Prepared   
         select *   
         from sys.dm_exec_cached_plans cp   
         cross apply sys.dm_exec_sql_text(cp.plan_handle) st   
         where st.text like ‘%insert into%‘

最后总结一下：

1.对于一些比较简单的语句，SQL Server能自动进行参数化，会自动忽略空格。

2.但当语句有不同时，比如加了对象所属的架构后，系统就没办法进行参数化了，但很有意思的是query_hash和query_plan_hash都相同，也就是说明本质上这些查询都是一样的。

所以可以通过group by这些hash值，计算编译了多少次。

不想长大啊发布了416 篇原创文章 · 获赞 135 · 访问量 94万他的留言板关注

秒客网

SQL Server中语句的自动参数化

相关文章