Press "Enter" to skip to content

Category: Locks, Blocks, and Deadlocks

Sending E-mails on Database Blocking

Published 2022-06-20 by Kevin Feasel

Thomas Williams combines a few tools:

If your SQL Servers are under pressure, you want to know. Blocking and blocked processes impact end-users, and if not addressed can slow or even stop a database. In this post, I’ll outline a method I use to get timely notifications of blocking processes that you can use too.

I adapted my approach from Tom Collins’s excellent – and still relevant – 2017 article “How to monitor Blocked Processes with SQL Alert and email sp_whoisActive report” at https://www.sqlserver-dba.com/2017/01/how-to-monitor-blocked-processes-with-sql-alert-and-email-sp_whoisactive-report.html. You could implement exactly what Tom covers in his post and come out on top. I’ve gone one small step further to send a formatted HTML e-mail with a table of blocking & blocked processes; like Tom, I generate the table using Adam Machanic’s fantastic sp_WhoIsActive stored procedure, which I’ve assumed is present in the master system database. The complete solution is the sp_WhoIsActive stored procedure (which you’ll need to download and create, see http://whoisactive.com/downloads/), a SQL Agent job with a job step that runs sp_WhoIsActive and sends the e-mail, and an alert that calls the SQL Agent job when there’s blocked processes.

Read on for the script, as well as some important notes.

Leave a Comment

The Upsert Pattern and Unique Indexes in SQL Server

Published 2022-03-21 by Kevin Feasel

Michael J Swart recommends a unique index:

To avoid deadlocks when implementing the upsert pattern, make sure the index on the key column is unique. It’s not sufficient that all the values in that particular column happen to be unique. The index must be defined to be unique, otherwise concurrent queries can still produce deadlocks.

Read on for Michael’s thoughts as well as some smart commenters adding on.

Comments closed

Learning about Locks in SQL Server

Published 2022-02-22 by Kevin Feasel

Bob Dorr migrates over a couple of posts on locking. The first one describes lock enumeration:

SQL Server has a lock iterator class which is used by Lock Monitor (deadlock detection), DMVs such as dm_tran_locks and other workers.   The iterator is designed to remain lock free whenever possible to avoid contention with active queries.  To accomplish this the iterator uses a marker (bookmark) approach and the lock type of NL.  The NL lock does not block and provides a bookmark (reference count and location) supporting the iterator.

The other post covers lock partitioning:

Shared access blocks exclusive acquire requests. The shared access needs only to acquire on the worker’s local partition. An attempt to acquire exclusive access is blocked by the shared access holders. Conversely to acquire exclusive access the worker must acquire the exclusive access of every partition. The exclusive acquisition path is longer than the shared path because N partitions must be acquired to achieve the exclusive access. Increasing the number of partitions increases the work required to acquire exclusive access. This means that only certain protection paths should use the partitioning approach.

Both get pretty deep into internals.

1 Comment

Finding Locking Chains in SQL Server

Published 2022-02-10 by Kevin Feasel

David Fowler is looking for lead blockers:

By far the most common cause for poor performance that I see is blocking. Unless you’ve got some monitoring in place it’s near impossible to identify the cause of any historical blocking incidents. Luckily, if you’re doing as I do and running the First Responder Kit regularly into tables, you will have a history of running statements thanks to sp_BlitzWho. That data will contain the SPIDs of any blocking processes.

Now, if you’ve ever had to look at that data and tried to figure out what the blocking chain is then you’ll know that it can be a total headache.

But David has a solution to find those fullbacks and pulling guards.

Comments closed

Negative Blocking Session IDs

Published 2022-01-24 by Kevin Feasel

Bob Dorr explains what those negative session IDs actually mean:

SQL Server may report a blocking session id as a negative integer value. SQL Server uses negative sessions ids to indicate special conditions.​​ 

Click through for the table. Bob also includes information on -5, the “any task/session can release the latch” scenario. This also covers information on the latches themselves and is worth keeping around in case you run into an issue at some point.

Comments closed

Alerting on Blocking in SQL Server

Published 2021-12-13 by Kevin Feasel

Ajay Dwivedi sets up an alert:

Recently one of my LinkedIn friends contacted me for a blocking alert that would work on on-prem & cloud SQL Server instances alike. Previously I wrote https://ajaydwivedi.com/2018/08/how-to-setup-blocking-alerts-on-sql-server blog post for on-prem SQL Server blocking alert which makes use of WMI based event.

So I wrote the following blocking alert setup code for SQL Server that has the below features –

– Send mail notification to one or more recepients unlike SQL Agent job which is restricted to only one operator.

– Parameter to control the consistent blocking threshold. Nobody wants spontaneous blockings which comes/goes.

– Parameter to control the mail notification delay. Say, I want to be notified every X minutes (defult 15 minutes).

– Parameter to control the mail notification subject.

– Separate mail notification for Blocking & Script failure itself.

– Auto-clearing feature. Means, if the blocking is resolved, we should get an automated mail notification saying Blocking is cleared.

Click through for the instructions.

Comments closed

Locking Issue with Columnstore Indexes

Published 2021-12-03 by Kevin Feasel

Joe Obbish troubleshoots an issue on tables with columnstore indexes:

I recently ran into a production issue where a SELECT query that referenced a NOLOCK-hinted table was hitting a 30 second query timeout. Query store wait stats suggested that the issue was blocking on a table with a nonclustered columnstore index (NCCI). This was quite unexpected to me and I was eventually able to produce a reproduction of the issue. I believe this to be a bug in SQL Server that’s present in both RTM and the current CU as of this blog post (CU14). The issue also impacts CCIs as well but I did significantly less testing with that index type.

Read on for the issue, how you can replicate it, and a couple ways to work around it.

Comments closed

Intent Shared Locks in RCSI

Published 2021-11-23 by Kevin Feasel

Tomas Zika troubleshoots a deadlocking problem:

I was analyzing a deadlock graph and there was a mystery lock of type IS (Intent Shared). That was weird by itself because the database has Read Committed Snapshot Isolation (RCSI) enabled, which is the Optimistic Concurrency model that shouldn’t take shared locks. All the statements were contained in this database. Also, the locked table was seemingly unrelated to anything that has been going on in the deadlock report.

Click through for an image, a repro script, and an explanation as to what exactly is going on.

Comments closed

Finding Processes Waiting on Spinlocks

Published 2021-11-08 by Kevin Feasel

David Fowler helps us find processes waiting on spinlocks:

Spinlock contention is always a real headache to deal with. I recently saw an issue when spinlock contention on SOS_CACHESTORE was making the server virtually unresponsive. The issue was very intermittent with no obvious pattern but the assumption is that it was caused by a particular process in the application. Finding what that process was the tricky part, they don’t show up as waiting tasks so your usual scripts for looking for waiting processes may not work here.

It’s almost never spinlocks, but when it is, this is how you figure it out.

Comments closed