Blitz Result: User-Defined Functions

Good development practices dictate building reusable code, and user-defined functions seem like a good way to promote code reuse.  Unfortunately, it turns out that SQL Server doesn’t perform well with most scalar user-defined functions.

This part of our SQL Server sp_Blitz script checks the plan cache looking for resource-intensive queries that include user-defined scalar functions.

To Fix the Problem

Consider rewriting the query to do avoid the scalar UDF.  Learn more about why in Paul White’s excellent blog post about parallelism in UDFs. (Spoiler alert: queries with a scalar UDF require a serial zone in their execution plan, meaning they probably won’t go parallel.)

Return to sp_Blitz or Ask Us Questions

css.php