I'm fighting against NOLOCK in my current environment. One argument I've heard is that the overhead of locking slows down a query. So, I devised a test to see just how much this overhead might be.
I discovered that NOLOCK actually slows down my scan.
At first I was delighted, but now I 'm just confused. Is my test invalid somehow? Shouldn't NOLOCK actually allow a slightly faster scan? What's happening here?
Here's my script:
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())
What I've tried that didn't work:
- Running on different servers (same results, servers were 2016-SP1 and 2016-SP2, both quiet)
- Running on dbfiddle.uk on different versions (noisy, but probably same results)
- SET ISOLATION LEVEL instead of hints (same results)
- Turning off lock escalation on the table (same results)
- Examining actual execution time of the scan in the actual query plan (same results)
- Recompile hint (same results)
- Read only filegroup (same results)
The most promising exploration comes from removing the trash variable and using a no-results query. Initially this showed NOLOCK as slightly faster, but when I showed the demo to my boss, NOLOCK was back to being slower.
What is it about NOLOCK that slows down a scan with variable assignment?