33

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 ...


19

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 ...


15

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: BEGIN TRANSACTION INSERT INTO dbo.TestLock DEFAULT VALUES And look at the SQL Text via the most recent sql handle, you'll see ...


14

(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 : --Ref: https://sqlserverperformance.wordpress.com/category/diagnostic-queries/ ...


12

Free Unit 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? First, Wait Use sp_BlitzFirst @SinceStartup = 1;, and check out your wait stats. If LCK_ waits aren't near the top ...


10

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 ...


9

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 - ...


8

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 ...


8

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 ...


8

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 ...


8

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 ...


8

There may be other ways, but this technique seems simple enough: SET NOCOUNT ON BEGIN TRANSACTION declare @DummyVariable INT SET @DummyVariable = (SELECT Top 0 Null FROM Test1 with (holdlock, tablockx))


7

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 ...


7

Before you run your update: SELECT @@SPID; 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 FROM sys.dm_exec_requests WHERE session_id = <@@SPID from above>; If you get a value in blocking_session_id, then you can run the same query for ...


7

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 ...


7

You could update some records with a where clause that never matches like this: Test setup: CREATE TABLE locktest (id int, sometext nvarchar(50)); INSERT INTO locktest (id, sometext) VALUES (1, 'dqsmfkqdsfjm'), (2, 'qmsdfmdj'), (3, 'qkfjmsdfjk'); If you then, in one query window execute this: BEGIN TRAN; UPDATE locktest WITH (tablockx) SET id=null ...


6

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 ...


6

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 ...


6

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 ...


6

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 ...


5

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 ...


5

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 ...


5

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 ...


5

This: wait_type wait_time_ms_per_sec PAGELATCH_EX 733024.4 PAGELATCH_SH 328846.4 CPU_USED 7131.4 WRITELOG 6484.4 Shows that you have a latch contention problem, and only a latch contention problem. Since @ 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 ...


5

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 ...


5

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 ...


5

Check out this post from Erik Darling: CXCONSUMER Is Harmless? Not So Fast, Tiger That shows a really extreme example of a problem query where CXCONUSMER is the highest wait. So while the Microsoft recommendations indicate it's harmless, it definitely can be a sign of other problems. Based on that, and your screenshot, it's quite possible that you're ...


4

Actually in order to be blocking a session only has to hold a lock to a resource something else wants. It does not actually have to be doing anything. For example if you run the following BEGIN TRANSACTION UPDATE MyTable SET Col1 = 'ABC' And don't run a COMMIT then you are going to leave a transaction open. The session is not doing anything and in ...


4

Despite what the official documentation says, dropping a clustered index using ALTER TABLE DROP CONSTRAINT can be performed online (tested on SQL Server 2005-2014): USE Sandpit; ALTER DATABASE Sandpit SET RECOVERY SIMPLE; CREATE TABLE dbo.Test ( col1 integer NOT NULL, CONSTRAINT PK PRIMARY KEY CLUSTERED (col1) ); INSERT dbo.Test VALUES (1); ...


4

SQL Server by default adheres to ACID database rules. The A in ACID stands for atomic, meaning that the whole transaction happens, or none of it does. The I in ACID stands for isolation, meaning that transactions are isolated from one another, so either all the results from one transaction are visible to another transaction, or none of them are. This is ...


Only top voted, non community-wiki answers of a minimum length are eligible