Dlaczego NOLOCK sprawia, że ​​skanowanie z przypisywaniem zmiennych jest wolniejsze?

Walczę z NOLOCK w moim obecnym środowisku. Jednym z argumentów, jaki słyszałem, jest to, że narzut związany z blokowaniem spowalnia zapytanie. Dlatego opracowałem test, aby zobaczyć, ile to może kosztować.

Odkryłem, że NOLOCK faktycznie spowalnia mój skan.

Na początku byłem zachwycony, ale teraz jestem tylko zdezorientowany. Czy mój test jest jakoś nieważny? Czy NOLOCK nie powinien pozwolić na nieco szybsze skanowanie? Co tu się dzieje?

Oto mój skrypt:

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

To, co próbowałem, nie zadziałało:

• Działa na różnych serwerach (te same wyniki, serwery były 2016-SP1 i 2016-SP2, oba ciche)
• Działa na dbfiddle.uk w różnych wersjach (głośne, ale prawdopodobnie takie same wyniki)
• USTAW POZIOM IZOLACJI zamiast podpowiedzi (te same wyniki)
• Wyłączanie eskalacji blokady na stole (te same wyniki)
• Badanie rzeczywistego czasu wykonania skanu w rzeczywistym planie zapytań (te same wyniki)
• Wskazówka dotycząca ponownej kompilacji (te same wyniki)
• Grupa plików tylko do odczytu (te same wyniki)

Najbardziej obiecujące badanie polega na usunięciu zmiennej kosza i zastosowaniu zapytania „brak wyników”. Początkowo pokazało to NOLOCK jako nieco szybszy, ale kiedy pokazałem demo mojemu szefowi, NOLOCK powrócił do spowolnienia.

Co takiego jest w NOLOCK, który spowalnia skanowanie z przypisywaniem zmiennych?

UWAGA: może nie być to rodzaj odpowiedzi, której szukasz. Ale być może będzie to pomocne dla innych potencjalnych odpowiedzi, jeśli chodzi o wskazówki, od czego zacząć szukać

Kiedy uruchamiam te zapytania w ramach śledzenia ETW (za pomocą PerfView), otrzymuję następujące wyniki:

Plain  - 608 ms  
NOLOCK - 659 ms

Różnica wynosi 51 ms . Różnica jest dość martwa (~ 50ms). Moje liczby są nieco wyższe z powodu ogólnego obciążenia próbkowania profilera.

Znaleźć różnicę

Oto porównanie obok siebie pokazujące, że różnica 51 ms w FetchNextRowmetodzie w sqlmin.dll:

Zwykły wybór jest po lewej stronie przy 332 ms, podczas gdy wersja nolock jest po prawej przy 383 ( 51 ms dłużej). Możesz również zobaczyć, że dwie ścieżki kodu różnią się w następujący sposób:

• Równina SELECT

  • sqlmin!RowsetNewSS::FetchNextRow połączenia
    • sqlmin!IndexDataSetSession::GetNextRowValuesInternal

• Za pomocą NOLOCK

  • sqlmin!RowsetNewSS::FetchNextRow połączenia
    • sqlmin!DatasetSession::GetNextRowValuesNoLock który wzywa
      • sqlmin!IndexDataSetSession::GetNextRowValuesInternal lub
      • kernel32!TlsGetValue

To pokazuje, że w FetchNextRowmetodzie występuje rozgałęzienie oparte na wskazaniu poziomu izolacji / wskazówki nolock.

Dlaczego NOLOCKoddział trwa dłużej?

Oddział nolock faktycznie spędza mniej czasu dzwoniąc do GetNextRowValuesInternal(25ms mniej). Ale kod bezpośrednio w GetNextRowValuesNoLock(bez metod, które nazywa AKA kolumna „Exc”) działa przez 63 ms - co stanowi większość różnic (63 - 25 = 38 ms wzrost czasu procesora netto).

Więc jakie są pozostałe 13ms (51ms ogółem - dotychczas 38ms) narzutów FetchNextRow?

Wysyłka interfejsu

Myślałem, że to była bardziej ciekawostka niż cokolwiek innego, ale wersja nolock wydaje się ponosić pewne obciążenie interfejsu, wywołując metodę interfejsu Windows API kernel32!TlsGetValueprzez kernel32!TlsGetValueStub- w sumie 17ms. Zwykły wybór wydaje się nie przechodzić przez interfejs, więc nigdy nie uderza w odgałęzienie i spędza tylko 6ms na TlsGetValue(różnica 11ms ). Możesz to zobaczyć powyżej na pierwszym zrzucie ekranu.

_{Prawdopodobnie powinienem ponownie uruchomić ten ślad przy większej liczbie iteracji zapytania. Myślę, że istnieją pewne małe rzeczy, takie jak przerwania sprzętowe, które nie zostały wykryte przez częstotliwość próbkowania PerfView 1 ms}

Poza tą metodą zauważyłem inną małą różnicę, która powoduje, że wersja nolock działa wolniej:

Zwolnienie blokad

Gałąź nolock wydaje się bardziej agresywnie uruchamiać sqlmin!RowsetNewSS::ReleaseRowsmetodę, którą można zobaczyć na tym zrzucie ekranu:

Zwykły wybór jest na górze, przy 12 ms, podczas gdy wersja nolock jest na dole przy 26 ms (14 ms dłużej). W kolumnie „Kiedy” można również zobaczyć, że kod był wykonywany częściej podczas próby. Może to być szczegół implementacji nolocka, ale wydaje się, że wprowadza sporo obciążenia dla małych próbek.

Istnieje wiele innych drobnych różnic, ale są to duże fragmenty.