Równoległe wstawianie danych do tabel tymczasowych

ParallelismAndtempTables_00

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:

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ę:

Następnie wstawmy sobie do niej dane:

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:

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

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:

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.

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

Latest posts by Adrian Chodkowski (see all)

Leave a Comment

Your email address will not be published. Required fields are marked *