SQL Server里简单参数化的痛苦

原文:SQL Server里简单参数化的痛苦

在今天的文章里,我想谈下对于即席SQL语句(ad-hoc SQL statements),SQL Server使用的简单参数化(Simple Parameterization)的一些特性和副作用。首先,如果你的SQL语句包含这些,简单参数化不会发生:

  • JOIN
  • IN
  • BULK INSERT
  • UNION
  • INTO
  • DISTINCT
  • TOP
  • GROUP BY
  • HAVING
  • COMPUTE
  • Sub Queries

一般来说,如果你处理所谓的安全执行计划(Safe Execution Plan),SQL Server自动参数化你的SQL语句:不管提供的参数值,查询总必须通向一样的执行计划。如果你的执行计划里有书签查找,这就是不可能的例子。因为临界点定义了是否进行书签查找还是全表/聚集索引扫描。

自动参数化并不那么酷!

如果SQL Server能自动参数化你的SQL语句,你还是要考虑下SQL Server引入的自动参数化SQL语句的一些副作用。我们来看一个具体的例子。下列查询创建一个表,执行一个会被SQL Server自动参数化的简单SQL语句。

 1 -- Create a simple table
 2 CREATE TABLE Orders
 3 (
 4     Col1 INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
 5     Price DECIMAL(18, 2)
 6 )
 7 GO
 8
 9 -- This query gets auto parametrized, because it is a simple query with a safe (consistent) plan
10 SELECT * FROM Orders
11 WHERE Price = 5.70
12 GO
13
14 -- Analyze the Plan Cache
15 SELECT
16     st.text,
17     qs.execution_count,
18     cp.cacheobjtype,
19     cp.objtype,
20     cp.*,
21     qs.*,
22     p.*
23 FROM sys.dm_exec_cached_plans cp
24 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p
25 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
26 LEFT JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
27 WHERE st.text LIKE '%Orders%'
28 GO

然后当你查看计划缓存时,你会看到SQL Server能为你自动参数化SQL语句:

(@1 numeric(3,2))SELECT * FROM [Orders] WHERE [Price]=@1

但什么是选择的作为参数的数据类型?最小可能的那个!在这里是NUMERIC(3,2)!如果现在你执行下列2个查询:

1 -- Execute a slightly different query
2 SELECT * FROM Orders
3 WHERE Price = 8.70
4 GO
5
6 -- Execute a slightly different query
7 SELECT * FROM Orders
8 WHERE Price = 124.50
9 GO

SQL Server能重用为第1个使用8.7值SQL语句的参数化SQL语句的执行计划。但用124.50值的第2个SQL语句呢?对于这个SQL语句缓存的计划不能被重用,因为124.50值不符合NUMERIC(3,2)。在这个情况下,SQL Server用NUMERIC(5,2)数据类型生成你SQL语句的新参数化版本。你刚用你的SQL语句的额外的参数化版本污染了你的计划缓存!当你执行下列语句会变得更糟:

-- Execute a slightly different query
SELECT * FROM Orders
WHERE Price = 1204.50
GO

这个会再次给你新的用NUMERIC(6,2)数据类型的新参数化版本——计划缓存里另一个版本!当我展示这个行为的时候,很多人都建议我应该用逆序来执行刚才的SQL语句。我们通过首先清空计划缓存来试下。

 1 -- Clear the Plan Cache
 2 DBCC FREEPROCCACHE
 3 GO
 4
 5 -- Execute a slightly different query
 6 SELECT * FROM Orders
 7 WHERE Price = 1204.50
 8 GO
 9
10 -- Execute a slightly different query
11 SELECT * FROM Orders
12 WHERE Price = 124.50
13 GO
14
15 -- Execute a slightly different query
16 SELECT * FROM Orders
17 WHERE Price = 8.70
18 GO

然后当你看计划缓存时,没有任何改变:SQL Server还生成了3个不同的参数化SQL语句——每次都用最小可能的数据类型。

你怎么做没有一点关系,即你执行你SQL语句的顺序:在自动参数化期间,SQL Server总会选择最小可能的数据类型。当你依赖SQL Server这个特性时,好好考虑下。

VARCHAR如何呢?SQL Server自动参数化包含字符值(例如VARCHAR)的SQL语句时,事情会好点。假设有下列表定义和下列2个查询:

 1 -- Create another table to demonstrate this problem
 2 CREATE TABLE Orders3
 3 (
 4     Col1 INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
 5     Col2 VARCHAR(100)
 6 )
 7 GO
 8
 9 -- Clears the Plan Cache
10 DBCC FREEPROCCACHE
11 GO
12
13 -- A VARCHAR/CHAR column is always auto parametrized to a VARCHAR(8000)
14 SELECT * FROM Orders3
15 WHERE Col2 = 'Woody'
16 GO
17
18 -- A VARCHAR column is always auto parametrized to a VARCHAR(8000)
19 SELECT * FROM Orders3
20 WHERE Col2 = 'Tu'
21 GO

在这个情况下,SQL Server用VARCHAR(8000)生成1个自动参数化SQL语句——最大可能的数据类型。从刚才例子里,这是你所期待的行为。有时SQL Server好事坏事同时做……

小结

当你和简单SQL语句打交道时,自动参数化可以非常棒。但如你在这个文章里所见,你要知道SQL Server引入的副作用。另外SQL Server的简单参数化特性还会提供你强制参数化(Forced Parameterization)功能,这个我会在以后的文章里介绍。

感谢关注!

时间: 2024-09-15 18:51:22

SQL Server里简单参数化的痛苦的相关文章

简单介绍SQL Server里的闩锁_MsSql

在今天的文章里我想谈下SQL Server使用的更高级的,轻量级的同步对象:闩锁(Latch).闩锁是SQL Server存储引擎使用轻量级同步对象,用来保护多线程访问内存内结构.文章的第1部分我会介绍SQL Server里为什么需要闩锁,在第2部分我会给你介绍各个闩锁类型,还有你如何能对它们进行故障排除. 为什么我们需要闩锁?闩锁首次在SQL Server 7.0里引入,同时微软首次引入了行级别锁(row-level locking).对于行级别锁引入闩锁的概念是非常重要的,不然的话在内存中会

简单介绍SQL Server里的闩锁

在今天的文章里我想谈下SQL Server使用的更高级的,轻量级的同步对象:闩锁(Latch).闩锁是SQL Server存储引擎使用轻量级同步对象,用来保护多线程访问内存内结构.文章的第1部分我会介绍SQL Server里为什么需要闩锁,在第2部分我会给你介绍各个闩锁类型,还有你如何能对它们进行故障排除. 为什么我们需要闩锁? 闩锁首次在SQL Server 7.0里引入,同时微软首次引入了行级别锁(row-level locking).对于行级别锁引入闩锁的概念是非常重要的,不然的话在内存中

SQL Server里的INTERSECT ALL

原文:SQL Server里的INTERSECT ALL 在上一篇文章里,我讨论了INTERSECT设置操作的基础,它和INNER JOIN的区别,还有为什么需要好的索引设计支持.今天我想谈下SQL Server里并未实现的INTERSECT ALL操作. INTERSECT ALL是SQL特性的一部分,但SQL Server并不考虑它.和INTERSECT操作的区别非常简单:INTERSECT ALL不会剔除重复行.在SQL Server里的好处是你可以模拟INTERSECT ALL.我们来试

SQL Server里PIVOT运算符的”红颜祸水“

原文:SQL Server里PIVOT运算符的"红颜祸水" 在今天的文章里我想讨论下SQL Server里一个特别的T-SQL语言结构--自SQL Server 2005引入的PIVOT运算符.我经常引用这个与语言结构是SQL Server里最危险的一个--很快你就会知道为什么.在我们进入特定问题和陷阱前,首先我想给你下使用SQL Server里的PIVOT能实现什么的一个基本概述. 概述 SQL Server里PIVOT运算符背后的基本思想是在T-SQL查询期间,你可以旋转行为列.运

SQL Server里的INTERSECT

原文:SQL Server里的INTERSECT 在今天的文章里,我想讨论下SQL Server里的INTERSECT设置操作.INTERSECT设置操作彼此交叉2个记录集,返回2个集里列值一样的记录.下图演示了这个概念.   INTERSECT与INNER JOIN 你会发现,它和2个表间的INNER JOIN几乎一样.但今天我会介绍它们之间的一些重要区别.让我们从创建作为输入的2个简单表开始.  1 -- Create the 1st table 2 CREATE TABLE t1 3 (

SQL Server里因丢失索引造成的死锁

原文:SQL Server里因丢失索引造成的死锁 在今天的文章里我想演示下SQL Server里在表上丢失索引如何引起死锁(deadlock)的.为了准备测试场景,下列代码会创建2个表,然后2个表都插入4条记录. 1 -- Create a table without any indexes 2 CREATE TABLE Table1 3 ( 4 Column1 INT, 5 Column2 INT 6 ) 7 GO 8 9 -- Insert a few record 10 INSERT IN

在SQL Server里如何进行页级别的恢复

在今天的文章里我想谈下每个DBA应该知道的一个重要话题:在SQL Server里如何进行页级别还原操作.假设在SQL Server里你有一个损坏的页,你要从最近的数据库备份只还原有问题的页,而不是还原整个数据库. 我们来破坏一个页 第一步我想向你展示下如何建立表(或索引)里有个特定页损坏的情景,这里我们会进行一些魔术,因为开箱即用(out-of-box)的SQL Server本身不会引入任何损坏的页(如果有的话,恭喜你找到了一个BUG).我们从创建一个新的数据库,往新建的表插入一些记录开始. 1

分析MS SQL Server里函数的两种用法

server|函数 SQL Server里函数的两种用法(可以代替游标) 1. 因为update里不能用存储过程,然而要根据更新表的某些字段还要进行计算.我们常常采用游标的方法,这里用函数的方法实现. 函数部分: 以下是引用片段: CREATE FUNCTION [DBO].[FUN_GETTIME] (@TASKPHASEID INT) RETURNS FLOAT AS BEGIN DECLARE @TASKID INT, @HOUR FLOAT, @PERCENT FLOAT, @RETUR

SQL Server里函数的两种用法(可以代替游标)

server|函数|游标 SQL Server里函数的两种用法(可以代替游标)1. 因为update里不能用存储过程,然而要根据更新表的某些字段还要进行计算.我们常常采用游标的方法,这里用函数的方法实现. 函数部分:CREATE FUNCTION [DBO].[FUN_GETTIME] (@TASKPHASEID INT) RETURNS FLOAT AS BEGIN   DECLARE @TASKID INT,          @HOUR FLOAT,           @PERCENT