I have code to query partition data.
But when the database creates an index, my query does not work because of the LCK_M_SCH_S lock, although I use the NOLOCK hint for all tables.
Is there any way around this lock or get this data without lock?
SELECT DB_NAME() AS DatabaseName , OBJECT_SCHEMA_NAME(p.OBJECT_ID) as TableschemaName , OBJECT_NAME(p.OBJECT_ID) AS TableName , p.index_id AS 'IndexId' , CASE WHEN p.index_id = 0 THEN 'HEAP' ELSE i.name END AS 'IndexName' , p.partition_number AS PartitionNumber , prv_left.value AS LowerBoundary , prv_right.value AS UpperBoundary , ps.name as PartitionScheme , pf.name as PartitionFunction , c.name AS [Partitioning Column] , TYPE_NAME(c.user_type_id) AS [Column Type] , CASE WHEN fg.name IS NULL THEN ds.name ELSE fg.name END AS 'FileGroupName' , CAST(p.used_page_count * 0.0078125 AS NUMERIC(18,2)) AS UsedPagesMB , CAST(p.in_row_data_page_count * 0.0078125 AS NUMERIC(18,2)) AS DataPagesMB , CAST(p.reserved_page_count * 0.0078125 AS NUMERIC(18,2)) AS ReservedPagesMB , CASE WHEN p.index_id IN (0,1) THEN p.row_count ELSE 0 END AS RowsQuantity ,CASE WHEN p.index_id IN (0,1) THEN 'data' ELSE 'index' END AS Type FROM sys.dm_db_partition_stats p WITH (NOLOCK) JOIN sys.indexes i WITH (NOLOCK) ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id JOIN sys.data_spaces ds WITH (NOLOCK) ON ds.data_space_id = i.data_space_id LEFT JOIN sys.partition_schemes ps WITH (NOLOCK) ON ps.data_space_id = i.data_space_id LEFT JOIN sys.partition_functions pf WITH (NOLOCK) ON ps.function_id = pf.function_id LEFT JOIN sys.destination_data_spaces dds WITH (NOLOCK) ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number LEFT JOIN sys.filegroups fg WITH (NOLOCK) ON fg.data_space_id = dds.data_space_id LEFT JOIN sys.partition_range_values prv_right WITH (NOLOCK) ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number LEFT JOIN sys.partition_range_values prv_left WITH (NOLOCK) ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1 LEFT JOIN sys.index_columns ic WITH (NOLOCK) ON ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id AND ic.partition_ordinal >= 1 LEFT JOIN sys.columns c WITH (NOLOCK) ON p.OBJECT_ID = c.[object_id] AND ic.column_id = c.column_id WHERE OBJECTPROPERTY(p.OBJECT_ID, 'ISMSSHipped') = 0 AND p.index_id IN (0,1) AND ps.name IS NOT NULL
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
In certain editions of SQL Server, you can create an index “Online”. This means that the server does not take a
Sch-M lock until the very end of the process, for a brief period.
You can do this through the SSMS index properties, or using
ONLINE = ON in T-SQL.
Important things to note about doing this:
- It is only supported in Enterprise and Developer editions.
- You need disk space for the whole index again, then some.
- It may take longer to do the index operation than an offline.
- It may slow down other DML transactions.
- Don’t put it inside a long explicit transaction, otherwise the final
Sch-Mlock is still held until the end.
See also the documentation.
There is no workaround. This is by design. As per Microsoft documentation:
Occurs when a task is waiting to acquire a Schema Share lock. See
Schema Locks for more information.
The SQL Server Database Engine uses schema stability (Sch-S) locks
when compiling and executing queries. Sch-S locks do not block any
transactional locks, including exclusive (X) locks. Therefore, other
transactions, including those with X locks on a table, continue to run
while a query is being compiled. However, concurrent DDL operations,
and concurrent DML operations that acquire Sch-M locks, cannot be
performed on the table.
Here is a repo of what you are seeing and why you are being blocked. I am using the AdventureWorks database provided by Microsoft and big tables created using a query written by Adam Machanic.
enter link description here
Run this from one of the query windows:
USE AdventureWorks; GO BEGIN TRAN CREATE NONCLUSTERED INDEX IX_ProductId_TransactionDate ON dbo.bigTransactionHistory ( ProductId, TransactionDate ) INCLUDE ( Quantity, ActualCost ); GO --COMMIT TRAN
Open a new window and do a select with NOLOCK.
USE AdventureWorks; GO SELECT * FROM dbo.bigTransactionHistory WITH (NOLOCK)
Now check wait type/blocking and locks held by each session:
EXEC sp_whoisactive @get_locks = 1
If you see the locks column from the above output and click on the XML you will see that select with NOLOCK is requesting an
Sch-S (Schema stability lock) lock and is waiting because this lock is not compatible with
<Database name="AdventureWorks"> <Locks> <Lock request_mode="S" request_status="GRANT" request_count="1" /> </Locks> <Objects> <Object name="bigTransactionHistory" schema_name="dbo"> <Locks> <Lock resource_type="OBJECT" request_mode="Sch-S" request_status="WAIT" request_count="1" /> </Locks> </Object> </Objects> </Database>
Here is a Q&A How to avoid a select query for holding a Sch-S lock where Mike Walsh explains why this is necessary.
General locking information (copied from here):
- For the complete lock compatibility matrix, see the Books Online page Lock Compatibility.
- For information on the lock hierarchy, see the Books Online page Lock Granularity and Hierarchies.
- For information on some of the lock modes, see the Books Online page Lock Modes.
- For other locking topics, see the Books Online page Locking in the Database Engine.