{"id":486,"date":"2012-07-27T07:31:05","date_gmt":"2012-07-27T07:31:05","guid":{"rendered":"\/blogs\/joe\/post\/SpillToTempDb-warning-and-SpillLevele28099s-mapping-to-single-versus-multiple-pass.aspx"},"modified":"2013-01-02T20:31:54","modified_gmt":"2013-01-03T04:31:54","slug":"spilltotempdb-warning-and-spilllevels-mapping-to-single-versus-multiple-pass","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/spilltotempdb-warning-and-spilllevels-mapping-to-single-versus-multiple-pass\/","title":{"rendered":"SpillToTempDb warning and SpillLevel\u2019s mapping to single versus multiple pass"},"content":{"rendered":"<p>&#160;<\/p>\n<p>This post shows a few examples of Sort related SpillToTempDb execution plan warnings and the associated SpillLevel attribute.&#160; <\/p>\n<p>This blog post is based on SQL Server 2012, version 11.0.2316 and I\u2019m using the AdventureWorksDW2012 database and creating a separate version of the FactInternetSales table called FactInternetSales_Spill:<\/p>\n<pre class=\"csharpcode\"><span class=\"kwrd\">SELECT<\/span> ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate\r\n<span class=\"kwrd\">INTO<\/span> [dbo].[FactInternetSales_Spill]\r\n<span class=\"kwrd\">FROM<\/span> [dbo].[FactInternetSales];<\/pre>\n<style type=\"text\/css\">\n<p>.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }<\/style>\n<\/p>\n<p>I started off with 60,398 rows and no indexes.&#160; I then created a clustered index and had the Include Actual Execution Plan enabled:<\/p>\n<pre class=\"csharpcode\"><span class=\"kwrd\">ALTER<\/span> <span class=\"kwrd\">TABLE<\/span> [dbo].[FactInternetSales_Spill] \r\n<span class=\"kwrd\">ADD<\/span>  <span class=\"kwrd\">CONSTRAINT<\/span> [PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber_Spill] \r\n<span class=\"kwrd\">PRIMARY<\/span> <span class=\"kwrd\">KEY<\/span> <span class=\"kwrd\">CLUSTERED<\/span> \r\n(\r\n    [SalesOrderNumber] <span class=\"kwrd\">ASC<\/span>,\r\n    [SalesOrderLineNumber] <span class=\"kwrd\">ASC<\/span>\r\n)<span class=\"kwrd\">WITH<\/span> (ONLINE = <span class=\"kwrd\">OFF<\/span>) <span class=\"kwrd\">ON<\/span> [<span class=\"kwrd\">PRIMARY<\/span>];\r\nGO<\/pre>\n<style type=\"text\/css\">\n<p>.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }<\/style>\n<\/p>\n<p>The associated execution plan had no spill warnings:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/044331a7\/image.png\"><img fetchpriority=\"high\" decoding=\"async\" style=\"background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/718e37ef\/image_thumb.png\" width=\"874\" height=\"80\" \/><\/a><\/p>\n<p>I pumped up the size of this table to 664,378 rows:<\/p>\n<pre class=\"csharpcode\">INSERT [dbo].[FactInternetSales_Spill]\r\n(ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate)\r\n<span class=\"kwrd\">SELECT<\/span> ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, <span class=\"kwrd\">LEFT<\/span>(<span class=\"kwrd\">CAST<\/span>(NEWID() <span class=\"kwrd\">AS<\/span> NVARCHAR(36)),20), SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate\r\n<span class=\"kwrd\">FROM<\/span> [dbo].[FactInternetSales];\r\n<span class=\"kwrd\">GO<\/span> 10<\/pre>\n<style type=\"text\/css\">\n<p>.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }<\/style>\n<p>Dropping and and re-creating the index, I still didn\u2019t see spill warnings, so I pumped it up to 7,912,138 rows:<\/p>\n<pre class=\"csharpcode\">INSERT [dbo].[FactInternetSales_Spill]\r\n(ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate)\r\n<span class=\"kwrd\">SELECT<\/span> ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, <span class=\"kwrd\">LEFT<\/span>(<span class=\"kwrd\">CAST<\/span>(NEWID() <span class=\"kwrd\">AS<\/span> NVARCHAR(36)),20), SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate\r\n<span class=\"kwrd\">FROM<\/span> [dbo].[FactInternetSales];\r\n<span class=\"kwrd\">GO<\/span> 120<\/pre>\n<style type=\"text\/css\">\n<p>.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }<\/style>\n<\/p>\n<p>Creating the clustered index on this larger table caused the spill warning to be raised for the Sort iterator (screen shot from the graphical plan, properties of the Sort iterator and the blurb from the XML showplan):<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/62e38c0a\/image.png\"><img decoding=\"async\" style=\"background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/41f01963\/image_thumb.png\" width=\"824\" height=\"455\" \/><\/a><\/p>\n<p>Now the \u201cSpillLevel=\u201d8\u201d was interesting to me.&#160; I was also running \u201cold-school\u201d profiler AND an extended events session at the time to see what they had to say about these warnings.<\/p>\n<p>In profiler, I saw <em><strong>9 <\/strong><\/em>sort warning events, 8 of which are \u201c2 \u2013 Multiple pass\u201d.&#160; The single pass means the sort table was written to disk and only a single pass over the data was required for the sorted input (but as you see, there were multiple spill events).&#160; The multiple pass means that, well, multiple passes over the spilled data were needed in order to obtain the sorted output:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/6f050631\/image.png\"><img decoding=\"async\" style=\"background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/40ab8084\/image_thumb.png\" width=\"404\" height=\"262\" \/><\/a><\/p>\n<p>As I expected, Extended Events tells me the same thing:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/74dfa9ca\/image.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/06500aa3\/image_thumb.png\" width=\"457\" height=\"206\" \/><\/a><\/p>\n<p>What was also interesting is the behavior if I set ONLINE = ON for my index creation:<\/p>\n<pre class=\"csharpcode\"><span class=\"kwrd\">ALTER<\/span> <span class=\"kwrd\">TABLE<\/span> [dbo].[FactInternetSales_Spill] \r\n<span class=\"kwrd\">ADD<\/span>  <span class=\"kwrd\">CONSTRAINT<\/span> [PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber_Spill] \r\n<span class=\"kwrd\">PRIMARY<\/span> <span class=\"kwrd\">KEY<\/span> <span class=\"kwrd\">CLUSTERED<\/span> \r\n(\r\n    [SalesOrderNumber] <span class=\"kwrd\">ASC<\/span>,\r\n    [SalesOrderLineNumber] <span class=\"kwrd\">ASC<\/span>\r\n)<span class=\"kwrd\">WITH<\/span> (ONLINE = <span class=\"kwrd\">ON<\/span>) <span class=\"kwrd\">ON<\/span> [<span class=\"kwrd\">PRIMARY<\/span>];\r\nGO<\/pre>\n<p>Each event sub class now shows as \u201cSingle Pass\u201d: <\/p>\n<pre class=\"csharpcode\"><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/3a8433e9\/image.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/0c2aae3c\/image_thumb.png\" width=\"422\" height=\"246\" \/><\/a><\/pre>\n<style type=\"text\/css\">\n<p>.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }<\/style>\n<p>But that\u2019s not all\u2026 The execution plan for my \u201cONLINE=ON\u201d index creation shows a spill level of \u201c1\u201d \u2013 not \u201c8\u201d or \u201c9\u201d.&#160; So each single pass \u2013 even though it happens nine times, just shows up as just SpillLevel=\u201d1\u201d:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/7256780c\/image.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/71ea4517\/image_thumb.png\" width=\"369\" height=\"62\" \/><\/a><\/p>\n<p>Now my index creation was executing with parallelism, and indeed 8 threads were involved the Sort iterator execution:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/38673520\/image.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/69f2a2b5\/image_thumb.png\" width=\"316\" height=\"242\" \/><\/a><\/p>\n<p>&#160;<\/p>\n<p>Removing parallelism from the picture, aside from my index creation taking significantly longer, I see only <em>one<\/em> spill warning now:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/5b47f6d0\/image.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/7af6d098\/image_thumb.png\" width=\"355\" height=\"71\" \/><\/a><\/p>\n<p>And setting the maximum degree of parallelism to \u201c4\u201d \u2013 I see a total of 5 warnings:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/364a3657\/image.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/5a035df1\/image_thumb.png\" width=\"330\" height=\"175\" \/><\/a><\/p>\n<p>And the plan itself shows a spill level of \u201c4\u201d:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/794604c4\/image.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/0dcb5443\/image_thumb.png\" width=\"336\" height=\"57\" \/><\/a><\/p>\n<p>So the \u201cmultiple pass\u201d increases the spill level one-for-one.&#160; If I have multiple \u201csingle pass\u201d events for the same sort, it shows up as a spill level \u201c1\u201d.<\/p>\n<p>This was just for my particular scenario.&#160; Have you seen other behaviors as well?&#160; If so, please share here.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#160; This post shows a few examples of Sort related SpillToTempDb execution plan warnings and the associated SpillLevel attribute.&#160; This blog post is based on SQL Server 2012, version 11.0.2316 and I\u2019m using the AdventureWorksDW2012 database and creating a separate version of the FactInternetSales table called FactInternetSales_Spill: SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22],"tags":[],"class_list":["post-486","post","type-post","status-publish","format-standard","hentry","category-execution-plan"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SpillToTempDb warning and SpillLevel\u2019s mapping to single versus multiple pass - Joe Sack<\/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:\/\/www.sqlskills.com\/blogs\/joe\/spilltotempdb-warning-and-spilllevels-mapping-to-single-versus-multiple-pass\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SpillToTempDb warning and SpillLevel\u2019s mapping to single versus multiple pass - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"&#160; This post shows a few examples of Sort related SpillToTempDb execution plan warnings and the associated SpillLevel attribute.&#160; This blog post is based on SQL Server 2012, version 11.0.2316 and I\u2019m using the AdventureWorksDW2012 database and creating a separate version of the FactInternetSales table called FactInternetSales_Spill: SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/spilltotempdb-warning-and-spilllevels-mapping-to-single-versus-multiple-pass\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2012-07-27T07:31:05+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-03T04:31:54+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/718e37ef\/image_thumb.png\" \/>\n<meta name=\"author\" content=\"Joseph Sack\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph Sack\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/spilltotempdb-warning-and-spilllevels-mapping-to-single-versus-multiple-pass\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/spilltotempdb-warning-and-spilllevels-mapping-to-single-versus-multiple-pass\/\",\"name\":\"SpillToTempDb warning and SpillLevel\u2019s mapping to single versus multiple pass - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2012-07-27T07:31:05+00:00\",\"dateModified\":\"2013-01-03T04:31:54+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/spilltotempdb-warning-and-spilllevels-mapping-to-single-versus-multiple-pass\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/spilltotempdb-warning-and-spilllevels-mapping-to-single-versus-multiple-pass\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/spilltotempdb-warning-and-spilllevels-mapping-to-single-versus-multiple-pass\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Execution Plan\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/execution-plan\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"SpillToTempDb warning and SpillLevel\u2019s mapping to single versus multiple pass\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\",\"name\":\"Joe Sack\",\"description\":\"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\",\"name\":\"Joseph Sack\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"caption\":\"Joseph Sack\"},\"description\":\"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.\",\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/joe\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SpillToTempDb warning and SpillLevel\u2019s mapping to single versus multiple pass - Joe Sack","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:\/\/www.sqlskills.com\/blogs\/joe\/spilltotempdb-warning-and-spilllevels-mapping-to-single-versus-multiple-pass\/","og_locale":"en_US","og_type":"article","og_title":"SpillToTempDb warning and SpillLevel\u2019s mapping to single versus multiple pass - Joe Sack","og_description":"&#160; This post shows a few examples of Sort related SpillToTempDb execution plan warnings and the associated SpillLevel attribute.&#160; This blog post is based on SQL Server 2012, version 11.0.2316 and I\u2019m using the AdventureWorksDW2012 database and creating a separate version of the FactInternetSales table called FactInternetSales_Spill: SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/spilltotempdb-warning-and-spilllevels-mapping-to-single-versus-multiple-pass\/","og_site_name":"Joe Sack","article_published_time":"2012-07-27T07:31:05+00:00","article_modified_time":"2013-01-03T04:31:54+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/spilltotempdb-and-spilllevel\/718e37ef\/image_thumb.png"}],"author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/spilltotempdb-warning-and-spilllevels-mapping-to-single-versus-multiple-pass\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/spilltotempdb-warning-and-spilllevels-mapping-to-single-versus-multiple-pass\/","name":"SpillToTempDb warning and SpillLevel\u2019s mapping to single versus multiple pass - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2012-07-27T07:31:05+00:00","dateModified":"2013-01-03T04:31:54+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/spilltotempdb-warning-and-spilllevels-mapping-to-single-versus-multiple-pass\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/spilltotempdb-warning-and-spilllevels-mapping-to-single-versus-multiple-pass\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/spilltotempdb-warning-and-spilllevels-mapping-to-single-versus-multiple-pass\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Execution Plan","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/execution-plan\/"},{"@type":"ListItem","position":3,"name":"SpillToTempDb warning and SpillLevel\u2019s mapping to single versus multiple pass"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/","name":"Joe Sack","description":"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648","name":"Joseph Sack","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","caption":"Joseph Sack"},"description":"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.","sameAs":["http:\/\/3.209.169.194\/blogs\/joe","https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack"],"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/486","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/comments?post=486"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/486\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=486"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=486"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=486"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}