Last week, I was lucky enough to present on the topic of TSQL User Defined Functions (UDFs) in SQL Server at the PASS Summit.
My session was live-streamed, and the video, scripts, and slides are below.
Want to watch more Summit sessions for free? Check out PASS TV here.
Video (1 hour 10 minutes)
Note: if you’re just here to see the SQL Server 2019 (CTP2.1+) scalar UDF inlining, that starts at 54 minutes into the video.
Scripts
[download id=”71390?]
Slides
Below the slides is an outline of the content, generated by SlideShare.
[slideshare id=122936667&doc=kendralittlefixmyfunctions20181108nohiddenslides-181113192740]
1. Speeding Up Scalar and Table Valued UDFs Kendra Little, Redgate Fix My Functions
2. Kendra Little Evangelist at Redgate Founder, SQL Workbooks Microsoft MVP, MCM Kendra.Little@red-gate.com @Kendra_Little
3. This talk Level: 200 Audience: DBAs & Devs Goals: Speed up functions
4. “It is a known fact amongst practitioners that UDFs are “evil” when it comes to performance considerations [35, 28].”
5. Agenda
6. Why we use functions Modular meerkat
7. Why functions? Modularity Code reuse Simplify queries
8. Function types Scalar: returns a single value Multi-statement TVF: returns table Inline TVF: returns table
9. CREATE FUNCTION [schema].[function_name] (@parameter_name AS INT) RETURNS INT –WITH SCHEMABINDING, … AS BEGIN RETURN END GO Scalar function syntax
10. CREATE FUNCTION [schema].[function_name] (@parameter_name AS INT) RETURNS @return_variable TABLE (/* table type definition */) –WITH SCHEMABINDING, … AS BEGIN RETURN END GO Multi-statement TVF syntax
11. CREATE FUNCTION [schema].[function_name] (@parameter_name AS INT) RETURNS TABLE –WITH SCHEMABINDING, … AS RETURN ( ) GO Inline TVF syntax
12. SELECT TOP (10) qp.dbid, qp.query_plan, cp.size_in_bytes / 1024. / 1024. AS size_in_mb FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp ORDER BY size_in_mb DESC; CROSS/OUTER APPLY and TVFs
13. Demo: why so slow?
14. sp_WhoIsActive – free procedure from Adam Machanic: WhoIsActive.com
15. sp_WhoIsActive – free procedure from Adam Machanic: WhoIsActive.com
16. Lightweight Statistics Profiling SQL Server 2014 SP2 through 2017 • Trace Flag 7412 • Install KB 4078596 (2016 & 2017 only) SQL Server 2016 SP1+ MUCH lower overhead SQL Server 2019 no trace flag needed https://blogs.msdn.microsoft.com/sql_server_team/query-progress- anytime-anywhere
17. sp_WhoIsActive Free procedure Written by @AdamMachanic WhoIsActive.com
18. Find what functions hide Curious corg
19. Get tuning data for functions Estimated & actual execution plans Finding function calls in operators UdfCpuTime and UdfElapsedTime in actual execution plans
20. Demo: spy on function internals
21. Estimated plans help! Scalar functions and multi-statement TVFs: estimated plan shows the function logic • Does not appear in an actual execution plan • Plans for the calling query and the function are stored in sys.dm_exec_query_stats, but you must find them individually
22. UDFs and parallelism TSQL scalar UDFs – serial plan Multi-statement TVFs – serial zone Computed column with TSQL UDF – parallelism eradicator, BEWARE
23. MSTVFs and row estimates SQL Server 2005 – 2012 SQL Server 2014 – 2016 SQL Server 2017+ 100 1 ?
24. Interleaved execution Part of adaptive query processing, all Editions Introduced in SQL Server 2017… • MSTVFS only • Read only queries • Cannot be on the inside of an APPLY • Compatibility level 140+
25. Interleaved execution (continued) Diagram by Joe Sack @JoeSackMSFT https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/i ntroducing-interleaved-execution-for-multi-statement-table-valued- functions/
26. Scalar UDF Inlining Clever crow
27. https://aka.ms/iqp
28. Why are scalar UDFs slow? Executed row by agonizing row Scalar operators not ‘costed’ No cross-statement optimization No parallelism https://docs.microsoft.com/en-us/sql/relational-databases/user-defined- functions/scalar-udf-inlining
29. Automatic inlining Rewrite scalar UDF Substitute rewrite into calling query Then optimize https://docs.microsoft.com/en-us/sql/relational-databases/user-defined- functions/scalar-udf-inlining
30. Demo: wizardry
31. Controlling behavior Database compatibility level 150 CREATE FUNCTION … WITH INLINE = OFF USE HINT (‘DISABLE_TSQL_SCALAR_UDF_INLINING’) https://docs.microsoft.com/en-us/sql/relational-databases/user-defined- functions/scalar-udf-inlining
32. No scalar UDF inlining if it… Uses GETDATE() Uses table variables or TVPs Is in computed column Is in a check constraint https://docs.microsoft.com/en-us/sql/relational-databases/user-defined- functions/scalar-udf-inlining
33. Takeaways
34. Tips for tuning functions Scalar UDFs and Multi-Statement TVFs inhibit parallelism Use SCHEMABINDING if your function doesn’t do data access Use inline TVFs (single statement) or persist data when possible
35. The future of scalar UDFs 2019 inlining is VERY compelling Edition has not been announced Releasing with “high coverage”
36. References & links Lightweight query profiling reference – Pedro Lopes https://blogs.msdn.microsoft.com/sql_server_team/query- progress-anytime-anywhere/ SQL Server Functions, the basics – Jeremiah Peschka https://www.red-gate.com/simple-talk/sql/t-sql- programming/sql-server-functions-the-basics/ Froid: Optimization of Imperative Programs in a Relational Database – Karthik Ramachandra et al http://www.vldb.org/pvldb/vol11/p432-ramachandra.pdf
37. References & links continued Interleaved execution for multi-statement TVFs – Joe Sack https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introd ucing-interleaved-execution-for-multi-statement-table-valued-functions/ Parallelism inhibitors – Paul White http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing -a-parallel-query-execution-plan.aspx
38. Thank You Where to find me… @Kendra_Little Kendra.Little@Red-Gate.com