Tag: create index in sql server

In SQL Server, Blocking Begets Deadlocking

Posted on 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:

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

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.

Grievances While Working With RAISERROR In SQL Server

Posted on 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:

Poor SQL Server documentation
peekaboo

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

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.

A Quirk When Rewriting Scalar UDFs In SQL Server

Posted on 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;
SQL Server Query Results
who i smoke

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.

SQL Server Query Results
torment

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

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.

SQL Server 2022: FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION

Posted on 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:

SQL Server Query Plan
it’s just you and me

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!

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.

SQL Server Query Plans Need A Replay Button

Posted on by Erik Darling

No Repro, No Cry

Sometimes you pull a query plan from the plan cache or query store, and it’s not really clear why it was slow. You want to get an actual execution plan, but…

When you look at the query text, there are a whole bunch of parameters, and… You know what comes next.

CREATE OR ALTER PROCEDURE
    dbo.CollectemAll
(
    @Id int,
    @AcceptedAnswerId int,
    @AnswerCount int,
    @CommentCount int,
    @FavoriteCount int,
    @LastEditorUserId int,
    @OwnerUserId int,
    @ParentId int,
    @PostTypeId int,
    @Score int,
    @ViewCount int,
    @TheDiff int,
    @ClosedDate datetime,
    @CommunityOwnedDate datetime,
    @CreationDate datetime,
    @LastActivityDate datetime,
    @LastEditDate datetime,
    @Body nvarchar(MAX),
    @LastEditorDisplayName nvarchar(80),
    @Tags nvarchar(300),
    @Title nvarchar(500)
)

You have to dig into the plan XML, grab an unfortunate wall of text, and…

  • If it’s a stored procedure, grab the values and execute it
  • If it’s an ORM or other parameterized query, have a real bad time
<ColumnReference Column="@LastActivityDate" ParameterDataType="datetime" ParameterCompiledValue="'2008-01-01 00:00:00.000'" ParameterRuntimeValue="'2008-01-01 00:00:00.000'" />
<ColumnReference Column="@CreationDate" ParameterDataType="datetime" ParameterCompiledValue="'2008-01-01 00:00:00.000'" ParameterRuntimeValue="'2008-01-01 00:00:00.000'" />
<ColumnReference Column="@Score" ParameterDataType="int" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
<ColumnReference Column="@PostTypeId" ParameterDataType="int" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)" />
<ColumnReference Column="@OwnerUserId" ParameterDataType="int" ParameterCompiledValue="(22656)" ParameterRuntimeValue="(22656)" />

You can either create a temporary stored procedure to execute the ORM query, or declare a bunch of variables and use dynamic SQL to have them behave like proper parameters. Local variables are not a substitute.

This sucks.

Storage Unit

Since all of these details are already stored in the query plan, why do we have to do all this work?

I get it to a point. Things like temporary objects or modification queries complicate things. But for simple select queries it should be a clear shot to hit play and have the query execute to get an actual execution plan.

There’s also limitations in the plan XML, like if you’re one of those awful people who writes 50 page queries that can’t fully be stored in there. This becomes somewhat less of an issue with query store, since the full query text is available alongside the execution plan.

But I’m fine with hitting errors in those cases, because you usually have a lot more factors to contend with.

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.

Tuning SQL Server Queries With Filtered Indexes, Computed Columns, And Indexed Views Recap

Posted on by Erik Darling

Tuning SQL Server Queries With Filtered Indexes, Computed Columns, And Indexed Views Recap

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.

Tuning SQL Server Queries With Indexed Views: Gotchas With Parameters And Variables Demo

Posted on by Erik Darling

Tuning SQL Server Queries With Indexes Views: Gotchas With Parameters And Variables Demo

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.

Tuning SQL Server Queries With Indexed Views: Gotchas With Parameters And Variables Lecture

Posted on by Erik Darling

Tuning SQL Server Queries With Indexes Views: Gotchas With Parameters And Variables

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.

Tuning SQL Server Queries With Indexed Views Demo

Posted on by Erik Darling

Tuning SQL Server Queries With Indexed Views Demo

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.

Tuning SQL Server Queries With Indexed Views Lecture

Posted on by Erik Darling

Tuning SQL Server Queries With Indexed Views Lecture

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.