SQL Server优化器特性-动态检索

前段时间我写的文章 SQL Server 隐式转换引发的躺枪死锁 中有的朋友评论回复说在SQL2008R2测试时并未出现死锁,自己一测果然如此,因此给大家带来的疑惑表示抱歉,这里我就解释下其原因.

 

回顾:SQL2012中发生死锁的原因已经向大家解释了,因为隐式转换造成的表扫描扩大了锁规模.但在SQL2008R2中就未有同样的现象出现,很显然锁规模没有扩大,原因在于SQL Server的优化器为我们做了额外的事情-动态检索

 

动态检索:基于索引查找的优势,SQL Server(部分版本)会尝试将一些情形进行内部转换,使得索引检索的覆盖面更广,对其实重要补充.

 

还是之前那篇的实例,我们在SQL2008R2中看到的update的执行计划如图1-1

Code 生成测试数据

create table testlock
(ID varchar(10) primary key clustered,
col1 varchar(20),
col2 char(200))
go----------create test table

declare @i int
set @i = 1
while @i < 100
begin
insert into testlock
select right(replicate('0',10)+ cast(@i as varchar(10)),10),'aaa','fixchar'
set @i = @i+1
end
go----------generate test data

Code 死锁语句

declare @ID nvarchar(10)

begin tran 

select  top 1 @ID = ID from testlock with(updlock, rowlock, readpast)
where col1 = 'aaa'
order by id asc

select  @ID

waitfor delay '00:00:20'

update testlock set col1 = 'bbb' where id = @ID

commit tran

 

                                                图1-1

 

可以看到因为SQL Server将变量@ID进行了额外的转换运算,使得其作为数值进行处理,从而进行索引查找以提升效率,这就是动态检索的初衷,在此却也同时规避了死锁的发生.

关于动态检索

在进行动态检索时,优化器会将常量,标量的计算的CPU，IO的预估消耗置0,以避免查询子树的大小变化造成可能的执行计划改变,同时将相应的检索数值区间及检索方式作为查询操作的输入进行检索.如图1-2

 

                                                图1-2

 

实现细节

可以看到图1-2中的输出列表Expr-1013，Expr-1014，Expr-1012而在实际执行操作中这三个输出对象分别代表常量的开始值,结束值,和所需执行的操作,打开其XML执行计划详细信息可以看到Expr-1013值为@ID, Expr-1014值为@ID, Expr-1012值为62,而62就是代表”=”

如图1-3所示

 

                                                    图1-3

另一个实例

 

declare @ID nvarchar(10)

set @ID=0000000006

update testlock set col1 = 'bbb' where id > @ID

 

如果是大于则相应的XML执行计划如图1-4

 

                                                    图1-4

注:其输出表达式代表的含义各版本中应相同,但未验证.

   输出列表中检索方式其它运算符的值代表含义感兴趣的朋友可以自行测试验证.

 

后记:此现象已经反馈给SQL Server 相关team.

再次祝大家羊年大吉,钱途无量!