Blog Post

Azure Database Support Blog
3 MIN READ

Lesson Learned #330: Blocking issues Sch-M and Sch-S

Jose_Manuel_Jurado's avatar
Feb 16, 2023

Today, I worked in a very interested case. Our customer is trying to run an ALTER TABLE <TableName> to add two columns but this process is taking too much time, even, when the documentation reported "Starting with SQL Server 2012 (11.x) Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously despite the number of rows in the table, because the existing rows in the table aren't updated during the operation" https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=azuresqldb-current#adding-not-null-columns-as-an-online-operation

 

So, let's try to analyze what is happening behind the scenes and explain why is taking too much time. 

 

We have the following table: 

 

 

CREATE TABLE [dbo].[Table1](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](200) NULL,
	[Details] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
	[ID] ASC
))

 

 

We added around 7M of rows in this table adding random values. 

 

 

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

 

 

 

Right now, let's try to run a query that reads the data, for example, Select Name from Table 1 and other session trying to run the query: ALTER table Table1 ADD mynewdata char(2) 

 

Meanwhile the query Select Name from Table1 (Session:74) is still running the ALTER TABLE (Session:86) will be waiting for session 74 completion. 

 

 

select * from sys.dm_tran_locks

select conn.session_id as blockerSession,conn2.session_id as BlockedSession,req.wait_time as Waiting_Time_ms,cast((req.wait_time/1000.) as decimal(18,2)) as Waiting_Time_secs,
cast((req.wait_time/1000./60.) as decimal(18,2)) as Waiting_Time_mins,t.text as BlockerQuery,t2.text as BlockedQuery, req.wait_type from sys.dm_exec_requests as req
inner join sys.dm_exec_connections as conn on req.blocking_session_id=conn.session_id
inner join sys.dm_exec_connections as conn2 on req.session_id=conn2.session_id
cross apply sys.dm_exec_sql_text(conn.most_recent_sql_handle) as t
cross apply sys.dm_exec_sql_text(conn2.most_recent_sql_handle) as t2

 

 

As you could see in the following picture the session 74 has Sch-S Granted and the session 86 is waiting for 74.

 

 

Reviewing this URL: sys.dm_tran_locks (Transact-SQL) - SQL Server | Microsoft Learn

 

LCK_M_SCH_S and LCK_M_SCH_M wait types are wait states for schema stability (Sch-S) and schema modification (Sch-M) locks. As Sch-M requires exclusive access to the table all request would be blocked. As Sch-S vs. Sch-M: Sch-S locks are incompatible with Sch-M locks. So, Sch-M means that you are not immediately granted and you have to wait for other Sch-S have been released. 

 

Sch-S locks are used to avoid table alterations when tables are in use. SQL Server acquires during the query compilation and execution of SELECT queries. In the majority of cases, you could address this situation by changing deployment or database maintenance strategies in the system.

 

Enjoy!

Updated Feb 16, 2023
Version 3.0
No CommentsBe the first to comment