主页 / dba / 问题 / 207422
Accepted
Forrest
Asked: 2018-05-22 14:21:59 +0800 CST

为什么 NOLOCK 会使带有变量赋值的扫描变慢？

  • 772

我在当前环境中与 NOLOCK 作斗争。我听到的一个论点是锁定的开销会减慢查询速度。因此，我设计了一个测试来查看此开销可能有多少。

我发现 NOLOCK 实际上减慢了我的扫描速度。

起初我很高兴，但现在我只是感到困惑。我的测试不知何故无效吗？NOLOCK 实际上不应该允许稍微快一点的扫描吗？这里发生了什么事？

这是我的脚本：

USE TestDB
GO

--Create a five-million row table
DROP TABLE IF EXISTS dbo.JustAnotherTable
GO

CREATE TABLE dbo.JustAnotherTable (
ID INT IDENTITY PRIMARY KEY,
notID CHAR(5) NOT NULL )

INSERT dbo.JustAnotherTable
SELECT TOP 5000000 'datas'
FROM sys.all_objects a1
CROSS JOIN sys.all_objects a2
CROSS JOIN sys.all_objects a3

/********************************************/
-----Testing. Run each multiple times--------
/********************************************/
--How fast is a plain select? (I get about 587ms)
DECLARE @trash CHAR(5), @dt DATETIME = SYSDATETIME()

SELECT @trash = notID  --trash variable prevents any slowdown from returning data to SSMS
FROM dbo.JustAnotherTable
ORDER BY ID
OPTION (MAXDOP 1)

SELECT DATEDIFF(MILLISECOND,@dt,SYSDATETIME())

----------------------------------------------
--Now how fast is it with NOLOCK? About 640ms for me
DECLARE @trash CHAR(5), @dt DATETIME = SYSDATETIME()

SELECT @trash = notID
FROM dbo.JustAnotherTable (NOLOCK)
ORDER BY ID --would be an allocation order scan without this, breaking the comparison
OPTION (MAXDOP 1)

SELECT DATEDIFF(MILLISECOND,@dt,SYSDATETIME())

我尝试过但没有用的：

  • 在不同的服务器上运行（相同的结果，服务器是 2016-SP1 和 2016-SP2，都安静）
  • 在不同版本的 dbfiddle.uk上运行（嘈杂，但结果可能相同）
  • 设置隔离级别而不是提示（相同的结果）
  • 关闭表上的锁升级（相同的结果）
  • 检查实际查询计划中扫描的实际执行时间（相同结果）
  • 重新编译提示（相同的结果）
  • 只读文件组（相同结果）

最有前途的探索来自删除垃圾变量和使用无结果查询。最初这显示 NOLOCK 稍快一些，但当我向我的老板展示演示时，NOLOCK 又变慢了。

NOLOCK 减慢变量赋值扫描的原因是什么？

sql-server sql-server-2016

1 个回答

  1. Best Answer

    注意：这可能不是您正在寻找的答案类型。但就提供从哪里开始寻找的线索而言，这可能对其他潜在的回答者有所帮助

    当我在 ETW 跟踪（使用 PerfView）下运行这些查询时，我得到以下结果：

    Plain  - 608 ms  
NOLOCK - 659 ms

    所以差异是51ms。这与您的差异（〜50ms）完全不同。由于探查器采样开销，我的数字总体略高。

    寻找差异

    这是一个并排比较，显示 51 毫秒的差异在于 sqlmin.dll 中的FetchNextRow方法：

    获取下一行

    Plain select 位于左侧 332 ms，而 nolock 版本位于右侧 383（长51ms）。您还可以看到这两个代码路径的不同之处在于：

    • 清楚的SELECT

      • sqlmin!RowsetNewSS::FetchNextRow打电话
        • sqlmin!IndexDataSetSession::GetNextRowValuesInternal

    • 使用NOLOCK

      • sqlmin!RowsetNewSS::FetchNextRow打电话
        • sqlmin!DatasetSession::GetNextRowValuesNoLock哪个调用
          • sqlmin!IndexDataSetSession::GetNextRowValuesInternal或者
          • kernel32!TlsGetValue

    这表明FetchNextRow基于隔离级别/nolock 提示的方法中存在一些分支。

    为什么NOLOCK分支需要更长的时间？

    nolock 分支实际上花费更少的时间调用GetNextRowValuesInternal（少 25 毫秒）。但是直接在其中的代码GetNextRowValuesNoLock（不包括它称为“Exc”列的方法）运行了 63 毫秒 - 这是差异的大部分（63 - 25 = CPU 时间净增加 38 毫秒）。

    那么另外 13 毫秒（总共 51 毫秒 - 到目前为止占 38 毫秒）的开销是FetchNextRow多少？

    接口调度

    我认为这比任何事情都更令人好奇，但是 nolock 版本似乎通过调用 Windows API 方法来招致一些接口调度开销kernel32!TlsGetValue——kernel32!TlsGetValueStub总共 17 毫秒。普通选择似乎没有通过界面，因此它永远不会碰到存根，并且只花费 6 毫秒TlsGetValue（相差11毫秒）。您可以在上面的第一个屏幕截图中看到这一点。

    我可能应该用更多的查询迭代再次运行这个跟踪，我认为有一些小东西，比如硬件中断，没有被 PerfView 的 1ms 采样率拾取

    在该方法之外，我注意到另一个导致 nolock 版本运行速度变慢的小差异：

    释放锁

    nolock 分支似乎更积极地运行该 sqlmin!RowsetNewSS::ReleaseRows方法，您可以在此屏幕截图中看到：

    释放锁

    plain select 位于顶部，耗时 12ms，而 nolock 版本位于底部，耗时 26ms（长14ms）。您还可以在“何时”列中看到代码在示例期间执行得更频繁。这可能是 nolock 的一个实现细节，但它似乎为小样本引入了相当多的开销。

    还有很多其他的小差异，但这些都是大块。

    • 14

相关问题