SQL Server optimizer features-dynamic retrieval

Some time ago I wrote the article SQL Server implicit conversion caused by lying gun deadlock in some friends commented back said that in the SQL2008R2 test did not appear deadlock, their own measurement is true, so to everyone to bring the doubts expressed sorry, I'll explain the reason here.

Review: The cause of the deadlock in SQL2012 has been explained, because the table scan caused by the implicit conversion expands the lock size. But there is no such thing in sql2008r2, and it is clear that the lock size has not expanded because the optimizer of SQL Server did something extra for us-dynamic retrieval

Dynamic Retrieval: based on the benefits of index lookups, SQL Server (some versions) will attempt to convert some scenarios internally, making index retrieval more extensive and important additions.

Or an example of the previous one, the update execution plan we saw in SQL2008R2 1-1

Code Generation test Data

Create TableTestlock (IDvarchar(Ten)Primary Key Clustered, col1varchar( -), col2Char( $))Go----------Create test tableDeclare @i intSet @i = 1 while @i <  -beginInsert  intoTestlockSelect  Right(Replicate('0',Ten)+ cast(@i  as varchar(Ten)),Ten),'AAA','Fixchar'Set @i = @i+1EndGo----------Generate test Data

View Code

Code Deadlock Statement

Declare @ID nvarchar(Ten)begin Tran Select  Top 1 @ID =Id fromTestlock with(Updlock, Rowlock, Readpast)whereCol1= 'AAA'Order  byIdASCSelect  @IDwaitforDelay'00:00:20'UpdateTestlockSetCol1= 'BBB' whereId= @IDCommit Tran

View Code

Figure 1-1

You can see that because SQL Server has made the variable @id an additional conversion operation, so that it is processed as a numeric value, so that the index lookup to improve efficiency, this is the original intention of dynamic retrieval, but also at the same time to circumvent the occurrence of deadlocks.

About Dynamic retrieval

In the case of dynamic retrieval, the optimizer will set 0 of the estimated consumption of the constant, scalar computed cpu,io, to avoid the change of the size of the query subtree resulting in possible execution plan changes, and retrieve the corresponding retrieval range and retrieval mode as input of the query operation. 1-2

Figure 1-2

Implementation Details

You can see the output list in Figure 1-2 expr-1013,expr-1014,expr-1012 and in the actual execution, these three output objects represent the starting value of the constant, the ending value, and the action required to perform the operation, Open its XML execution plan details to see that the Expr-1013 value is @id, the Expr-1014 value is @id, the Expr-1012 value is 62, and 62 means "="

1-3 is shown

Figure 1-3

Another instance

Declare @ID nvarchar (ten) Set @ID = 0000000006 Update Set = ' BBB ' where > @ID

If it is greater then the corresponding XML execution plan 1-4

Figure 1-4

Note: its output expression means that the meaning of each version should be the same, but not verified.

Retrieve in output list the values of other operators mean that friends who are interested in the meaning can test the validation themselves.

PostScript: This behavior has been fed back to SQL Server related team.

Once again I wish you all the best of the goat!

SQL Server optimizer features-dynamic retrieval