A Fine Slice Of SQL Server
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 closedBob 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 CommentDavid 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 closedBob 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 closedAjay 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 closedJoe 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 closedTomas 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 closedDavid 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 closedPaul Randal does some explaining:
I had an email question over the weekend where someone noticed that while executing a scan of a heap using NOLOCK, there was a BULK_OPERATION lock held on the heap for the duration of the scan. The question was why is the BULK_OPERATION lock needed, as surely there’s no way for the NOLOCK scan to read a problematic page?
Well, the answer is that the extra lock is needed *precisely* because the NOLOCK scan *can* read a problematic page if there’s a bulk operation happening on the heap at the same time.
And don’t call me Shirley.
Click through for a demonstration of this answer.
Comments closedErik Darling says yes to locks…kinda:
That warning often comes with a lot of questions about how to fix blocking problems so you can get rid of those hints.
After all, if you get rid of them, your SQL Server queries will (most likely) go back to using the READ COMMITTED isolation level and we all know that read committed is a garbage isolation level, anyway.
Sadly, a quick regex through your code base isn’t sufficient…
Comments closed