The command you wish to run does take an ACCESS EXCLUSIVE lock on the table, preventing all other access to that table. But the duration of this lock should be just a few milliseconds, as adding a column like the one you want to add does not require the table to be re-written, it just requires metadata to be updated.
Where the problem can come in, and I ...
As documented in Books Online, UPDLOCK takes update locks and holds them to the end of the transaction.
Without an index to locate the row(s) to be locked, all tested rows are locked, and locks on qualifying rows are held until the transaction completes.
The first transaction holds an update lock on the row where name = 1. The second transaction is blocked ...
SQL Server doesn't keep a history of the commands that have been executed1,2. You can determine what objects have locks, but you cannot necessarily see what statement caused those locks.
For example, if you execute this statement:
INSERT INTO dbo.TestLock DEFAULT VALUES
And look at the SQL Text via the most recent sql handle, you'll see ...
(If you have access to DMVs then look into sp_whoisactive with @find_block_leaders = 1. Just tell you DBA (if you are not) to deploy it and grant you execute permission.)
SQL server dynamic management views are your best friend :
Below are several ways to find out blocking :
Since you're using the First Responder Kit, I'll stick to using it in my examples to teach you how to diagnose this further.
No Missing Indexes?
Why doesn't SQL Server have any missing index requests in the DMVs or Query Plans?
Use sp_BlitzFirst @SinceStartup = 1;, and check out your wait stats.
If LCK_ waits aren't near the top ...
If I understand the request correctly, the goal is to delete batches of rows, while at the same time, DML operations are occurring on rows throughout the table. The goal is to delete a batch; however, if any underlying rows contained within the range defined by said batch are locked, then we must skip that batch and move to the next batch. We must then ...
DDL operations usually lock the object they are acting upon, so should not be performed outside planned maintenance windows (when your users are expecting disruption or the system to be completely offline for up to a planned amount of time) - there is nothing you can do about this easily1.
Some operations only keep a write lock, so your application can keep ...
So, we are changing the approach to delete small batch of rows at at time.
This is a really good idea to delete in small careful batches or chunks. I would add a small waitfor delay '00:00:05' and depending on the recovery model of the database - if FULL, then do a log backup and if SIMPLE then do a manual CHECKPOINT to avoid bloating of transaction log - ...
Unfortunately there is not much you can do about this, this behaviour is by design. The problem manifests itself when user sessions time out because the report is taking too much time. You can try to improve the reports, or configure the session timeout to be a bit longer than the longest running report
See this link for an explanation about the why and ...
Because the metadata functions do not obey transaction isolation semantics. If you want to avoid getting blocked, join to sys.schemas and sys.objects instead of using the metadata functions. This will also allow you to set the isolation level in a single statement instead of peppering NOLOCK hints all over the query...
This was reported by Adam Machanic on ...
Your main options are:
Tune the SELECT query so it uses fewer I/O resources.
Run the query at a quiet time.
Run the query on a separate copy of the database (e.g. a readable secondary).
Run the query in an I/O-limited resource pool as described here*.
Move to a size/tier with higher I/O capacity.
Side note: This answer does not mention using isolation ...
If your intention is to avoid readers from blocking writers and visa-versa in the default READ_COMMITTED isolation level, turn on the READ_COMMITTED_SNAPSHOT database option. This will cause row versioning instead of locking to be used to implement statement-level read consistency.
Although often confused, the ALLOW_SNAPSHOT_ISOLATION option is not related ...
You could update some records with a where clause that never matches like this:
CREATE TABLE locktest (id int, sometext nvarchar(50));
INSERT INTO locktest (id, sometext) VALUES
If you then, in one query window execute this:
UPDATE locktest WITH (tablockx) SET id=null ...
There may be other ways, but this technique seems simple enough:
SET NOCOUNT ON
declare @DummyVariable INT
SET @DummyVariable = (SELECT Top 0 Null FROM Test1 with (holdlock, tablockx))
Take a look at this blog post by Bob Dorr: How It Works: Orphan DTC Transaction (Session/SPID = -2)
As Bob says, it's a bit of a misnomer that it's an orphaned transaction by DTC, but that is usually how it represents itself. It's due to (as per Bob's words) "NO ENLISTED SESSIONS on the SQL Server but the transaction is active yet". You should take a look ...
Before you run your update:
Now, run the update, if it is not completing quick enough, then in another window run:
SELECT status, command, wait_type, last_wait_type, blocking_session_id
WHERE session_id = <@@SPID from above>;
If you get a value in blocking_session_id, then you can run the same query for ...
If this third party app is holding those row locks long enough to cause contention, and
you can't change that behavior,
and the blocked queries are read queries, then
One option would be to enable Read Committed Snapshot Isolation in that database:
ALTER DATABASE [YourDatabaseName]
SET READ_COMMITTED_SNAPSHOT ON;
This should stop those UPDATE queries ...
There are two type of timeouts.
One is when the client application (API) aborts the batch after a certain amount of time. Called "attention" in a SQL server trace. Default in many APIs is 30 seconds.
The other is when the client requests that SQL server abort the query with error 1222 after a certain time. SET LOCK_TIMEOUT. Default is indefinitely.
I can't test this theory at the moment, but based on the most recent capture data posted to GitHub, I would say that the reason that thee <process> node is empty is that it requires a currently running request (many of the attributes are found in sys.dm_exec_requests and not in sys.dm_exec_sessions) and without a currently running request, it can't ...
To complement Max's answer, I have found below utilities extremely useful:
beta_lockinfo - by Erland Sommarskog
I use the beta_lockinfo when I want to deep dive into blocking and analyze what and how blocking arose - which is extremely useful.
beta_lockinfo is a stored procedure that provides information about processes and the locks they hold as well ...
I'd suggest looking for long-running sessions, using an XEvents session.
The problem you're describing sounds like you have client code that is performing row-by-agonizing-row (RBAR) processing instead of using efficient set-based approaches.
Poorly designed client applications may do something like this:
Connect to SQL Server to get a list of items ...
It sounds like you experienced the behavior that's described in this post on the PFE blog:
AlwaysOn Availability Groups unable to query against readable secondary replica database: Wait Type HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING
Essentially, there happened to be a long-running transaction on the primary when the secondary was made readable, and ...
Are locks by default acquired at the beginning of a transaction, or just when they are needed?
Locks are acquired immediately before reading or writing occurs. Depending on the locking granularity selected by the storage engine, locks may be acquired at the row, page, partition, or object (table) level.
If the latter is true, would it therefore be ...
Enabling ALLOW_SNAPSHOT_ISOLATION does not alter the behaviour of your code. Instead it lays the groundwork for you to be able to use row versioning if you wish. Once snapshot isolation has been applied to the database a query can succesfully use row versioning if the following statement is used:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
This post by Kendra ...
You are comparing totally different things which are not comparable.
XE trace you are capturing an event called sqlserver.blocked_process_report which is described here.
Here you are capturing all kind of blocking exceeding a fixed amount of time. If it is less than that duration (set up by sp_configure 'blocked process threshold') it will not be ...
Shows that you have a latch contention problem, and only a latch contention problem.
@ below was run 15 mins after we did node failover
The CPU_USED row should be accurate. And so your workload is only managing to use ...
I wrote Blocking Detector which uses event notification. You can find it here
Below is how it works :
This script will create
- a blocking detection alert
- table to hold blocking information
- modify the 'blocked process threshold' sp_configure option to 5 mins (300 sec)
- create a sql agent job that will fire in resonse to the alert to ...
We are loading potentially thousands of rows into this temp table and just dumping the whole thing out, basically it aggregates a bunch of data.
Temporary tables can be a big performance and reliability win, when used correctly.
For example, it can be very useful to store a relatively small (and narrow) intermediate result set in a temporary table so SQL ...
I know that when a write is happening to a SQL Server database table, a read to that table will have to wait.
This isn't necessarily true. Proper indexing can help mitigate blocking waits by giving queries adequate paths to data, and helping modification queries find the data they're going to be modifying.
I know that it is always best to perform ...