Execution Plan

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Execution Plan

Execution Plan

Rate Topic

Display Mode

Topic Options

Author

Message

Confusing Queries

Posted Tuesday, January 10, 2012 7:16 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 29, 2012 2:10 PM
Points: 197, Visits: 437

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

Lynn Pettis

Posted Tuesday, January 10, 2012 8:33 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 5:20 PM
Points: 21,602, Visits: 27,428

How about saving your actual execution plan and uploading it to this thread? That would help us a lot.

Cool

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

SeaQL

Posted Tuesday, January 10, 2012 11:36 PM

SSChasing Mays

Group: General Forum Members
Last Login: Monday, April 29, 2013 6:06 PM
Points: 619, Visits: 229

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 BigGrin

Jan Studenovsky

Post #1233738

Paul White

Posted Tuesday, January 10, 2012 11:46 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 7:18 PM
Points: 10,989, Visits: 10,532

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.aspx http://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

Paul White

Posted Tuesday, January 10, 2012 11:52 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 7:18 PM
Points: 10,989, Visits: 10,532

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 (81 views, 8.81 KB)

ExecutionPlan.sqlplan (4 views, 4.63 KB)

Post #1233743

Confusing Queries

Posted Wednesday, January 11, 2012 9:29 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 29, 2012 2:10 PM
Points: 197, Visits: 437

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

Paul White

Posted Thursday, January 12, 2012 12:39 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 7:18 PM
Points: 10,989, Visits: 10,532

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 »

Permissions