A Fine Slice Of SQL Server
Erik Darling hits on an interesting issue:
You’ll see the exchange event, and you’ll also see the same query deadlocking itself.
This is an admittedly odd situation, but one I’ve had to troubleshoot a bunch of times.
In other words, parallel threads on the same query causing the query to deadlock on itself. Click through to learn what you can do about it.
Comments closedMichael J. Swart walks us through spinlock contention:
When that occurred, the number of batch requests that the server could handle dropped significantly. So we saw more CPU use, but less work was getting done.
The high demand kept the CPU at 100% with no relief until the demand decreased. When that happened, the database seemed to recover. Throughput was restored and the database’s metrics became healthy again. During this trouble we looked at everything including the number of spins reported in the sys.dm_os_spinlock_stats dmv.
The spins and backoffs reported seemed extremely high, especially for the category “XVB_LIST”, but we didn’t really have a baseline to tell whether those numbers were problematic. Even after capturing the numbers and visualizing them we saw larger than linear increases as demand increased, but were those increases excessive?
Read on for the answer.
Comments closedAaron Bertrand walks us through a painful scenario:
We recently performed a DDL operation against a SQL Server table – simply increasing the size of a varchar column – which should have been instantaneous. Instead, we killed the SQL Server process after observing 20 minutes of HARD_SYNC_COMMIT waits and a blocked replication log reader. Could this issue have been avoided? What went wrong?
I spotted the issue pretty quickly, but it’s easy to miss in a code review. Read the whole thing.
Comments closedJosh Darnell reminds us to check constructor defaults:
Despite the fact that the SQL Server default isolation level is
READ COMMITTED, the default isolation level when usingTransactionScopeisSERIALIZABLE.This problem has been around a long time. David Browne (Microsoft) wrote about it in checks watch 2010!
Read the whole thing.
Comments closedDan Jackson explains the concept of latching in SQL Server:
To start with, a basic definition: ‘Latches are lightweight synchronization objects, that are used by the storage engine of SQL Server to protect the internal memory structures’. Compare this with locks in SQL server, which are a transaction level construct to manage concurrency, latches work at the thread level to maintain data integrity within the internal memory structures. They are not exposed outside of the SQL Server Operating System (SQLOS). They are only managed by SQL Server itself, not by users (unlike locks that can be overridden via lock hints or changing isolation level). It is useful to keep in mind that a single transaction can use multiple threads at the same time.
Latching is a funny concept to me, in that I think people say “This must be a latching problem” far more than there actually is, but when there is a proper latching problem, it usually winds up being a pretty big deal.
Comments closedDan Jackson provides us a primer on how SQL Server implements locking:
We have all heard of locks and can probably even name a few; shared or exclusive locks, for example. We notice them more when something goes wrong and we run into blocking or other performance problems, but what are locks and how do they work in SQL Server?
In this blog, I aim to give a basic answer to that question and provide you with an overview of the different lock modes in SQL Server and how it all works…
Let us make it clear from the off; locks are an essential part of SQL Server. In a multi-user system, there will be many users who wish to access the same resources at the same time. This means that SQL Server must have measures in place to handle concurrency and prevent adverse side effects. Locking is one of those measures.
Read the whole thing.
Comments closedErik Darling notes a locking scenario specifically with LOB data:
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.
Read the whole thing.
Comments closedErik Darling shows that the meter’s only running on lock waits when there’s a blocking condition:
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.
Read on for the demo, as well as an expired coupon.
Comments closedJana Sattainathan diagnoses a deadlocking issue:
We know what deadlocks are and some of the common reasons they happen. If you need a refresher, I recommend this good article. I am not going to rehash what has already been said although these high level points are worth noting to resolve them:
1) Examine known Parallelism (where you have parallelized jobs)
2) Examine unknown Parallelism (unknown jobs or users interfere with your jobs in parallel)
3) Arrange order of tables doing DML to be the same across all code. E.g., Always Customers first, Orders second, OrderDetails third.
4) Examine the indexes on the affected tables to eliminate full-table scans
5) Reduce the amount of time spent in a transaction
6) Update in chunks especially if updating/deleting across sessions
7) Avoid RBAR (Row By Agonizing Row) CRUD operations! Do statement based mass changes.
Read on to understand Jana’s situation and solution.
Comments closedFor example, I want to prevent users from executing the same stored procedure at the same time. To demonstrate the scenario, I have created a stored procedure named procInsertEmployees, which inserts data into the tblEmployee table. I want to make sure that no one can access the stored procedure until the stored procedure inserts the data in the tblEmpoyee table. Moreover, I have added a waitfor delay ’00:00:15’ statement to simulate the blocking for the 15 seconds.
Application locks also allow for more complicated scenarios and you can get a bit creative when assigning resources (such as combining a process name and a parent ID).
Comments closed