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

9/1/2015来源:SQL技巧人气:5286

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

QQAAAAAAEBOVIg0AAAAAACgXIg0AAAAAACgYIg0AAAAAACgYIg0AAAAAACgYIg0AAAAAACgYIg0AAAAAACgYIg0AAAAAACgYIg0AAAAAACgYIg0AAAAAACgYIg0AAAAAACgYIg0AAAAAACgYIg0AAAAAACgYIg0AAAAAACgYIg0AAAAAACgYIg0AAAAAACgYIg0AAAAAACgYIg0AAAAAACgYIg0ABARjZmqdetU69apDuZLvSoAAAAQVWQRadQqoooP07wjrTX/INVvi88fDmY+SQfXqdYdTApqHlIS2A9ZmSHYP4KyiEpF4pM8A6ga1soa0d/hfPP3U2sSUamIOivgGQQrK1Ng/zeqw5E6hvPj5+tzVuY6daNnTVw5hyqZjfMj04dnSrerAAAAQAFkEWkIIWoRkSYpnoSkZ5N0MDQ9JIH5tOYfDL7LnnQwvJEmVPtTAHs/tOYfVGU2ksbMcEYaQuJZkSOeJ6L4O5xv/n5qTZIy0vjSqA51pPGqCayjz3G+NGa64g31GqkGAAAA+IUm0tCvOmepPZewk+KJSkWSkohK5SpJ7m5iK4mfH6jOokWaLM/Ink4jbWT3JBxv+cxFzKu/nqvFXm959+eSDrqHH4yn3hKYD7VSXJHGMx/v/pnnQvXBfHqMoV67b3eo1mUKbxy1t6n9EO/eFZz7R2B/8uNfT96r6bzRTlykGc4/uI7loJgsFM/apPh4kpXk2RGtgQ7nmz8X1xFRu/5JnRrUUWhNIuokz1HwzIunPlOzik9ynWueSfyq/wJ3z2hVWt3oI9IEcFyyMqkK06jmWrqPSBOiawQAAAAQrUJ2l8brqnOWJ41QXTF694t6rabFG68Hz7I83bukeK9+I+ddhaR4z3xak0T16jjvrtD76K35B92pht6Xysr0Sjt+3aVJOriOfpXaPZ+szHXurltr/kH6w2a0SOP7EnVrkmefZ6k9+0Fg//h1l4ZvPfm2yz1mMJEmcPTqRYmnJZPWJE/O8Hc43/w5tZJ47+my1K7pqGTifk0/X3jrcxYtt2S5To0A6j/hOC6Nak/FC8uDZ1SkcT1y5u9dGvY/AQAAAGgiEWnoscQ1Wpb3YzutPHcVVL4iDetug9cFbx4cUYR114WWIlzXldl3IfyINMP58d4dMs9Fa/6+o+vuh+jOpZq9EwT3jz+Rhmc9ebfLRbK7NGzx3jXDvfH+DvcTNV1SvCtsuO9Gcp8vwvU5i/WwW0D1n7CPC+NwDOfHh+cujevCASINAAAAhFQkIg39crbPSEO//M34vAFnr1LMh3AYuKKIuA8PeN+FiECkUWcG9CmCLM/VeoH9I/9IE0qMz8Z4ooufw/1E3ZZRx5MsNUnK8ty04TtfhOozO9IEVP9J0JEmAK6K4b524PNuHh48AwAAANFCGWnoV5q9HjzjethJTbuc7HlQinW7hhFpXGkny3M1Okvt9+e0Pc+Y0T52zPcdUF6f0ffuZnHOx7XuHA+e0fv6noTgvdxGNe1jOe5L2mJSjVd0zPL6/Abf/uHcn3z41pNvuyhyijS0Ssl4wMyv4f7KImrqEGSReNodS75II1SfuSJNAPWfcD945qljjAcsQ0I4k/iKNF6rBwAAAMAQum88oz0DQ30fgPsbAtRqrk/8074GQKX2fJymlfZdAtSE9J4f59M1no/Fi3zwxv37GMwHyTgePOMcyD8f+lNq3m95P9tG/zoB+iJoecb1Jc7uD+ULZwCBncD7Fs/+5F0E13rybBfvfmB+aW9kfqKEql5q2vcntAY0PACtJH7+xqNa5fVBGlfdnj8KjI+fMQ4N/alC9gOZous/f/30VOZ1qszM0H+cZjg/nnZO+fklzsyv3wAAAACgC/uXODMePAOA2OT57j58KgYAAABCKryRhv61wgAAAAAAACEnl5/aBAAAAAAACAAiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKBgiDQAAAAAAKJjCIk2WmsQnSb0SAAAAAAAgGwqLNJyS4klWOMcHAAAAAADZCmWkyVITlYqoVEQVT5LiSVKra3hS/PxwFckihLSSeBVRxRM1NSRr/i2+4YQQQlqTXP/0uktDTUIrauGwwj8+tZLxSZ4Fud7J8ozsXjQ1jlrtvV0AAAAAABBxIYs0WWqiUrteUz1+KtIkxdNCSBZRxVNjkHgqTmS58kBrElFn8Q8nnjmzHzwL2V2aLFceo167lpvlGTkp3rMy1Da64xYehwMAAAAAkIR/kYaBPiN1PNfsW0m82mtAlpoktRLSSuLnkwMVhDyRhnO4e35hjjQcycT7xg490nhWjHNCAAAAAAAipz8ijbBojTRqlecJOvrKINIAAAAAAMiBygIAAAAAAKBYqnYWw7zOzs579+6lpqbu27dv3759+2nYQwAAAAAAot4+b6mpqffu3esE6XR1dam6+PX29qampvb09MzMzEwDAAAAAIC3rq6u1NTUXpBOX1+fqp+lb97Q0NCBAwdmZ2enp2emp6enpjxlcnJqcnKKPgQFBQUFBQUFBQUl6gvVDZ6cnJqYmBwbGxsbG9doNEMQQcMsqrGxscnJybGxsUGW0dHRgwcPzszMTk9PT05OCnyRAAAAhM/U1PTMzOzMzCznC6nXDgAgRo2NjQ8NjYyMjB45cmQUpDM2NqaanJxcsGDB5OQkX6SZnp6ZmJgcGxsfGRlFQUFBQYl8mZqa/m18vPr99xkvNqxfPzU1LfnqoaCgoMRmGRoa6R8YHBoaQqSR1tjYmOpf/uVfVq5cKRhppsfGJ0ZGRoeGhlFQUFBQIl8mJ6fee/fdEykpjBeXLl6cnJySfPVQUFBQYrMMDA719vUj0khubGxM9dRTTx09elQg0kxNTY+Ojg0NDQ8MDKKgoKCgRL5MTEx8vnLl5cuXGS9yc3MnJiYkXz0UFBSU2Cx9ff09Pb2Dg4OINNIaGxtTrVq16sKFCwKRZnBwyGjsbGlpa2xsQkFBQUGJfOnp6aWSDONFbm5uT0+v5KuHgoKCEpulvr6xqrq2uaUFkUZaY2Njqu3bt9+4cUMg0gwMDBoMHXp9c21tAwoKCgpK5EtnZzeVZBgvcnNzOzu7JV89FBQUlNgs1dV1Ol2lXt+MSCOtsbEx1ebNm7VarUCk6e8faGlpq6trrKysQUFBQUGJfGlvN+5OSsrJyWG8KC4ubm83Sr56KCgoKLFZynVVJSXljY16RBppub7xLCsrSyDS9PX16/UtVVW1ZWUVKCgoKCiRL21t7ZcuXiwrK2O8qKmpaWtrl3z1UFBQUGKzlJSUFxQU19c3ItJIa2xszPfv0nR191RW1hQWlhYUFKOgoKCgRL5UVdU2NbU2NbVyvpB89VBQUFBis+TnF2m192rr6hFppDU2NqZiJxlGpGlra8/LK8jNvXsnJw8FBQUFBQUFBQUF5c6dvNu3tbdu5dbU1CLSSGtsbEw1wjI8b3x8PCMjo6ysvLq6rqKiWqerREFBQUFBQUFBQUFxl6KikpMnT46DpFRj/CYmJqqqqi5cuHAMAAAAAABYTp8+nZ2dPQERNMmiGgIAAAAAgOAIfJoDQou981UpAAAAAAAAiqWS+sYRAAAAAABA4BBpAAAAAABAwRBpAAAAAABAwRBpAAAAAABAweQYabZu3x/jReojAAAAAACgGDKNNCSGIdIAAAAAAIinkvyOBGfh7OvHqXX0QgjRjRC1WsP3rkIh0gAAAAAAiKeakx++SKPW6DQ6otYRtYZQoUWt1qg1GrVGo1Zr2O8qFCINAAAAAIB4qry8vLy8vCveUlJOT0lHfKSh36VRa3RnyibOlE1odIg0AAAAAACxQqXRMHv/Q0NDao1GnpFGILSIjDQqlYrztfCYYt7lHJ8aKHJkCiINAAAAAIB4nkgzOTmpGyG6EXKmbEKt1kxKJ+BIQy9ioohArnC/pfImEEsEIo3IkSmINAAAAAAA4rkijV6v1+l05eXlZ8omNBqdWq0JaqYq3i9SE3jLTSDSiAktwsREGuH7M2LmLDCQ776NGyINAAAAAIB4Ko1Gp9Hprly5otHoUlJOU0Wt1oyLplKpxLzgw55h+L7EmX6zhe/2C999FTEzF1gifbnCM0ekAQAAAAAQj+OzNGfKJly3a3Q6jU5nMBhGfVGpVPQX9P8yRuCbkE4g0lDfB6Ab8StrMDMG+7XAvRQ24h2NOOfMNw5BpAEAAAAACCmVRqPr7u4eGhoaGhrS6Qj1YRV391qj0cXFqQ0Gw4g/VCoVewgf9uQCkUat1lCFbwSfxEQagSjCHsIXe/gWgUgDAAAAABBCKo1GZzAY8uZduXJFo9MRQohGQzRqjUZXXl5OpZphLiqViv6ajT0a3+Ruwg+eqTWB5xlCiyv07MEOIeIjjcAIBJEGAAAAACDMOB48c0UaQgghBoMhTq2Oi1NTqYaTSqVivOB8iw97hj4iTXC3aMTcpQlsiECkEROZ3BBpAAAAAADEc0Ua6huch4aGdCPzkUajJmo1IUStidNodHFqdasvnInF/RbfJOyBfJEmTqOiikqjUmkC+VIywnO3JJhII5CL/FqiGyINAAAAAIB4nkhD5RndCKHft5mcnDQYDBqdLi5O3eKLSqXiGyJwl4Y9H95Io4tTE7VaFxeniwtHpOG8gSM8RHgSv+bvhkgDAAAAACCeK9K484xONx9p1Gqijuvu7lZr4qhI08RPpVJR/2XjG1mAQKTRUB/xIXFxwUWaAPJJAA+eMWaISAMAAAAAEHLekUZH1Lq4lJTTVN96aGhIr9fn5eVR33vWyEOlUjFe8L3FHpM9SWNjY/gijTto8b3L91pgKs5J2P8k3jeIEGkAAAAAAEJCRX2nGfWDm1RxRRp1HFHHlZeXx6njqEhTz0WlUnG+ZgyhXjBG4BxYX18vEGnoRThgKBciDQAAAACAeCrqo/9vv/12nFpNFSrSuH5qU6PTaHQpKafj4tS1vqhUKsY/qSH04YzH0thT1dbWCn/jWdRDpAEAAAAAEE9Vw0IFGHZhjxkmiDRS1woAAAAAAMVQVckPIo3UtQIAAAAAQDFUW7fvl2GROlZISfKdj4KCgoKCgoKCgqKgEuBXIQMAAAAAAMgBIg0AAAAAACgY7w/eC00j7icjZUL8Gor5eU32W37tjYBXRsya+LsIkTMUHkHM4sTvWM45c+5qAAAAAACKSiTGNMITS7EhvMK0XZz9bM6+u7+7V8xsxS+Rb8Jg1tCvHcW3ksIrQx+N/l8AAAAAAAaOrqrIHqRArzRE6xYawawPX9ig97w5s4fPlCKwRL+ihcDKiFwie/yQVwCRkUYpNQoAAAAAZCWo53w4O9xy64AGvD4i8wk70vgMQnwRxd+VZyxaZHLwObeQV4CAh/i7uwAAAAAgBvl4eorvtgDfu+yB3IzJCZ5uaqI2sHX3ok3kmo/ITjC7xyywExgDGb15gTwjMBOBFRP5ro/5G5MTEpIDmFVIKoDIrBVANBLPXeESko2Mt9yV0estbSLf+AAAAAAgK74vfrNHCE2kCUmQoc2PMxlxXulndJ3pI3NOxblEzp0gkCsiFmn41oc78/HHLYERRFYAnztfYM7smXMOEcmYnOBOJrSXhBBCtImq+bBHf0ubSL3m220AAAAAIBe+e58c0/D3WUnwkcZ1zdy/fiSznyqOQH+aMYTzLfb2Cs9BTIferwwgMJxjfWh9d85dIWYNfW6U8E4QGMIe7mNl/Kkn9FzCyCjz0YX1D9ylAQAAAFAIH5+a8NmjZQ/nm5UX+oNnjH52AJGG7x6NL5zrz/lPzq628GjCy/I5PhGxPwXWkGv+QjccwloBRA7xLxr5V0/mAwprCkakYc7PPZ3gY3sAAAAAIKFg79Kwh4uKNDSB5hEarvsPAlvE