我有查询分区数据的代码。但是当数据库创建索引时,由于 LCK_M_SCH_S 锁,我的查询不起作用,尽管我对所有表都使用了 NOLOCK 提示。有没有办法绕过这个锁或在没有锁的情况下获取这些数据?
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
没有解决方法。这是设计使然。根据微软文档:
这是您所看到的内容以及您被阻止的原因的回购。我正在使用Microsoft 提供的 AdventureWorks 数据库和使用 Adam Machanic 编写的查询创建的大表。 在此处输入链接描述
从查询窗口之一运行它:
打开一个新窗口并使用 NOLOCK 进行选择。
现在检查每个会话持有的等待类型/阻塞和锁:
您将看到由于 LCK_M_SCH_S 锁而阻塞了带有 NOLOCK 的选择。
如果您从上面的输出中看到 locks 列并单击 XML,您将看到带有 NOLOCK 的 select 正在请求一个
Sch-S
(Schema 稳定性锁)锁并且正在等待,因为该锁与LCK_M_SCH_S
.这是一个问答如何避免持有 Sch-S 锁的选择查询,其中 Mike Walsh 解释了为什么这是必要的。
一般锁定信息(从此处复制):
在某些 SQL Server 版本中,您可以创建索引"Online"。这意味着服务器在进程结束之前不会
Sch-M
在短时间内锁定。您可以通过 SSMS 索引属性或
ONLINE = ON
在 T-SQL 中使用来执行此操作。执行此操作需要注意的重要事项:
Sch-M
锁仍然保持到最后。另请参阅文档。