• Home
  • SQL Server
    • Articles
    • T-SQL Puzzles
    • Output Puzzles
    • Interview Questions
    • Performance Tuning
    • SQL SERVER On Linux
    • Resources
  • SSRS
    • SSRS Articles
    • Interview Questions
  • SSAS
    • SSAS Articles
    • DAX
  • SQL Puzzles
  • Interview Questions
    • SQL Interview Questions
    • Data Interview Questions
  • Python Interview Puzzles
  • New Features(SQL SERVER)
    • SQL SERVER 2017
    • SQL SERVER 2016
    • SQL SERVER On Linux
  • Social
    • Expert Exchange
      • Top Expert in SQL
      • Yearly Award
      • Certifications
      • Achievement List
      • Top Expert of the Week
    • HackerRank (SQL)
    • StackOverflow
    • About Me
      • Contact Me
      • Blog Rules

Improving my SQL BI Skills

Improving my SQL BI Skills

Tag Archives: Spools in Execution Plan?

SQL Server – Logical and Physical Operators

04 Friday Sep 2015

Posted by Pawan Kumar Khowal in SQL Performance Tuning

≈ Leave a comment

Tags

26 performance tuning questions and solutions, Avoid SQL Server functions in the WHERE clause for Performance, Avoid Using Function in WHERE Clause – Scan to Seek, Avoid Using Functions in WHERE Clause tutorial and example, Functions in the WHERE Clause, How to avoid convert function in the where clause, How to handle large number of insertions in SQL Server?, How to tune SQL queries, Increase SQL Server performance avoid using functions in Where Clause, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, Operators in SQL SERVER, 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?, Spools in SQL Server, sql - How do I avoid functions like UPPER in where clause, SQL - Spools, SQL Complex Queries, 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 and Optimization - Where CTEs are stored ?, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL Server - Logical and Physical Operators, SQL Server 2008 Query Performance Tuning Distilled, SQL Server Interview Question - How to handle large number of insertions in SQL Server?, SQL SERVER Interview questions, SQL Server Optimization - Parallel Execution Plans, 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 Query Optimization: Parallel Plans, SQL SERVER Tips, SQL Tuning Overview, SQL Where Clause Performance, T-SQL Best Practices - Don't Use Scalar Value Functions in Where Clause, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, WHERE Clause Functions: A Word of Caution, Where CTEs are stored ?</, Which one is better for performance Table Variables or Temp Tables – Only in terms of performance optimization?


SQL Server – Logical and Physical Operators

Operators describe how SQL Server executes a query or a Data Manipulation Language (DML) statement.

The query optimizer uses these operators to build an execution plan to create the result specified in the query, or to perform the operation specified in the DML statement. The query plan is a tree consisting of physical operators. You can check these operators in the execution plan.

Types of Operators are-

1. Logical Operators
2. Physical Operators

Logical Operators

Logical operators describe the relational algebraic operation used to process a statement. In other words, logical operators describe conceptually what operation needs to be performed.

Physical Operators

Physical operators implement the operation described by logical operators. Each physical operator is an object or routine that performs an operation. For example, some physical operators access columns or rows from a table, index or view. Other physical operators perform other operations such as calculations, aggregations, data integrity checks or joins. Physical operators have costs associated with them.

The physical operators initialize, collect data, and close. Specifically, the physical operator can answer the following three method calls

Usually, a logical operation can be implemented by multiple physical operators. However, in rare cases, a physical operator can implement multiple logical operations as well.

Example of Logical and Physical Operator


SELECT TOP 10 ID, FromPoint, ToPoint, Distance
FROM [dbo].[Area]

Check out the execution plan for Logical and Physical Operators

Pawan Khowal - Execution Plan - Logical and Physical Operators
Pawan Khowal – Execution Plan – Logical and Physical Operators

Right click on each operator and click properties to check out the logical and physical operations.

Related Post

https://msbiskills.com/2015/06/30/what-are-the-internal-methods-of-physical-operators-how-they-work

In the next post i will explain how a query executes in SQL Server, What are the internal steps used by Query Optimizer. Cheers, Thanks for reading !

-Pawan Khowal

MSBISkills.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

SQL Server – Table Variables & Estimated No of Rows

03 Thursday Sep 2015

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL Performance Tuning, SQL Server Interview Questions

≈ Leave a comment

Tags

26 performance tuning questions and solutions, Avoid SQL Server functions in the WHERE clause for Performance, Avoid Using Function in WHERE Clause – Scan to Seek, Avoid Using Functions in WHERE Clause tutorial and example, Functions in the WHERE Clause, How to avoid convert function in the where clause, How to handle large number of insertions in SQL Server?, How to tune SQL queries, Increase SQL Server performance avoid using functions in Where Clause, 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?, Spools in SQL Server, sql - How do I avoid functions like UPPER in where clause, SQL - Spools, SQL Complex Queries, 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 and Optimization - Where CTEs are stored ?, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL Server 2008 Query Performance Tuning Distilled, SQL Server Interview Question - How to handle large number of insertions in SQL Server?, SQL SERVER Interview questions, SQL Server Optimization - Parallel Execution Plans, 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 Query Optimization: Parallel Plans, SQL SERVER Tips, SQL Tuning Overview, SQL Where Clause Performance, T-SQL Best Practices - Don't Use Scalar Value Functions in Where Clause, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, WHERE Clause Functions: A Word of Caution, Where CTEs are stored ?</, Which one is better for performance Table Variables or Temp Tables – Only in terms of performance optimization?


Today I give you more information about table variables in SQL Server (http://msdn.microsoft.com/en-us/library/ms175010.aspx) and their statistics. Check out the table below-

Pawan Khowal - Table Variable Statistics

Pawan Khowal – Table Variable Statistics

Table variables do not have statistics. Using a table variable you can avoids the recompiles, possibly speeding up your processes. That’s regardless of the number of rows.

But, if you are then using a JOIN or a WHERE clause on a table variable, they have no statistics, so the optimizer will assume a set number of rows (1 for all versions prior to 2014 and 100 for 2014 and 2016). This is fine if you have only a few rows, but if you have more than the set number, it can lead to really bad performance caused by bad choices by the optimizer.

Related Posts on Table Variables are –

https://msbiskills.com/2015/07/09/table-variable-estimated-no-of-rows-1-no-statistics/

https://msbiskills.com/2015/06/12/which-one-is-better-temp-table-or-table-variable/

Cheers, Thanks for reading !

-Pawan Khowal

MSBISkills.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

Types of Spools in Execution Plan – Deep Dive

02 Wednesday Sep 2015

Posted by Pawan Kumar Khowal in SQL Performance Tuning

≈ Leave a comment

Tags

26 performance tuning questions and solutions, Avoid SQL Server functions in the WHERE clause for Performance, Avoid Using Function in WHERE Clause – Scan to Seek, Avoid Using Functions in WHERE Clause tutorial and example, Functions in the WHERE Clause, How to avoid convert function in the where clause, How to handle large number of insertions in SQL Server?, How to tune SQL queries, Increase SQL Server performance avoid using functions in Where Clause, 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?, Spools in SQL Server, sql - How do I avoid functions like UPPER in where clause, SQL - Spools, SQL Complex Queries, 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 and Optimization - Where CTEs are stored ?, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL Server 2008 Query Performance Tuning Distilled, SQL Server Interview Question - How to handle large number of insertions in SQL Server?, SQL SERVER Interview questions, SQL Server Optimization - Parallel Execution Plans, 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 Query Optimization: Parallel Plans, SQL SERVER Tips, SQL Tuning Overview, SQL Where Clause Performance, T-SQL Best Practices - Don't Use Scalar Value Functions in Where Clause, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, WHERE Clause Functions: A Word of Caution, Where CTEs are stored ?</, Which one is better for performance Table Variables or Temp Tables – Only in terms of performance optimization?


Types of Spools in Execution Plan – Deep Dive

Today let’s talk about types of spools in detail. The Spool operator saves an intermediate query result to the tempdb database. 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.  I have explained spool operator in details here (https://msbiskills.com/2015/07/26/spools-in-execution-plan-are-they-bad/). Today I am going to talk about the types of spools. So the types of spools are-

1. Table Spool
2. Index Spool
3. Row Count Spool
4. Window Spool
5. Eager Spool
6. Lazy Spool

Let’s first understand each of the above spool in details and then we will proceed for examples-

Table Spool

1. Table Spool is a physical operator.

2. The Table Spool operator scans the input and places a copy of each row in a hidden spool table that is stored in the tempdb database. In tempdb it is stored as a Worktable.

3. It exists only for the lifetime of the query. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.

Index Spool

1. Index Spool is a physical operator

2. The Index Spool operator scans its input rows, placing a copy of each row in a hidden spool file (stored in the tempdb database as a worktable and existing only for the lifetime of the query)

3. After this optimizer creates a Nonclustered index on the rows. This will give the optimizer to use the seeking capability of indexes to output only those rows that satisfy the SEEK:() predicate.

4. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.

Row Count Spool

1. Row Count Spool is a physical Operator

2. The Row Count Spool operator scans the input, counting how many rows are present and returning the same number of rows without any data in them.

3. This operator is used when it is important to check for the existence of rows, rather than the data contained in the rows.

4. For example, if a Nested Loops operator performs a left semi join operation and the join predicate applies to inner input, a row count spool may be placed at the top of the inner input of the Nested Loops operator. Then the Nested Loops operator can determine how many rows are output by the row count spool (because the actual data from the inner side is not needed) to determine whether to return the outer row.

Window Spool

1. Window spool is a logical and physical operator.

2. The Window Spool operator expands each row into the set of rows that represents the window associated with it.

3. Example – In a query, the OVER clause defines the window within a query result set and a window function then computes a value for each row in the window.

4. When using the default window frame (Range Unbounded preceding) window spool operator uses an on disk worktable to process the window, but it doesn’t not spill the data to tempdb.

Eager Spool

1. Eager Spool is a logical operator.

2. The Eager Spool operator takes the entire input, storing each row in a hidden temporary object stored in the tempdb database. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.

3. If rebinding is needed, the spooled data is discarded and the spool object is rebuilt by rescanning the (rebound) input.

4. The Eager Spool operator builds its spool file in an “eager” manner: when the spool’s parent operator asks for the first row, the spool operator consumes all rows from its input operator and stores them in the spool.

Lazy Spool

1. Lazy Spool is a logical operator.

2. The Lazy Spool logical operator stores each row from its input in a hidden temporary object stored in the tempdb database.

3. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.

4. If rebinding is needed, the spooled data is discarded and the spool object is rebuilt by rescanning the (rebound) input.

5. The Lazy Spool operator builds its spool file in a “lazy” manner, that is, each time the spool’s parent operator asks for a row, the spool operator gets a row from its input operator and stores it in the spool, rather than consuming all rows at once.

Some common examples where we find these kind of spools

1. Let’s start with Table and Index Spool. We have a simple recursive CTE query of a very common business problem i.e. Manager and the employee. Check out the query below-


--

;WITH CTE(EmpName , EmpId, Level,FullyQualifiedName) AS (
     Select E.EmpName, E.EmpID, 0 Level
     , Cast('.'+E.EmpName+'.' as Varchar(MAX)) FullyQualifiedName         
     From Employees E Where E.ReportsTo IS NULL
     UNION ALL
     Select E.EmpName, E.EmpID, c.Level + 1 , c.FullyQualifiedName+'.'+E.EmpName+'.' FullyQualifiedName  
     From Employees E INNER JOIN CTE c on c.EmpID = e.ReportsTo 
)
SELECT SPACE(LEVEL*4) + H.EmpName Hierarchy,SPACE(LEVEL*4) + CAST(H.EmpID AS VARCHAR(MAX)) Hierarchy , * FROM CTE H 
ORDER BY H.FullyQualifiedName

--

Let’s go through the execution plan. Here we have 2 spool operators – Table and index spool

Pawan Khowal - Table & Index Spool

Pawan Khowal – Table & Index Spool

2. Window Spool


--

SELECT * , SUM(ID) OVER (PARTITION BY ID ORDER BY FromPoint) rnk FROM dbo.Area

--

Let’s go through the execution plan. Here we have 1 spool operator – Window Spool

Pawan Khowal - Window Spool

Pawan Khowal – Window Spool

3. Row Count Spool


--

SELECT ID, Vendor
  FROM Products 
 WHERE NOT EXISTS(SELECT 1
                    FROM Products 
                   WHERE Dates = '20050101'
                     AND Quantiy > 10)
OPTION(MAXDOP 1)
--

Let’s go through the execution plan. Here we have 1 spool operator – Row Count Spool

Pawan Khowal - Row Count

Pawan Khowal – Row Count

Cheers, Thanks for reading !

-Pawan Khowal

MSBISkills.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

SQL Query Performance & Optimization – What you should look in execution plans?

05 Wednesday Aug 2015

Posted by Pawan Kumar Khowal in SQL Performance Tuning

≈ 1 Comment

Tags

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, Execution plan, execution plan operators, 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, 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


SQL Query Performance & Optimization – What you should look at execution plan?

So I haven’t posted anything, kind of busy with some other stuff. Basically last week was very painful in terms of professional achievements. Moving on today let’s talk about what we should look at in execution plan. The question is very common in technical interviews. What the interviewer normally asks is you have got a slow query, what you do to make it fast or how do you tune that query. So I am here jotting down the top 10 things we should look at while analyzing the query plan. Also please note that by looking at the operators you will not get the solution but you will know where your problem lies. Nothing is right or wrong in the execution plan. It’s just that sometimes optimizer does not gives us optimal plan and it may be due to the following.

1. We are unable to provide the updates statistical information.
2. Or optimizer losses it along the way due to other operations
3. Or we wrote extremely bad query
4. Or maybe something else 

Please note that if your query is taking too long to complete go with the estimated plan.

So the things you should in the execution plan are-

1. First Operator –

Always start with checking first operator. First operator can be Select, Insert and Delete. Please note that we don’t have Update as the first operator. Right click on the first operator to get tons of properties and information. Major properties you should check here are given below-

a. Check optimization level – I think it should be FULL.
b. Reason for Early Termination of Statement – I think it should be “Good Enough Plan Found”.
c. Parameter compiled value and Parameter runtime value – This will help in finding parameter sniffing issues.
d. Estimated Sub tree cost

Example


--

CREATE PROC FindCounts
(
	 @Female VARCHAR(1)
	,@Male VARCHAR(1)
)
AS
	SELECT c1.DeptID, COUNT(*) TotalEmpPerDept
		,SUM( CASE WHEN EmpGender = @Male THEN 1 ELSE 0 END ) MaleCount
		,SUM( CASE WHEN EmpGender = @Female THEN 1 ELSE 0 END ) FemaleCount
	FROM [dbo].[CountMaleFemale] c1
	GROUP BY DeptID

--


CHECKPOINT
DBCC FREEPROCCACHE
GO

EXEC FindCounts 'F', 'M'
GO

Execution Plan

First Operator in Execution Plan

Pawan Khowal – First Operator in Execution Plan

First Operator in Execution Plan - Properties

Pawan Khowal – First Operator in Execution Plan – Properties

2. High cost operators –

Okay we should look at physical operations with high cost. What is the most expensive piece of this query? E.g. what is taking up 95% of the cost is. 95% is where your problem is. So always keep an eye on the most costly operators.

So one may say that this cost is only based on the estimates and it is same in estimated and actual execution plan. We don’t have any mechanism to calculate the actual cost. So we use estimated numbers only. We should still use them. These are accurate more often than not and can quickly inform you to the possible source of the problem.

Example


SELECT c1.DeptID, COUNT(*) TotalEmpPerDept
	,SUM( CASE WHEN EmpGender = 'M' THEN 1 ELSE 0 END ) MaleCount
	,SUM( CASE WHEN EmpGender = 'F' THEN 1 ELSE 0 END ) FemaleCount
FROM [dbo].[CountMaleFemale] c1
GROUP BY DeptID

Execution Plan

Pawan Khowal - High Cost Operators in Execution Plan

Pawan Khowal – High Cost Operators in Execution Plan

In the above example index scan is the most costly operation. Here I am just showing the costly operation there is nothing incorrect about the query.

3. Warnings

This means exclamation marks. Exclamation marks are bad. Hover over the execution plan, it may or may not be bad. But always check that because it’s an exclamation mark.

Execute the below query on AdventureWorks2014


SELECT
    ProductID, Name, ProductNumber, MakeFlag, FinishedGoodsFlag, Color, SafetyStockLevel, ReorderPoint
  , StandardCost, ListPrice, Size, SizeUnitMeasureCode, WeightUnitMeasureCode, Weight, DaysToManufacture
  , ProductLine, Class, Style, ProductSubcategoryID, ProductModelID
  , SellStartDate, SellEndDate, DiscontinuedDate FROM [dbo].[bigProduct]
WHERE CAST(ProductID AS NVARCHAR(10)) = N'1001'

Warning from properties-

Type conversion in expression (CONVERT(nvarchar(10),[AdventureWorks2014].[dbo].[bigProduct].[ProductID],0)) may affect “CardinalityEstimate” in query plan choice, Type conversion in expression (CONVERT(nvarchar(10),[AdventureWorks2014].[dbo].[bigProduct].[ProductID],0)=[@1]) may affect “SeekPlan” in query plan choice

Execution Plan of above query

Pawan Khowal - Warnings in Execution Plans

Pawan Khowal – Warnings in Execution Plans

4. Thick lines / Fat pipes

Here always look at the think lines from the execution plan, you have seeks and scans with a huge fat pipe with millions of rows. So this can be the source of your problem is. Look at the estimated and actual number of rows. If they are way of then that might be the source of your problem. So always keep you data size as small as possible. Try to reduce the data size as early as possible.

Example

–Note – this is a sample query. Don’t use * in your queries.


SELECT
	*
FROM bigTransactionHistory  

Pawan Khowal - Thick Lines or Fat Pipes in Execution Plan

Pawan Khowal – Thick Lines or Fat Pipes in Execution Plan

5. Scans

Scans are basically are of three types – Table scan, Clustered Index Scan and Non Clustered Index Scan. Scan means you are reading all data from your table. Meaning if you have billions of rows then we might have a problem there. So you must need to check what’s going to on there. Please understand that nothing is bad or good in execution plans, on the same lines scans are not necessarily bad for performance. In general try to reduce the dataset as soon as possible and try to work over smaller datasets.

Scans are bad if seek is possible and you have written bad query and because of that you are getting a scan.

Example

–Check below queries


SELECT c1.EmpId, c1.EmpName
FROM [dbo].[CountMaleFemale] c1
WHERE LEFT(EmpName,1) = 'P'

SELECT c1.EmpId, c1.EmpName
FROM [dbo].[CountMaleFemale] c1
WHERE EmpName LIKE 'P%'

Execution Plans – In one case we are getting scan and in one case we are getting seek.

6. Actual no of rows and Estimated number of rows

Ok this is one is really important. If we failed to inform the proper information about the data to the optimizer we will get huge difference in actual and estimated number of rows. This information you can get by hovering over the iterator using tool tip or by using properties of iterators.

Example


SELECT c1.EmpId, c1.EmpName
FROM [dbo].[CountMaleFemale] c1
WHERE EmpName LIKE 'P%'

Execution plan

Pawan Khowal - Actual and Estimated Number or Rows

Pawan Khowal – Actual and Estimated Number or Rows

Pawan Khowal - Actual and Estimated Number or Rows - Properties

Pawan Khowal – Actual and Estimated Number or Rows – Properties

7. Check for set options

If you are two execution plans for the same query then you need to check set options I mentioned in the First Operator. This is common if you running a query for application and executing the same query from SSMS and for both queries you are getting different execution plans. Most of the time the issue lies with Set Options and one of them behaves badly.

Example- Right Click on First Operator

Pawan Khowal - Set Options in Execution Plan

8. Missing indexes/ Stats

These comes with a green colour and SQL Server informs us that if you create this index you can get performance improvement of this much percentage. Don’t always go for creating the index. First check the percentage impact and check how many indexes are already present for the table. My rule of thumb is 5 indexes per table.

9. Extra operations

Okay one simple example of extra operation is sort. You will get a expensive sort operator if you don’t have index on the column(s) you are ordering. Other example is spools. If optimizer thinks that its better to save data in tempdb and use it further, Spools are used for that purpose. So these extra operations can also be painful sometimes.

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 !

-Pawan Khowal

MSBISkills.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

Spools in Execution Plan. Are they bad?

26 Sunday Jul 2015

Posted by Pawan Kumar Khowal in SQL Performance Tuning

≈ 2 Comments

Tags

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?

Spools in Execution Plan

Spools in Execution Plan

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

Types of Spools in Execution Plans

Types of Spools in Execution Plans

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 !

-Pawan Khowal

MSBISkills.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

Blog Stats

  • 1,097,620 hits

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,131 other subscribers

Pawan Khowal

502 SQL Puzzles with answers

Achievement - 500 PuzzlesJuly 18, 2018
The big day is here. Finally presented 500+ puzzles for SQL community.

200 SQL Server Puzzle with Answers

The Big DayAugust 19, 2016
The big day is here. Completed 200 SQL Puzzles today

Archives

June 2023
M T W T F S S
 1234
567891011
12131415161718
19202122232425
2627282930  
« Oct    

Top Articles

  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.files.wordpress…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…

Archives

  • October 2020 (29)
  • September 2018 (2)
  • August 2018 (6)
  • July 2018 (25)
  • June 2018 (22)
  • May 2018 (24)
  • April 2018 (33)
  • March 2018 (35)
  • February 2018 (53)
  • January 2018 (48)
  • December 2017 (32)
  • November 2017 (2)
  • October 2017 (20)
  • August 2017 (8)
  • June 2017 (2)
  • March 2017 (1)
  • February 2017 (18)
  • January 2017 (2)
  • December 2016 (5)
  • November 2016 (23)
  • October 2016 (2)
  • September 2016 (14)
  • August 2016 (6)
  • July 2016 (22)
  • June 2016 (27)
  • May 2016 (15)
  • April 2016 (7)
  • March 2016 (5)
  • February 2016 (7)
  • December 2015 (4)
  • October 2015 (23)
  • September 2015 (31)
  • August 2015 (14)
  • July 2015 (16)
  • June 2015 (29)
  • May 2015 (25)
  • April 2015 (44)
  • March 2015 (47)
  • November 2012 (1)
  • July 2012 (8)
  • September 2010 (26)
  • August 2010 (125)
  • July 2010 (2)

Article Categories

  • Analysis Services (6)
    • DAX (6)
  • Data (2)
    • Data warehousing (2)
  • Integration Services (2)
  • Magazines (3)
  • Python (29)
  • Reporting Services (4)
  • SQL SERVER (820)
    • Download SQL Interview Q's (212)
    • SQL Concepts (323)
    • SQL Performance Tuning (155)
    • SQL Puzzles (331)
    • SQL SERVER 2017 Linux (6)
    • SQL Server Interview Questions (308)
    • SQL SERVER Puzzles (332)
    • T SQL Puzzles (547)
    • Tricky SQL Queries (439)
  • UI (30)
    • ASP.NET (5)
    • C# (13)
    • CSS (9)
    • OOPS (3)
  • Uncategorized (5)

Recent Posts

  • Python | The Print and Divide Puzzle October 30, 2020
  • Python | Count consecutive 1’s from a list of 0’s and 1’s October 30, 2020
  • Python | How to convert a number into a list of its digits October 26, 2020
  • Python | Validate an IP Address-IPV6(Internet Protocol version 6) October 26, 2020
  • Python | Print the first non-recurring element in a list October 26, 2020
  • Python | Print the most recurring element in a list October 26, 2020
  • Python | Find the cumulative sum of elements in a list October 26, 2020
  • Python | Check a character is present in a string or not October 26, 2020
  • Python | Check whether a string is palindrome or not October 26, 2020
  • Python | Find the missing number in the array of Ints October 26, 2020
  • Python | How would you delete duplicates in a list October 26, 2020
  • Python | Check whether an array is Monotonic or not October 26, 2020
  • Python | Check whether a number is prime or not October 26, 2020
  • Python | Print list of prime numbers up to a number October 26, 2020
  • Python | Print elements from odd positions in a list October 26, 2020
  • Python | Print positions of a string present in another string October 26, 2020
  • Python | How to sort an array in ascending order October 26, 2020
  • Python | How to reverse an array October 26, 2020
  • Python | Find un-common words from two strings October 26, 2020
  • Python | How to convert a string to a list October 26, 2020
  • Python | Find unique words from a string October 26, 2020
  • Python | Calculate average word length from a string October 26, 2020
  • Python | Find common words from two strings October 26, 2020
  • Python | Find the number of times a substring present in a string October 26, 2020
  • Python | Find maximum value from a list October 26, 2020
  • Python | How to find GCF of two numbers October 26, 2020
  • Python | How to find LCM of two numbers October 26, 2020
  • Python | How to convert a list to a string October 26, 2020
  • Python | Replace NONE by its previous NON None value October 26, 2020
  • Microsoft SQL Server 2019 | Features added to SQL Server on Linux September 26, 2018

Create a website or blog at WordPress.com

  • Follow Following
    • Improving my SQL BI Skills
    • Join 231 other followers
    • Already have a WordPress.com account? Log in now.
    • Improving my SQL BI Skills
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...
 

You must be logged in to post a comment.