Loading…

READY TO DEAL WITH YOUR DATA?

Get some tips on how to do that!
Start exploring

SQL Server – śledzenie procesu tworzenia indeksu kolumnowego

Ostatnimi czasy w projektach i prezentacjach pojawia się u mnie wątek indeksów kolumnowych w SQL Server (głównie w wersji 2016, gdzie ta funkcjonalność jest naprawdę mocno dopracowana). Siedząc u klientów i zakładając indeksy kolumnowe nierzadko na tabelach z miliardami wierszy, pomyślałem, że dobrze byłoby mieć wgląd w progres procesu powstawania indeksu. I stąd pomysł na prosty skrypt:

USE TwojaBaza;
GO
SELECT 
  FORMAT(SUM(s.row_count), N'N0', N'pl-PL') AS RowsProcessed, 
  FORMAT((SUM(s.row_count)/???.), N'P2', N'pl-PL') AS PercentRowsProcessed
FROM sys.column_store_segments AS s WITH (NOLOCK)
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON s.partition_id = p.partition_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
ON p.object_id = o.object_id
WHERE	o.name = N'nazwa_tabeli' 
	AND o.schema_id = SCHEMA_ID(N'nazwa_schematu') 
	AND s.column_id = 1;
GO

Słowo komentarza:

  • Zanim uruchomisz ten skrypt musisz wiedzieć, ile wierszy jest w tabeli – podstaw tę liczbę zamiast ???. Porównanie wyniku do liczby wierszy w tabeli będzie dla Ciebie stopniem postępu procesu zakładania indeksu.
  • Niestety, nie dało się używać funkcji OBJECT_ID, ponieważ wygląda na to, że jej użycie generuje blokady będące w konflikcie z blokadami stawianymi przez proces zakładania indeksu.
  • Uwaga na kolumnę rows w widoku sys.partitions – nie warto jej używać do śledzenia liczby wierszy w tabeli z indeksem kolumnowym klastrowanym, ponieważ zdarza się, że przekłamuje liczbę wierszy (row_count z sys.column_store_segments mówi wtedy całą prawdę). Cuda potrafią się dziać zwłaszcza przy operacji REORGANIZE na indeksie kolumnowym (sceny typu podwojona liczba wierszy w raportach w SQL Server Management Studio korzystających z widoku sys.partitions to norma). Acha, i nie próbujcie uruchamiać raportów pokazujących liczby wierszy w tabelach w SQL Server Management Studio – pod spodem zadawane są zapytania do widoku sys.partitions bez hinta NOLOCK… :-)
  • Funkcja FORMAT pozwala ogarnąć liczby wierszy idące w miliardy i wyżej :-)
  • Oczywiście, należy podstawić odpowiednie nazwy schematu i tabeli :-)

Do tego warto dopisać jeszcze drugie zapytanie:

SELECT * FROM sys.dm_os_waiting_tasks WITH (NOLOCK) WHERE session_id = ??;

Zamiast znaków zapytania podstawiamy numer sesji, w ramach której zakładany jest indeks kolumnowy (zastąp ?? numerem sesji). I teraz – dopóki w kolumnie wait_type zwracanej przez powyższe zapytanie obserwujesz jedynie stany oczekiwania wskazujące na odczyt danych (na ogół zrównoleglony) – PAGEIOLATCH_SH, LATCH_SH i CXPACKET, liczba zwracana przez pierwsze zapytanie będzie zwracało NULL. I do zakończenia tego procesu progresu nie będziesz obserwować. Pierwsza liczba z pierwszego zapytania pojawi się po stworzeniu pierwszego pełnego segmentu (nieco ponad 1 mln wierszy). Czyli początek wygląda tak:

image

Później jest już tylko lepiej. Kiedy obok CXPACKET pojawia się wiele mówiący stan oczekiwania COLUMNSTORE_BUILD_THROTTLE, to znak, że za chwilę powinien “wyskoczyć pierwszy milion”, a po nim następne:

image

Proces tworzenia indeksu kolumnowego może być długotrwały, ale śledząc czas i liczbę zwracaną przez pierwsze zapytanie można przynajmniej spróbować określić, ile zostało do jego końca ;-)

Nagroda za cierpliwość może być wielka – u jednego z klientów algorytm COLUMNSTORE_ARCHIVE (SQL Server 2016) pozwolił osiągnąć 77-krotną kompresję (z tabeli 100 GB zrobiła się tabela 1,3 GB), że o czasach zapytań nie wspomnę (z kilkudziesięciu minut często do czasów poniżej 1 s). Zdecydowanie, indeksy kolumnowe to obecnie jedna z moich ulubionych funkcjonalności SQL Server 2016.

Leave a Reply