A Fine Slice Of SQL Server
Erik 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.
Leave a CommentErik 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 closedThat’s just going to sit there and wait, and wait, and wait, and wait. Because the table was created inside a transaction, the metadata about the table is uncommitted, and thus unavailable to the second session. The result is that my second session waits & waits & waits until the first session is committed (or rolled back).
OK, so it’s being blocked due to an uncommitted transaction. We could try doing dirty reads, right? I’ll kill my second session that’s been blocked, and I’ll throw in
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;. That’s like just like using a nolock hint:
USE AM2_WTF;GOSETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;SELECTOBJECT_NAME(581577110);What the… It’s still being blocked by the uncommitted
CREATE TABLE.
Read on to understand why, and what you can do about it.
Comments closedJamie Wick helps us figure out what’s causing deadlocks:
Recently I started getting random alerts that a job on one of the SQL servers was failing because of a deadlock problem.
The source of the problem wasn’t immediately discernible as there wasn’t any pattern to when the job was failing. Troubleshooting was further complicated by the database being written/maintained by a 3rd party vendor that encrypts all of their stored procedures.
So… How to find out what was causing the deadlock?
Extended Events are an ideal solution for this situation.
Read on to learn how.
Comments closedTo hold onto Shared locks, you’d need to use an isolation level escalation hint, like REPEATABLE READ.
I could do that here if I were a lazy cheater.
Instead, I’m going to show you a more common and interesting scenario.
This leaves the classes of non-lazy cheater and a lazy non-cheater (because non-lazy non-cheater sounds batty). Regardless of your answer, great post by Erik.
Comments closedEmanuele Meazzo has painted a picture of sorts:
I love when it happens
Granted, this is not a picture you want showing up in your production environment, but we can appreciate its beauty from afar.
Comments closedDavid Fowler explains what an intent lock is and why it’s useful:
Let’s just imagine a World without intent locks for a moment. In that World, a user has just decided to select a row from our database. SQL at that point is going to put down a shared lock against the row.
Now what’s going to happen when another user decides to modify a bunch of rows? Now because of the number or rows involved in this modification, SQL is going to want to take out an exclusive page lock. Where’s the issue here?
Read on to learn what the issue is.
Comments closedJonathan Kehayias walks us through an interesting scenario:
A recent consulting engagement was focused on blocking issues inside of SQL Server that were causing delays in processing user requests from the application. As we started to dig into the issues being experienced, it became clear that from a SQL Server standpoint, the problem revolved around sessions in a Sleeping status that were holding locks inside of the Engine. This is not a typical behavior for SQL Server, so my first thought was that there was some sort of application design flaw that was leaving a transaction active on a session that had been reset for connection pooling in the application, but this was quickly proven not to be the case since the locks were later being released automatically, there was just a delay in this occurring. So, we had to dig in further.
Click through to learn what the root cause was.
Comments closed