Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Execution Plan Expand / Collapse
Author
Message
Posted Tuesday, January 10, 2012 7:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:32 PM
Points: 209, Visits: 528
I executed this query and checked the execution plan and it is showing segment and sequence project. What does SQL Server do in Segment and Sequence Project? I mean what happens internally to give the result.

SELECT
ROW_NUMBER() OVER (
ORDER BY (select 7/3))as columnn
FROM

( VALUES
(1),(1),(1),(1),('1'),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
) AS V (n)


Thanks...
Post #1233678
Posted Tuesday, January 10, 2012 8:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:02 PM
Points: 20,862, Visits: 32,893
How about saving your actual execution plan and uploading it to this thread? That would help us a lot.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1233689
Posted Tuesday, January 10, 2012 11:36 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, November 21, 2014 10:47 AM
Points: 656, Visits: 268
From MSDN:

Segment http://msdn.microsoft.com/en-us/library/ms180774(v=SQL.100).aspx] is a physical and a logical operator. It divides the input set into segments based on the value of one or more columns. These columns are shown as arguments in the Segment operator. The operator then outputs one segment at a time.


The Sequence Project http://msdn.microsoft.com/en-us/library/ms187041(v=SQL.100).aspx] operator adds columns to perform computations over an ordered set. It divides the input set into segments based on the value of one or more columns. The operator then outputs one segment at a time. These columns are shown as arguments in the Sequence Project operator.

Microsoft SQL Server supports four types of functions: RANK, DENSE_RANK, ROW_NUMBER, and NTILE. Sequence Project will generate plans that have a Sequence Project and generally two segments.

Sequence Project is a physical and a logical operator.



Yeah, I know, it helps a lot

Jan Studenovsky
Post #1233738
Posted Tuesday, January 10, 2012 11:46 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 9,928, Visits: 11,207
nirnaykhatri (1/10/2012)
What does SQL Server do in Segment and Sequence Project? I mean what happens internally to give the result?

I wrote about the internals of Segment and Sequence Project here:

http://sqlblog.com/blogs/paul_white/archive/2010/07/28/the-segment-and-sequence-project-iterators.aspx

You might also find these entries interesting:
http://sqlblog.com/blogs/paul_white/archive/2010/07/28/the-segment-top-query-optimisation.aspxhttp://sqlblog.com/blogs/paul_white/archive/2010/07/28/partitioning-and-the-common-subexpression-spool.aspx
http://sqlblog.com/blogs/paul_white/archive/2010/07/28/cool-query-plan-transformation-of-the-day.aspx




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1233741
Posted Tuesday, January 10, 2012 11:52 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 9,928, Visits: 11,207
Lynn Pettis (1/10/2012)
How about saving your actual execution plan and uploading it to this thread? That would help us a lot.

The execution plan looks like this:


An example actual execution plan is also attached.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi


  Post Attachments 
Forum.png (133 views, 8.81 KB)
ExecutionPlan.sqlplan (4 views, 4.63 KB)
Post #1233743
Posted Wednesday, January 11, 2012 9:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:32 PM
Points: 209, Visits: 528
Thanks Paul for educating me

I am new to this website.....I just wanted to know how to upload pics and stuff here just like you uploaded yesterday?

Thanks...
Post #1234516
Posted Thursday, January 12, 2012 12:39 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 9,928, Visits: 11,207
nirnaykhatri (1/11/2012)
I am new to this website.....I just wanted to know how to upload pics and stuff here just like you uploaded yesterday?

When you are writing a post, there is an Edit Attachments button in the Post Options section below where you enter the text of your message. This allows you to create attachments quite easily, and is all most users ever need. If you want to make the image appear in-line with the post, it's a bit harder: You have to get the address of the image you attach (right click on the uploaded image) and enter that address between [ img ] and [ /img ] tags (without the spaces).




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1234548
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse