PkzSdcgx

1USE TestDB
2GO
3
4--Create a five-million row table
5DROP TABLE IF EXISTS dbo.JustAnotherTable
6GO
7
8CREATE TABLE dbo.JustAnotherTable (
9ID INT IDENTITY PRIMARY KEY,
10notID CHAR(5) NOT NULL )
11
12INSERT dbo.JustAnotherTable
13SELECT TOP 5000000 'datas'
14FROM sys.all_objects a1
15CROSS JOIN sys.all_objects a2
16CROSS JOIN sys.all_objects a3
17
18/********************************************/
19-----Testing. Run each multiple times--------
20/********************************************/
21--How fast is a plain select? (I get about 587ms)
22DECLARE @trash CHAR(5), @dt DATETIME = SYSDATETIME()
23
24SELECT @trash = notID  --trash variable prevents any slowdown from returning data to SSMS
25FROM dbo.JustAnotherTable
26ORDER BY ID
27OPTION (MAXDOP 1)
28
29SELECT DATEDIFF(MILLISECOND,@dt,SYSDATETIME())
30
31----------------------------------------------
32--Now how fast is it with NOLOCK? About 640ms for me
33DECLARE @trash CHAR(5), @dt DATETIME = SYSDATETIME()
34
35SELECT @trash = notID
36FROM dbo.JustAnotherTable (NOLOCK)
37ORDER BY ID --would be an allocation order scan without this, breaking the comparison
38OPTION (MAXDOP 1)
39
40SELECT DATEDIFF(MILLISECOND,@dt,SYSDATETIME())