All Questions
Tagged with sql-server-2017 locking
8 questions
2
votes
1
answer
105
views
Is there any information in Query Store that can be used to find block leaders?
I am on a server with major blocking issues. Not deadlocks, just plain blocks. We know that it is the server's primary bottleneck. However, they do not know what the block leaders are, so I do not ...
- 855
3
votes
0
answers
69
views
request_status is Grant and Convert on the same resource_type at the same time
I have a typical blocking scenario due to lock contention.
Lead blocker has these locks on an object.
<Object name="Table1" schema_name="dbo">
<Locks>
<Lock ...
- 13.6k
0
votes
1
answer
342
views
Table Lock Escalation - Performance Questions
I have few questions on table lock escalations, please confirm or disprove:
Index Scan operator in a query results in Table Lock Escalation ? always or not (small tables) ?
When Shared lock is ...
- 5,964
1
vote
1
answer
55
views
Lock addresses and page addresses don't match
Today, I discovered the command DBCC PAGE(). I understand that it displays information about the data stored at the page level.
(This question is about SQL Server 2017 - v14.0)
So this is what I do :...
- 111
1
vote
1
answer
2k
views
How to find out which table/object the KEY or PAGE or EXTENT locks in the sys.dm_tran_locks belong to?
When I query sys.dm_tran_locks, a couple of questions arise:
I can see there are some locks that are placed at KEY, PAGE, EXTENT, RID level (resource_type column). How to find out to which table or ...
- 5,964
0
votes
2
answers
1k
views
Why Table Lock Escalation is happening in my scenario
I have a table Table1 that is updated in small chunks using below query:
update top (1000) Table1
set VarcharColumn1 = 'SomeValue'
from Table1
where ID in (select ID from Table2)
and ...
- 5,964
14
votes
1
answer
476
views
Why does this RX-X lock not appear in Extended Events?
The Problem
I have a pair of queries that, under serializable isolation, cause an RX-X lock. However, when I use Extended Events to watch lock acquisition, the RX-X lock acquisition never appears, it ...
- 4,179
1
vote
0
answers
425
views
Why does an UPDATE to a full-text-indexed column lock the full text index?
I'm troubleshooting a deadlock on a SQL Server full-text index. From what I can see in SentryOne's monitoring, two queries deadlocked when trying to acquire locks on the full-text index; one query was ...
- 113