Description:
In this video, I dive into the intricacies of scalar UDF inlining and why sometimes rewriting a function can be the best approach to improve performance. Erik Darling from Darling Data shares insights on how the presence of certain built-in functions like 'getDate' within a UDF can prevent it from being inlined by SQL Server, leading to suboptimal query execution plans. I demonstrate this with an example using Stack Overflow's database and highlight the significant difference in execution time when we rewrite the function to accept 'getDate' as a parameter rather than invoking it inside the UDF. By doing so, we enable the UDF to be inlined, resulting in parallel execution and drastically reducing query runtime. This video is packed with practical tips for optimizing SQL Server queries, making it a valuable resource for anyone looking to fine-tune their database performance.
CHAPTERS
00:00:00 - Introduction
00:01:00 - Why Rewrite UDFs
00:02:09 - Membership Link
00:03:15 - Content on Website
00:04:00 - Live Events
00:05:08 - Upcoming Data Saturday Dallas
00:06:37 - UDF Performance Drawbacks
00:08:29 - Function Example
00:09:15 - Query Plan Analysis
00:10:45 - Compute Scalar Issue
00:12:00 - sys.dm_exec_function_stats
00:13:36 - Simplifying UDFs
00:14:20 - Inlined Query Performance
00:15:10 - Parameter Snippet Benefits
━━━━━━━━━━━━━━━━━━━━━━━━━━
???? TRAINING & COURSES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Get AI-Ready With Erik
https://training.erikdarling.com/get-ai-ready-with-erik?coupon=AIREADY
SQL Server Performance Engineering Course
https://training.erikdarling.com/sql-server-performance-engineering?coupon=ENGINEERING
Learn T-SQL with Erik
https://training.erikdarling.com/learn-t-sql-with-erik?coupon=ADVANCEDTSQL
Everything Bundle:
https://training.erikdarling.com/?coupon=SPRINGCLEANING
━━━━━━━━━━━━━━━━━━━━━━━━━━
????️ CONSULTING & SERVICES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Need SQL Server performance help?
https://training.erikdarling.com/sqlconsulting
━━━━━━━━━━━━━━━━━━━━━━━━━━
???? CONNECT
━━━━━━━━━━━━━━━━━━━━━━━━━━
Ask questions at Office Hours
https://erikdarling.com/officehours/
Become a channel member
https://www.youtube.com/@ErikDarlingData/join
━━━━━━━━━━━━━━━━━━━━━━━━━━
Share this link via
Or copy link