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

Compute Scalars, Expressions and Execution Plan Performance

Published Wednesday, September 5, 2012 12:22 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

 

Erik Eckhardt said:

Paul,

You always have very fascinating and informative blog posts. Thanks for teaching us all. I can't imagine how I would acquire knowledge like this on my own, and I appreciate it. I can sound all awesome and knowledgeable just from a bit of reading! :)

September 4, 2012 10:00 PM
 

Jeff Moden said:

Absolutely BRILLIANT!  Great research and great read.  Thanks, Paul.

September 5, 2012 12:17 AM
 

ALZDBA said:

Great piece of documentation and test cases.

Outstanding recommendations.

Thanks, Paul

September 5, 2012 2:08 AM
 

Gianluca Sartori said:

Great read, Paul!

I'm highly fascinated by SQL Server internals, but also scared and humbled by the overwhelming amount of knowledge required to understand what REALLY happens behind the scenes.

Your post is just another outstanding proof.

Thanks for sharing.

September 5, 2012 3:58 AM
 

peter-757102 said:

I learned something today, several things in fact, thus today is a very good day for me.

Thanks to you Paul!

September 5, 2012 5:46 AM
 

Alejandro Mesa said:

Paul,

I am very glad to have you around. Your knowledge about SQL Server and your willingness to share it, is really appreciated.

Now talking about the QO; I always admire how the QO evolves from CU/SP/Version to the next, but in this case I wonder if it can't foresee that the evaluation will take place more than one time, if it is pushed down the stream, as in this case?

--

AMB

September 5, 2012 9:00 AM
 

Usman Butt said:

As always, top notch stuff. I admire how you keep in-depth details so simple yet so effective. Not to mention there is always a new learning experience. Hats off to you ;)

September 5, 2012 10:24 AM
 

Andrew Lockwood said:

This is some really impressive analysis!

Thank you very much for taking the time to write this up and share with the community Paul

Cheers!

September 5, 2012 1:26 PM
 

Paul White said:

Thanks everyone!

@AMB: Conor talks about some of the QO limitations in this area in the 'Scalar Operator Costing' link in the further reading section above.  The QO primarily reasons about alternative relational constructions - it has very little to say about expressions (which aren't relational).  The lack of any sort of reasonable costing information for expressions means it would probably do a poor job anyway.  I suppose this might improve in future, but I have heard nothing to indicate that.

Paul

September 5, 2012 5:53 PM
 

Jason said:

Hi Paul,

I tried using both LEFT(@@DBTS, 0) *and* LEFT(CRYPT_GEN_RANDOM(1), 0) but my queries are still really slow.  Do you know of other functions that I can try?

jk, great piece as always.

Jason

September 6, 2012 12:06 PM
 

Paul White said:

Hi Jason,

I checked the scripts in this post on SQL Server 2005, 2008, 2008R2, and 2012.  Are you seeing different behaviour from these scripts in your environment?  If so, I'd be happy to hear the details.

On the other hand, if you are using @@DBTS and CRYPT_GEN_RANDOM to try to 'fix' a query of your own, I would just repeat the advice in the main text: don't!

Paul

September 7, 2012 12:34 AM
 

Jay said:

Great post!!!

December 12, 2012 6:51 AM
 

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
 

Rochelle said:

I thought that the first part of the post was very compelling, and I was looking forward to seeing how to look under the hood regarding a stack trace.

Unfortunately the examples started with code that had syntax I was unfamiliar with, and then the stack trace code which I would love to learn, but have never seen before. At that point I was completely lost and frustrated.

There doesn't seem to be a way to bridge the gap between basic blogs on the subject, and the advanced stuff which I really want to know.

September 12, 2013 4:41 PM
 

Mikael Eriksson said:

Great post, thanks.

"Please do not suggest improvements to the XML string splitter unless it handles all input characters and can accept a semicolon as a delimiter ;c)"

Well... I have one that does not defer the compute scalar, can use semicolon as a delimiter and accepts all input characters except the control characters below char(32).

Let me know if you are interested (I won't hold my breath :) )

May 12, 2014 5:03 PM
 

Paul White said:

Hello Mikael,

I am interested, please send it my way.

Cheers,

Paul

May 12, 2014 5:13 PM
 

Martin Smith said:

Regarding the passage discussing dominant performance effects this seems to be collation dependant.

With a SQL collation SQL_Latin1_General_CP1_CS_AS I found the XML conversion was indeed more expensive than the string replace (image from windows performance toolkit, numbers are CPU weight - whole process call stack was using 21.13)

http://i.stack.imgur.com/t55Os.png

But with Windows collation Latin1_General_CS_AS the string replace then becomes dominant (whole process was using 23.01)

http://i.stack.imgur.com/KF6dL.png

August 22, 2015 7:38 AM
 

Paul White said:

Hi Martin,

Yes that's true. You reminded me about a Connect item I opened 6 years ago, before I understood the magnitude of the difference:

https://connect.microsoft.com/SQLServer/feedback/details/512459/replace-function-extremely-slow-with-non-binary-windows-collation

Anyway, thanks.

Paul

August 22, 2015 8:10 AM

Leave a Comment

(required) 
(required) 
Submit
Privacy Statement