26 performance tuning questions and solutions, Advanced SQL interview questions, Advanced SQL Queries, Advanced SQL tutorial, Advanced SQL tutorial pdf, Difficult SQL Interview Questions, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Free Download SQL SERVER Interview questions, How to tune SQL queries, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, Spools in Execution Plan. Are they bad?, Spools in Execution Plan?, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL Complex Queries, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, sql performance tuning interview questions, sql performance tuning tips, SQL Queries asked in interviews, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL Questions, SQL Server, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, SQL SERVER Interview questions and answers for experienced, sql server interview questions and answers for net developers, SQL SERVER Interview questions for experienced, SQL SERVER Interview questions pdf, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL SERVER Tips, SQL SERVER Tricky questions, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, SQL Tricky question, SQL Tuning Overview, SQL Tutorial, T-SQL, T-SQL Interview questions, T-SQL Programming, T-SQL Server Interview Questions, T-SQL Tutorial, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, TOP 100 SQL SERVER INTERVIEW QUESTIONS, Top 50 SQL Server Questions & Answers, Tough PL SQL Interview Questions, Tough SQL Interview Questions, Tough SQL Queries, Tough SQL Queries Interview Questions, Tough SQL Questions, Tricky SQL Interview Questions, Tricky SQL Interview Questions and answers, Tricky SQL Queries for Interview, Tricky SQL SERVER Interview Questions and answers
Spools in Execution Plan. Are they bad?
So what is a Spool?
1. Spool is a physical operator that shows up in the execution plan.
2. Spool operator effectively saved an intermediate result to the tempdb for further usage.
3. Consider Spools are a cache in the query processor.
How they are implemented?
Spools are implemented as hidden tables in tempdb. Means you get all of your data in tempdb and then queries it. Query optimizer uses spools when it thinks that it’s better to put data in the temp table rather than hitting a table again and again using seeks and scans. So what it does is, it picks data from the table and saves that in the temp table as a hidden table and uses it whenever query optimizer requires it.
Note – Please note that this hidden table is not same as temp table. Consider this as a hidden table (Work table) for a particular query.
Why we have spools in execution plan?
• This is because you didn’t create proper indexes perhaps. Example Unique key, Primary Key, etc.
• You didn’t provide correct information to the query processor about the uniqueness of your data.
• Sometimes even you have unique key or primary key on your table, optimizer losses it along the way due to the behaviour of various iterators in the execution plan. We can bring this case by using keyword distinct to tell the optimizer that the data is unique and it doesn’t have to cache.
• Recursion while updating data( The Halloween problem )
Are spools bad?
1. Well Optimizer uses spools as a performance optimization technique. So effectively we cannot say it a bad or good.
2. Consider a scenario where you have pick all data from a physical table and put that into tempdb and then optimizer queries it, and again pick another table put that in the tempdb and then queries, Now will that a fast query? probably not. So spools are not good in most of the cases. I will explain scenarios like how we can identify bad things like spools and how to avoid them in the upcoming posts.
Types of Spools
In the upcoming posts, I will explain different types of spools and how we can avoid then(if possible).
I hope you have enjoyed the article. Thanks for reading !