Najlepsze rozwiązania dotyczące dedykowanych pul SQL w usłudze Azure Synapse Analytics

  • Artykuł

Ten artykuł zawiera zbiór najlepszych rozwiązań, które ułatwiają osiągnięcie optymalnej wydajności dedykowanych pul SQL w usłudze Azure Synapse Analytics. Jeśli pracujesz z bezserwerową pulą SQL, zobacz Najlepsze rozwiązania dotyczące bezserwerowych pul SQL, aby uzyskać szczegółowe wskazówki. Poniżej znajdziesz podstawowe wskazówki i ważne obszary, na których należy skoncentrować się podczas tworzenia rozwiązania. Każda sekcja zawiera wprowadzenie do koncepcji, a następnie wskazuje bardziej szczegółowe artykuły, które bardziej szczegółowo omawiają koncepcję.

Ładowanie dedykowanych pul SQL

Aby uzyskać wskazówki dotyczące ładowania dedykowanych pul SQL, zobacz Wskazówki dotyczące ładowania danych.

Obniżenie kosztów dzięki wstrzymaniu i skalowaniu

Aby uzyskać więcej informacji na temat obniżania kosztów poprzez wstrzymanie i skalowanie, zobacz Zarządzanie obliczeniami.

Prowadzenie statystyk

Dedykowana pula SQL można skonfigurować do automatycznego wykrywania i tworzenia statystyk dotyczących kolumn. Plany zapytań utworzone przez optymalizator są tylko tak dobre, jak dostępne statystyki.

Zalecamy włączenie AUTO_CREATE_STATISTICS dla baz danych i aktualizowanie statystyk codziennie lub po każdym obciążeniu, aby upewnić się, że statystyki kolumn używane w zapytaniach są zawsze aktualne.

Aby skrócić czas konserwacji statystyk, należy selektywnie określać, które kolumny mają statystyki lub które wymagają najczęściej aktualizowania. Na przykład możesz zaktualizować kolumny dat, w których można codziennie dodawać nowe wartości. Skoncentruj się na uzyskaniu statystyk dotyczących kolumn zaangażowanych w sprzężenia, kolumn używanych w klauzuli WHERE i kolumn znalezionych w kolumnach GROUP BY.

Dodatkowe informacje na temat statystyk można znaleźć w artykułach Zarządzanie statystykami tabel, TWORZENIE STATYSTYK i UPDATE STATISTICS .

Dostosowywanie wydajności zapytań

Grupowanie instrukcji INSERT w partie

Jednorazowe ładowanie do małej tabeli z instrukcją INSERT, na INSERT INTO MyLookup VALUES (1, 'Type 1')przykład może być najlepszym podejściem w zależności od potrzeb. Jeśli jednak trzeba załadować tysiące lub miliony wierszy w ciągu dnia, prawdopodobnie jednotonowe instrukcje INSERTS nie są optymalne.

Jednym ze sposobów rozwiązania tego problemu jest opracowanie jednego procesu zapisu w pliku, a następnie innego procesu okresowego ładowania tego pliku. Aby uzyskać więcej informacji, zapoznaj się z artykułem INSERT .

Korzystanie z funkcji PolyBase do szybkiego ładowania i eksportowania danych

Dedykowana pula SQL obsługuje ładowanie i eksportowanie danych za pomocą kilku narzędzi, takich jak Azure Data Factory, PolyBase i BCP. W przypadku małych ilości danych, gdy wydajność nie ma decydującego znaczenia, można zastosować dowolne narzędzie.

Uwaga

Technologia PolyBase jest najlepszym wyborem podczas ładowania lub eksportowania dużych ilości danych lub gdy potrzebujesz szybszej wydajności.

Obciążenia funkcji PolyBase można uruchomić za pomocą instrukcji CTAS lub INSERT INTO. Usługa CTAS zminimalizuje rejestrowanie transakcji i jest najszybszym sposobem ładowania danych. Usługa Azure Data Factory obsługuje również obciążenia technologii PolyBase i może osiągnąć wydajność podobną do CTAS. Technologia PolyBase obsługuje różne formaty plików, w tym pliki Gzip.

Aby zmaksymalizować przepływność podczas korzystania z plików tekstowych Gzip, podziel pliki na 60 lub więcej plików, aby zmaksymalizować równoległość obciążenia. W celu uzyskania szybszej całkowitej przepływności warto rozważyć równoległe ładowania danych. Dodatkowe informacje dotyczące tej sekcji znajdują się w następujących artykułach:

Ładowanie i przesyłanie zapytań dotyczących tabel zewnętrznych

Technologia PolyBase nie jest optymalna dla zapytań. Tabele polyBase dla dedykowanych pul SQL obsługują obecnie tylko pliki obiektów blob platformy Azure i usługę Azure Data Lake Storage. Te pliki nie mają żadnych zasobów obliczeniowych, na których są one obsługiwane. W związku z tym dedykowane pule SQL nie mogą odciążać tej pracy i muszą odczytać cały plik, ładując go w taki sposób, aby tempdb mógł odczytać dane.

Jeśli masz kilka zapytań dotyczących wykonywania zapytań dotyczących tych danych, lepiej jest załadować te dane raz i zapytania używają tabeli lokalnej. Dalsze wskazówki dotyczące technologii PolyBase znajdują się w przewodniku dotyczącym korzystania z technologii PolyBase .

Dystrybucja dużych tabel z użyciem skrótów

Domyślnym sposobem dystrybucji tabel jest działanie okrężne. To ustawienie domyślne ułatwia użytkownikom rozpoczęcie tworzenia tabel bez konieczności decydowania o tym, jak powinny być dystrybuowane ich tabele. Tabele działania okrężnego mogą działać wystarczająco dla niektórych obciążeń. Jednak w większości przypadków kolumna dystrybucji zapewnia lepszą wydajność.

Najczęstszym przykładem tabeli rozproszonej przez kolumnę przewyższającą tabelę działania okrężnego jest sprzężenie dwóch dużych tabel faktów.

Jeśli na przykład masz tabelę zamówień dystrybuowaną przez order_id, a tabela transakcji jest również dystrybuowana przez order_id, gdy dołączasz tabelę zamówień do tabeli transakcji w order_id, to zapytanie staje się zapytaniem przekazywanym. Operacje przenoszenia danych są następnie usuwane. Mniej kroków to szybsze kwerendy. Mniejsza konieczność przenoszenia danych wpływa także na skrócenie czasu działania kwerend.

Napiwek

Podczas ładowania tabeli rozproszonej dane przychodzące nie powinny być sortowane na kluczu dystrybucji. Spowoduje to spowolnienie obciążeń.

Linki do artykułu podane poniżej zawierają dodatkowe szczegóły dotyczące poprawy wydajności za pośrednictwem wybierania kolumny dystrybucji. Ponadto znajdziesz informacje o sposobie definiowania tabeli rozproszonej w klauzuli WITH instrukcji CREATE TABLE:

Unikanie nadmiernego partycjonowania

Chociaż partycjonowanie danych może być skuteczne w celu utrzymania danych przez przełączanie partycji lub optymalizowanie skanów za pomocą eliminacji partycji, zbyt wiele partycji może spowolnić zapytania. Często strategia partycjonowania o wysokim stopniu szczegółowości, która może działać dobrze w programie SQL Server, może nie działać dobrze w dedykowanej puli SQL.

Zbyt wiele partycji może zmniejszyć skuteczność klastrowanych indeksów magazynu kolumn, jeśli każda partycja ma mniej niż 1 milion wierszy. Dedykowane pule SQL automatycznie partycjonuje dane na 60 baz danych. Dlatego jeśli utworzysz tabelę z 100 partycjami, wynikiem będzie 6000 partycji. Każde obciążenie jest inne, więc najlepszą poradą jest eksperymentowanie z partycjonowaniem, aby zobaczyć, co działa najlepiej dla obciążenia.

Jedną z opcji do rozważenia jest użycie stopnia szczegółowości, który jest niższy niż zaimplementowany przy użyciu programu SQL Server. Rozważ na przykład użycie partycji cotygodniowych lub miesięcznych zamiast partycji dziennych.

Więcej informacji na temat partycjonowania znajduje się w artykule Partycjonowanie tabel.

Minimalizowanie rozmiarów transakcji

Instrukcje INSERT, UPDATE i DELETE są uruchamiane w transakcji. Gdy się nie powiedzie, muszą zostać wycofane. Aby zmniejszyć potencjał długiego wycofywania, zminimalizuj rozmiary transakcji zawsze, gdy jest to możliwe. Zminimalizowanie rozmiarów transakcji można wykonać przez podzielenie instrukcji INSERT, UPDATE i DELETE na części. Jeśli na przykład masz obiekt INSERT, który ma potrwać 1 godzinę, możesz podzielić wstawianie na cztery części. Każde uruchomienie zostanie następnie skrócone do 15 minut.

Napiwek

Stosuj szczególne przypadki minimalnego rejestrowania, takie jak CTAS, TRUNCATE, DROP TABLE lub INSERT, do opróżniania tabel, aby zmniejszyć ryzyko wycofywania.

Innym sposobem na eliminację procesu wycofywania zmian jest korzystanie z operacji z właściwościami Tylko metadane, takich jak przełączanie partycji pod kątem zarządzania danymi. Na przykład zamiast wykonywać instrukcję DELETE, aby usunąć wszystkie wiersze w tabeli, w której order_date znajdował się w październiku 2001 r., dane można podzielić na partycje co miesiąc. Następnie możesz przełączyć partycję z danymi dla pustej partycji z innej tabeli (zobacz ALTER TABLE examples ).

W przypadku tabel, które nie są partycjonowane, rozważ użycie funkcji CTAS do zapisywania danych, które mają być zachowywane w tabeli, a nie przy użyciu funkcji DELETE. Jeśli CTAS zajmuje tyle samo czasu, znacznie bezpieczniejsze jest uruchomienie, ponieważ ma minimalne rejestrowanie transakcji i można je anulować szybko w razie potrzeby.

Więcej informacji na temat zawartości związanej z tą sekcją znajduje się w poniższych artykułach:

Zmniejsz rozmiary wyników zapytania

Zmniejszenie rozmiarów wyników zapytania pomaga uniknąć problemów po stronie klienta spowodowanych dużymi wynikami zapytania. Możesz edytować zapytanie, aby zmniejszyć liczbę zwracanych wierszy. Niektóre narzędzia do generowania zapytań umożliwiają dodanie składni "top N" do każdego zapytania. Wynik zapytania można również cetAS utworzyć w tabeli tymczasowej, a następnie użyć eksportu technologii PolyBase do przetwarzania w dół.

Użycie możliwie najmniejszego rozmiaru kolumny

Podczas definiowania języka DDL użyj najmniejszego typu danych, który będzie obsługiwał dane, co spowoduje zwiększenie wydajności zapytań. To zalecenie jest szczególnie ważne w przypadku kolumn CHAR i VARCHAR. Jeśli najdłuższa wartość w kolumnie ma 25 znaków, należy zdefiniować typ kolumny jako VARCHAR(25). Należy unikać domyślnego definiowania wszystkich kolumn znaków jako kolumn długich wartości. Ponadto zdefiniuj kolumny jako VARCHAR, gdy jest to wszystko, co jest potrzebne, zamiast używać NVARCHAR.

Aby uzyskać bardziej szczegółowy przegląd podstawowych pojęć związanych z powyższymi informacjami, zobacz artykuły Omówienie tabel, Typy danych tabel i TWORZENIE TABEL .

Korzystanie z tymczasowych tabel stosów dla danych przejściowych

W przypadku tymczasowego lądowania danych w dedykowanych pulach SQL tabele stert zwykle przyspieszają ogólny proces. Jeśli ładujesz dane tylko w celu przygotowania ich przed uruchomieniem większej liczby przekształceń, ładowanie tabeli do tabeli sterty będzie szybsze niż ładowanie danych do tabeli klastrowanego magazynu kolumn.

Ładowanie danych do tabeli tymczasowej jest również znacznie szybsze niż ładowanie tabeli do magazynu trwałego. Tabele tymczasowe zaczynają się od "#" i są dostępne tylko dla sesji, która ją utworzyła. W związku z tym mogą one działać tylko w ograniczonych scenariuszach. Tabele stosu definiuje się przy użyciu klauzuli WITH instrukcji CREATE TABLE. W przypadku użycia tabeli tymczasowej należy pamiętać o utworzeniu dla niej statystyk.

Aby uzyskać więcej informacji, zobacz artykuły Tabele tymczasowe, CREATE TABLE i CREATE TABLE AS SELECT .

Optymalizowanie tabel klastrowanego magazynu kolumn

Klastrowane indeksy magazynu kolumn to jeden z najbardziej wydajnych sposobów przechowywania danych w dedykowanej puli SQL. Domyślnie tabele w dedykowanej puli SQL są tworzone jako Clustered ColumnStore. Dla uzyskania najlepszej wydajności kwerend w odniesieniu do tabel magazynu kolumn ważne jest zapewnienie dobrej jakości segmentów. Jeśli wiersze są zapisywane w tabelach magazynu kolumn przy dużym wykorzystaniu pamięci, może to spowodować obniżenie jakości segmentów w magazynie kolumn.

Jakość segmentu można zmierzyć według liczby wierszy w skompresowanej grupie wierszy. Zobacz artykuł Przyczyny niskiej jakości indeksu magazynu kolumn w artykule Indeksy tabel, aby uzyskać instrukcje krok po kroku dotyczące wykrywania i poprawiania jakości segmentów dla tabel klastrowanego magazynu kolumn.

Ponieważ segmenty magazynu kolumn wysokiej jakości są ważne, dobrym pomysłem jest użycie identyfikatorów użytkowników, które znajdują się w średniej lub dużej klasie zasobów na potrzeby ładowania danych. Użycie niższych jednostek magazynu danych oznacza, że chcesz przypisać większą klasę zasobów do użytkownika ładującej.

Tabele magazynu kolumn zwykle nie wypychają danych do skompresowanego segmentu magazynu kolumn, dopóki nie będzie więcej niż 1 milion wierszy na tabelę. Każda dedykowana tabela puli SQL jest dystrybuowana do 60 różnych dystrybucji. W związku z tym tabele magazynu kolumn nie będą korzystać z zapytania, chyba że tabela zawiera ponad 60 milionów wierszy.

Napiwek

W przypadku tabel z mniej niż 60 milionami wierszy indeks magazynu kolumn może nie być optymalnym rozwiązaniem.

W przypadku partycjonowania danych każda partycja będzie musiała mieć 1 milion wierszy, aby skorzystać z klastrowanego indeksu magazynu kolumn. W przypadku tabeli z 100 partycjami musi mieć co najmniej 6 miliardów wierszy, aby korzystać z magazynu kolumn klastrowanych (60 dystrybucji 100 partycji 1 milion wierszy ).

Jeśli tabela nie ma 6 miliardów wierszy, masz dwie główne opcje. Zmniejsz liczbę partycji lub rozważ użycie tabeli stert. Warto również eksperymentować, aby sprawdzić, czy można uzyskać lepszą wydajność przy użyciu tabeli sterty z indeksami pomocniczymi, a nie tabelą magazynu kolumn.

Podczas wykonywania zapytania odnoszącego się do tabeli magazynu kolumn kwerendy będą uruchamiane szybciej, jeśli wybrane zostaną tylko niezbędne kolumny. Więcej informacji na temat indeksów tabel i magazynu kolumn można znaleźć w poniższych artykułach:

Użycie większej klasy zasobów w celu poprawy wydajność przesyłania zapytań

Pule SQL używają grup zasobów jako sposobu przydzielania pamięci do zapytań. Początkowo wszyscy użytkownicy są przypisani do małej klasy zasobów, która przyznaje 100 MB pamięci na dystrybucję. Zawsze istnieją 60 dystrybucji. Każda dystrybucja ma co najmniej 100 MB. Łączna alokacja pamięci całego systemu wynosi 6000 MB lub nieco poniżej 6 GB.

Stosowanie wybranych kwerend, takich jak duże sprzężenia lub obciążenia odnoszące się do klastrowanych tabel magazynu kolumn, pozwolą korzystać z większych alokacji pamięci. Niektóre zapytania, takie jak czyste skanowania, nie będą widzieć żadnych korzyści. Wykorzystanie większych klas zasobów ma wpływ na współbieżność. Dlatego przed przeniesieniem wszystkich użytkowników do dużej klasy zasobów należy pamiętać o tych faktach.

Aby uzyskać dodatkowe informacje na temat klas zasobów, zapoznaj się z artykułem Klasy zasobów na potrzeby zarządzania obciążeniami .

Użyj mniejszej klasy zasobów, aby zwiększyć współbieżność

Jeśli zauważysz duże opóźnienie w zapytaniach użytkowników, użytkownicy mogą działać w większych klasach zasobów. Ten scenariusz promuje użycie miejsc współbieżności, co może powodować kolejkowanie innych zapytań. Aby określić, czy zapytania użytkowników są kolejkowane, uruchom polecenie SELECT * FROM sys.dm_pdw_waits , aby sprawdzić, czy są zwracane jakieś wiersze.

Klasy zasobów do zarządzania obciążeniami i artykuły sys.dm_pdw_waits zawierają więcej informacji.

Korzystanie z widoków DMV do monitorowania i optymalizowania zapytań

Dedykowane pule SQL mają kilka widoków DMV, których można użyć do monitorowania wykonywania zapytań. Poniższy artykuł monitorowania zawiera szczegółowe instrukcje dotyczące wyświetlania szczegółów wykonywania zapytania. Aby szybko wyszukać zapytania w tych widokach DMV, z zapytaniami można użyć opcji LABEL. Aby uzyskać dodatkowe szczegółowe informacje, zobacz artykuły zawarte na poniższej liście:

Następne kroki

Zobacz również artykuł Dotyczący rozwiązywania problemów, aby zapoznać się z typowymi problemami i rozwiązaniami.

Jeśli potrzebujesz informacji, które nie zostały podane w tym artykule, wyszukaj stronę pytań i odpowiedzi firmy Microsoft dotyczącą usługi Azure Synapse , aby zadawać pytania innym użytkownikom i grupie produktów usługi Azure Synapse Analytics.

Firma Microsoft aktywnie monitoruje to forum, aby mieć pewność, że użytkownicy uzyskują odpowiedzi od innych użytkowników lub pracowników firmy Microsoft. Jeśli wolisz zadawać pytania w witrynie Stack Overflow, mamy również forum usługi Azure Synapse Analytics Stack Overflow.