SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Scalar functions can be the hidden boogie man

Ran across a comment the other day that scalar functions prohibit parallelism for a query when included. I thought it would be worth taking a look, but didn’t take it 100% seriously.

Came across the same indication today when reviewing MVP deep dives, so I put it to the test. Turns out even a simple select with a dateadd in a scalar format was affected enough with that one action to drop 5% on the execution plan. When dealing with merge or other processes that would benefit from parallelism, this would become even more pronounced.

Suggest reading “Death by UDF” section by Kevin Boles. This comment is buried at the very end of the chapter. He indicates: “One final parting gift: scalar UDFs also void the
use of parallel query plans, which is why the FormatDate UDFpegged only ONE CPU
core on my laptop! ” (Page 194-summary)

Sheldon Hull

After working in the mortgage industry for 7 years, I transitioned into Business Intelligence and began learning SQL and .NET. My goal is to integrate my business knowledge into my development to intelligently analyze and find solutions to problems. Blogging offers me an option to share what I've learned as well as receive feedback on better practices and solutions.


Leave a comment on the original post [www.bitbarbarian.com, opens in a new window]

Loading comments...