Bypassing LCK_M_SCH_S lock when requesting partition information

by

The 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

The Solutions:

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.

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

Method 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.
Bypassing LCK_M_SCH_S lock when requesting partition information

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):


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment