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
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.
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
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
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.
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 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.
missing persons
But nope nyet nein etc. one cannot. Buried a bit further down is this helpful gem:
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.
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;
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.
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 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:
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.
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.
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
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 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 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.
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.
