Posted on Leave a comment

Fix My Functions: Speeding Up Scalar and Table Valued UDFs (video)

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

Fix My Functions demo script (zip)

Slides

Below the slides is an outline of the content, generated by SlideShare.

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 [email protected] @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 [email protected]

Posted on Leave a comment
Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.