Bypassing LCK_M_SCH_S lock when requesting partition information



  • 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
    


  • 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 https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks and big tables created using a query written by Adam Machanic. http://sqlblog.com/blogs/adam_machanic/archive/2011/10/17/thinking-big-adventure.aspx

    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. enter image description here

    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.

    
      
        
      
      
        
          
            
          
        
      
    
    

    Here is a Q&A https://dba.stackexchange.com/questions/207433/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 https://www.sqlskills.com/help/waits/lck_m_sch_s/ 😞

    • For the complete lock compatibility matrix, see the Books Online page https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186396(v=sql.105)?redirectedfrom=MSDN
    • For information on the lock hierarchy, see the Books Online page Lock https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189849(v=sql.105)?redirectedfrom=MSDN
    • For information on some of the lock modes, see the Books Online page https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175519(v=sql.105)?redirectedfrom=MSDN .
    • For other locking topics, see the Books Online page https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190615(v=sql.105)?redirectedfrom=MSDN .

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2