Bypassing LCK_M_SCH_S lock when requesting partition information

Question

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

Answer 1

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-M lock is still held until the end.

See also the documentation.

Answer 2

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

You will see that select with NOLOCK is blocked because of LCK_M_SCH_S lock.

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 LCK_M_SCH_S.

<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.