THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Page Free Space

See also my SQL Server technical articles on SQLperformance.com

Inside the Optimizer: Row Goals In Depth

Published Wednesday, August 18, 2010 10:29 PM by Paul White

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Page Free Space: Paul White said:

You might recall (from my last post) that query plans containing a row goal tend to favour nested loops

August 22, 2010 1:48 AM
 

Page Free Space: Paul White said:

Summary: A detailed look at costing, and more undocumented optimizer fun. The SQL Server query optimizer

September 1, 2010 2:34 AM
 

Rishabh K said:

Hi Paul,

Sorry for the late question..but I recently looked into this.I have a few doubts. It may seem a stupid question but still...

1. Is there any significance of calculating the estimated number of rows as a floating point value(as the number of rows can't be in decimal).

2. Can you explain the estimated number of execution for the inner side of nested loop join as 101.524498 ??

May 3, 2012 8:23 AM
 

David Clary said:

Paul, this is a fantastic explanation of why my particular circumstance (Select top 100 * from x takes 56 seconds, when select top 5000 * from x takes only 3 seconds).

What I don't see here, and could really use advice on is... How to fix?

November 16, 2012 4:53 PM
 

Paul White said:

Hi David,

You could use a trick like:

DECLARE @Rows bigint = 100;

SELECT TOP (@Rows) <your query>

OPTION (OPTIMIZE FOR (@Rows = <value that works well>);

November 16, 2012 5:45 PM
 

Paul White: Page Free Space said:

This is a post for T-SQL Tuesday #43 hosted by my good friend Rob Farley . The topic this month is Plan

June 11, 2013 5:00 AM
 

Paul White: Page Free Space said:

Summary: A deep dive into SQL Server parallelism, and a potential performance problem with parallel plans

September 1, 2013 6:16 AM

Leave a Comment

(required) 
(required) 
Submit
Privacy Statement