SQL Server Archives | Page 109 of 163

A Bug With Recursive UDFs When Inlined In SQL Server 2019

Posted on May 11, 2021May 16, 2022 by Erik Darling

Enough Already

I see people do things like this fairly often with UDFs. I don’t know why. It’s almost like they read a list of best practices and decided the opposite was better.

This is a quite simplified function, but it’s enough to show the bug behavior.

While writing this, I learned that you can’t create a recursive (self-referencing) scalar UDF with the schemabinding option. I don’t know why that is either.

Please note that this behavior has been reported to Microsoft and will be fixed in a future update, though I’m not sure which one.

Swallowing Flies

Let’s take this thing. Let’s take this thing and throw it directly in the trash where it belongs.

CREATE OR ALTER FUNCTION dbo.how_high
(
    @i int,
    @h int
)
RETURNS int
WITH
    RETURNS NULL ON NULL INPUT
AS
BEGIN

    SELECT 
       @i += 1;
    
    IF @i < @h
    BEGIN
        SET 
            @i = dbo.how_high(@i, @h);
    END;

    RETURN @i;

END;
GO

Seriously. You’re asking for a bad time. Don’t do things like this.

Unless you want to pay me to fix them later.

Froided

In SQL Server 2019, under compatibility level 150, this is what the behavior looks like currently:

/*
Works
*/
SELECT 
    dbo.how_high(0, 36) AS how_high;
GO 

/*
Fails
*/
SELECT 
    dbo.how_high(0, 37) AS how_high;
GO

The first execution returns 36 as the final result, and the second query fails with this message:

Msg 217, Level 16, State 1, Line 40
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

A bit odd that it took 37 loops to exceed the nesting limit of 32.

This is the bug.

Olded

With UDF inlining disabled, a more obvious number of loops is necessary to encounter the error.

/*
Works
*/
SELECT 
    dbo.how_high(0, 32) AS how_high
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));
GO 

/*
Fails
*/
SELECT 
    dbo.how_high(0, 33) AS how_high
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));
GO

The first run returns 32, and the second run errors out with the same error message as above.

Does It Matter?

It’s a bit hard to imagine someone relying on that behavior, but I found it interesting enough to ask some of the nice folks at Microsoft about, and they confirmed that it shouldn’t happen. Again, it’ll get fixed, but I’m not sure when.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Going Further

Introducing sp_QuickieStore: What To Do If You Hit A Problem

Posted on April 30, 2021May 16, 2022 by Erik Darling

No One’s Perfect

I’ve been working on scripts like this for long enough to know that I don’t know everything that might happen out there in your kooky-krazy environments.

The beauty of open source is that it’s really easy to let me know, or jump in and get to fixing things on your own.

To help you figure out where things are getting messy, I’ve added a few parameters to make troubleshooting easier.

Help

The first place to look for information or details is the help parameter.

EXEC dbo.sp_QuickieStore
    @help = 1;

You’ll get information about parameters, results, shortcomings, and licensing.

Of course, if you’re hitting an error whether it’s red text or logical, you probably need a bit more than this.

Debug

If you want to see the queries that the dynamic SQL builds and executes, you can use the debug mode.

EXEC dbo.sp_QuickieStore
    @database_name = 'StackOverflow2013',
    @debug = 1;

What debug mode returns:

Dynamic SQL statements, and statement length in the Messages tab
Supplied parameter values to the procedure
Declared parameter values
All temp table contents with table name identifiers
Replacement selects for empty tables with table name identifiers

This should help you figure out what’s going wrong and when. If you find something, open an issue on GitHub to tell me about it.

Performance

If you find this procedure is regularly running slowly for you, you can use run this to return query plans and see where the trouble’s at.

EXEC dbo.sp_QuickieStore
    @database_name = 'StackOverflow2013',
    @troubleshoot_performance = 1;

This will SET STATISTICS XML ON; for queries that touch Query Store views, where we typically will have performance issues, along with a table of queries and runtimes.

Once data is in temp tables, things are fast because they’re small. Before that, you’re at the whim and fancy of the elements.

If you come across something that you think I can fix (and isn’t just poor Query Store schema design/indexing), open an issue on GitHub to tell me about it.

Wrapper’s Delight

This week we covered all the major aspects and features of sp_QuickieStore. I’ll do some video code reviews to close out the series for now.

Thanks for reading!

Going Further

Introducing sp_QuickieStore: Formatting Is Everything

Posted on April 29, 2021May 16, 2022 by Erik Darling

Random Assortment

Sometimes it’s hard to get people to understand the scale of a number without some commas.

I don’t know why that is, but that’s why I added the option to format numbers.

You can do that by running the procedure like so:

EXEC dbo.sp_QuickieStore
    @database_name = 'StackOverflow',
    @expert_mode = 1,
    @format_output = 1;

Overview

What you get back should look something like this.

This isn’t the most elegant screenshot in the world, I just want to give you an idea of what you should see.

Essentially, if it’s a number, it will get commas inserted into the proper places.

Yep

I know that this is a somewhat Americanized version of things, and that the FORMAT function supports other language stuff.

If that’s something you care about and you’d like to contribute, head over here. Code contributions are always welcome.

Tomorrow, we’ll look at options you have to get help, and troubleshoot code and performance issues.

Thanks for reading!

Going Further

Introducing sp_QuickieStore: What You Can Filter

Posted on April 28, 2021May 16, 2022 by Erik Darling

Safe Words

What good is writing all this dynamic SQL and whatnot if you’re just going to firehose data at people?

None. None at all.

You’re going to want to answer questions about what happened and when, and you’re not going to want to struggle with it.

So what can you do?

Answer Me

First, you can control how many queries you’re going to get back in your result set:

EXEC dbo.sp_QuickieStore
    @database_name = 'StackOverflow2013',
    @top = 10;

You can also filter on start and end times. These filters are applied to the last_execution_time column in avg_duration in query_store_runtime_stats.

EXEC dbo.sp_QuickieStore
    @database_name = 'StackOverflow2013',
    @start_date = '20210101',
    @end_date = '20211231';

If you want to target queries that execute a lot, or that run for over a certain amount of time, you can use filter like this.

These filters will be applied to count_executions and avg_duration respectively, also in query_store_runtime_stats.

EXEC dbo.sp_QuickieStore
    @database_name = 'StackOverflow2013',
    @execution_count = 20;

EXEC dbo.sp_QuickieStore
    @database_name = 'StackOverflow2013',
    @duration_ms = 1000;

Targeting a specific stored procedure can be done like this. If you don’t specify a schema, I’ll assume you’re looking in dbo.

EXEC dbo.sp_QuickieStore
    @database_name = 'StackOverflow2013',
    @procedure_schema = 'some_schema',
    @procedure_name = 'some_procedure';

To include or ignore a single or list of query or plan IDs, you can use these parameters. Spaces and other whitespace should be handled alright.

EXEC dbo.sp_QuickieStore
    @database_name = 'StackOverflow2013',
    @include_plan_ids = '1,2,3,4,5';

EXEC dbo.sp_QuickieStore
    @database_name = 'StackOverflow2013',
    @include_query_ids = '1,2,3,4,5';

EXEC dbo.sp_QuickieStore
    @database_name = 'StackOverflow2013',
    @ignore_plan_ids = '1,2,3,4,5';

EXEC dbo.sp_QuickieStore
    @database_name = 'StackOverflow2013',
    @ignore_query_ids = '1,2,3,4,5';

Finally, if you want to search for query text, you can do this. I’ve found this search to be a bit unreliable, unless you’re generous with wildcards. If you don’t add them at the beginning and end, I’ll do that for you.

EXEC dbo.sp_QuickieStore
    @database_name = 'StackOverflow2013',
    @query_text_search = 'some text here';

Over And Out

I think that covers most of the important filtering you’d want to do in Query Store. Of course, if you think anything else would be useful, you can request it here.

Tomorrow, we’ll look at some formatting options available.

Thanks for reading.