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

Tip

Microsoft Fabric Data Warehouse to magazyn relacyjny w skali przedsiębiorstwa na podstawie bazy danych data lake z architekturą gotową do użycia w przyszłości, wbudowaną sztuczną inteligencją i nowymi funkcjami. Jeśli dopiero zaczynasz korzystać z magazynowania danych, zacznij od Fabric Data Warehouse. Istniejące obciążenia dedykowanej puli SQL mogą zostać zaktualizowane do Fabric, aby uzyskać dostęp do nowych możliwości w zakresie nauki o danych, analizy w czasie rzeczywistym i raportowania.

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ż koszty 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

Dedykowaną pulę 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ęstszego aktualizowania. Można na przykład aktualizować kolumny danych, w których codziennie mogą być dodawane nowe wartości. Skoncentruj się na posiadaniu statystyk dotyczących kolumn zaangażowanych w połączenia, kolumn używanych w klauzuli WHERE i kolumn znalezionych w 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 za pomocą instrukcji INSERT, takiej jak INSERT INTO MyLookup VALUES (1, 'Type 1'), może być najlepszym podejściem w zależności od Twoich 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 PolyBase można uruchamiać 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 wspierających je. W związku z tym dedykowane pule SQL nie mogą odciążać tej pracy i muszą odczytać cały plik, ładując go do tempdb, aby móc odczytać dane.

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

Hasz dystrybuuje duże tabele

Domyślnym sposobem dystrybucji tabel jest Round Robin. 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 Round Robin mogą być wystarczająco wydajne przy niektórych obciążeniach. Jednak w większości przypadków kolumna rozdzielcza zapewnia lepszą wydajność.

Najczęstszym przykładem tabeli rozproszonej przez kolumnę, która jest bardziej wydajna niż tabela typu round-robin, jest połączenie 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. Mniejsza liczba kroków oznacza szybsze wykonywanie zapytań. Mniejsza konieczność przenoszenia danych wpływa także na skrócenie czasu działania kwerend.

Tip

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ą więcej szczegółów na temat poprawy wydajności za pośrednictwem wybierania kolumny dystrybucji. Ponadto znajdziesz informacje o sposobie definiowania tabeli rozproszonej w klauzuli WITH instrukcji CREATE TABLE:

Nie należy nadmiernie partycjonować

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 dzielą dane na 60 fragmentów 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 Twojego 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 Partycyjonowanie 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. Na przykład, jeśli masz operację INSERT, którą spodziewasz się, że zajmie 1 godzinę, możesz podzielić INSERT na cztery części. Każde uruchomienie zostanie następnie skrócone do 15 minut.

Tip

Użyj specjalnych przypadków minimalnego logowania, takich jak CTAS, TRUNCATE, DROP TABLE lub INSERT w celu wypełnienia pustych tabel, aby zmniejszyć ryzyko wycofania.

Innym sposobem wyeliminowania wycofywania jest użycie operacji tylko metadanych, takich jak przełączanie partycji na potrzeby 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 zamienić partycję z danymi na pustą partycję z innej tabeli (zobacz przykłady ALTER TABLE).

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, jest znacznie bezpieczniejszy do uruchomienia, ponieważ ma minimalne zapisy transakcji i można go szybko anulować 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 PolyBase do dalszego przetwarzania.

Użycie możliwie najmniejszego rozmiaru kolumny

Podczas definiowania definicji DDL użyj najmniejszego typu danych, który będzie obsługiwał Twoje dane, aby poprawić wydajność 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ć definiowania wszystkich kolumn znakowych z dużą domyślną długością. Ponadto zdefiniuj kolumny jako VARCHAR, gdy jest to wszystko, co jest potrzebne, zamiast używać NVARCHAR.

Zobacz artykuły Omówienie tabel, Typy danych tabel i CREATE TABLE, aby uzyskać bardziej szczegółowy przegląd podstawowych pojęć związanych z powyższymi informacjami.

Użyj tymczasowych tabel stertowych dla danych przejściowych

W przypadku tymczasowego umieszczania danych w dedykowanych pulach SQL, tabele stertowe 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 danych do tabeli w pamięci trwałej. 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 stert są definiowane w klauzuli WITH tabeli CREATE. W przypadku użycia tabeli tymczasowej należy pamiętać o utworzeniu dla niej statystyk.

Aby uzyskać więcej informacji, zobacz artykuły Tablice 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. Dobra jakość segmentu jest ważna, aby uzyskać najlepszą wydajność zapytań dotyczących tabel kolumnowych. Gdy wiersze są zapisywane w tabelach columnstore pod wpływem ograniczeń pamięci, jakość segmentów columnstore może ucierpieć.

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

Ponieważ segmenty magazynujące kolumny wysokiej jakości są ważne, dobrym pomysłem jest użycie identyfikatorów użytkowników należących do średniej lub dużej klasy zasobów w celu ładowania danych. Użycie niższych jednostek magazynu danych oznacza, że chcesz przypisać większą klasę zasobów dla użytkownika ładującego.

Tabele kolumnowe zwykle nie przenoszą danych do skompresowanego segmentu kolumnowego, dopóki nie będzie więcej niż 1 milion wierszy w każdej tabeli. Każda dedykowana tabela puli SQL jest dystrybuowana do 60 różnych dystrybucji. W związku z tym tabele kolumnowe nie przyniosą korzyści zapytaniu, chyba że tabela zawiera ponad 60 milionów wierszy.

Tip

W przypadku tabel z mniej niż 60 milionami wierszy kolumnowy indeks magazynujący 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 ze 100 partycjami musi mieć co najmniej 6 miliardów wierszy, aby korzystać ze zgrupowanego magazynu kolumnowego (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 typu heap. Warto również eksperymentować, aby sprawdzić, czy można uzyskać lepszą wydajność przy użyciu tabeli typu heap z indeksami pomocniczymi zamiast tabeli 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 indeksów kolumnowych można znaleźć w poniższych artykułach:

Użyj większej klasy zasobów, aby poprawić wydajność wykonywania 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 jest 60 dystrybucji. Każdej dystrybucji przydzielane jest co najmniej 100 MB. Łączna alokacja pamięci całego systemu wynosi 6000 MB lub nieco poniżej 6 GB.

Niektóre zapytania, takie jak duże sprzężenia lub obciążenia do tabel klastrowanego magazynu kolumn, skorzystają z większych alokacji pamięci. Niektóre zapytania, takie jak czyste przeszukiwania, nie przyniosą ż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ć więcej informacji na temat klas zasobów, zobacz artykuł 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 zachęca do zużywania gniazd 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.

Artykuły Klasy zasobów do zarządzania obciążeniami oraz sys.dm_pdw_waits dostarczą więcej informacji.

Użyj widoków DMV do monitorowania i optymalizowania zapytań

Dedykowane pule SQL posiadają kilka dynamicznych widoków zarządzania, 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 znaleźć zapytania w tych widokach dynamicznego zarządzania (DMV), użycie opcji LABEL w Twoich zapytaniach może pomóc. Aby uzyskać dodatkowe szczegółowe informacje, zobacz artykuły zawarte na poniższej liście:

Powiązana zawartość

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.

  • Last updated on