I was trying to capture blocking query using blocked process report past 1 hour as this link https://www.mssqltips.com/sqlservertip/4480/find-blocking-processes-using-sql-server-profiler/ I can get the blocked process query using sqlhandle but for blocking process query i cannot get it because no sqlhandle and inputbuf doesnt have any SQL statement. Any idea? This is first time i m using this blocked process report.

We have an instance of SQL Server which has multiple databases. A process in one database seems to be blocking a process in another database. When I look in activity monitor I can see a header blocker (A one in the header blocker column). This seems to be blocking other processes in different databases. I can see their ids in the blocked by column when I select one from the drop down. Am I correct that it is cross database blocking? I didn't think this was possible. They are all running exactly the same stored procedure, but they have their own instance in each database. They are doing updates and inserts, but only within their own databases. eg UPDATE SCA SET SCA.date_last_read_only = TDR.date_seen FROM [dbo].[SINGLE_CERT_ACC] SCA INNER JOIN #TMP_DELTA_READONLY TDR ON SCA.id = TDR.id SET @RecsUpdated = @RecsUpdated + @@ROWCOUNT

I'm using XE blocked_process_report to detect and analyse the blocking. But since it's a point-in-time situation, I only see the blocking leader's statement currently being run and the incompatible locks the blocked session is trying to get. So if the blocking leader had several batches/statements in a transaction, I cannot find out which of the previous statements causes the blocking. Repro script

/* Set up tables */
CREATE TABLE dbo.FirstQuery (Id int PRIMARY KEY)
CREATE TABLE dbo.SecondQuery (Id int PRIMARY KEY)

INSERT INTO dbo.FirstQuery (Id)
OUTPUT Inserted.Id INTO dbo.SecondQuery ( Id )
VALUES (1), (2), (3)

/* set up the blocked process event */

EXEC sys.sp_configure
        @configname = 'blocked process threshold (s)' -- varchar(35)
      , @configvalue = 10 -- int
    
RECONFIGURE 

CREATE EVENT SESSION [blocked_process_report] ON SERVER 
ADD EVENT sqlserver.blocked_process_report
(
    ACTION(sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)
)
GO
ALTER EVENT SESSION [blocked_process_report] ON SERVER STATE = START

/* Session 1 - run first and don't commit or rollback */
BEGIN TRANSACTION
    DELETE FROM dbo.FirstQuery
GO
    DELETE FROM dbo.SecondQuery
-- ROLLBACK

/* Session 2 - run second */
BEGIN TRANSACTION
    DELETE FROM dbo.FirstQuery

The blocked process report

BEGIN TRANSACTION
       DELETE FROM dbo.FirstQuery   
        
    
    
        
            
            
       DELETE FROM dbo.SecondQuery
   -- ROLLBACK

I can only see the currently running batch in the input buffer. I could find the held lock info in the sys.dm_tran_locks, but if the blocking is short, I won't capture it in time.

; -- Previous statement must be properly terminated 
WITH LockInfo
AS
(
    SELECT
        dtl.request_session_id
	  , dtl.resource_type
      , dtl.resource_description
      , dtl.resource_associated_entity_id
      , dtl.request_mode
      , dtl.request_status
    FROM 
        sys.dm_tran_locks AS dtl
)
SELECT 
	*
FROM LockInfo AS blcked
JOIN LockInfo AS blcker
    ON blcker.request_session_id = 121 /* blocker session ID */
    AND blcker.resource_type = blcked.resource_type
    AND blcker.resource_description = blcked.resource_description
    AND blcker.resource_associated_entity_id = blcked.resource_associated_entity_id
WHERE 
    blcked.request_session_id = 125 /* blocked session ID */ 
    AND blcked.request_status = N'WAIT'

Is there any automatic and efficient way to find which statement is causing blocking and which locks are being held? I hope to find anything that helps me track the earlier blocker from session 1 (in this case, DELETE FROM dbo.FirstQuery in the code base or to set up additional monitoring. - TSQL Stack - Query Hash - ObjectId (if part of a procedure) - sql_text I can probably infer the held locks from the statement.

## Context My AG's diagnostics use sp_server_diagnostics, so I decided to learn more about it. [The documentation](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-server-diagnostics-transact-sql?view=sql-server-ver17) is light on detail about where it gets its information from. Today, I am particularly interested in its "query_processing" section. I noticed that this contains several references to ecids, which I only know of from sp_who. I therefore decided to compare the two. ## Problem When I have a blocked and blocking query running, sp_who reports sensible results. | spid | ecid | status | loginame | hostname | blk | dbname | cmd | request_id | |------|------|------------|------------|---------|-----|--------|--------|------------| | 54 | 0 | suspended | sa | MyBox | 64 | master | SELECT | 0 | | 64 | 0 | suspended | SuperAdmin | MyBox | 0 | master | SELECT | 0 | | 64 | 1 | runnable | | MyBox | 0 | master | SELECT | 0 | | 64 | 3 | runnable | | MyBox | 0 | master | SELECT | 0 | | 64 | 2 | runnable | | MyBox | 0 | master | SELECT | 0 | | 64 | 4 | runnable | | MyBox | 0 | master | SELECT | 0 | Yet the query_process details report something much less sensible. To save your eyes, I've asked an AI to convert the XML to a table and chopped off many columns. | spid | ecid | status | loginname | hostname | type | processid | schedulerid | trancount | query | |------|------|-----------|------------|----------|----------|----------------|--------------|-----------|------------------| | 64 | 0 | suspended | SuperAdmin | MyBox | Blocked | processf15fbd468 | 2 | 1 | MyFirstBadQuery | | 64 | 1 | suspended | | MyBox | Blocking | | | 0 | MyFirstBadQuery | | 64 | 1 | suspended | | MyBox | Blocked | processf15be6ca8 | 3 | 0 | MyFirstBadQuery | | 64 | 2 | running | | MyBox | Blocking | | | 0 | MyFirstBadQuery | | 64 | 2 | running | | MyBox | Blocked | processf17dd88c8 | 4 | 0 | MyFirstBadQuery | | 64 | 3 | running | | MyBox | Blocked | processf1ebc4ca8 | 1 | 0 | MyFirstBadQuery | | 64 | 4 | running | | MyBox | Blocked | processf1ebc48c8 | 2 | 0 | MyFirstBadQuery | I know that [thread-level blocking information is a bad idea](http://whoisactive.com/docs/14_blockers/) . However, I do not expect the contradictions that these two tables appear to show. ### My Questions Clearly, sp_server_diagnostics isn't copying from sp_who. So: 1. Where does sp_server_diagnostics pull its query_processing information from? 2. Can I call that myself? 3. Why does sp_server_diagnostics think that individual ecids (specifically, 1 and 2) are both blocking and blocked? sp_who does not make this mistake. ### Ugly XML The full XML is below.

MyFirstBadQuery
  
 
 
  
 


 
  
   
MyFirstBadQuery
  
 
 
  
   
MyFirstBadQuery
  
 


 
  
   
MyFirstBadQuery
  
 
 
  
   
MyFirstBadQuery
  
 


 
  
   
MyFirstBadQuery
  
 
 
  
 


 
  
   
MyFirstBadQuery

I'm trying to work with the blocked process reports captured using Extended Events and, while I can find numerous blog articles and Q&A about specific details (e.g.1 2 3 ), I can't find any documentation from MS about the contents of the report & how to interpret each field. For example, I'd like to know: * What are clientoption1and clientoption2 * Why do I sometimes get an object_id that doesn't exist in my database (like this question ) * What does index_id indicate (probably answered at link above) * How to decode waitresource (here's a start ) * ... and the list goes on. I don't just want answers to these questions, I want something more complete. Is this an undocumented feature or am I just crap at finding it?

Given the following database with two tables having foreign key relationship between them: CREATE DATABASE FKLocksTest GO ALTER DATABASE FKLocksTest SET READ_COMMITTED_SNAPSHOT OFF USE FkLocksTest GO CREATE TABLE dbo.Department ( DeptID INT PRIMARY KEY, Deptname NVARCHAR(10) ) INSERT INTO dbo.Department VALUES (1,'IT'),(2,'HR') CREATE TABLE dbo.Person ( PersonID INT PRIMARY KEY, PersonName NVARCHAR(10), DepartmentId INT FOREIGN KEY REFERENCES dbo.Department(DeptId) ) INSERT INTO dbo.Person VALUES (1,'JohnSmith',2) If I run some updates, I can verify which locks are taken with traceflag 1200. Firstly, we can verify the object ids: SELECT OBJECT_ID('dbo.Department') AS Department, OBJECT_ID('dbo.Person') AS Person Now I enable TF1200 and run some updates, I can see the locks taken in the messages tab. I'll also ensure I am using read committed: DBCC TRACEON(1200,-1) SET TRANSACTION ISOLATION LEVEL READ COMMITTED and run my first update UPDATE dbo.Person SET PersonName = 'Jim' The Plan makes no reference to Department and the Traceflag output makes no reference to object id 581577110 which makes sense Next up, if I run UPDATE dbo.Person SET DepartmentId = 1 WHERE PersonID = 1 In this case,the execution plan references the department table and the Traceflag output shows locks taken on 581577110. This also makes sense as SQL Server has to check that the DepartmentId we are setting exists in the Department table This time, if I update the parent table and update the Primary key column UPDATE dbo.Department SET DeptID = 10 WHERE DeptID = 1 The execution plan shows a reference to the Person table and I can see locks taken on 613577224. This makes sense as SQL Server will need to check that there are no Person records with the old DepartmentID that we are updating. I also get an error as there is a Person who is associated with DeptId 1 Finally, if I run UPDATE dbo.Department SET Deptname = 'aaa' WHERE DeptID = 1 I can see in the Traceflag output that a lock is taken on the person table (613577224) but there is no reference to the person table in the plan The abridged output of TF1200 shows the locks taken on the Person table ...... Process 59 acquiring Sch-S lock on OBJECT: 9:613577224:0 (class bit0 ref1) result: OK ...... Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 ...... Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 acquiring X lock on OBJECT: 9:613577224:0 [UPDSTATS] (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 releasing lock on OBJECT: 9:613577224:0 [UPDSTATS] Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 ...... Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 ...... Process 59 releasing lock on OBJECT: 9:613577224:0 ...... Process 59 acquiring IS lock on OBJECT: 9:613577224:0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 ...... Msg 547, Level 16, State 0, Line 1 The UPDATE statement conflicted with the REFERENCE constraint "FK__Person__Departme__267ABA7A". The conflict occurred in database "FKLocksTest", table "dbo.Person", column 'DepartmentId'. The statement has been terminated. The lock on the Person table possibly appears to be part of the compilation process, if I run the UPDATE multiple times, I eventually get a much shorter list of locks (below in full) although the plan is a trivial plan Process 59 acquiring IX lock on OBJECT: 9:581577110:0 (class bit2000000 ref1) result: OK Process 59 acquiring IX lock on PAGE: 9:1:280 (class bit2000000 ref1) result: OK Process 59 acquiring X lock on KEY: 9:72057594043170816 (8194443284a0) (class bit2000000 ref1) result: OK Process 59 releasing lock reference on KEY: 9:72057594043170816 (8194443284a0) Process 59 releasing lock reference on PAGE: 9:1:280 My questions are, when we update a non-key column in the Department table: - Why is the lock on the Person table needed - Why is the lock only taken at compilation time

We are experiencing rolling blocking when a specific stored procedure runs. This SP runs multiple times a minute, and does heavily use temp tables. It doesn't block all the time, but during peak times its prevalent. blocking lasts for 30 seconds, the SP times out, then the next in line blocks. When it happens the blocked process shows as this: waitresource="OBJECT: 5:1371949573:0 [COMPILE]" The object is the SP itself. we don't see any specific statement inside the SP blocking, its just this compile wait. Looking at the plan cache, this SP has about 2500+ different, single use plans in cache. We have not been able to determine why this SP is not reusing a plan. Has anyone ran into this situation before, and more importantly, how can I find out the reason that it is not getting plan reuse? Is there an extended events session that can track executions of this SP, and specifically the reasons why its compiling so frequently? the only thing i can think of is that maybe since a temp table in this stored procedure gets multiple updates, inserts, and deletes would cause this? the SP does make use of multiple instances of Option(keepfixed plan) as well.

I have a busy, transactional SQL Server (2012) which is having intermittent problems with blocking (every couple of days or so). I've been using extended events to capture blocked-process reports, but these show that the blocking-process seems to be idle, with the input buffer being use [_dummy]. The application connecting to this server uses multiple databases, so _dummy is a blank database which connection pools open connections to; the database context is switched as required. What I thought was happening was that a query was timing out after obtaining locks, not rolling back the transaction and the connection was being released by the application back to the connection pool, which was resetting the database context before problems occurred due to the open transaction. To try and resolve that I changed the user connection settings so that XACT_ABORT is on by default, thinking that this would cause timed out transaction to rollback automatically, but this doesn't seem to have resolved the problem. Is there anything else which could cause an idle connection (part of a .NET connection pool) to block other queries? Is there any way I can capture more information about the last query run on the blocking-process? Note: I'm also monitoring deadlocks and the blocked-processes are sometimes blocked for several minutes but do not become deadlocks.

I have now 3 tables which are partitioned on *datetime* (a timestamp for when the app writes data) with the same partitioning function (so partition switch works). Table A is the table which is written to by the application. Table B contains old data with partitions from 2024 and older on yearly partitions, until December 5, 2024, when we renamed the old table (old table A became table B) and created a new, empty one (which is now Table A). Table C is a table I created, which I switched out the yearly data from December 5, 2024, until 2024-12-31 to have an empty partition in the production table (table A). The situation now is that we have a partition 19 which contains data in 2 tables (B and C, because I switched partition 19 from Table A to Table C and Table B already contained most of the 2024-data. The partitioning function has yearly partitions until end of 2024. For 2025 we have monthly partitions (and a few months forward in 2025). I was under the impression that I could Split partition 19 on the exact date of December 5, 2024 to create 2 partitions for 2024, and that it wouldn't create any locking issues for the app since the app is writing to partition 20 in table A. The split will involve partition 19, which only has data in Table B and Table C. All data is located on the primary filegroup. When I run alter partition function AUDITSPartitionFunction_V1() split range ('2024-12-05 12:10:28.529999'); it definitely blocks inserts with LCK_M_IX waits. Oldest record in table A is now about **2025-01-01 00:00:01.4800000* and the app is writing to partition 20. The partitioning function is defined as:

CREATE PARTITION FUNCTION [AUDITSPartitionFunction_V1](datetime2(7)) 
AS RANGE RIGHT FOR VALUES (
N'2007-01-01T00:00:00.000', N'2008-01-01T00:00:00.000', N'2009-01-01T00:00:00.000', 
N'2010-01-01T00:00:00.000', N'2011-01-01T00:00:00.000', N'2012-01-01T00:00:00.000', 
N'2013-01-01T00:00:00.000', N'2014-01-01T00:00:00.000', N'2015-01-01T00:00:00.000', 
N'2016-01-01T00:00:00.000', N'2017-01-01T00:00:00.000', N'2018-01-01T00:00:00.000', 
N'2019-01-01T00:00:00.000', N'2020-01-01T00:00:00.000', N'2021-01-01T00:00:00.000', 
N'2022-01-01T00:00:00.000', N'2023-01-01T00:00:00.000', N'2024-01-01T00:00:00.000', 
N'2025-01-01T00:00:00.000', N'2025-02-01T00:00:00.000', N'2025-03-01T00:00:00.000', 
N'2025-04-01T00:00:00.000', N'2025-05-01T00:00:00.000', N'2025-06-01T00:00:00.000', 
N'2025-07-01T00:00:00.000', N'2025-08-01T00:00:00.000', N'2025-09-01T00:00:00.000');

The final goal is to split 2024 into monthly chunks, switching them into Table A (and eventually make an automatic switch-procedure which have 12-13 months of data available in Table A, and keeping old data in Table B. Is there a way to split this data without blocking writing to Table A? I tried to search for similar cases, but couldn't find anything useful.

My shop had two significant blocking storms yesterday with most of the blocking victims piling up behind Drop table commands in a user database for short life processing tables (coding practice that is under review). This wait resource has surprisingly few online references. I haven’t seen this one before. **WaitText**

-none
LCK_M_SCH_M

**WaitResource**

-none              
METADATA: database_id = 18
ALL_AUDIT_SPECIFICATIONS_AND_ACTIONS(database_id = 18), lockPartitionId = 0

Any insights or resources on this wait resource would be appreciated. --- Audit scripts:

/****** Object:  Audit [DatabasePermissionsAudit]    Script Date: 12/18/2024 3:30:13 PM ******/

CREATE SERVER AUDIT [name1]

TO FILE

(              FILEPATH = N'C:\mypath\DatabaseAuditLogs\'

               ,MAXSIZE = 500 MB

               ,MAX_FILES = 5

               ,RESERVE_DISK_SPACE = OFF

) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, AUDIT_GUID = 'b6c33b5a-93a0-419f-ad33-d0d12ce73f62')
USE [master]

GO

 

CREATE SERVER AUDIT SPECIFICATION [Name1]

FOR SERVER AUDIT [Name1]

ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),

ADD (DATABASE_PERMISSION_CHANGE_GROUP),

ADD (DATABASE_PRINCIPAL_CHANGE_GROUP)

WITH (STATE = ON)

GO

 

 

USE [master]

GO

 



ALTER SERVER AUDIT [Name1] WITH (STATE = ON)

GO

 

 

/****** Object:  Audit [ServerPermissionsAudit]    Script Date: 12/18/2024 3:30:55 PM ******/

CREATE SERVER AUDIT [Name2]

TO FILE

(              FILEPATH = N'C:\mypath\ServerAuditLogs\'

               ,MAXSIZE = 500 MB

               ,MAX_FILES = 5

               ,RESERVE_DISK_SPACE = OFF

) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, AUDIT_GUID = '966a14e5-6efe-4085-87af-3f48d61b5dad')

ALTER SERVER AUDIT [name2] WITH (STATE = ON)
CREATE SERVER AUDIT SPECIFICATION [Name2]

FOR SERVER AUDIT [Name2]

ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),

ADD (SERVER_PRINCIPAL_CHANGE_GROUP)

WITH (STATE = ON)

GO

GO

We recently migrated from LogShipping standby/read-only setup to Multi Subnet AG setup with readable secondaries. Generally on old setup we have select queries running for longer duration as the database in question is over 20 TB and has mix of read write workload on primary. After moving to new setup of AG we have started seeing blocking which i am not able to understand. Why select queries on secondary are blocking other select queries in my readable secondary replica instance, even when the database being queried has RCSI enabled? Below is what i have captured - Lead blocker is some long running SELECT query does not show any specific waittype as particular, lets say SPID 129 - SPID 129 blocks a session ID 45 ( i am sure this is not a user id) for almost 6 hours which is dependent on spid129 and wait type is LCK_M_SCH_M - Here comes the problem when this SPID 45 just blocks all other select queries now in that 6 hour duration. I am not able to understand what is happening. Can someone help me troubleshoot or look in correct direction?

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 know which queries to tune. I could install more monitoring, but I've noticed that they have Query Store enabled. Is there any information in Query Store that can be used to find block leaders?

I have a situation where the following DDL Statement is rising to the top of my list of processes which I have ordered by the number of processes that each process is blocking (directly or indirectly). Killing this process frees up everything. Before you kill it almost everything backs up behind it (almost, but not quite). CREATE TRIGGER [dbo].[MailReturnCBRHandling] ON dbo.ACC AFTER UPDATE AS Declare @v_Agency_ID varchar(15), @n_SaleID numeric, @n_DebtorID numeric, @n_AccNo numeric, @v_AccStatID varchar(20), @b_AccStatMailReturned bit, @b_AccStatReportsToCBR bit, @b_DebtorMailReturned bit, @b_AccReportsToCBR bit IF UPDATE(Stat_ID) BEGIN DECLARE c_InsertedMailReturnCBR CURSOR FOR SELECT Agency_ID, SaleID , Debtor_ID, AccNo , Stat_ID, IsNull(IsBureau,0) FROM INSERTED OPEN c_InsertedMailReturnCBR FETCH NEXT FROM c_InsertedMailReturnCBR INTO @v_Agency_ID, @n_SaleID, @n_DebtorID, @n_AccNo, @v_AccStatID, @b_AccReportsToCBR WHILE @@FETCH_STATUS = 0 BEGIN -- ************************************************* Get AccStat MailReturned & ReportsToCBR ************************************************* DECLARE c_AccStat CURSOR FOR SELECT MailReturned , ReportsToCBR FROM AccStat WHERE Agency_ID = @v_Agency_ID AND AccStat = @v_AccStatID OPEN c_AccStat FETCH NEXT FROM c_AccStat INTO @b_AccStatMailReturned, @b_AccStatReportsToCBR -- ************************************************* Get Debtor MailReturned ************************************************* DECLARE c_Debtor CURSOR FOR SELECT IsNull(MailReturned,0) FROM Debtor WHERE Agency_ID = @v_Agency_ID AND Debtor_ID = @n_DebtorID OPEN c_Debtor FETCH NEXT FROM c_Debtor INTO @b_DebtorMailReturned -- ************************************************* UPDATE MailReturned ************************************************* IF @b_AccStatMailReturned IS NOT NULL BEGIN IF @b_AccStatMailReturned = 1 AND @b_DebtorMailReturned = 0 BEGIN UPDATE Debtor SET MailReturned = 1 WHERE Debtor_ID = @n_DebtorID INSERT INTO DebtorNote (AGENCY_ID, DEBTOR_ID, NOTE, Priority, StampTime, Supress) VALUES (@v_Agency_ID, @n_DebtorID, 'Auto Generated Note --> Debtor Mail Returned changed from: False To: True','C', GetDate() ,0) END IF @b_AccStatMailReturned = 0 AND @b_DebtorMailReturned = 1 BEGIN UPDATE Debtor SET MailReturned = 0 WHERE Debtor_ID = @n_DebtorID INSERT INTO DebtorNote (AGENCY_ID, DEBTOR_ID, NOTE, Priority, StampTime, Supress) VALUES (@v_Agency_ID, @n_DebtorID, 'Auto Generated Note --> Debtor Mail Returned changed from: True To: False','C', GetDate() ,0) END END -- ************************************************* UPDATE ReportsToCBR ************************************************* IF @b_AccStatReportsToCBR IS NOT NULL BEGIN IF @b_AccStatReportsToCBR = 1 AND @b_AccReportsToCBR = 0 BEGIN UPDATE ACC SET IsBureau = 1 WHERE AccNo = @n_AccNo INSERT INTO DebtorNote (AGENCY_ID, DEBTOR_ID, NOTE, Priority, StampTime, Supress,AccNo,SaleID) VALUES (@v_Agency_ID, @n_DebtorID, 'Auto Generated Note --> Reports To Bureau changed from: False To: True','C', GetDate() , 0, @n_AccNo, @n_SaleID) END IF @b_AccStatReportsToCBR = 0 AND @b_AccReportsToCBR = 1 BEGIN UPDATE ACC SET IsBureau = 0 WHERE AccNo = @n_AccNo INSERT INTO DebtorNote (AGENCY_ID, DEBTOR_ID, NOTE, Priority, StampTime, Supress,AccNo,SaleID) VALUES (@v_Agency_ID, @n_DebtorID, 'Auto Generated Note --> Reports To Bureau changed from: True To: False','C', GetDate() , 0, @n_AccNo, @n_SaleID) END END FETCH NEXT FROM c_InsertedMailReturnCBR INTO @v_Agency_ID, @n_SaleID, @n_DebtorID, @n_AccNo, @v_AccStatID, @b_AccReportsToCBR CLOSE c_Debtor DEALLOCATE c_Debtor CLOSE c_AccStat DEALLOCATE c_AccStat END CLOSE c_InsertedMailReturnCBR DEALLOCATE c_InsertedMailReturnCBR END The trigger is written by the company that provides us with the software that we use to run out business. There is no need to create it because it already exists. It should instantly fail because an object of that name already exists. The vendor provided this software runs the entire application logged in as SA. I have begged my management to make them run all sql under the windows credential of the logged in user but they claim that they have no way to force the software vendor to to that so the SQL process list does not tell me anything about who submitted the create trigger and under what circumstances. We are running SQL Server 2016 SP3. I am wondering: 1) Why would this suspend? Why not just return an error tha t says an object of that name already exists? 2) Given that it has suspended, why do all the other tasks back up behind it? Put another way, what resource is this process holding that the others need. I can't believe that they are all waiting to create or alter triggers or that something that this process needs to change is in a page that everyone else would want. It would be as if this process was able to put a lock on the entire database, but why? This happens about every two weeks. Exact same trigger. I have not yet determined a set of circumstances that trigger it. Thank you.

TL;DR We have a postgres stored procedure running as a job, which calls another stored procedure, which calls yet another stored procedure, and this internal stored procedure acquires ROW EXCLUSIVE lock on a table. This whole thing runs for over a minute, and the lock is kept until the end. This blocks another application from acquiring an EXCLUSIVE lock on the same table. Full story We have PostgreSQL 16.2 installed on RedHat 8.9. We have two separate databases on it, each database has multiple schemas and users with appropriate rights. We have a locking problem between two schemas. One of the schemas has a table called EMAIL_MESSAGE, which stores emails to be sent. We have two instances of a mass email sender application, which first try to assign a specific amount of emails to themselves as step 1, and in step 2 they read the assigned email data and process them. In order for the two email sender applications to not overwrite each other's assigned emails, the assignment starts by locking the table. It is handled in a stored procedure like so: 1. Stored procedure starts with a BEGIN, so it starts a transaction 2. Verify some input parameters, exit in case of error 3. Another BEGIN, starting a subtransaction 4. Get EXCLUSIVE lock on the EMAIL_MESSAGE table, waiting for 15 seconds 5. If unsuccessful, we get 55P03 error, which is caught in the EXCEPTION block of the subtransaction 6. If successful, UPDATE a number of records with the instance id of the email sender application (stored procedure input parameter), then end the subtransaction, and end the procedure as well Then, the email senders will SELECT all data with their instance id, which was previously assigned to them. Some excerpts from the procedure:

CREATE OR REPLACE PROCEDURE assign_emails(
    IN pi_instance TEXT,
    ... other params)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    -- verify params, exit if wrong params
    BEGIN
        SET LOCAL lock_timeout = '15s';
        LOCK TABLE email_message IN EXCLUSIVE MODE;

        -- further processing after lock is acquired
        UPDATE email_message SET processing_instance = pi_instance WHERE ...;
    EXCEPTION
        IF SQLSTATE = '55P03' THEN
            -- logging lock error into a log table
        ELSE
            -- logging generic error into a log table
        END IF;
    END;

    COMMIT; -- not sure if needed here? doesn't seem to do anything, but no error also
EXCEPTION
    -- logging error into a log table
END;
$BODY$;

This on its own works well, the two instances don't bother each other, the above detailed UPDATE assignment takes very small time only, 15 seconds of wait time for the lock is more than enough. However, we added another schema for another development, which contains a table, and a REST API is receiving data and saving it into this table. There is a job executing every minute (via PgAgent) which takes the new records on this table based on status, as well as some of the old records. For the new records we have to send an email, for the old records, based on time difference and status, we need to send a reminder email. The problem is, that this job can sometimes take a lot of time, sometimes even more than a minute, and it seems like it is preventing the above detailed EXCLUSIVE lock on the EMAIL_MESSAGE table. Not sure why though. The job's stored procedure also starts with a BEGIN so it starts a transaction, and it SELECTs from the EMAIL_MESSAGE table, so it acquires ACCESS SHARE lock, but this should be fine. But in order to insert the emails to be sent, we invoke stored procedures in the original email-related schema. These stored procedures will of course INSERT and UPDATE on the EMAIL_MESSAGE table, acquiring ROW EXCLUSIVE lock, which is a problem because it will prevent the EXCLUSIVE lock we need from the email senders. And when this ROW_EXCLUSIVE lock is acquired, relatively soon when this job starts, it is never released until the end. Which again can take a minute, so the table is in ROW EXCLUSIVE lock for a minute, which is not good for me. Parts from this offending job stored procedure:

CREATE OR REPLACE PROCEDURE process_minutely()
LANGUAGE 'plpgsql'
SECURITY DEFINER
AS $BODY$
DECLARE
    v_cur CURSOR FOR SELECT ...;
    v_rec RECORD;
BEGIN
    -- loop through the appropriate records
    OPEN v_cur;

    LOOP
        FETCH v_cur INTO v_rec;
        EXIT WHEN NOT FOUND;
        -- process v_rec, very complex processing logic here depending on status, time difference, related table records, etc.

        SELECT col INTO var FROM email_message; -- this will acquire ACCESS SHARE lock

        CALL register_email(params...); -- we call the email sending here
    END LOOP;

    CLOSE v_cur;
EXCEPTION
    -- logging error into a log table
END;
$BODY$;

The mentioned register_email procedure, this is invoked multiple times from the above procedure, hence it is separated, this will invoke the real internal email sending procedures:

CREATE OR REPLACE PROCEDURE register_email(params...)
LANGUAGE 'plpgsql'
SECURITY DEFINER
AS $BODY$
BEGIN
    -- preprocessing, parameter checks
    BEGIN
        -- these procedures will internally acquire the ROW EXCLUSIVE lock by INSERT-ing and UPDATE-ing the email_message table
        CALL original_create_email(params...);
        CALL original_prepare_email(params...);

        -- COMMIT; -- tried putting commit here, results in error
    EXCEPTION
        -- logging error into a log table
    END;

    -- COMMIT; -- tried putting commit here, results in error
EXCEPTION
    -- logging error into a log table
END;
$BODY$;

So to add some images, maybe it helps. This is the correct behaviour, EXCLUSIVE lock is acquired by one of the application instances, it does its thing quickly, then releases it, and the other can proceed: This is the incorrect behaviour, where this new job stored procedure is blocking the application with the automatic ROW EXCLUSIVE lock for over a minute: I tried to put the part of this new stored procudedure which acquires the ROW EXCLUSIVE lock into its own transaction by putting a BEGIN END around it, it doesn't help. I tried to put a COMMIT to various places, into the subtransaction, and after the subtransaction, it is not allowed with "invalid transaction termination" error. I admit that PostgreSQL transaction handling is a bit mysterious to me, I was more used to Oracle (pragma autonomous_transaction...), but trying to learn. As far as I understand, when I enter into a BEGIN EXCEPTION END block, it is a subtransaction automatically, commited when it exits, and rolled back in the EXCEPTION block. Doesn't it mean that when it exits from the block inside the

procedure, it should release the ROW EXCLUSIVE lock which was acquired inside it, and allow the EXCLUSIVE lock which is already waiting? How could I achieve correct functionality? Is it even achievable? What I would like to see is that the ROW EXCLUSIVE lock is released when it's not needed in the long running job, and reacquired when needed. I understand this will result in performance issues, but this is not necessarily a problem, it is a background processing job, and it is not too important if the email is sent now or 2 minutes later. But now, due to the blocking, the emails are never sent, because the email sender application is not able to proceed. As a workaround, I will make this job run every 5 minutes only, hopefully that will give some time to the email senders to work between the job executions. ---------------------------------- Update: After making some changes based on the comments, the situation is better now significantly, although not perfect. I guess the main problem is that I misunderstand the lock handling. So what I did now is: 1. The long running procedure is still mostly the same, but now it inserts the emails to be sent into a new table, not directly into the EMAIL_MESSAGE table 2. This way the lock is not kept on the EMAIL_MESSAGE table for a long time, so the email sender app can work 3. I added a new procedure which runs every 5 minutes, and it has 3 BEGIN EXCEPTION END blocks internally: 1. To mark the emails that I want to process (due to parallel inserts from the other procedure) 2. To calculate the email bodies and email sending time (email body is a template with some parameters being replaced) 3. To batch insert everything into the EMAIL_MESSAGE table as a single INSERT INTO ... SELECT ... FROM ... statement 4. Then, and this is not in its own BEGIN EXCEPTION END block, we update the original input table, the same table which the long running procedure is also working on 5. Then finally we delete from this new table everything that we processed So far so good, this new middle-layer procedure is quite fast, normally it finishes within a second. But sometimes I notice that it hangs in step number 3.4 above, updating the input table, and that is because it is waiting for the main long running procedure, which holds the lock on the same table. I can understand why this is. But, at the same time, the email sending application also hangs, because it cannot access the EMAIL_MESSAGE table. It is locked in step number 3.3 above in the middle-layer stored procedure, even though at the same time the procedure is already in step 3.4, so it is out of the BEGIN EXCEPTION END block, the changes are already commited (I googled that I can do that by embedding BEGIN EXCEPTION END blocks to make a subtransaction), so why doesn't it release the lock?

I ran the following commands to setup extended event captures for blocked processes:

CREATE EVENT SESSION [blocked_process] ON SERVER
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name)) ,
ADD EVENT sqlserver.xml_deadlock_report (
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name))
ADD TARGET package0.asynchronous_file_target
(SET filename = N'c:\temp\XEventSessions\blocked_process.xel',
     metadatafile = N'c:\temp\XEventSessions\blocked_process.xem',
     max_file_size=(65536),
     max_rollover_files=5)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS)
GO

I also ran the following to set the blocked process report threshold to 5 seconds:

EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
/* Enabled the blocked process report */
EXEC sp_configure 'blocked process threshold', '5';
RECONFIGURE
GO
/* Start the Extended Events session */
ALTER EVENT SESSION [blocked_process] ON SERVER
STATE = START;

Using SQL studio manager's UI, I started the blocked_process extended event capture. In order to test the setup, I explicitly started a transaction in one query window, edited a row, and attempted to edit the same row in another window. The second window wouldn't complete until I ran "commit transaction" in the first window. Despite the fact that I waited well over the 5 second blocking threshold, the blocked_process extended event capture didn't capture anything. Nor did it capture any blocking that occurred within our application, which it should have because I know there are blocking issues. We are running sql server 2022 enterprise edition. Is there some other configuration option I'm missing that may explain why I'm not capturing anything with my extended event session?

### Scenario I have a large table partitioned on an INT column. When I run two different MERGE statements on two different partitions of this table, they seem to be blocking each other. Sample code to recreate the scenario: #### 1. Preparation. Create tables and some dummy data

SET NOCOUNT ON
GO
--
--	Create parition function and partition scheme
--
	DROP FUNCTION IF EXISTS PF_Site_ID 
	GO
	CREATE PARTITION FUNCTION PF_Site_ID (INT)  
		AS RANGE RIGHT FOR VALUES 	(
										0,
										1, 2, 3, 4, 5, 6, 7, 8, 9, 10
									)
	GO  
	DROP PARTITION SCHEME PS_Site_ID
	GO
	CREATE PARTITION SCHEME PS_Site_ID
		AS PARTITION PF_Site_ID
		ALL TO ('PRIMARY')
	GO

--
-- Large table partitioned on Site_ID. Two STG tables. And some dummy data
--
	DROP TABLE IF EXISTS dbo.PartitionedLargeTable
	GO
	CREATE TABLE dbo.PartitionedLargeTable
	(
		  ID       INT          NOT NULL IDENTITY(1,1)
		, Site_ID  INT          NOT NULL
		, Name     VARCHAR(50)
	) ON PS_Site_ID (Site_ID)
	GO
	ALTER TABLE dbo.PartitionedLargeTable SET (LOCK_ESCALATION = AUTO)
	GO

--
--	STG tables
--
	DROP TABLE IF EXISTS dbo.STG_Test1
	GO
	CREATE TABLE dbo.STG_Test1
	(
		  ID       INT          NOT NULL IDENTITY(1,1)
		, Site_ID  INT          NOT NULL
		, Name     VARCHAR(50)
	) ON [PRIMARY]
	GO
	DROP TABLE IF EXISTS dbo.STG_Test2
	GO
	CREATE TABLE dbo.STG_Test2
	(
		  ID       INT          NOT NULL IDENTITY(1,1)
		, Site_ID  INT          NOT NULL
		, Name     VARCHAR(50)
	) ON [PRIMARY]
	GO

--
--	Dummy data
--
	INSERT INTO dbo.PartitionedLargeTable (Site_ID, Name) SELECT 1, NEWID()
	INSERT INTO dbo.PartitionedLargeTable (Site_ID, Name) SELECT 2, NEWID()
	GO 10000

	INSERT INTO dbo.PartitionedLargeTable (Site_ID, Name)
	SELECT Site_ID, Name FROM dbo.PartitionedLargeTable
	GO 5

	INSERT INTO dbo.STG_Test1(Site_ID, Name) SELECT 1, NEWID()
	GO 10000
	INSERT INTO dbo.STG_Test2(Site_ID, Name) SELECT 2, NEWID()
	GO 10000

	INSERT INTO dbo.STG_Test1 (Site_ID, Name)
	SELECT Site_ID, Name FROM dbo.STG_Test1
	GO 7

	INSERT INTO dbo.STG_Test2 (Site_ID, Name)
	SELECT Site_ID, Name FROM dbo.STG_Test2
	GO 7

#### 2. MERGE 1 In one SSMS window, run this MERGE statement:

MERGE dbo.PartitionedLargeTable AS TGT

USING (SELECT ID, Site_ID, Name FROM dbo.STG_Test1) AS SRC
    ON  SRC.Site_ID = TGT.Site_ID
    AND SRC.ID      = TGT.ID

WHEN MATCHED THEN
    UPDATE 
        SET TGT.Name = SRC.Name

WHEN NOT MATCHED THEN
    INSERT (Site_ID, Name)
    VALUES (SRC.Site_ID, SRC.Name);

#### 3. MERGE 2 In a second SSMS window, run this MERGE statement:

MERGE dbo.PartitionedLargeTable AS TGT

USING (SELECT ID, Site_ID, Name FROM dbo.STG_Test2) AS SRC
    ON  SRC.Site_ID = TGT.Site_ID
    AND SRC.ID      = TGT.ID

WHEN MATCHED THEN
    UPDATE 
        SET TGT.Name = SRC.Name

WHEN NOT MATCHED THEN
    INSERT (Site_ID, Name)
    VALUES (SRC.Site_ID, SRC.Name);

--- The two MERGE statements run on different *Site_IDs* (so two different partitions). One of the performance benefits of partitioned tables is that we could manipulate partitions independently of each other (within reason). So, something like INSERT or UPDATE on one partition will not block similar operations on other partitions. Compare this to when the table is NOT partitioned, if we perform two large INSERT operations (or two large UPDATE operations), then one blocks the other once the number of rows manipulated goes over a certain number (something like 3k or 5k rows), then the PAGE lock is escalated to TABLOCK. Hence INSERT blocks INSERT (or UPDATE blocks UPDATE) To avoid such lock escalation to TABLOCK, this table was partitioned with LOCK_ESCALATION = AUTO, which limits locks up to HOBT level (and not table). But with MERGE, the blocking still happens. Any ideas on how to prevent this blocking? We have 10 parallel MERGE statements running, on 10 different partitions of this large table (and they are blocking each other). The image below shows the nature of blocking. When a table is partitioned, the lock escalation is supposed to only go up to the partition (not to the whole table). When these MERGE statements are running, I see the HOBT id's that each MERGE is querying (locking). And in some cases, the HOBT ID does not match the partition IDs of this table. The actual table I work with has a COLUMNSTORE CLUSTERED index on the partitioning scheme.

I need to be able to iterate all tables and issue a TRUNCATE command, however, I don't want the loop to be held up by blocking. Essentially, I am looking for the best way to determine if a SCH-M lock can be obtained prior to issuing a truncate command. >Microsoft SQL Server 2019 (RTM-CU26) - 15.0.4365.2 (X64) Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0

There is a table that seems to have constant updates. There is also a stored procedure that runs periodically and iterates through tables and attempts to truncate a partition of data using the command below: TRUNCATE TABLE [dbo].[Table] WITH (PARTITIONS (10)) The looping procedure always gets locked up on the same table, the table with constant updates fails even when data is not being updated in the partition targeted for deletion. The calling process can't be updated. Is there a trick to bind the updates to a partition outside of modifying the query or some other way to force non changing partitions to not be blocked/locked? Will a page lock cause truncate with partition to fail? Like could two partitions share a single page and attempting to delete with partition(2) fails because some process is updating data in partition 1 that 2 happens to be sharing a page with? CREATE TABLE [dbo].[Table] ( [TableID] INT NOT NULL ... , [SourceID] NVARCHAR(4000) NOT NULL , CONSTRAINT [PK_Table_TableID] PRIMARY KEY NONCLUSTERED([TableTableID] ASC , [SourceID] ASC) ON psSourceID(SourceID) , CONSTRAINT [CIX_Table_TableID] UNIQUE CLUSTERED ([SourceID] ASC, [TableID] ASC) ON psSourceID(SourceID) ) ON psSourceID(SourceID) GO ALTER TABLE [dbo].[Table] SET (LOCK_ESCALATION = AUTO) GO The TRUNCATE command is being blocked by a session running updates against the table. In lower environments a blocking process was never hit after many trials so the assumption was that WITH PARTITION would only place meta data locks by partition, not the entire table.

I have a stored procedure called sp_radhe that I put on my servers and it has been helping me to "see" what is happening internally. here is the code of this stored procedure: USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --DROP PROCEDURE dbo.sp_radhe GO CREATE PROCEDURE dbo.sp_radhe AS /* ======================================================================= Script : SP_RADHE Author : Marcelo Miorelli Date : 04 MAR 2013 Wednesday Desc : shows the current processes Usage : sp_radhe -- same as sp_who2 ======================================================================= History Date Action User Desc ----------------------------------------------------------------------- 27-oct-2014 changed Marcelo Miorelli commented out the line --and es.status = 'running' so the procedure returns any es.status ======================================================================= */ --====================================== -- describe primary blocks of processing --====================================== ------------------------------------------------ -- describe action of logical groups of commands ------------------------------------------------ -- describe individual actions within a command set BEGIN SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT es.session_id AS session_id ,COALESCE(es.original_login_name, 'No Info') AS login_name ,COALESCE(es.host_name,'No Info') AS hostname ,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch ,es.status ,COALESCE(er.blocking_session_id,0) AS blocked_by ,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype ,COALESCE(er.wait_time,0) AS waittime ,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype ,COALESCE(er.wait_resource,'') AS waitresource ,coalesce(db_name(er.database_id),'No Info') as dbid ,COALESCE(er.command,'AWAITING COMMAND') AS cmd ,sql_text=st.text ,transaction_isolation = CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END ,COALESCE(es.cpu_time,0) + COALESCE(er.cpu_time,0) AS cpu ,COALESCE(es.reads,0) + COALESCE(es.writes,0) + COALESCE(er.reads,0) + COALESCE(er.writes,0) AS physical_io ,COALESCE(er.open_transaction_count,-1) AS open_tran ,COALESCE(es.program_name,'') AS program_name ,es.login_time FROM sys.dm_exec_sessions es LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st where es.is_user_process = 1 and es.session_id @@spid --and es.status = 'running' GO exec sys.sp_MS_marksystemobject 'sp_radhe' GO However, when a process is being blocked by some other process that is not active, I am struggling to figure it out the T-SQL of the blocking code. For example: the session 92 you can see on the above picture is a select and the session 75 is an update that I left the transaction open. **session 92** SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SELECT TOP 1000 [accountID] ,[accountCreateKeyID] ,[totalAccountCreditValueLocal] ,[accountCreateDate] ,[createdDate] ,[createdBy] ,[modifiedDate] ,[modifiedBy] FROM [TableBackups].[dbo].[_AO20150806_crm_build_account_DoNotDelete] WITH (HOLDLOCK) **session 75** BEGIN TRANSACTION T1 SELECT @@TRANCOUNT update [TableBackups].[dbo].[_AO20150806_crm_build_account_DoNotDelete] set [totalAccountCreditValueLocal] = 1000 where accountID = 1 How can I find the code of the blocking session, in this case, the session 75 when the session's status is 'sleeping'? **New version** this new version shows also the blocking session, however, I could not find out how to get the database name and other data from a sleeping session. SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT es.session_id AS session_id ,COALESCE(es.original_login_name, 'No Info') AS login_name ,COALESCE(es.host_name,'No Info') AS hostname ,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch ,es.status ,COALESCE(er.blocking_session_id,0) AS blocked_by ,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype ,COALESCE(er.wait_time,0) AS waittime ,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype ,COALESCE(er.wait_resource,'') AS waitresource ,coalesce(db_name(er.database_id),'No Info') as dbid ,COALESCE(er.command,'AWAITING COMMAND') AS cmd ,sql_text=st.text ,transaction_isolation = CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END ,COALESCE(es.cpu_time,0) + COALESCE(er.cpu_time,0) AS cpu ,COALESCE(es.reads,0) + COALESCE(es.writes,0) + COALESCE(er.reads,0) + COALESCE(er.writes,0) AS physical_io ,COALESCE(er.open_transaction_count,-1) AS open_tran ,COALESCE(es.program_name,'') AS program_name ,es.login_time FROM sys.dm_exec_sessions es LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st where es.is_user_process = 1 and es.session_id @@spid UNION SELECT es.session_id AS session_id ,COALESCE(es.original_login_name, 'No Info') AS login_name ,COALESCE(es.host_name,'No Info') AS hostname ,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch ,es.status ,COALESCE(er.blocking_session_id,0) AS blocked_by ,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype ,COALESCE(er.wait_time,0) AS waittime ,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype ,COALESCE(er.wait_resource,'') AS waitresource ,coalesce(db_name(er.database_id),'No Info') as dbid ,COALESCE(er.command,'AWAITING COMMAND') AS cmd ,sql_text=st.text ,transaction_isolation = CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END ,COALESCE(es.cpu_time,0) + COALESCE(er.cpu_time,0) AS cpu ,COALESCE(es.reads,0) + COALESCE(es.writes,0) + COALESCE(er.reads,0) + COALESCE(er.writes,0) AS physical_io ,COALESCE(er.open_transaction_count,-1) AS open_tran ,COALESCE(es.program_name,'') AS program_name ,es.login_time FROM sys.dm_exec_sessions es INNER JOIN sys.dm_exec_requests ec2 ON es.session_id = ec2.blocking_session_id LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS st where es.is_user_process = 1 and es.session_id @@spid

I have a simple partitioned table in PostgreSQL 14, like this: create table entity ( dataset_id integer not null references dataset (id), ... ) partition by list (dataset_id); create table entity_1 ( like entity including constraints including defaults, constraint entity_1_pkey primary key (entity_id), ); alter table entity attach partition entity_1 for values in (1); The reason for creating a partition "detached" first and then attaching it in a separate statement is to avoid taking an exclusive lock on the parent table (entity), which would block creating a partition as long as a client was reading from any other partition. This was the solution suggested by **Laurenz Albe** in https://stackoverflow.com/a/67016755/1536933 and it works great for creating partitions. Unfortunately, I have the same problem when *dropping* partitions: as long as a client is reading data from any partition I cannot drop any other partition: not only is drop table entity_1 blocked, but even alter table entity detach partition entity_1 concurrently is blocked! I cannot see any relevant lock in pg_locks for the "detach concurrently" statement (nothing with granted=false), but the pgAdmin dashboard shows Wait event: Lock: virtualxid and Blocking PIDs: (pid of the reading process) What can I do to drop partitions while data is being read from other partitions? Surely this should be possible?