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 tabledeclare @i intset @i = 1while @i < 100begininsert into testlockselect right(replicate(‘0‘,10)+ cast(@i as varchar(10)),10),‘aaa‘,‘fixchar‘set @i = @i+1endgo----------generate test data

View Code

Code 死結語句

declare @ID nvarchar(10)begin tran select  top 1 @ID = ID from testlock with(updlock, rowlock, readpast)where col1 = ‘aaa‘order by id ascselect  @IDwaitfor delay ‘00:00:20‘update testlock set col1 = ‘bbb‘ where id = @IDcommit tran

View Code

 

                                                圖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=0000000006update testlock set col1 = ‘bbb‘ where id > @ID

 

如果是大於則相應的XML執行計畫1-4

 

                                                    圖1-4

注:其輸出運算式代表的含義各版本中應相同,但未驗證.

   輸出資料行表中檢索方式其它運算子的值代表含義感興趣的朋友可以自行測實驗證.

 

後記:此現象已經反饋給SQL Server 相關team.

再次祝大家羊年大吉,錢途無量!

 

SQL Server最佳化器特性-動態檢索