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
Posted on Leave a comment

Register for my upcoming session on digital transformation

I’m excited to have a session accepted to GroupBy, a free online conference targeting the Microsoft data platform community. The conference is sponsored by Brent Ozar Unlimited, and sessions are chosen by community votes. 

My session will be given on Fri, Dec 21, along with five other terrific looking sessions. You should  register for GroupBy here.

About my session

My session is called “What ‘Digital Transformation’ means, and how you can use it to advance your career (without being a robot).” Here’s the abstract:

Whether you love or hate buzzwords, the big ones signify critical cultural changes. In this session, Kendra Little will explain what executives mean when they describe a ‘digital transformation’, why this transformation is happening across all industries, and how understanding this gives developers and database administrators an advantage in building their careers.

You will learn what motivates CEOs to modify their business models in a digital transformation, and patterns and anti-patterns of companies that have attempted these transformations – with different results.

You’ll leave the session with an understanding of the core ideas and philosophies behind digital transformation that will help you prioritize what to learn, guide your interactions at work, and strategize your career path.

Group By session abstract

I wasn’t sure that this session would get votes

I’m particularly excited that the community of voters saw value in this topic because I haven’t seen any talks on this topic before in the data platform community. 

While we hear the phrase ‘digital transformation’ in conference keynotes frequently these days, I think many technologists don’t think much about what it means. Or worse, assume it doesn’t mean anything

‘Digital transformation’ is meaningful, and is absolutely worth understanding

For the last several months, I have been researching what worries and inspires CEOs and CIOs. I was surprised to find a large body of studies and predictions of massive trends in “digital transformation” across all industries.

What I once thought was “just a buzzword” is a shorthand that executives use to represent major changes in business models. It’s incredibly useful for practitioners, team leads, and managers to understand what the goals of a digital transformation are and how success is measured. This understanding helps you communicate better with executives, align your team’s work with business strategies, and ask for the right resources and headcount to execute on critical tasks for your organization.

I hope you join me in December at GroupBy!

Posted on Leave a comment
Posted on Leave a comment

DevOps Terms – and Why They Matter to Database Specialists

In this 20 minute session, I define scrum, continuous deployment, test driven development, DevOps, and related concepts. I close with a quick discussion of why Database Administrators and Developers should care about DevOps.

Prefer to listen on the go? Subscribe on iTuneslisten on Google Play, or plug this URL into your favorite podcasting app: http://dearsqldba.libsyn.com/rss#

If you enjoy Dear SQL DBA, reviews  on iTunes help me out a ton.

Slides from the presentation

Posted on Leave a comment
Posted on Leave a comment

Employee Agreements & Contracts: Best Practices (23 minute video)

So you’ve got an employee agreement in front of you: now what? In this  episode, I talk about practical steps you should take to make sure that you understand the terms of your contract, and how to potentially negotiate the terms.

Listen on the go

Subscribe on iTuneslisten on Google Play, or plug this URL into your favorite podcasting app: http://dearsqldba.libsyn.com/rss#

Video

Slides from the talk

Posted on Leave a comment
Posted on 2 Comments

How to cause a simple spill to tempdb

Sometimes it’s useful to know how to cause a problem.

Maybe you’ve never encountered the problem, and want to get hands-on experience. Maybe you’re testing a monitoring tool, and want to see if a condition flags an alert. Maybe you’re testing out a new client tool, and want to see how it displays it.

I recently was going through some demos in SQL Operations Studio, and I found that a spill on a sort operator wasn’t causing a warning to visibly show in the graphic execution plan.

I wanted to file an issue on this and let the Ops Studio team know that would be helpful – but my demo code was somewhat complex and required restoring a rather large database. So I set up a quick code sample to cause a spill that could be run in any database.

What’s the easiest way to cause a spill on a sort operator?

Populate a non-indexed table variable with a bunch of rows, then query the table variable and order the output. Ordering the output will require a sort, and since table variables don’t support column statistics, SQL Server won’t allocate enough memory for the sort.

Voila, super quick sample code…

declare @foo table (i int);

WITH Ten(N) AS 
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)   
insert @foo
SELECT T1000000.N
FROM Ten T10
CROSS JOIN Ten T100
CROSS JOIN Ten T1000
CROSS JOIN Ten T10000
CROSS JOIN Ten T100000
CROSS JOIN Ten T1000000;

select 0
FROM @foo
ORDER BY i DESC

Credit: This code is a simple adaptation of Paul White’s answer on this StackExchange question.

And here’s what the spill looks like in action

Here’s a quick view of what the spills look like in SQL Server Management Studio, and then in Operations Studio.

In Operations Studio, you can currently only see one actual plan at a time (I’ve got an issue filed for that here), and there’s no little warning on the sort operator, although you can see the details of the spill in the tooltip (I commented on this issue to potentially reactivate it).

A quick screencap of the issue turned into an animated gif

Posted on 2 Comments
Posted on 3 Comments

Take the SQLChallenge: Tuning a Stored Procedure

I’ve just published a new SQLChallenge course for subscribers, and I think it’s one of the best ones yet.

Your mission is to:

  1. Identify which statement is slowing down our stored procedure the most
  2. Tune the code to speed it up. You can change the query that is slow as well as anything else in the procedure that will help you make that statement faster.

In the solution videos, I’ll step through multiple strategies to figure out which statement in the procedure is slowing it down the most – because in real life, you need to have a whole bag of tricks in different situations. 

Solution scripts and videos include demos of:

  • Trace flag 7412, how it impacts sp_WhoIsActive, and live query plans
  • Finding the slow query in Query Store – with TSQL as well as the graphic reports
  • Finding the slow query in the plan cache
  • Methods of rewriting the procedure to tune the query
  • Additional code patterns that make tuning procedures simpler.

The challenge script in the first lesson creates a database on your test instance – no need to restore anything — so it’s fast to get going.

Get started here.

Watch all the videos, or just what you need

The course has an hour and ten minutes of videos, each one of which is 15 minutes or less. 

Want to take SQLChallenges?

Pick the right product for you and get started:

Posted on 3 Comments
Posted on Leave a comment

Employee Agreements & Contracts: Anti-Patterns (31 minute video)

When you take a new job in software engineering or in IT, within the paperwork there often lurks an employee agreement: a contract between you and your employer. In this half-hour live episode, I talk about why these contracts exist, and multiple anti-patterns you should avoid.

This is the first of a two part series. In the next episode, I’ll discuss best practices for understanding and negotiating the contract.

Want to listen on the go?

Subscribe on iTuneslisten on Google Play, or plug this URL into your favorite podcasting app: http://dearsqldba.libsyn.com/rss#

Video (31 minutes)

Slides from the talk

Posted on Leave a comment