Locking Hints Make Everything Confusing

King Of The DMV


Many people will go their entire lives without using or seeing a lock hint other than NOLOCK.

Thankfully, NOLOCK only ever leads to weird errors and incorrect results. You’ll probably never have to deal with the stuff I’m about to talk about here.

But that’s okay, you’re probably busy with the weird errors and incorrect results.

Fill The Void


It doesn’t matter who you are, or which Who you use, they all look at the same stuff.

If I run a query with a locking hint to use the serializable isolation level, it won’t be reflected anywhere.

SELECT 
    u.*
FROM dbo.Users AS u WITH(HOLDLOCK)
WHERE u.Reputation = 2;
GO 100

Both WhoIsActive and BlitzWho will show the query as using Read Commited.

EXEC sp_WhoIsActive 
    @get_task_info = 2,
    @get_additional_info = 1;

EXEC sp_BlitzWho 
    @ExpertMode = 1;

This isn’t to say that either of the tools is broken, or wrong necessarily. They just use the information available to them.

ah well

Higher Ground


If you set the isolation level at a higher level, they both pick things up correctly.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT 
    u.*
FROM dbo.Users AS u WITH(HOLDLOCK)
WHERE u.Reputation = 2;
GO 100
gratz

Deadlocks, Too


If we set up a deadlock situation — and look, I know, these would deadlock anyway, that’s not the point — we’ll see the same isolation level incorrectness in the deadlock XML.

BEGIN TRAN

UPDATE u
    SET u.Age = 1
FROM dbo.Users AS u WITH(HOLDLOCK)
WHERE u.Reputation = 2;

UPDATE b
    SET b.Name = N'Totally Tot'
FROM dbo.Badges AS b WITH(HOLDLOCK)
WHERE b.Date >= '20140101'

ROLLBACK

Running sp_BlitzLock:

EXEC sp_BlitzLock;
grousin’

 

Again, it’s not like the tool is wrong. It’s just parsing out information from the deadlock XML. The deadlock XML isn’t technically wrong either. The isolation level for the transaction is read committed, but the query is asking for more.

The problem is obvious when the query hints are right in front of you, but sometimes people will bury hints down in things like views or functions, and it makes life a little bit more interesting.

Thanks for reading!

A Word From Our Sponsors


First, a huge thank you to everyone who has bought my training so far. You all are incredible, and I owe all of you a drink.

Your support means a lot to me, and allows me to do nice stuff for other people, like give training away for free.

So far, I’ve donated $45k (!!!) worth of training to folks in need, no questions asked.

Next year, I’d like to keep doing the same thing. I’d also like to produce a whole lot more training to add value to the money you spend. In order to do that, I need to take time off from consulting, which isn’t easy to do. I’m not crying poor, but saying no to work for chunks of time isn’t easy for a one-person party.

I’m hoping that I can make enough in training bucks to make that possible.

Because this sale is extra sale-y, I’ve decided to name it after the blackest black known to man.

From today until December 31st, you can get all 25 hours of my recorded training content for just $100.00. If you click the link below to add everything to your cart, and use the discount code AllFor100 to apply a discount to your cart.

Everything

Everything

Everything

Some fine print: It only works if you add EVERYTHING. It’s a fixed amount discount code that you need to spend a certain amount to have kick in.

Thank for reading, and for your support.

LOB Data And Weird Locks

Working On It


I’ve been re-working a lot of the demos in a presentation that I’m working on called Index Internals That Matter For Performance, because after a first walk through them, I had a lot of realizations about not only what I was presenting, but the way it was presented, and the order I was presenting it in.

This isn’t abnormal, and it’s hardly my finest moment in video history. But I wanted to stream it because a lot of people are out there who might be thinking about getting into blogging or presenting, and I wanted to show that it’s a process, not something you just walk out and nail like a half-court-no-look-over-the-shoulder-nothing-but-net-shot.

Anyway, I came across a weird thing, and had to make some changes to my helper script WhatsUpLocks to get some more information.

And not get blocked.

Read Committed is a trash isolation level. Don’t @ me.

The Not Weirdness


This is the simplest I could work things out to. I don’t have anything very practical to say about it right now.

Here’s an update:

BEGIN TRAN

UPDATE p
    SET p.Score += 1
FROM dbo.Posts AS p
WHERE p.Id = 999;

ROLLBACK

If we run it up to the rollback, it finishes pretty quickly. We are, after all, just updating a single row that we locate via the primary key.

My original idea for the demo was to show some of the odder things you can run into with blocking, so I wrote this query to return a bunch of rows, but get blocked at the very end.

SELECT TOP (100) p.Id, p.Body
FROM dbo.Posts AS p
WHERE p.Id > 900;

Which is exactly what happens. We get to Id 997 and crap out.

endless

Now if we check on those sessions with WhatsUpLocks, we can see what happened.

SELECT *
FROM dbo.WhatsUpLocks(58) AS wul; --Writer SPID

SELECT *
FROM dbo.WhatsUpLocks(57) AS wul; --Reader SPID
i am stuck

Why is this not weird? Well, comparatively, we take a normal number of overall locks and get blocked in a fairly predictable spot. We get blocked waiting on one of the keys that we need to keep going.

The Weirdness


To backtrack a little bit, part of what I wanted to show was that using order by can sometimes result in “more” blocking. I don’t mean more locks; what I mean is that when we need to order by Score, but we don’t have Score indexed in a useful way, the query will get hung up without showing any rows whatsoever.

SELECT TOP (100) p.Id, p.Body
FROM dbo.Posts AS p
WHERE p.Id > 900
ORDER BY p.Score;

Originally this was a SELECT * query, but I want to show you that it’s specific to the Body column because it’s an NVARCHAR(MAX).

Here’s what comes back from looking at the locks now:

uwot

LOOK HOW MANY LOCKS WE TAKE ON PAGES. That’s bananas.

Watch my video on readers blocking writers for a little background on why this could be troublesome.

If I change my query to not have the Body column in the select list, the locks go back to normal.

SELECT TOP (100) p.Id, p.Score
FROM dbo.Posts AS p
WHERE p.Id > 900
ORDER BY p.Score;
nermal

Of course, sticking Body in the WHERE clause results in an uptick in shared locks taken:

SELECT TOP (100) p.Id, p.Score
FROM dbo.Posts AS p
WHERE p.Id > 900
AND p.Body LIKE N'_%'
ORDER BY p.Score;
that’s nice, dear

But Of Course…


This kind of thing is maybe not the most likely thing you’ll see happening IRL, because you probably have other indexes that queries can use to access data in different ways. For instance, if I have this index on the Posts table, the first query will still get blocked, but all of the other queries will finish instantly.

CREATE INDEX surprise ON dbo.Posts(Score, Id);

Is this another reason to avoid MAX datatypes? Maybe probably.

Don’t forget that they also prevent predicate pushdown, and they can really mess with memory grants.

But hey, I’m sure you have more than enough indexes to fix everything.

Thanks for reading!

Locking And Blocking Week: Recap

Sonic Deducer


This week I’m giving away a sampling of the videos from my Locking and Blocking course.

If you want to see the entire thing, it’s available this week for just .99 cents.

All you have to do is add it to your cart, and the discount will be applied at checkout.

If you like what you see here, sign up for my email list to get 50% off your next purchase.

 

Locking And Blocking Week: Weird Blocking

Dead and Alive


This week I’m giving away a sampling of the videos from my Locking and Blocking course.

If you want to see the entire thing, it’s available this week for just .99 cents.

All you have to do is add it to your cart, and the discount will be applied at checkout.

If you like what you see here, sign up for my email list to get 50% off your next purchase.

Locking And Blocking Week: Tuning Begin Tran

Beginning OF The Tran


This week I’m giving away a sampling of the videos from my Locking and Blocking course.

If you want to see the entire thing, it’s available this week for just .99 cents.

All you have to do is add it to your cart, and the discount will be applied at checkout.

If you like what you see here, sign up for my email list to get 50% off your next purchase.

Locking And Blocking Week: Tuning Blocking With Indexes

All This And More


This week I’m giving away a sampling of the videos from my Locking and Blocking course.

If you want to see the entire thing, it’s available this week for just .99 cents.

All you have to do is add it to your cart, and the discount will be applied at checkout.

If you like what you see here, sign up for my email list to get 50% off your next purchase.

Locking And Blocking Week: Introduction

I Wanna Know What Lock Is


This week I’m giving away a sampling of the videos from my Locking and Blocking course.

If you want to see the entire thing, it’s available this week for just .99 cents.

All you have to do is add it to your cart, and the discount will be applied at checkout.

If you like what you see here, sign up for my email list to get 50% off your next purchase.

 

Lock Waits Come From Blocking, Not Locks Being Taken

Waiting Game


In SQL Server, troubleshooting blocking problems is a pain. It’s one of those things you really have to be monitoring for pretty actively in order to catch the full extent of it.

You might catch glimpses of it in sp_WhoIsActive or sp_BlitzWho, but it’s really hard to get the full picture and blocking chain for the duration of the event.

This is much easier done via dedicated monitoring tools, where the GUI will often give you a tree view of the blocking.

Does Query Store Help?


In SQL Server 2017, we got some high level wait stats associated with queries.

These are generally more helpful to identify which queries were being blocked, but still not who was doing the blocking.

Regular wait stats (i.e. from sys.dm_os_wait_stats) have no association to the queries that caused them.

If you’re on SQL Server 2017 or better, and you’re using Query Store, you can get a look at those waits with a query like this:

SELECT   qsws.wait_category_desc,
         SUM(qsws.total_query_wait_time_ms) / 1000. AS total_query_wait_time_s,
         AVG(qsws.avg_query_wait_time_ms) / 1000. AS avg_query_wait_time_s,
         MAX(qsws.max_query_wait_time_ms) / 1000. AS max_query_wait_time_s
FROM     sys.query_store_wait_stats AS qsws
GROUP BY qsws.wait_category_desc
ORDER BY total_query_wait_time_s DESC;

The view of your wait stats is far less detailed, but at least it’s only this one database. Look, at least you get that.

With that out of the way, let’s take that simple query and make a couple minor adjustments to get some other information out.

WITH qs_waits AS (
SELECT   qsws.wait_category_desc,
         qsws.plan_id,
         SUM(qsws.total_query_wait_time_ms) / 1000. AS total_query_wait_time_s,
         AVG(qsws.avg_query_wait_time_ms) / 1000. AS avg_query_wait_time_s,
         MAX(qsws.max_query_wait_time_ms) / 1000. AS max_query_wait_time_s
FROM     sys.query_store_wait_stats AS qsws
WHERE qsws.wait_category_desc = 'Lock'
GROUP BY qsws.wait_category_desc, qsws.plan_id
)
SELECT qsw.*,
       r.*,
       p.query_sql_text,
       TRY_CONVERT(XML, p.query_plan) AS query_plan
FROM qs_waits AS qsw
OUTER APPLY
(
    SELECT TOP (1) qsp.plan_id,
                   qsp.query_plan,
                   qsqt.query_sql_text
    FROM sys.query_store_plan AS qsp
    JOIN sys.query_store_query AS qsq
        ON qsq.query_id = qsp.query_id
    JOIN sys.query_store_query_text AS qsqt
        ON qsq.query_text_id = qsqt.query_text_id
    WHERE qsw.plan_id = qsp.plan_id
    ORDER BY qsp.last_execution_time DESC
) AS p
OUTER APPLY
(
    SELECT TOP (1) qsrs.avg_duration / 1000. AS avg_duration, 
                   qsrs.avg_cpu_time / 1000. AS avg_cpu_time
    FROM sys.query_store_runtime_stats AS qsrs
    WHERE qsrs.plan_id = p.plan_id
    ORDER BY qsrs.last_execution_time DESC
) AS r;

There we go.

Nice, clean, simple, but most important totally intuitive. I love how easy it is to quickly get the information you want.

😶

Lookin’ For Locks


That query will get information about queries which waited on Locks — that’s why we’re getting plan_id up in our CTE.

After that, we’re getting the plan and text of any queries that waited on locks, and a couple vanity metrics.

Growing Away

Only one query waited on locks. Make a couple notes here, though:

  • avg_query_wait_time is 21 seconds
  • avg_query_duration is 21 seconds
  • avg_cpu_time is 0.12 seconds

My workload is Highly Contrived™ so the avg wait and query duration line up. In real life, you probably won’t have queries that were only ever waiting on locks to be released.

But it’s worth making some comparisons like this when you’re having blocking problems, especially when you tie wait times and durations in with cpu time.

If cpu is very low but duration is high, generally, you’ve found blocked queries.

Collectiving

Looking at the text and plan, we can also reasonably surmise that this read query wasn’t blocking anything.

But Still…


This doesn’t tell us what query was blocking the select. If you want to figure that out, you’ve got some options:

Sure, you could also go digging through DMVs to find modification queries that last ran around the same time, but I’ve hit a lot of dead ends there.

A lot of this pain would go away if SQL Server were optimistic by default, or if you’re allowed to enable an optimistic isolation level.

And remember: Queries taking locks don’t register locking waits. Only queries being blocked register locking waits.

For a limited time, get my Locking and Blocking course for just 99 cents!

Thanks for reading!

Indexed View Maintenance Is Only As Bad As Your Indexes

Wah Wah Wah


Yes, indexed view maintenance can be quite rough. I don’t mean like, rebuilding them. I will never talk about that.

I mean that, in some cases locks are serializable, and that if you don’t mind your indexes you may find run-of-the-mill modifications taking quite a long time.

Let’s go look!

Mill Town


Let’s get update a small chunk of the Posts table.

BEGIN TRAN
UPDATE p
SET p.Score += 100
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656;
ROLLBACK

Let’s all digress from the main point of this post for a moment!

It’s generally useful to give modifications an easy path to find data they need to update. For example:

Uh no

This update takes 1.6 seconds because we have no useful index on OwnerUserId. But we get a daft missing index request, because it wants to include Score, which would mean we’d need to then update that index as well as read from it. Locking leads to NOLOCK hints. I tend to want to introduce as little of it as possible.

With an index on just OwnerUserId, our situation improves dramatically.

100000X IMPROVEMENT

Allow Me To Reintroduce Myself


Let’s see what happens to our update with an indexed view in place.

CREATE OR ALTER VIEW dbo.PostScoresVotes
WITH SCHEMABINDING
AS 
SELECT   p.Id, 
         SUM(p.Score * 1.0) AS ScoreSum, 
		 COUNT_BIG(v.Id) AS VoteCount,
		 COUNT_BIG(*) AS OkayThen 
FROM     dbo.Posts AS p
JOIN     dbo.Votes AS v
    ON p.Id = v.PostId
WHERE p.PostTypeId = 2
AND   p.CommunityOwnedDate IS NULL
GROUP BY p.Id;
GO

CREATE UNIQUE CLUSTERED INDEX c_Id 
    ON dbo.PostScoresVotes(Id);

Our update query now takes about 10 seconds…

Oof dawg

With the majority of the time being spent assembling the indexed view for maintenance.

Yikes dawg

The Problem Of Course


Is that our indexes are bad. We’ve got no helpful index between Posts and Votes to help with the assembly.

Our first clue may have been when creating the indexed view took a long time, but hey.

Let’s fix it.

CREATE INDEX v ON dbo.Votes(PostId);

Now our update finishes in about a second!

Cleant Up

Thanks for reading!

Waiting For Deadlocks

Try And Retry

I’ll sometimes see people implement retry logic to catch deadlocks, which isn’t a terrible idea by itself. The problem that may arise is when the deadlock monitor takes a full 5 seconds to catch a query, which can block other queries, and may generally make things feel slower.

Different Locks


An alternative is to set a lock timeout that’s shorter than five seconds.

DECLARE @lock_try INT = 0

WHILE @lock_try < 5 
BEGIN
    BEGIN TRY

        SET LOCK_TIMEOUT 5; /*five milliseconds*/

        SELECT COUNT(*) AS records FROM dbo.Users AS u;

    END TRY
    BEGIN CATCH

        IF ERROR_NUMBER() <> 1222 /*Lock request time out period exceeded.*/
		RETURN;

    END CATCH;

SET @lock_try += 1;

WAITFOR DELAY '00:00:01.000' /*Wait a second and try again*/

END;

While 5 milliseconds is maybe an unreasonably short time to wait for a lock, I’d rather you start low and go high if you’re trying this at home. The catch block is set up to break if we hit an error other than 1222, which is what gets thrown when a lock request times out.

This is a better pattern than just hitting a deadlock, or just waiting for a deadlock to retry. Normally when a deadlock occurs, one query throws an error, and there’s no attempt to try it again (unless a user is sitting there hitting submit until something works). Waiting ~5 seconds (I know I’m simplifying here, and the deadlock monitor will wake up more frequently after it detects one)

The big question is: are you better off doing this in T-SQL than in your application?

Thanks for reading!