Równoległe wstawianie danych do tabel tymczasowych

Z tabel tymczasowych korzysta niemal każdy związany w swojej pracy zawodowej z SQL Server. Są one pomocne w przypadku czasowego przechowywania rezultatów zapytań i często mogą być również techniką optymalizacyjną. Nie inaczej jest w przypadku ładowań większych tabel wchodzących w skład hurtowni danych gdzie tabelki z hashem pojawiają się nagminnie czy to w procedurach składowanych czy też w zapytaniach ad-hoc. Wstawiając większe wolumeny danych do tabel tego typu zależy nam aby samo wstawianie wykonywane było jak najszybciej. Do tej pory mieliśmy jasną sytuację jeśli chodzi o tabele tymczasowe i równoległe wstawianie danych, jednakże w ostatnim czasie pojawiło się kilka niejasnych aspektów z tym związanych (pozdrowienia dla Jessiego, który razem ze mną inwestygował ten problem), które postaram się opisać w ramach niniejszego artykułu.

Na samym początku powiedzmy sobie w jaki sposób możemy wstawiać większe ilości danych do tabel tymczasowych. Skupimy się na dwóch najpopularniejszych sposobach czyli INSERT SELECT oraz SELECT INTO. W większości publikacji na jakie możemy natrafić w internecie znajdziemy oczywistą informację o tym, że SELECT INTO tworzy tabele, a INSERT SELECT wymaga aby tabela istniała w momencie wywołania operacji wstawienia. Z tą różnicą nie ma co polemizować i odnosi się zarówno do tabel tymczasowych jak i trwałych. My jednak chcemy powiedzieć o czymś innym, a mianowicie o tym czy możliwe jest osiągnięcie równoległego wstawiania danych do tabel tymczasowych używając obu wymienionych wcześniej technik.

Jak zapewne wiecie od SQL Server 2014 wprowadzona została równoległa operacja SELECT INTO – ma to oczywiście również zastosowanie w przypadku tabel tymczasowych. Sprawdźmy to sobie na konkretnym przykładzie. Spróbujmy skopiować sobie zawartość tabeli Fact.Sales bazy WideWorldImportersDW do tabeli tymczasowej:

USE WideWorldImportersDW
GO

SELECT  [Sale Key]
      ,[City Key]
      ,[Customer Key]
      ,[Bill To Customer Key]
      ,[Stock Item Key]
      ,[Invoice Date Key]
      ,[Delivery Date Key]
      ,[Salesperson Key]
      ,[WWI Invoice ID]
      ,[Description]
      ,[Package]
      ,[Quantity]
      ,[Unit Price]
      ,[Tax Rate]
      ,[Total Excluding Tax]
      ,[Tax Amount]
      ,[Profit]
      ,[Total Including Tax]
      ,[Total Dry Items]
      ,[Total Chiller Items]
      ,[Lineage Key]
INTO #FactSaleSelectInto
  FROM [Fact].[Sale]
GO

Wyświetlmy plan wykonania tego zapytania:

Plan jest tak samo prosty jak nasze zapytanie, jednakże możemy zauważyć, że poszczególne iteratory działają na kilku wątkach. Świadczy o tym również fakt wystąpienia operatora Gather Streams. Standardowy plan, który powinien wyglądać dokładnie w ten sposób od wersji 2014 serwera SQL, aż do teraz.

W drugim przykładzie zasymulujemy sobie podobną operację tylko tym razem z wykorzystaniem operatora INSERT SELECT. Najpierw oczywiście musimy sobie stworzyć odpowiednią tabelę:

CREATE TABLE #FactSaleInsertSelect(
	[Sale Key] [bigint]  NOT NULL,
	[City Key] [int] NOT NULL,
	[Customer Key] [int] NOT NULL,
	[Bill To Customer Key] [int] NOT NULL,
	[Stock Item Key] [int] NOT NULL,
	[Invoice Date Key] [date] NOT NULL,
	[Delivery Date Key] [date] NULL,
	[Salesperson Key] [int] NOT NULL,
	[WWI Invoice ID] [int] NOT NULL,
	[Description] [nvarchar](100) NOT NULL,
	[Package] [nvarchar](50) NOT NULL,
	[Quantity] [int] NOT NULL,
	[Unit Price] [decimal](18, 2) NOT NULL,
	[Tax Rate] [decimal](18, 3) NOT NULL,
	[Total Excluding Tax] [decimal](18, 2) NOT NULL,
	[Tax Amount] [decimal](18, 2) NOT NULL,
	[Profit] [decimal](18, 2) NOT NULL,
	[Total Including Tax] [decimal](18, 2) NOT NULL,
	[Total Dry Items] [int] NOT NULL,
	[Total Chiller Items] [int] NOT NULL,
	[Lineage Key] [int] NOT NULL
 )
 GO

Następnie wstawmy sobie do niej dane:

INSERT INTO #FactSaleInsertSelect 
SELECT  [Sale Key]
      ,[City Key]
      ,[Customer Key]
      ,[Bill To Customer Key]
      ,[Stock Item Key]
      ,[Invoice Date Key]
      ,[Delivery Date Key]
      ,[Salesperson Key]
      ,[WWI Invoice ID]
      ,[Description]
      ,[Package]
      ,[Quantity]
      ,[Unit Price]
      ,[Tax Rate]
      ,[Total Excluding Tax]
      ,[Tax Amount]
      ,[Profit]
      ,[Total Including Tax]
      ,[Total Dry Items]
      ,[Total Chiller Items]
      ,[Lineage Key]
  FROM [Fact].[Sale]

Plan zapytania wygląda tu już nieco bardziej interesująco:

Widzicie pewną różnicę pomiędzy oboma zapytaniami? INSERT SELECT wykonywany jest na jednym wątku! Co ciekawe nie tylko INSERT był jednowątkowy ale również SELECT. Czy to oznacza, że polecenie to może być wykonane tylko w taki sposób? Oczywiście, że nie przecież jedną z nowości w SQL Server 2016 było właśnie całkowicie zrównoleglona operacja INSERT SELECT – jeden z ukrytych aspektów, które niejako z pudełka dawał nam wzrost wydajności. Aby osiągnąć w pełni równoległy plan musieliśmy po prostu mieć bazę w compatibility level =130 i dodatkowo w przypadku tabel stałych użyć hinta TABLOCK. W przypadku tabel tymczasowych nie było takiej potrzeby i nawet bez rzeczonego hinta plan był równoległy – ze względu na fakt, iż do lokalnej tabeli tymczasowej wyłączny dostęp miała tylko jedna sesja. Jako dowód podsyłam wam plan dokładnie tego samego zapytania tylko tym razem na innej instancji:

Jak to możliwe, że raz to zapytanie zostało wykonane równolegle a raz nie? Odpowiedź jest prosta, w pierwszym przypadku mamy do czynienia z SQL Server 2016 z SP1 w drugim natomiast mówimy o SQL Server 2016 bez żadnego Service Pack. Czyli SP1 oprócz tego, że dał nam możliwości używania “ficzerów” wersji Enterprise we wszystkich innych wersjach to wyrzucił nam równoległość wstawiania danych do tabel tymczasowych poleceniem INSERT SELECT! Dlaczego tak się dzieje? Microsoft podaje nam informacje, że równoległe wstawianie danych do tabel tego typu może powodować rywalizację o dostęp do strony systemowej PFS w TEMPDB. Wcześniejszym zaleceniem było to, że w przypadku wystąpienia problemów z PFS należy użyć MAXDOP 1.Obecnie musimy postępować odwrotnie tzn.  aby mieć równoległość musimy podobnie jak w przypadku zwykłych tabel wyspecyfikować TABLOCK – Przetestujmy tą tezę. Przy pomocy narzędzia ostress wykonamy sobie wiele zapytań wstawiających dane do tabel tymczasowych używając INSERT SELECT. Całe zapytanie wykonuje się w nieco mniej niż sekundę – postaramy się to zmierzyć odpytując ciągle widok sys.dm_os_waiting_tasks i wrzucając jego rezultat do tabeli. Będziemy sprawdzać czy występuje wait na stronie danych będącej częścią łańcucha stron 8088 ponieważ właśnie co tyle stron pojawia się strona PFS – sprawdzamy oczywiście w bazie oznaczonej identyfikatorem 2 czyli tempdb:

WHILE 1 = 1
    BEGIN
    WAITFOR DELAY '00:00:02'

        INSERT INTO dbo.WaitStats
               SELECT session_id,
                      wait_type,
                      wait_duration_ms,
                      blocking_session_id,
                      resource_description,
                      ResourceType = CASE
                                         WHEN CAST(RIGHT(resource_description, LEN(resource_description)-CHARINDEX(':', resource_description, 3)) AS INT) % 8088 = 0
                                         THEN 'Is PFS Page'
                                  
                                         ELSE '-'
                                     END
               FROM sys.dm_os_waiting_tasks
               WHERE wait_type LIKE 'PAGE%LATCH_%'
                     AND resource_description LIKE '2:%';
    END;

W rezultacie przy uruchomieniu zapytań z hintem TABLOCK (czyli z równoległym wstawianiem danych) otrzymaliśmy następujące statystyki oczekiwań:

SELECT ResourceType,count(*) FROM dbo.WaitStats WITH (nolock)
GROUP BY ResourceType

Według mojego wzorca rzeczywiście otrzymaliśmy bardzo dużą liczbę oczekiwań związaną ze stroną PFS. Sprawdźmy rzeczywiście czy tak jest i podejrzyjmy sobie wybraną stronę na której wystąpił wait:

Wybrałem stronę 2:1:121320 – podejrzyjmy ja standardowo używając DBCC PAGE:

DBCC TRACEON(3604)

DBCC PAGE(2,1,121320,0)

W nagłówku strony mamy atrybut m_type równy 11 co jawnie wskazuje stronę PFS! Tak więc rzeczywiście mamy do czynienia z pewną blokadą związaną z tą stroną. Poniżej przedstawiam również wyniki testu dla zapytania INSERT SELECT bez hinta TABLOCK, a więc działającego na jednym wątku:

Również mamy oczekiwania na PFS ale jest ich zdecydowanie mniej – oczekiwania na tej stronie są naturalne w przypadku wstawiania danych do tabel tymczasowych ponieważ potrzeba sprawdzać dane o alokacji i wolnym miejscu, które właśnie na tej stronie się znajdują. Oczywiście powyższy test ma jedynie charakter poglądowy ale ogólnie rzecz biorąc warto mieć na uwadze jak to wszystko działa. Blokad będzie tym więcej im więcej wątków jest zaangażowanych we wstawianie danych – jeśli nie jest to dla nas problem możemy użyć TABLOCK w przeciwnym razie otrzymamy seryjny plan. Ja osobiście natknąłem się na ten temat trochę przypadkiem przy przygotowywaniu prezentacji dotyczącej migracji SQL Server 2012 na SQL Server 2016 – wy nie bądźcie zaskoczeni i reagujcie w zależności od potrzeb.