Lesson Learned #330: Blocking issues Sch-M and Sch-S on Database ‘XXX’ on server ‘xyz’

Posted on by — No Comments ↓

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

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

 

 

 

Jose_Manuel_Jurado_0-1676577869631.png

 

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.

 

Jose_Manuel_Jurado_0-1676578859648.png

 

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!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.