• 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

Monthly Archives: June 2015

What are the internal methods of Physical Operators? How they work?

30 Tuesday Jun 2015

Posted by Pawan Kumar Khowal in SQL Performance Tuning

≈ 1 Comment

Tags

26 performance tuning questions and solutions, Close(), GetNext(), How to tune SQL queries, Init(), Internal operator methods, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, Methods of Physical Operators, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, 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 interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL SERVER Interview questions, 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 Tuning Overview, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, What are the internal methods of Physical Operators?, What are the internal methods of Physical Operators? How they work?


What are the internal methods of Physical Operators? How they work?

Download PDF- Methods of Physical Operators

All the operators present in the execution plan are physical operators. We can see the physical operation by hovering over each operator in the execution plan.

Physical Operators 1

Picture above showing example of physical operators in an execution plan

Physical Operators 2

For example- Picture showing how each operator requests rows and how rows are returned. Please note that each operator asks for a row one at a time. So works like one record come at a time and goes out at a time.

All operators used in execution plans, implement three methods. They are
1. Init()
2. GetNext()
3. Close()

Init() method-

This is the first method for each physical operator.

It does following steps.
a. It initializes the physical operator.
b. It sets up the required data structures.
c. Some operators can receive more than one input, so, these inputs will be processed at the Init() method. The concatenation is one example of these operators.

GenNext() method-

a. This operator requests next record
b. It can have zero or more rows
c. It is responsible for setting Actual Rows property in the execution plan. Example below-

Physical Operators 3

Close() method-

a. It is called once for each physical operator. It cleans up the things and shut down the operator.

Summary

So all in all what we have each operator asks for a row from his next operator and sucks it through. It works from left to right and the data flows from right to left.

That’s all folks; I hope you’ve enjoyed learning about physical operators and their internal methods, and I’ll see you soon with more “Performance Tuning” articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

What controls an Index Scan to read all pages from leaf level? Or An Index scan operator always reads all pages from leaf level?

30 Tuesday Jun 2015

Posted by Pawan Kumar Khowal in SQL Performance Tuning

≈ Leave a comment

Tags

26 performance tuning questions and solutions, How to tune SQL queries, Interview questions for SQL Server Performance Tuning, Is a Index Scan always a Index Scan?, Looking for SQL Optimization Interview Questions, Or An Index scan operator always reads all pages from leaf level?, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, 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 interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL SERVER Interview questions, 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 Tuning Overview, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, What controls an Index Scan to read all pages from leaf level?


What controls an Index Scan to read all pages from leaf level? Or An Index scan operator always reads all pages from leaf level?

Download PDF – What controls an Index Scan to read all pages from leaf level? Or An Index scan operator always reads all pages from leaf level?

In continuation in the performance series, let’s check whether an index scan operator always reads all the pages from the leaf level or not. Well, not always. It depends on the query you have written. If you are using TOP, MAX or Min in your query then your query will not read all the pages. These operators control the index scan operator and once their requirement is fulfilled, then they just says that the index scan just go away I am done. This is like this because your logic flows from left to right and your data flow from right to left. Let’s go through some of the examples and try to understand what’s going on behind the scenes.

Let’s check out a query with TOP Operator

--

USE AdventureWorks2012
GO

SELECT TOP 5 DepartmentID, Name, GroupName, ModifiedDate 
	FROM
		 [HumanResources].[Department]


--

Now add the actual execution plan and execute the query. Below is what we got. By the ways in this table ([HumanResources].[Department]) we have 16 rows.

WhatControlsaScan1

Now what happens internally here Select operator asks do you have any row for me, Top says that I don’t have let me ask scan operator, Hey scan operator do you have any rows for me. Now the scan operator says yes I do have row for me. Now in our query top expression is 5, now when the top operator consumes 5 rows it says hey scan I don’t need any more rows, just go away and breaks the execution and doesn’t return any more rows to the select operator. Now this means our query execution is done and we got 5 rows as output.

Okay one more example let’s check out a query with MIN & MAX Operator-

WhatControlsaScan2

This second query returns the minimum and the maximum of the column TransactionID. TransactionID column has a Clustered Key column on it. Let’s examine the execution plan. Here we have clustered index scan 2 times only to retrieve the maximum and minimum transaction id from transaction history table.

The explanation given for the top query applies here also. Top operator here consumes first row from the forward clustered index scan in case of minimum transaction id and first rows from a backward clustered index scan for maximum value, After that we are using nested loop join to join these values and then a stream aggregate. The stream aggregate is used to group rows by one or more columns and used to calculate aggregation expression.

Summary

Scan is not really always a complete scan in the execution plan. Operators like Top, Min and Max can restrict the full scan of the table.

That’s all folks; I hope you’ve enjoyed learning this article, and I’ll see you soon with more “Performance Tuning” articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

Implicit Conversions are Bad for Performances? Why?

26 Friday Jun 2015

Posted by Pawan Kumar Khowal in SQL Performance Tuning

≈ 1 Comment

Tags

26 performance tuning questions and solutions, Aaron Bertrand : Bad habits to kick : choosing the wrong data type, and Residuals, Beware of Implicit Conversions | Database Performance, Convert Implicit and the related performance issues with SQL Server, How to tune SQL queries, Identifying and Correcting SQL Server Implicit Conversion, Implicit Conversions, Implicit Conversions - MSDN Blogs, Implicit Conversions are Bad for Performances, Implicit Conversions are Bad for Performances? Why?, Implicit Conversions that cause Index Scans, Interview questions for SQL Server Performance Tuning, Is CONVERT_IMPLICIT Better Than CONVERT?, Join Performance, Looking for SQL Optimization Interview Questions, Mini-Tip #10 - Implicit vs. Explicit Conversion, Performance Effects of SQL Server Implicit Conversions, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, 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 interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL SERVER Interview questions, 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 Tuning Overview, The Seven Sins against TSQL Performance, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, Type casting impact over execution performance in C#


Implicit Conversions are Bad for Performances? Why?

Pawan Kumar Khowal – Why Implicit Conversions are bad for Performances_NoCopy

What is Implicit Conversion?

When you write your T-SQL queries, you often write where clause and compare column values with expressions and constants. Now if these things are NOT of same data type then SQL Server will NOT throw any error for it but internally it will implicitly convert one of the data type to match with the other one. This is called Implicit Conversion. Common places where we see implicit conversions are where and from clause.

Pawan Kumar Khowal - Implicit Conversion

Pic – Implicit Conversion

You can see the Implicit Conversion in execution plan by hovering over the warning over operators (Like we have on Select Operator). Well Implicit conversion is NOT a good thing in the execution plan, and can lead to excessive CPU use.

In some cases, converting from one type to another may cause a loss of precision also. Please check out the chart showing all the data types and their conversions at http://msdn.microsoft.com/en-us/library/ms187928.aspx.

Now the million dollars question why Implicit Conversion is bad?

It is bad because in this case we may get a sub optimal plan. Let’s go through an example of Implicit Conversion in a where clause. Before that let’s first create a table called Customers, insert some data and create some indexes on it. In this example we are comparing Varchar value to an NVarchar value.

--

--Create a Sample table

CREATE TABLE Customers
(
	 ID INT
	,NAME VARCHAR(35)
)
GO

--Insert couple of data

INSERT INTO Customers VALUES (1,’Ramesh’),(2,’Ganesh’),(3,’Isha’),(4,’Sharlee’),(5,’Satya’),(6,’Rajesh’)

--Insert some more rows using a number table

INSERT INTO Customers
SELECT t.number , ‘Rims’
	FROM master..spt_values t
CROSS APPLY
	(SELECT Number FROM master..spt_values q WHERE q.number = t.number ) r
WHERE t.Number between 1 and 5000

--

Create some indexes on the customers table.

--

CREATE CLUSTERED INDEX Ix_ID ON Customers(ID)
CREATE NONCLUSTERED INDEX Ix_Name ON Customers(Name)

--

Now we execute below query and check out its execution plan.

--

SELECT ID,Name FROM Customers WHERE Name = N’Satya’

--

Pawan Kumar Khowal - Implicit Conversion1

In the above query we are reading complete leaf level pages using nonclustered index scan even if we have a nonclustered index on Name column and on top of that we are getting an implicit conversion so all in all we can say that our query is not performing well.

Now the question is why the optimizer is converting Name from Varchar data type to Nvarchar data type. This is because of data Type Precedence. When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.

Now let’s rewrite our query.

--

SELECT ID, Name FROM Customers WHERE Name = ‘Satya’

--

Please note that we have just removed the character N from our old query. Now let’s execute both the queries and check their execution plans.

Pawan Kumar Khowal - Implicit Conversion2

Ok so our old query is taking 81% cost and the second query is taking 19% of the cost and we have an index seek in 2nd query compared to index scan in the first query. So it is clearly evident that due to implicit conversion the cost of our query grows from 19% to 81% and also notes that we are reading complete table which is not necessary since in our case we have a nonclustered index on LastName column. All in this entire means our query is 4 times more scalable.

Summary

Now if you have a proper database design in place and if you prevent implicit conversions then your queries will perform better as less CPU and IO usage will be there. Basically you got to understand how your query optimizer works and sometimes if you can help him a little bit then you application can perform in an excellent manner. Now even you have a do a conversion do it explicitly so that SQL Server don’t have to perform it internally for you.

That’s all folks; I hope you’ve enjoyed learning about how we can eliminate implicit conversions for performance, and I’ll see you soon with more “Performance Tuning” articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

How to find latest order Id from Orders table for each product? (Top n orders for each product) Let’s write a better query.

25 Thursday Jun 2015

Posted by Pawan Kumar Khowal in SQL Performance Tuning

≈ Leave a comment

Tags

26 performance tuning questions and solutions, 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, 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 interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL SERVER Interview questions, 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 Tuning Overview, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases


How to find latest order Id from Orders table for each product? (Top n orders for each product) Let’s write a better query.

Download PDF – How to find latest order Id from Orders table for each product? (Top n orders for each product) Let’s write a better query.

Let’s say I wanted to find out the maximum sales order id for each project. Connect to Adventure works 2012 database. Now for that let’s say we wrote below query.


--

SELECT s.ProductID,MAX(SalesOrderID)
FROM [Sales].[SalesOrderDetail] s
GROUP BY s.ProductID

--

Well the above query works fine and returns 266 records. Now let’s examine the execution plan. Here we have a nonclustered index scan and a stream Aggregate. The Stream Aggregate is used to group some rows by one or more columns, and to calculate any aggregation expressions that are specified in the query. The common types of aggregation are: SUM, COUNT, AGV, MIN, and MAX.

Pawan Kumar Khowal - BadPlan1

Now problem with approach is that we are reading all the pages from leaf level and if we have million or billion row this approach will slow down the performance of the query. A more appropriate and better approach is given below.


--

SELECT p.ProductID,tr.SalesOrderID
FROM [Production].[Product] p
CROSS APPLY
	(
	  SELECT MAX(s.SalesOrderID) SalesOrderID
	  FROM [Sales].[SalesOrderDetail] s
	  WHERE s.ProductID = p.ProductID
	) tr

--

Now let’s put both the queries in the query window and execute. Let’s compare the execution plan and the cost taken by each query.

Pawan Kumar Khowal - Plan2

What we are doing in the second query is we are reading all product ids from products master table and then for each product id we are hitting orders table to find out the latest salesorderid which works perfectly in our case since we need only 1 salesorderid per product that too we are getting with the help of nonclustered index seek. Also check the cost each query took. The first query is taking 82% of the most and the second query is taking 18% cost only. Thus it is clearly evident that second query excels over first one.

Summary

Always execute your query and check what is there in the execution plan and see if you can tweak the query to get better performance. Apply operator is one of the most efficient tool to improve your queries.

When we are checking things for performance always try to find out these small things. These can help you queries performance greatly. We are reducing I/O by reading less data pages.

That’s all folks; I hope you’ve enjoyed learning about how we can write better that will perform better, and I’ll see you soon with more “Performance Tuning” articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

How to properly read an Execution Plan/Query Plan?

25 Thursday Jun 2015

Posted by Pawan Kumar Khowal in SQL Performance Tuning

≈ Leave a comment

Tags

26 performance tuning questions and solutions, How to properly read an Execution Plan/Query Plan?, How to properly read an Execution Plan?, How to read an Execution Plan?, 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, SQL Complex Queries, SQL Execution plan, 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 Query Optimizer, SQL Query Tuning or Query Optimization, SQL SERVER Interview questions, 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 Tuning Overview, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases


How to properly read an Execution Plan/Query Plan?

Download PDF – How to properly read execution plan?

Data flows from RIGHT (TOP) to LEFT in the query plan. That is it data centric view of the query plan.

Logic in the query plan flows from LEFT to RIGHT. This is logic centric view of the query plan.

Well there is nothing like correct or incorrect way of reading the plan. It’s just that we have two different ways of reading the plan.

Pawan Kumar Khowal - How to properly read execution plan

All the operators used in the execution plan are called physical operators.

Let’s go through an example. We know that select and top operator cannot have rows from their own. They are dependent on Clustered Index scan. So when the execution starts the “Select” operator asks the “Top” operator that hey do you have any rows for me, Top operator says that I don’t have rows let me ask “Clustered Index Scan”. Hey “Clustered Index scan” do you have any rows for me and “Clustered index scan” says yes I have rows for you.

That is how the logic flows from left to right and data flows from right to left.

Summary

By reading the execution plan both the ways we can easily track down issues and bottlenecks very quickly. Basically we will easily understand what’s going on in the execution plan.

That’s all folks; I hope you’ve enjoyed learning about how we can read execution plan more effectively and I’ll see you soon with more “Performance Tuning” articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email
← Older posts

Blog Stats

  • 1,074,522 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 2015
M T W T F S S
1234567
891011121314
15161718192021
22232425262728
2930  
« May   Jul »

Top Articles

  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • 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.