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!