User-Defined Functions Slowing Down SQL Server
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.)