Chapter 20. Death by UDF
When Microsoft released SQL Server 2000, developers around the world rejoiced at the introduction of user-defined functions (UDFs). They were finally able to, within their Transact-SQL code, adhere to two of the main developer mantras: code encapsulation and code reuse. But unfortunately scalar UDFs weren’t fully integrated into the SQL Server optimizer and relational engine. This resulted in a number of issues, some of which can lead to unbelievably horrible query performance. In this chapter I’ll focus on two of these issues: bad optimizer estimates and especially row-by-row query processing. I’ll use a variety of standard tuning devices, including STATISTICS IO output, query plan analysis, and Profiler tracing. You’ll discover that SSMS does not tell the truth when it comes to UDF execution metrics. Most importantly, you’ll learn two techniques for avoiding the downsides of scalar UDFs.
The query optimizer in the SQL Server engine uses a wide array of numerical values, formulas, and logic to quickly and efficiently provide a “good enough” query plan to be used to perform the requested action. Among the main facets used are statistics about both row counts and value distributions in the tables and columns involved in the query.
