{"id":810,"date":"2012-03-20T10:00:39","date_gmt":"2012-03-20T14:00:39","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=810"},"modified":"2014-11-13T13:59:25","modified_gmt":"2014-11-13T18:59:25","slug":"key-lookup-operator-in-update-statements","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/","title":{"rendered":"Key Lookup Operator in Update Statements"},"content":{"rendered":"
\n

\nWhen thinking about the indexes for update statements it is easy to overlook an important peculiarity in the way SQL Server finds the rows to update before updating them.\n<\/p>\n

Those pesky Lookups<\/h3>\n

\nIf you have an UPDATE statement that filters on two columns and you have an index on one of them, you might be tempted to think that the necessary lookup for the second column is not going to cost a lot - particular when that second column is not selective at all - as SQL Server has to access the storage place of each row anyway when executing the actual update. This however is not true, as SQL Server executes the update in two separate phases: One to identify the rows and one to update those rows. That means a lookup in the find phase can be very expensive.\n<\/p>\n

\nLet us look at an example. Use the following script to create two identical tables and insert 200,000 rows into each of them.\n<\/p>\n

\n[sql]\n

CREATE TABLE dbo.tst1(
\n Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
\n Val INT,
\n Filter INT,
\n IdxKey INT,
\n Fill CHAR(1000) DEFAULT 'Fill'
\n);<\/p>\n

CREATE TABLE dbo.tst2(
\n Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
\n Val INT,
\n Filter INT,
\n IdxKey INT,
\n Fill CHAR(1000) DEFAULT 'Fill'
\n);<\/p>\n

INSERT INTO dbo.tst1(Val,Filter,IdxKey)
\nOUTPUT INSERTED.Val,INSERTED.Filter,INSERTED.IdxKey INTO dbo.tst2(Val,Filter,IdxKey)
\nSELECT 0,0, n%1000
\nFROM dbo.GetNums(200000);
\n[\/sql]\n<\/p><\/div>\n

\nThe Filter column will be used to force the lookup later on by adding \"Filter = 0\" to the where clause. It is valued 0 in all rows, so no row will be excluded by this filter.\n<\/p>\n

\nAfter creating the tables, create the following indexes:\n<\/p>\n

\n[sql]\nCREATE INDEX dbo_tst1_IdxKey ON dbo.tst1(IdxKey);
\nCREATE INDEX dbo_tst2_IdxKey ON dbo.tst2(IdxKey) INCLUDE (Filter);
\n[\/sql]\n<\/div>\n

\nBoth indexes are on the IdxKey column, but the index on the dbo.tst2 table also includes the Filter column.\n<\/p>\n

\nNow run the following two identical update statements against the two tables:\n<\/p>\n

\n[sql]\nUPDATE dbo.tst1
\n SET Val = 17
\nWHERE IdxKey <30 AND Filter = 0;<\/p>\n

UPDATE dbo.tst2
\n SET Val = 17
\nWHERE IdxKey <30 AND Filter = 0;
\n[\/sql]\n<\/p><\/div>\n

\nThe first one requires a lookup operator as the Filter column is not included in the index on dbo.tst1. Below are the two execution plans:\n<\/p>\n

\n\"Update<\/a>\n<\/p>\n

\n\"Update<\/a>\n<\/p>\n

\nAs you can clearly see, the update operator is separated from the data retrieval operator(s) by several other operators. It looks like all the filtering and value preparations happen before the update operator which is then just updating all rows passed to it without any further filtering.\n<\/p>\n

Statistics<\/h3>\n

\nI ran the above statements in a loop, each executing 2000 times and recorded time and logical reads. The averaged results are below:\n<\/p>\n\n\n\n\n\n\n
cpu_time<\/th>\ntotal_elapsed_time<\/th>\nlogical_reads<\/th>\nexec_count<\/th>\ncommand<\/th>\n<\/tr>\n<\/thead>\n
98<\/td>\n175<\/td>\n37212<\/td>\n2000<\/td>\n UPDATE dbo.tst1 SET Val = 17 WHERE IdxKey <30 AND Filter = 0; <\/td>\n<\/tr>\n
47<\/td>\n72<\/td>\n19211<\/td>\n2000<\/td>\n UPDATE dbo.tst2 SET Val = 17 WHERE IdxKey <30 AND Filter = 0; <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\nThe cpu time, the elapsed time and the logical reads were each about twice as high in the query against dbo.tst1, the one requiring the lookup operator. This was the case even though the lookup did not actually filter any rows - every row accessed by the lookup had to be updated as well.\n<\/p>\n

Conclusion<\/h3>\n

\nThe above statistics prove what the execution plan already suggested: The finding of rows for an update and the update itself are separate steps in SQL Server update queries. That means the \"find\" portion has to be optimized as if it were a standalone SELECT. In particular, lookup operations can not be ignored as SQL Server accesses the rows twice in this case: Once for the lookup and once for the update.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

When thinking about the indexes for update statements it is easy to overlook an important peculiarity in the way SQL Server finds the rows to update before updating them. Those pesky Lookups If you have an UPDATE statement that filters [more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[5,19,14],"tags":[],"class_list":["post-810","post","type-post","status-publish","format-standard","hentry","category-general","category-performance","category-sql-server-internals"],"acf":[],"yoast_head":"\nKey Lookup Operator in Update Statements - sqlity.net<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Key Lookup Operator in Update Statements - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"When thinking about the indexes for update statements it is easy to overlook an important peculiarity in the way SQL Server finds the rows to update before updating them. Those pesky Lookups If you have an UPDATE statement that filters [more...]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2012-03-20T14:00:39+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:59:25+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/03\/update_with_bookmark_lookup.png\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/810\\\/key-lookup-operator-in-update-statements\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/810\\\/key-lookup-operator-in-update-statements\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Key Lookup Operator in Update Statements\",\"datePublished\":\"2012-03-20T14:00:39+00:00\",\"dateModified\":\"2014-11-13T18:59:25+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/810\\\/key-lookup-operator-in-update-statements\\\/\"},\"wordCount\":468,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/810\\\/key-lookup-operator-in-update-statements\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/03\\\/update_with_bookmark_lookup.png\",\"articleSection\":[\"General\",\"Performance\",\"SQL Server Internals\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/810\\\/key-lookup-operator-in-update-statements\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/810\\\/key-lookup-operator-in-update-statements\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/810\\\/key-lookup-operator-in-update-statements\\\/\",\"name\":\"Key Lookup Operator in Update Statements - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/810\\\/key-lookup-operator-in-update-statements\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/810\\\/key-lookup-operator-in-update-statements\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/03\\\/update_with_bookmark_lookup.png\",\"datePublished\":\"2012-03-20T14:00:39+00:00\",\"dateModified\":\"2014-11-13T18:59:25+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/810\\\/key-lookup-operator-in-update-statements\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/810\\\/key-lookup-operator-in-update-statements\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/810\\\/key-lookup-operator-in-update-statements\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/03\\\/update_with_bookmark_lookup.png\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/03\\\/update_with_bookmark_lookup.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/810\\\/key-lookup-operator-in-update-statements\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Key Lookup Operator in Update Statements\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Key Lookup Operator in Update Statements - sqlity.net","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/","og_locale":"en_US","og_type":"article","og_title":"Key Lookup Operator in Update Statements - sqlity.net","og_description":"When thinking about the indexes for update statements it is easy to overlook an important peculiarity in the way SQL Server finds the rows to update before updating them. Those pesky Lookups If you have an UPDATE statement that filters [more...]","og_url":"https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2012-03-20T14:00:39+00:00","article_modified_time":"2014-11-13T18:59:25+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/03\/update_with_bookmark_lookup.png","type":"","width":"","height":""}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Key Lookup Operator in Update Statements","datePublished":"2012-03-20T14:00:39+00:00","dateModified":"2014-11-13T18:59:25+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/"},"wordCount":468,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/03\/update_with_bookmark_lookup.png","articleSection":["General","Performance","SQL Server Internals"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/","url":"https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/","name":"Key Lookup Operator in Update Statements - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/03\/update_with_bookmark_lookup.png","datePublished":"2012-03-20T14:00:39+00:00","dateModified":"2014-11-13T18:59:25+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/03\/update_with_bookmark_lookup.png","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/03\/update_with_bookmark_lookup.png"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/810\/key-lookup-operator-in-update-statements\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Key Lookup Operator in Update Statements"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-d4","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/810","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=810"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/810\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=810"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=810"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=810"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}