{"id":453,"date":"2018-06-24T20:08:37","date_gmt":"2018-06-24T20:08:37","guid":{"rendered":"http:\/\/dbtut.com\/?p=453"},"modified":"2018-11-08T11:20:44","modified_gmt":"2018-11-08T11:20:44","slug":"spool-concept-in-execution-planeager-spool-lazy-spool","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/","title":{"rendered":"Spool Concept in Execution Plan(Eager Spool, Lazy Spool)"},"content":{"rendered":"

 <\/p>\n

Sometimes we see operators like Eager Spool, Lazy Spool In the Execution Plan .<\/p>\n

In this article we will see what these operators do. Eager Spool and Lazy Spool are logical operators.<\/p>\n

In general, we see these expressions with physical operators such as Index Spool, Table Spool, Window Spool, and Row Count Spool.<\/p>\n

 <\/p>\n

What is the purpose of these operators and when we see them in the execution plan?<\/h3>\n

1)If the selected dataset is needed again later;<\/h3>\n

If the sql server query needs the same data (such as a result of a calculation) again during operation, it will create a work table under tempdb and insert it into it. And when this query is needed during the operation, data is retrieved from this work table.<\/p>\n

 <\/p>\n

2)When the data must be kept away from the original source;<\/h3>\n

Many people have not heard the Halloween scenario. This problem was discovered in 1976. The physical location of the row changes after an update in the database.<\/p>\n

On the\u00a0 Halloween day, a company has run a query to raise 10% to whose salary is less than $ 25,000, and the query worked properly, but at the end of the query the salary of everyone in the database was $ 25,000.<\/p>\n

Because the query continued to raise until the limit of $ 25,000 was reached. That’s why it’s called the Halloween problem.<\/p>\n

In SQL Server, with Hallowen Protection, it has a mechanism for preventing this problem. That keeps the data separate from its original location (with spool operators)<\/p>\n

 <\/p>\n

Logical Operators<\/h4>\n\n\n\n\n
Eager Spool<\/td>\nWe have said that Eager Spool and Lazy Spool are always seen with other physical spool operators.<\/p>\n

When a request from the previous operator is received, Eager Spool takes all the rows at once and transfers them to TEMPDB. (You can see it in the Hallowen scenario.)<\/td>\n<\/tr>\n

Lazy Spool<\/td>\nWhen a request from the previous operator is received, Lazy Spool only takes that row and transfer it to tempdb.<\/p>\n

You can see it with the Nested Loop operator. Because the line-by-line reading is performed in the nested loop<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Sometimes we see too many spools in long tsql code or stored procedures.<\/p>\n

Once, a query by developed a friend filled out the tempdb because of these spools.<\/p>\n

To understand how I found the source of problem, you should first read the article entitled “Which Queries Fill the Tempdb<\/a>“.<\/p>\n

I have seen in the above article that objects created internally have filled TEMPDB.<\/p>\n

With the script that shows the queries that fill Tempdb, I also see which query consumes tempdb and tell the related person to correct the problem in the script.<\/p>\n

In my case this person was trying to produce a report of the last 3 years by fetching data from more than 10 tables from 3 different databases.<\/p>\n

The problem was resolved when we run the same query with 3-month periods.<\/p>\n\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"excerpt":{"rendered":"

  Sometimes we see operators like Eager Spool, Lazy Spool In the Execution Plan . In this article we will see what these operators do. Eager Spool and Lazy Spool are logical operators. In general, we see these expressions with physical operators such as Index Spool, Table Spool, Window Spool, and Row Count Spool.   …<\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[342,480,477,343,479,478,336,476,344,474,475],"class_list":["post-453","post","type-post","status-publish","format-standard","","category-mssql","tag-eager-spool","tag-eager-spool-in-the-execution-plan","tag-hallowen-scenario","tag-lazy-spool","tag-lazy-spool-in-the-execution-plan","tag-logical-spool-operators","tag-nested-loop","tag-physical-spool-operators","tag-spool","tag-spool-in-sql-server","tag-spools-in-sql-server"],"aioseo_notices":[],"yoast_head":"\nSpool Concept in Execution Plan(Eager Spool, Lazy Spool) - Database Tutorials<\/title>\n<meta name=\"description\" content=\"Spool Concept in Execution Plan(Eager Spool, Lazy Spool)\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Spool Concept in Execution Plan(Eager Spool, Lazy Spool) - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"Spool Concept in Execution Plan(Eager Spool, Lazy Spool)\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-24T20:08:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-11-08T11:20:44+00:00\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\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:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"Spool Concept in Execution Plan(Eager Spool, Lazy Spool)\",\"datePublished\":\"2018-06-24T20:08:37+00:00\",\"dateModified\":\"2018-11-08T11:20:44+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/\"},\"wordCount\":483,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"keywords\":[\"Eager Spool\",\"Eager Spool In the Execution Plan\",\"Hallowen scenario\",\"Lazy Spool\",\"Lazy Spool In the Execution Plan\",\"Logical Spool Operators\",\"Nested loop\",\"physical spool operators\",\"Spool\",\"Spool in SQL Server\",\"Spools in SQL Server\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/\",\"name\":\"Spool Concept in Execution Plan(Eager Spool, Lazy Spool) - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"datePublished\":\"2018-06-24T20:08:37+00:00\",\"dateModified\":\"2018-11-08T11:20:44+00:00\",\"description\":\"Spool Concept in Execution Plan(Eager Spool, Lazy Spool)\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Spool Concept in Execution Plan(Eager Spool, Lazy Spool)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Spool Concept in Execution Plan(Eager Spool, Lazy Spool) - Database Tutorials","description":"Spool Concept in Execution Plan(Eager Spool, Lazy Spool)","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:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/","og_locale":"en_US","og_type":"article","og_title":"Spool Concept in Execution Plan(Eager Spool, Lazy Spool) - Database Tutorials","og_description":"Spool Concept in Execution Plan(Eager Spool, Lazy Spool)","og_url":"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/","og_site_name":"Database Tutorials","article_published_time":"2018-06-24T20:08:37+00:00","article_modified_time":"2018-11-08T11:20:44+00:00","author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"Spool Concept in Execution Plan(Eager Spool, Lazy Spool)","datePublished":"2018-06-24T20:08:37+00:00","dateModified":"2018-11-08T11:20:44+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/"},"wordCount":483,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"keywords":["Eager Spool","Eager Spool In the Execution Plan","Hallowen scenario","Lazy Spool","Lazy Spool In the Execution Plan","Logical Spool Operators","Nested loop","physical spool operators","Spool","Spool in SQL Server","Spools in SQL Server"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/","url":"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/","name":"Spool Concept in Execution Plan(Eager Spool, Lazy Spool) - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"datePublished":"2018-06-24T20:08:37+00:00","dateModified":"2018-11-08T11:20:44+00:00","description":"Spool Concept in Execution Plan(Eager Spool, Lazy Spool)","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Spool Concept in Execution Plan(Eager Spool, Lazy Spool)"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/453","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=453"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/453\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=453"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=453"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=453"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}