A Fine Slice Of SQL Server
Michael J. Swart says don’t worry, be happy:
What’s the best way to avoid most blocking issues in SQL Server? Turn on Read Committed Snapshot Isolation (RCSI). That’s it.
Do check out Erik Darling’s comment as well for one thing to keep in mind if you turn on RCSI.
The other thing to keep in mind is that, if you have WITH(NOLOCK) hanging around everywhere in your code, you won’t get as much of a benefit with RCSI until you remove them.
Paul White continues a series on lock escalation:
When SQL Server reads data under locking read committed isolation (the default for non-cloud offerings) and chooses row-level locking granularity, it normally only locks one row at a time. The engine processes the row through parent operators before releasing the shared lock prior to locking and reading the next row.
When the engine chooses page-level locking granularity, it takes and holds a shared page lock while processing rows on the current page and then releases the lock right before acquiring a lock on the next page.
In general, SQL Server is careful to take and hold locks only when needed and only long enough to guarantee correct results, taking account of the current isolation level. When locking at row granularity, the engine can skip row locks entirely when it’s safe to do.
Read on to understand when SQL Server either gets it wrong or when exigent factors alter the story.
Comments closedErik Darling twiddles some bits and gets away with it:
Recently while working with a client to fix some deadlock issues, they asked about the client options stored in the XML.
I could remember looking at them at one point to see if there was a decoder ring for them, but never found anything definitive.
Read on for Erik’s go at it, as well as Peter Larsson’s great comments.
Comments closedPaul White complicates matters:
In part one of this series, I explained how the lock manager maintains a count of held locks per transaction, and individual access methods keep a separate lock count per HoBt for each plan operator performing data access.
Parallelism adds an extra layer of complexity because both lock counts are maintained per thread.
There are two underlying reasons for this:
Paul explains the reasons and shows off plenty of tests along the way.
Comments closedPaul White gets into the weeds:
This article isn’t about the act of lock escalation itself, which is already well documented and generally well understood. Some myths (like row locks escalating to page locks) persist, but challenging those yet again probably wouldn’t change much.
Instead, the question I’ll address here is exactly how and when lock escalation is triggered. Much of the documentation is incorrect or at least imprecise about this and I’ve been unable to find a correct description in other writings.
There are good reasons you haven’t seen a simple demo of lock escalation taking place at 5000 locks. I’ve seen suggestions such as lock escalation isn’t deterministic, or some types of locks don’t count toward the 5000 lock threshold. Neither of those assertions is true, but the details are interesting, as I’ll explain.
As always, Paul brings clarity to a difficult topic.
Comments closedThere was a need to make changes to a table with an Indexed View. Since Indexed Views must be created with SCHEMABINDING, the View must be dropped and recreated.
From past experience, I knew that this operation blocked all queries (Read/Write) that referenced any table from the View’s definition for the duration of the Clustered index creation, even under the RCSI level.
Because the index might be large and the maintenance window small, I want to do that as fast as possible.
Tom does a good job of taking us through the problem as well as the solution. Definitely worth a read if you’re creating indexed views with Enterprise Edition.
Comments closedWilliam Assaf shows us the bad idea of the day:
Error 1065 states “The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.” However, NOLOCK can still dangerously be used as the source of the write. (NOLOCK can also fail with error 601 on even basic SELECT statements, but coders just aren’t always scared off by that.)
Here’s a very elementary example to share at parties in order to scare developers away from using NOLOCKs adventurously:
Read on for an example which shows the level of pain you can find yourself in with NOLOCK.
Comments closedGrant Fritchey digs into a useful Extended Event:
One of my favorite little bits of information about Extended Events is the fact that everyone running a full instance of SQL Server has deadlock information available to them, even if they never enabled Trace Flag 1222 through the system_health session. That captures the xml_deadlock_report which has the full deadlock graph. However, what if you want to capture deadlock info, but, you’re dealing the GDPR, and transmitting query values could be problematic? Enter xml_deadlock_report_filtered.
Read on to see how it works, though note Grant’s warning that this is a non-documented event.
Comments closedShobika Selvaraj documents lock types in Apache Hive and what commands acquire which types:
In the Shared lock there are two types one is Shared_read and Shared_write. Shared_read means anyother shared_read and shared_write query can run at a time. Shared_write lock which means any other shared_read can be performed but no shared_write lock can acquire at that time.
In Exclusive locks no shared_read or shared_write can perform at the same time.
There are three types of lock state:
(a) Acquired – transaction initiator hold the lock
(b) Waiting – transaction initiator is waiting for the lock
(c) Aborted – the lock has timed out but has not yet been cleaned
I was a bit surprised about inserts being shared read but that’s not a typo in the table—Shobika brings receipts.
Comments closedThomas 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_WhoIsActivestored procedure, which I’ve assumed is present in the master system database. The complete solution is thesp_WhoIsActivestored procedure (which you’ll need to download and create, see http://whoisactive.com/downloads/), a SQL Agent job with a job step that runssp_WhoIsActiveand 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.
Comments closed