1

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
2
  • 2
    You might have better luck joining to sys.objects and filtering on is_ms_shipped = 0 rather than using OBJECTPROPERTY since those functions often ignore locking semantics. May 12 at 14:06
  • @ErikDarling - changed OBJECTPROPERTY to sys.objects - it didnt help:( May 13 at 9:54

2 Answers 2

2

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.

1
  • I think this is the solution, but we have the Standard version:) May 16 at 7:34
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. 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.

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

1
  • ONLINE=ON option should allow a Sch-S lock during the build phase? May 13 at 4:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.