Tag Archives: msbi skills

Execution Plan Operator – Properties, Types – Blocking / Non-Blocking?

05 Sunday Jul 2015

Posted by in SQL Performance Tuning

≈ 1 Comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


Execution Plan Operator – Properties, Types – Blocking / Non-Blocking?

Download PDF – Physical Operator Properties – Blocking & Non Blocking

Notes only

R – http://blogs.msdn.com/b/craigfr/archive/2006/06/19/637048.aspx

In this post, I’ll discuss properties of iterators and non-blocking vs. blocking. Note that these things can affect performance of the query.

SQL Server has two kinds of execution plan operators. They are

• Blocking
• Non-blocking ( Stop-and-go )

Non-blocking iterator get rows in and sends rows out at the same time (in the GetRow method).

For instance, the Nested Loops iterator gets rows from its outer input and sends the matching rows on to the next iterator without having to wait for all of the input rows.

Compute scalar iterator is also an example of a non-blocking iterator. It read an input row, computes a new output value using the input values from the current row, immediately outputs the new value, and continues to the next input row.

Blocking iterator – Consume all of the input rows (in the open method) before it can send rows out to the next iterator.

For instance sort is a blocking operator. It has to consume all of the input rows and sort them before it can send them on to the next iterator.

Blocking operators may or may not consume memory. It is also possible that iterators have phases and one phase is blocking and another one is non-blocking. Example of one such iterator is Hash Join. In this iterator build phase is blocking and probe is pipelined.

A blocking operator may lead to concurrency issues and reduce performance. Non-blocking iterators are good for OLTP systems.

NOTE – Microsoft has not released a definitive list about blocking and non-blocking operators. You can mostly check which of the operators within a plan are blocking or not by reading their descriptions.

Also note that all iterators require some amount of memory to perform calculations and state management. Every query will have to acquire some memory to execute the query. This is called Memory Grant. You can view this memory grant in the first operator that Select, Update or Insert. Now if the server is executing other queries and does not have enough memory to grant, our query will have to wait until the grant is available. I will explain Memory grant in detail in coming up articles are this is a very big topic.

Memory Grant

Now if the estimates are bad or we have not conveyed correct information to the optimizer and memory consuming operator requests a small amount of memory it will lead to spill data to disk during execution. Spills are not good for query performance due to extra I/O overhead.

Major memory consuming iterators are Sort, hash join and hash aggregate. Sort spills are very famous. We can track this using warning in the execution plan or using Sort Warnings in SQL profiler.

Summary

So all in all we have try to remove the blocking and memory consuming operators for OLTP systems to get good query performances.

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

Thanks!

Pawan Kumar Khowal

MSBISKills.com

T-SQL Query | [ The Multiple Join Single Query Puzzle ]

04 Monday May 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ The Multiple Join Single Query Puzzle ]

Puzzle Statement

  1. We have a table named Orders1 , dates and parts.
  2. You have join all these tables and return part information , date and count.
  3. Count is how many orders we have for this partid on this idate
  4. For details please check out the sample input and expected output

Orders

Sample Input

Parts

partid
1
2

Dates

idate
01-01-2008
02-01-2008

Orders1 Table

partid idate
1 01-01-2008
1 02-01-2008
2 01-01-2008

Expected Output

partid idate counts
1 01-01-2008 1
1 02-01-2008 1
2 01-01-2008 1
2 02-01-2008 0

Rules/Restrictions

  • Your solution should be should use “SELECT” statement or “CTE”
  • Your solution should be generic in nature.
  • No temp tables , No Ctes , No sub selects are allowed. Only Single Select required
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script Use the below script to generate the source table and fill them up with the sample data.

—


create table Dates(idate datetime)
insert into dates select '1/1/2008'
insert into dates select '1/2/2008'

create table parts(partid int)
insert into parts select 1
insert into parts select 2

create table orders1(partid int,idate datetime)
insert into orders1 select 1,'1/1/2008'
insert into orders1 select 1,'1/2/2008'
insert into orders1 select 2,'1/1/2008'

—

Update May 14 | Solution


--

/************   SOLUTION 1    | Deepak Sharma     ****************/



SELECT p.partid,d.idate,  COUNT(o.idate) Counts FROM parts p cross join dates d left join orders1 o ON d.idate = o.idate and p.partid = o.partid
GROUP BY p.partid, d.idate ORDER BY 1


/************   SOLUTION 2    | Pawan Kumar Khowal     ****************/

SELECT p.partid, d.idate,COUNT(o.partid) OVER (PARTITION BY o.partid , o.idate ORDER BY (SELECT NULL)) counts FROM orders1 o
FULL OUTER JOIN Dates d CROSS JOIN parts p ON d.idate = o.idate AND p.partid = o.partid
ORDER BY p.partid




--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

Http://MSBISkills.com

Pawan Kumar Khowal

T-SQL Query | [ The Bitwise AND Puzzle ]

04 Monday May 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ The Bitwise AND Puzzle ]

Puzzle Statement

1. We have a table named BitwiseAnd.
2. Here we have Id and Value columns, now iwe have group by column Id and Bitwise AND all the values in that group Id.
3. For Id = 1 there are three values so we have calculate the Bitwise And like 10 & 1 & 13 ; Output for this is 2
4. For details please check out the sample input and expected output

Bitwise

Sample Input

Id Value
1 10
1 3
1 14
3 13
4 1
4 12

Expected Output

Id Bitwise
1 2
3 13
4 0

 

Rules/Restrictions

  • Your solution should be should use “SELECT” statement or “CTE”
  • Your solution should be generic in nature.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script Use the below script to generate the source table and fill them up with the sample data.

—


CREATE TABLE BitwiseAnd 
(
	 Id INT
	,Value INT
)
GO

INSERT INTO BitwiseAnd 
VALUES 
(1,10),
(1,3),
(1,14),
(3,13),
(4,1),
(4,12)

—

Update May 14 | Solution


--

/************   SOLUTION 1         ****************/



;WITH CTE AS
(
   SELECT * , ROW_NUMBER() OVER ( PARTITION BY Id ORDER BY %%Physloc%% ) rnk FROM BitwiseAnd
)
,CTE1 AS
(
   SELECT Id , Value final , rnk FROM CTE WHERE rnk = 1
   UNION ALL
   SELECT c2.Id , c2.final & c1.Value final , c2.rnk + 1 FROM CTE1 c2 INNER JOIN CTE c1 ON c1.Id = c2.Id AND c1.rnk = c2.rnk 
)
SELECT x.Id , Final Bitwise
FROM CTE1 x
WHERE rnk = (SELECT MAX(rnk) FROM CTE1 y WHERE x.Id = y.Id )
ORDER BY Id




--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

SQL Server Query – 2 | [ Finding Next Value Puzzle ]

19 Thursday Jul 2012

Posted by in SQL Concepts, T SQL Puzzles

≈ 3 Comments

Tags

, , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [Finding Next Value Puzzle] – Write a query which will extract the next value from the currentQuota for each row.

Sample Input

BusinessEntityID SalesYear CurrentQuota
275 2005 367000
275 2005 556000
275 2006 502000
275 2006 550000
275 2006 1429000
275 2006 1324000

Expected Output

BusinessEntityID SalesYear CurrentQuota NextCurrentData
275 2005 367000 556000
275 2005 556000 502000
275 2006 502000 550000
275 2006 550000 1429000
275 2006 1429000 1324000
275 2006 1324000 0

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script Use the below script to generate the source table and fill them up with the sample data.


--Create table

CREATE TABLE lag
(
 BusinessEntityID INT
,SalesYear   INT
,CurrentQuota DECIMAL(20,4)
)

GO

--Insert Data
INSERT INTO lag
SELECT 275 , 2005 , '367000.00'
UNION ALL
SELECT 275 , 2005 , '556000.00'
UNION ALL
SELECT 275 , 2006 , '502000.00'
UNION ALL
SELECT 275 , 2006 , '550000.00'
UNION ALL
SELECT 275 , 2006 , '1429000.00'
UNION ALL
SELECT 275 , 2006 ,   '1324000.00'

--Check data
SELECT BusinessEntityID,SalesYear,CurrentQuota FROM lag

Update May 14 | Solutions 



--


---------------------------------------
--Sol 1 | Pawan Kumar Khowal
---------------------------------------


;WITH CTE AS
(
	SELECT BusinessEntityID,SalesYear,CurrentQuota,ROW_NUMBER() OVER (ORDER BY %%Physloc%%) rnk	 
	FROM lag
)
SELECT    BusinessEntityID,SalesYear,CurrentQuota
		, ISNULL(( SELECT TOP 1 CurrentQuota FROM CTE c2 WHERE c1.rnk < c2.rnk ),0) NextCurrentData
FROM CTE c1


---------------------------------------
--Sol 2 | Pawan Kumar Khowal
---------------------------------------


;WITH CTE AS
(
	SELECT BusinessEntityID,SalesYear,CurrentQuota,ROW_NUMBER() OVER (ORDER BY %%Physloc%%) rnk	 
	FROM lag
)
SELECT    BusinessEntityID,SalesYear,CurrentQuota
		  ,ISNULL(LEAD(CurrentQuota) OVER (ORDER BY rnk),0) NextCurrentData		
FROM CTE c1


--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

SQL Server Query – 6 | Nth Highest Salary

03 Tuesday Aug 2010

Posted by in T SQL Puzzles

≈ 3 Comments

Tags

, , , , , , , , , , , , , , , , , ,


T-SQL Query | [Nth Highest Salary Puzzle] – Write a query which will find the Nth highest salary from the table. In this case we are finding 2nd Highest Salary

Sample Input

Name Salary
e5 45000
e3 30000
e2 49000
e4 36600
e1 58000

Expected Output

Name Salary
e2 49000

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script

Use the below script to generate the source table and fill them up with the sample data.


--Create table

CREATE TABLE NthHighest
(
 Name  varchar(5)  NOT NULL,
 Salary  int  NOT NULL
)

--Insert the values
INSERT INTO  NthHighest(Name, Salary)
VALUES 
('e5', 45000),
('e3', 30000),
('e2', 49000),
('e4', 36600),
('e1', 58000)

--Check data
SELECT Name,Salary FROM NthHighest

Update May 14 | Solution


--


---------------------------------------
--Sol 1 | Pawan Kumar Khowal
---------------------------------------

SELECT * FROM NthHighest N WHERE 1 = (SELECT DISTINCT COUNT(*) FROM NthHighest N1 WHERE N1.Salary > N.Salary )

---------------------------------------
--Sol 2 | Pawan Kumar Khowal
---------------------------------------

SELECT TOP 1 WITH TIES Name , Salary FROM NthHighest N1 
WHERE Salary IN (SELECT TOP 2 WITH TIES Salary FROM NthHighest ORDER BY Salary DESC)
ORDER BY Salary

---------------------------------------
--Sol 3 | Pawan Kumar Khowal
---------------------------------------

SELECT A.Name, B.Salary
FROM (
	SELECT MAX(Salary) Salary
	FROM NthHighest N1 
	WHERE N1.Salary != (SELECT MAX(Salary) FROM NthHighest) ) B
CROSS APPLY (SELECT NAME FROM NthHighest WHERE SALARY = B.SALARY ) A

---------------------------------------
--Sol 4 | Pawan Kumar Khowal
---------------------------------------

;WITH CTE AS
(
	SELECT * , RANK() OVER (ORDER BY SALARY DESC) rnk FROM NthHighest
)
SELECT Name, Salary FROM CTE WHERE rnk = 2


--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com