SQL Server中语句的自动参数化

时间:2022-04-14 06:53:50

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

 


   
  
  1. use master
  2. go
  3. if exists( select * from sys.databases where name = ‘test‘)
  4. drop database test
  5. go
  6. --创建数据库
  7. create database test
  8. if exists( select * from sys.tables where name = ‘t‘)
  9. drop table t
  10. go
  11. --1.创建表t
  12. create table t(i int);
  13. --2.添加100000条记录,消耗56秒
  14. declare @i int;
  15. declare @ sql varchar( 1000);
  16. set @i = 1
  17. set @ sql = ‘‘;
  18. while @i <= 100000
  19. begin
  20. set @ sql = ‘insert into t values(‘ cast(@i as varchar) ‘)‘
  21. begin tran
  22. exec(@ sql)
  23. commit tran
  24. set @i = @i 1
  25. end
  26. --3.1查询的统计信息
  27. --但发现没有返回记录
  28. select st.text,
  29. SUBSTRING(st.text, (qs.statement_start_offset/ 2) 1,
  30. (( CASE qs.statement_end_offset
  31. WHEN -1 THEN DATALENGTH(st.text)
  32. ELSE qs.statement_end_offset
  33. END - qs.statement_start_offset)/ 2) 1) AS statement_text,
  34. qs.*
  35. from sys.dm_exec_query_stats qs
  36. cross apply sys.dm_exec_sql_text(qs.sql_handle) st
  37. where cast(st.text as varchar) like ‘%insert into t values%‘
  38. --3.2查询的统计信息
  39. --有返回,说明SQL Server在执行动态生成的语句时,已经参数化了
  40. --execution_count字段的值为100000
  41. select st.text,
  42. SUBSTRING(st.text, (qs.statement_start_offset/ 2) 1,
  43. (( CASE qs.statement_end_offset
  44. WHEN -1 THEN DATALENGTH(st.text)
  45. ELSE qs.statement_end_offset
  46. END - qs.statement_start_offset)/ 2) 1) AS statement_text,
  47. qs.*
  48. from sys.dm_exec_query_stats qs
  49. cross apply sys.dm_exec_sql_text(qs.sql_handle) st
  50. where cast(st.text as varchar) like ‘%insert into%‘
  51. --3.3 查看数据库是否强制参数化
  52. --并没有强制参数化,说明上面是系统自动对语句进行参数化了
  53. select name,
  54. is_parameterization_forced --返回0,说明并没有强制参数
  55. from sys.databases
  56. where name = ‘test‘
  57. --3.4 再次执行后,再次执行3.2后发现,execution_count为100001
  58. insert into t values( 100001)
  59. --3.5 这次在语句中加了空格,execution_count为100002
  60. insert into t values( 100002 )
  61. --3.5 这次在语句中加了架构
  62. --返回2条记录,一条为100002,另一个为1
  63. --虽然2条数据的sql_handle、plan_handle都不相同,但是query_hash、query_plan_hash相同
  64. insert into dbo.t values( 100003 )
  65. --3.6 显示缓存的执行计划
  66. --返回2条数据,一条为100002,一个为1
  67. --缓存对象类型为编译计划,对象类型为Prepared
  68. select *
  69. from sys.dm_exec_cached_plans cp
  70. cross apply sys.dm_exec_sql_text(cp.plan_handle) st
  71. where st.text like ‘%insert into%‘


 

 最后总结一下:

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

 

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

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

 

 

 

 

 

SQL Server中语句的自动参数化SQL Server中语句的自动参数化 不想长大啊 发布了416 篇原创文章 · 获赞 135 · 访问量 94万 他的留言板 关注