create temp table sql – Page 47

When Should I Use A Trigger Instead Of A Foreign Key In SQL Server?

Posted on April 7, 2023March 8, 2023 by Erik Darling

Mediation

The really annoying thing about foreign keys is that you can’t do anything to affect the query plans SQL Server uses to enforce them.

My good AND dear friend Forrest ran into an issue with them that I’ve seen played out at dozens of client sites. Sometimes there are actually foreign keys that don’t have indexes to help them, but even when there are, SQL Server’s optimizer doesn’t always listen to reason, or other cardinality estimation issues are at play. Some can be fixed, and some can’t.

When they can’t, sometimes implementing triggers which are more under your control can be used in place of foreign keys.

The following code isn’t totally suitable for production, but is good enough to illustrate examples.

If you need production-ready code, hit the link at the end of the post to schedule a sales call.

Setup

I’m going to use some slightly modified code from Forrest’s post linked above, since that’s what I started with to see if the foreign key issue still presents in SQL Server 2022 under compatibility level 160.

It does! So. Progress, right? Wrong.

CREATE TABLE
    dbo.p
(
    id int
       PRIMARY KEY,
    a char(1),
    d datetime DEFAULT SYSDATETIME()
);

CREATE TABLE
    dbo.c
(
    id int
       IDENTITY
       PRIMARY KEY,
    a varchar(5),
    d datetime DEFAULT SYSDATETIME(),
    pid int,
        INDEX nc NONCLUSTERED(pid)
);

INSERT
    dbo.p WITH(TABLOCKX)
(
    id,
    a
)
SELECT TOP (1000000)
    ROW_NUMBER() OVER
    (
        ORDER BY
            (SELECT 1/0)
    ),
    CHAR(m.severity + 50)
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2;

INSERT
    dbo.c WITH(TABLOCKX)
(
    a,
    pid
)
SELECT TOP (1100000)
    REPLICATE
    (
        CHAR(m.severity + 50),
        5
    ),
    ROW_NUMBER() OVER
    (
        ORDER BY
            (SELECT 1/0)
    ) % 1000000 + 1
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2;

For Fun

With the boring stuff out of the way, let’s skip to how to set up triggers to replace foreign keys.

For reference, this is the foreign key definition that we want to replicate:

ALTER TABLE
    dbo.c
ADD CONSTRAINT
    fk_d_up
FOREIGN KEY 
    (pid)
REFERENCES
    dbo.p (id)
ON DELETE CASCADE
ON UPDATE CASCADE;

While it’s a bit contrived to have an update cascade from what would be an identity value for most folks, we’re gonna run with it here for completeness.

Here at Darling Data we care about completion.

Inserts

I’m a big fan of instead of insert triggers for this, because they take a shortcut around all the potential performance ramifications of letting a thing happen and checking it afterwards.

Now, you’re going to notice that this trigger silently discards rows that would have violated the foreign key. And you’re totally right! But it’s up to you to decide how you want to handle that.

Ignore them completely
Log them to a table
Correct mismatches via lookup tables

Anyway, here’s the basic trigger I’d use, with hints included to illustrate the points from above about you getting control of plan choices.

CREATE OR ALTER TRIGGER
    dbo.instead_insert
ON
    dbo.c
INSTEAD OF INSERT
AS
BEGIN
    IF @@ROWCOUNT = 0
    BEGIN
        RETURN;
    END;

    SET NOCOUNT ON;

    INSERT
        dbo.c
    (
        a,
        pid
    )
    SELECT
        i.a,
        i.pid
    FROM Inserted AS i
    WHERE EXISTS
    (
        SELECT
            1/0
        FROM dbo.p AS p WITH(FORCESEEK)
        WHERE p.id = i.id
    )
    OPTION(LOOP JOIN);
END;

Updates

To manage updates, here’s the trigger I’d use:

CREATE OR ALTER TRIGGER
    dbo.after_update
ON
    dbo.p
AFTER UPDATE
AS
BEGIN
    IF @@ROWCOUNT = 0
    BEGIN
        RETURN;
    END;

    SET NOCOUNT ON;

    UPDATE c
        SET
           c.pid = i.id
    FROM dbo.c AS c WITH(FORCESEEK)
    JOIN Inserted AS i
      ON i.id = c.id
    OPTION(LOOP JOIN);
END;

Deletes

To manage deletes, here’s the trigger I’d use:

CREATE OR ALTER TRIGGER
    dbo.after_delete
ON
    dbo.p
AFTER DELETE
AS
BEGIN
    IF @@ROWCOUNT = 0
    BEGIN
        RETURN;
    END;

    SET NOCOUNT ON;

    DELETE c
    FROM dbo.c AS c WITH(FORCESEEK)
    JOIN Inserted AS i
      ON i.id = c.id
    OPTION(LOOP JOIN);
END;

Common Notes

All of these triggers have some things in common:

They start by checking to see if any rows actually changed before moving on
The SET NOCOUNT ON happens after this, because it will interfere with @@ROWCOUNT
You may need to use the serializable isolation level to fully protect things, which cascading foreign keys use implicitly
We don’t need to SET XACT ABORT ON because it’s implicitly used by triggers anyway

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Why SQL Server’s OPTIMIZE FOR UNKNOWN Hint Hurts Performance

Posted on April 6, 2023March 7, 2023 by Erik Darling

“Best Practice”

It’s somewhat strange to hear people carry on about best practices that are actually worst practices.

One worst practice that has strong staying power is the OPTIMIZE FOR UNKNOWN hint, which we talked about yesterday.

It probably doesn’t help that Microsoft has products (I’m looking at you, Dynamics) which have a setting to add the hint to every query. Shorter: If Microsoft recommends it, it must be good.

Thanks, Microsoft. Dummies.

Using the OPTIMIZE FOR UNKNOWN hint, or declaring variables inside of a code block to be used in a where clause have the same issue, though: they make SQL Server’s query optimizer make bad guesses, which often lead to bad execution plans.

You can read great detail about that here.

Mistakenly

We’re going to create two indexes on the Posts table:

CREATE INDEX
    p0
ON dbo.Posts
(
    OwnerUserId
)
WITH
(
    SORT_IN_TEMPDB = ON,
    DATA_COMPRESSION = PAGE
);
GO

CREATE INDEX
    p1
ON dbo.Posts
(
    ParentId,
    CreationDate,
    LastActivityDate
)
INCLUDE
(
    PostTypeId
)
WITH
(
    SORT_IN_TEMPDB = ON,
    DATA_COMPRESSION = PAGE
);
GO

The indexes themselves are not as important as how SQL Server goes about choosing them.

Support Wear

This stored procedure is going to call the same query in three different ways:

One with the OPTIMIZE FOR UNKNOWN hint that uses parameters
One with local variables set to parameter values with no hints
One that accepts parameters and uses no hints

CREATE OR ALTER PROCEDURE
    dbo.unknown_soldier
(
    @ParentId int,
    @OwnerUserId int
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;

    SELECT TOP (1)
        p.*
    FROM dbo.Posts AS p
    WHERE p.ParentId = @ParentId
    AND   p.OwnerUserId = @OwnerUserId
    ORDER BY
        p.Score DESC,
        p.Id DESC
    OPTION(OPTIMIZE FOR UNKNOWN);

    DECLARE
        @ParentIdInner int = @ParentId,
        @OwnerUserIdInner int = @OwnerUserId;

    SELECT TOP (1)
        p.*
    FROM dbo.Posts AS p
    WHERE p.ParentId = @ParentIdInner
    AND   p.OwnerUserId = @OwnerUserIdInner
    ORDER BY
        p.Score DESC,
        p.Id DESC;

    SELECT TOP (1)
        p.*
    FROM dbo.Posts AS p
    WHERE p.ParentId = @ParentId
    AND   p.OwnerUserId = @OwnerUserId
    ORDER BY
        p.Score DESC,
        p.Id DESC;

END;
GO

Placebo Effect

If we call the stored procedure with actual execution plans enabled, we get the following plans back.

EXEC dbo.unknown_soldier 
    @OwnerUserId = 22656, 
    @ParentId = 0;

SQL Server Query Plan With Optimize For Unknown Hint — Not a good guess.

The assumed selectivity that the OPTIMIZE FOR UNKNOWN hint produces as a cardinality estimate is way off the rails.

SQL Server thinks three rows are going to come back, but we get 6,050,820 rows back.

We get identical behavior from the second query that uses variables declared within the stored procedure, and set to the parameter values passed in.

SQL Server Query Plan With Local Variables — release me

Same poor guesses, same index choices, same long running plan.

Parameter Effect

The query that accepts parameters and doesn’t have any hints applied to it fares much better.

In this case, we get an accurate cardinality estimate, and a more suitable index choice.

Note that both queries perform lookups, but this one performs far fewer of them because it uses an index that filters way more rows out prior to doing the lookup.

The optimizer is able to choose the correct index because it’s able to evaluate predicate values against the statistics histograms rather than using the assumed selectivity guess.

Thanks for reading!

Going Further

Why Are Parameter Details Missing From SQL Server Execution Plans?

Posted on April 5, 2023March 7, 2023 by Erik Darling

Compiled

For a long while now, when parameterized queries get executed in SQL Server, whether they’re in stored procedures or from ORMs, execution plans stored in the plan cache or in query store will keep information about what the compile-time parameter values are.

You can see this list in the details of the query plan by going to the properties of whatever the root node is.

SQL Server Query Plan Properties — compile time, baby!

This is useful for when you want to execute the code to reproduce and address any performance issues.

You know, very professional stuff.

If you use local variables, apply the OPTIMIZE FOR UNKNOWN hint, or disable parameter sniffing using a database scoped configuration, cached and query store-d plans no longer have those parameters lists in them. Likewise, if you have queries where a mix of parameters and local variables are used, the local variable compile time values will be missing from the plan XML.

Thanks for reading!

Going Further

All The Wrong Ways To Search Numbers In SQL Server Queries

Posted on April 4, 2023March 7, 2023 by Erik Darling

Patternistic

I see this mistake quite a bit! Golly do I. And then I cry and drink and drink and cry and why do you people insist on ruining my makeup?

Most of the time, it’s some query that looks like this:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE p.OwnerUserId LIKE '2265%'

The cruddy thing here is that… SQL Server doesn’t handle this well.

Even with this index on OwnerUserId, bad things happen in the form of a full scan instead of a seek, because of the implicit conversion function.

CREATE INDEX
    p0
ON dbo.Posts
(
    OwnerUserId
);

Because the OwnerUserId column has to be converted to varchar(12) to accommodate the wildcard search, we’re unable to directly seek to data we care about.

Many Other Wrongs

As if LIKE weren’t bad enough, I often see other patterns that attempt to mimic the behavior with more typing involved:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE LEFT(p.OwnerUserId, 4) = '2265'; 

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE SUBSTRING
      (
          CONVERT(varchar(12), 
          p.OwnerUserId), 
          0, 
          5
      ) = '2265'; 

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE PATINDEX
      (
          '2265%', 
          CONVERT(varchar(12), p.OwnerUserId)
      ) > 0;

And even this, which doesn’t even work correctly because it’s more like saying ‘%2265%’.

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE CHARINDEX('2265', p.OwnerUserId) > 0;

These will have all the same problems as the original LIKE search pattern shown above, where we get a scan instead of a seek and a conversion of the OwnerUserId column from an integer to a varchar(12).

Is there a way to fix this? You betcha.

Computered

Adding a computed column does the job for us:

ALTER TABLE 
    dbo.Posts 
ADD OwnerUserId_varchar 
AS  CONVERT(varchar(12), OwnerUserId);

CREATE INDEX 
    pv 
ON dbo.Posts
(
    OwnerUserId_varchar
);

And without changing the original LIKE query, the optimizer will match it with our indexed computed column.

Note that none of the other attempts at rewrites using left, substring, or patindex will automatically match with new indexed computed column.

Thanks for reading!

Going Further

Some Questions I’ve Answered Recently On Database Administrators Stack Exchange

Posted on April 3, 2023March 6, 2023 by Erik Darling

Good Citizen

Here are some interesting questions that I’ve answered on dba.stackexchange.com:

I should probably get a real job.

Thanks for reading!

Going Further

Updates to sp_PressureDetector, sp_HumanEvents, sp_QuickieStore, And More!

Posted on March 31, 2023March 6, 2023 by Erik Darling

Updates!

If you use any of my stored procedures, I’ve been doing a lot of work on them lately to improve performance, add features, and fix bugs.

There have been a lot of changes over the past few months, and the full list would be a little unwieldy.

sp_HumanEvents: Mostly adding useful columns the output and improving the XML processing
sp_HumanEventsBlockViewer: This is a newer procedure to examine a blocked process report extended event session, and not a lot of folks know about it. Try it out!
sp_PressureDetector: Added tempdb configuration and usage details, some more wait stats, additional CPU details, etc. A fan favorite.
sp_QuickieStore: Added the ability to analyze multiple databases, improve speed, and add more helpful details to the output
sp_WhoIsActive Logging: Added a procedure to create a view over all of the daily logging tables

If you use any of these, get those updates going. If you’re not using them: what are you waiting for? They’re free!

Thanks for reading!

Going Further

In SQL Server, Blocking Begets Deadlocking

Posted on March 30, 2023September 25, 2023 by Erik Darling

Gateway Drug

Most servers I look at have some level of problems with queries deadlocking with one another. In many cases, they’re read queries deadlocking with write queries, which is easy to resolve using an optimistic isolation level.

My approach to resolving deadlocks is nearly identical to my approach for resolving blocking problems: make the queries go faster to reduce the potential for overlap.

Deadlocks are a result of queries blocking each other, where they’d drag on forever in an unwinnable grudge match. Sometimes this happens just because of bad timing, weird locking hints, using implicit transactions, or application bugs that leave sessions in a sleeping state while holding locks. The fastest queries in the world generally can’t fix those kinds of problems, because they’re going out of their way to do bad things.

But it still comes back to locks being taken and held. Not all blocking leads to deadlocks of course, but the longer you leave locks out there, the higher your chances of running into deadlocks is.

A lot of the time, just figuring out what deadlocks is only half the battle. You’ll also need to diagnose what’s blocking to fully resolve things.

How do you do that?

Blocked Process Report

Turning on the blocked process report is a good starting place. You can do that like so.

EXEC sys.sp_configure
    N'show advanced options',
    1;
RECONFIGURE;
GO
EXEC sys.sp_configure
    N'blocked process threshold',
    5; --Seconds
RECONFIGURE;
GO

The only real downside of the blocked process report is that you can’t go below five seconds for the block duration that you have to hit before things are logged.

We’ll talk about other options next, but first! How do you log to the blocked process report now that it’s enabled?

Extended events, my dear friends.

CREATE EVENT SESSION 
    blocked_process_report
ON SERVER
    ADD EVENT 
        sqlserver.blocked_process_report
    ADD TARGET 
        package0.event_file
    (
        SET filename = N'bpr'
    )
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);

ALTER EVENT SESSION
    blocked_process_report
ON SERVER 
    STATE = START;
GO

To read data from it, you can use my stored procedure sp_HumanEventsBlockViewer.

EXEC dbo.sp_HumanEventsBlockViewer
    @session_name = N'blocked_process_report';

That should get you most of the way to figuring out where your blocking problems are.

Logging sp_WhoIsActive

If you want try to catch blocking problems shorter than 5 seconds, one popular way to do that is to log sp_WhoIsActive to a table.

I have a whole set of code to help you do that, too. In that repo, you’ll find:

A stored procedure that creates a view that will walk the blocking chain
A stored procedure that logs sp_WhoIsActive to a table, creating a new table for each day
A script to create an agent job to call do automate the logging

It works pretty well for most use cases, but feel free to tweak it to meet your needs.

Getting To The Deadlocks

The best way known to god, dog, and man to look at deadlocks is to use sp_BlitzLock.

I put a lot of work into a big rewrite of it recently to speed things up and fix a lot of bugs that I noticed over the years.

You can use it to look at the system health extended event session, or to look at a custom extended event session.

CREATE EVENT SESSION 
    deadlock
ON SERVER
    ADD EVENT 
        sqlserver.xml_deadlock_report
    ADD TARGET 
        package0.event_file
    (
        SET filename = N'deadlock'
    )
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO

ALTER EVENT SESSION
    deadlock
ON SERVER 
    STATE = START;
GO

And then to analyze it:

EXEC dbo.sp_BlitzLock
    @EventSessionName = N'deadlock';

Problem Solving

Once you have queries that are blocking and deadlocking, you get to choose your own adventure when it comes to resolving things.

If you need help with that, click the link below to set up a sales call with me. If you’re gonna go it on your own, here are some basic things to check:

Isolation levels: are you using repeatable read or serializable without knowing it?
Do you have the right indexes for your queries to find data quickly?
Are your queries written in a way to take full advantage of your indexes?
Do you have any foreign keys or indexed views that are slowing modifications down?
Are you starting transactions and doing a lot of work before committing them?

Thanks for reading!

Going Further

Grievances While Working With RAISERROR In SQL Server

Posted on March 29, 2023March 6, 2023 by Erik Darling

Use Case

I know that in the world of professional error handling, most folks use THROW instead of RAISERROR to do things.

In my world, I use RAISERROR in stored procedures I write to give feedback on the state of various things in case I need to track down a bug or other issue. It’s quite useful to track the state of parameters and variables and counts of things along the way without bringing progress to a dead halt the way that THROW does, and with substitution options that PRINT doesn’t offer without building dynamic SQL that can get rather messy either with a lot of converting or nested REPLACE statements.

Anyway, there are some annoying things about working with RAISERROR that I wish were different. Let’s look at those.

Itty Bitty

The first-most annoying thing is that you can’t use the bit datatype directly as a substitution variable.

DECLARE
    @bit bit = 0;

RAISERROR(N'This is our message %d', 0, 1, @bit);
RAISERROR(N'This is our message %i', 0, 1, @bit);
RAISERROR(N'This is our message %o', 0, 1, @bit);
RAISERROR(N'This is our message %s', 0, 1, @bit);
RAISERROR(N'This is our message %u', 0, 1, @bit);
RAISERROR(N'This is our message %x', 0, 1, @bit);
RAISERROR(N'This is our message %X', 0, 1, @bit);

All of those attempts will return an error message like so:

Msg 2748, Level 16, State 1, Line 23

Cannot specify bit data type (parameter 4) as a substitution parameter.

Why? I have no idea. It is arguably the simplest data type. To work around this, you have to declare another variable with a workable data type, like a n/char(1) or tinyint to use instead.

Date Me

It’s often helpful to record start and end times for steps in a procedure to figure out which parts are slow, etc. But just like bits, you can’t pass any date or time related data types in.

Without being exhaustive, this fails just like above.

DECLARE
    @date date = GETDATE();

RAISERROR(N'This is our message %s', 0, 1, @date);

With a familiar-feeling error message.

Msg 2748, Level 16, State 1, Line 19

Cannot specify date data type (parameter 4) as a substitution parameter.

The only real option here is to use a string, which is slightly less annoying because at least using CONVERT, you can specify the format of it pretty nicely.

This I sort of understand to some degree, because SQL Server doesn’t store dates the way we present dates.

Bigger Integers

This is more of a documentation beef than anything. If one were to look at the table of substitution types, one might assume that one could use something from the list to pass a bigint to RAISERROR.

Poor SQL Server documentation — missing persons

But nope nyet nein etc. one cannot. Buried a bit further down is this helpful gem:

This one will work. Others will not.

DECLARE
    @bigint bigint = 0;

RAISERROR(N'This is our message %I64d', 0, 1, @bigint);

Forgetful

Finally, it would be nice if RAISERROR raised an error to aid forgetful users. Consider these scenarios:

DECLARE
    @bigint bigint = 0;

RAISERROR(N'This is our message %I64d', 0, 1);
RAISERROR(N'This is our message', 0, 1, @bigint);

These both successfully print the following messages:

This is our message (null)
This is our message

It might drive you batty thinking that somehow your parameter/variable got turned NULL somehow due to a logical bug in your code something.

You know when you execute a store procedure and either don’t pass a parameter value in that it expects, or you try to pass one in that doesn’t exist, and you get an error message immediately?

That would be useful here.

I know, I know. User error. But a little help would be nice here.

Thanks for reading!

Going Further

A Quirk When Rewriting Scalar UDFs In SQL Server

Posted on March 28, 2023March 4, 2023 by Erik Darling

Back To Business

I think I have probably spent 500 hours of my life rewriting T-SQL Scalar UDFs to avoid all the performance problems associated with them.

The obvious choice is the Inline Table Valued Function, which has fewer performance issues baked in. For the kids out there: they don’t spill trauma.

But getting the rewrite right can be tricky, especially when it’s possible for the function to return NULL values.

I’m going to walk through a simple example, and show you how to get the results you want, without adding abusing your developers.

What is not covered in this post are all the performance issues caused by UDFs. If you want to get into that, click the training link at the bottom of this post.

The Problem

Here’s the function we need to rewrite. It returns a simple bit value if a particular user was active after a certain date:

CREATE OR ALTER FUNCTION
    dbo.rewrite
(
    @UserId int,
    @LastAccessDate datetime
)
RETURNS bit
AS
BEGIN
    DECLARE
        @b bit = 0,
        @d datetime = GETDATE(); /*NOFROID4U*/
    
    SELECT
        @b = 
            CASE
                WHEN u.Id IS NOT NULL
                THEN 1
                ELSE 0
            END
    FROM dbo.Users AS u
    WHERE u.Id = @UserId
    AND   u.LastAccessDate > @LastAccessDate;

    RETURN
        @b;
END;
GO

Since I’m using SQL Server 2022 in compatibility level 160, I’m declaring a useless datetime parameter and using GETDATE() to set it to a value to avoid scalar UDF inlining.

We can call it about like so (again, this query is too trivial to suffer any performance issues), and get some reasonable-looking results back.

SELECT TOP (10)
    p.OwnerUserId,
    p.LastActivityDate,
    thing0 = 
        dbo.rewrite
        (
            p.OwnerUserId, 
            GETDATE()
        )
FROM dbo.Posts AS p
WHERE p.Score = 1;

Writeable Media

Rewriting this function looks straightforward. All we need to do is Robocop a few parts and pieces and badabing badaboom we’re done.

Note that to really complete this, we’d also need to add a convert to bit to avoid SQL Server implicitly converting the output of the case expression to a (potentially) different datatype, but we’ll fix that in the final rewrite.

CREATE OR ALTER FUNCTION
    dbo.the_rewrite
(
    @UserId int,
    @LastAccessDate datetime
)
RETURNS table
WITH SCHEMABINDING
AS
RETURN    
    SELECT
        b = 
            CASE
                WHEN u.Id IS NOT NULL
                THEN 1
                ELSE 0
            END
    FROM dbo.Users AS u
    WHERE u.Id = @UserId
    AND   u.LastAccessDate > @LastAccessDate;
GO

Of course, this alters how we need to reference the function in the calling query. Inline table valued functions are totally different types of objects from scalar UDFs.

SELECT TOP (10)
    p.OwnerUserId,
    p.LastActivityDate,
    thing1 = 
        (
            SELECT 
                t.b 
            FROM dbo.the_rewrite
            (
                p.OwnerUserId, 
                GETDATE()
            ) AS t
        )
FROM dbo.Posts AS p
WHERE p.Score = 1;

But the results are disappointing! Where we once had perfectly formed zeroes, now we have a bunch of NULLs that severely harsh our mellow.

This can obviously cause problems for whomever or whatever is ingesting the result set.

Expectations: 1 or 0
Reality: NULL

Shame, that.

Changing The Query

Many developers will attempt something like this first, to replace NULLs in the calling query:

SELECT TOP (10)
    p.OwnerUserId,
    p.LastActivityDate,
    thing1 = 
        (
            SELECT 
                ISNULL
                (
                    t.b, 
                    0
                ) 
            FROM dbo.the_rewrite
            (
                p.OwnerUserId, 
                GETDATE()
            ) AS t
        )
FROM dbo.Posts AS p
WHERE p.Score = 1;

But this will still produce NULL realities where we have zeroed expectations. We could take a step way back and do something like this:

SELECT TOP (10)
    p.OwnerUserId,
    p.LastActivityDate,
    thing1 = 
        ISNULL
        (
            (
                SELECT 
                    t.b
                FROM dbo.the_rewrite
                (
                    p.OwnerUserId, 
                    GETDATE()
                ) AS t
            ),
            0
        )
FROM dbo.Posts AS p
WHERE p.Score = 1;
GO

But this is an ugly and annoying thing to remember. Imagine having to explain this to someone reading or trying to incorporate our beautiful new function into a query.

We should fix this inside the function.

Fixer Upper

I’m not going to pretend this is the only way to do this. You can likely figure out half a million ways to pet this cat. It’s just easy.

CREATE OR ALTER FUNCTION
    dbo.the_inner_rewrite
(
    @UserId int,
    @LastAccessDate datetime
)
RETURNS table
WITH SCHEMABINDING
AS
RETURN    
    
    SELECT
        b = 
            CONVERT
            (
                bit,
                MAX(x.b)
            )
    FROM
    (
        SELECT
            b = 
                CASE
                    WHEN u.Id IS NOT NULL
                    THEN 1
                    ELSE 0
                END
        FROM dbo.Users AS u
        WHERE u.Id = @UserId
        AND   u.LastAccessDate > @LastAccessDate
        
        UNION ALL
        
        SELECT
            b = 0
    ) AS x;
GO

We have:

Our original query, which may return 1 or 0 based on existence
A union all to a zero literal so that a result is guaranteed to be produced
An outer max to get the higher value between the two inner selects

And this will produce expected results, with the final output converted to a bit.

Thanks for reading!

Going Further

SQL Server 2022: FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION

Posted on March 27, 2023March 3, 2023 by Erik Darling

Mouthful

SQL Server 2022 is kind of a boring release. I had high hopes that it would be a rich environment for new blog material, like other releases have been (Except SQL Server 2014. We don’t talk about SQL Server 2014.), but for performance tuners, it’s largely just some more layers of crap tacked on top of of an already complex set of adaptations and automations to sift through when tracking down performance issues.

One thing that apparently hasn’t caught anyone’s eye is the FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION database scoped configuration, which could come in handy when troubleshooting parameter sniffing problems that… SQL Server 2022 claims to solve.

Well, okay then. It also comes with this horrifying, terrifying, sky-is-falling note:

The FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION database scoped configuration option isn’t meant to be enabled continuously in a production environment, but only for time-limited troubleshooting purposes. Using this database scoped configuration option will introduce additional and possibly significant CPU and memory overhead as we will create a Showplan XML fragment with runtime parameter information[…]

So, I guess don’t flip this on if you’re already having CPU and memory problems potentially caused by parameter sniffing scenarios and you need to troubleshoot long running queries?

Hm. I guess I can see why this isn’t lighting the blogopshere on fire.

Enablement

If you’re running SQL Server 2022, and you’re feeling brave, you can flip this thing on like so:

ALTER DATABASE SCOPED CONFIGURATION 
    SET FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION = ON;

Now, the big question becomes: how do you see all this super helpful information at the cost of additional and possibly significant CPU and memory overhead?

You may have some meme scripts that you found on the internet that do things way worse than sp_WhoIsActive, but if you want to see these goodies you’ll need to hit the dm_exec_query_statistics_xml DMF, which… your meme scripts probably don’t do.

Sorry about that.

But you can do this, which is relatively simple and easy even for the most steadfast meme script users:

EXEC sp_WhoIsActive 
    @get_plans = 1;

Now, when you look at the properties of the root plan operator, you should see a parameter list like this:

Which has both the compile and run time values for any parameters your query was supplied. Keep in mind this won’t work with local variables, because they’re not parameters ;^}

Thanks for reading!