Eksplorowanie kontroli konserwacji bazy danych
Optymalizator zapytań wykorzystuje informacje statystyczne z indeksów, aby podjąć próbę utworzenia najbardziej optymalnego planu wykonywania.
W ramach zadań konserwacji usługi Azure SQL, takich jak kopie zapasowe i kontrole integralności, są obsługiwane, i chociaż możesz uciec od automatycznych aktualizacji, zachowując aktualność statystyk, czasami nie wystarczy.
Posiadanie indeksów i statystyk w dobrej kondycji zapewni, że każdy plan będzie działać w optymalnej wydajności. Konserwacja indeksu powinna być wykonywana regularnie, gdy dane w bazach danych zmieniają się wraz z upływem czasu. Strategię konserwacji indeksu można zmienić na podstawie częstotliwości modyfikacji danych.
Ponowne kompilowanie i reorganizacja
Fragmentacja indeksu występuje, gdy kolejność logiczna na stronach indeksu nie jest zgodna z kolejnością fizyczną. Strony mogą nie być uporządkowane podczas rutynowych instrukcji modyfikacji danych, takich jak UPDATE, DELETEi INSERT. Fragmentacja może powodować problemy z wydajnością ze względu na dodatkowe we/wy wymagane do zlokalizowania danych, do których odwołuje się wskaźnik na stronach indeksu.
W miarę wstawiania, aktualizowania i usuwania danych z indeksów kolejność logiczna w indeksie nie będzie już zgodna z kolejnością fizyczną wewnątrz stron i między stronami tworzącymi indeksy. Ponadto w miarę upływu czasu modyfikacje danych mogą spowodować rozproszenie lub fragmentacja danych w bazie danych. Fragmentacja może obniżyć wydajność zapytań, gdy aparat bazy danych musi odczytywać dodatkowe strony w celu zlokalizowania potrzebnych danych.
Reorganizacja indeksu to operacja online, która defragmentuje poziom liścia indeksu (zarówno klastrowany, jak i nieklastrowany). Ten proces defragmentacji fizycznie zmieni kolejność stron na poziomie liścia, aby dopasować kolejność logiczną węzłów od lewej do prawej. W trakcie tego procesu strony indeksu są również kompaktowane na podstawie skonfigurowanej wartości fillfactor.
Ponowna kompilacja może być w trybie online lub offline w zależności od wykonanego polecenia lub używanej wersji programu SQL Server. Proces ponownego kompilowania w trybie offline zostanie porzuceny i ponownie utworzy sam indeks. Jeśli możesz to zrobić w trybie online, nowy indeks zostanie utworzony równolegle z istniejącym indeksem. Po utworzeniu nowego indeksu istniejący zostanie porzucony, a następnie nowa nazwa zostanie zmieniona tak, aby była zgodna ze starą nazwą indeksu. Należy pamiętać, że wersja online będzie wymagać więcej miejsca, ponieważ nowy indeks jest wbudowany równolegle z istniejącym indeksem.
Typowe wskazówki dotyczące konserwacji indeksu to:
> 5%, ale < 30% — zreorganizowanie indeksu
>30% — ponowne kompilowanie indeksu
Użyj tych numerów jako ogólnych zaleceń. W zależności od obciążenia i danych może być konieczne bardziej asertywne lub w niektórych przypadkach może być możliwe odroczenie konserwacji indeksu dla baz danych, które najczęściej wykonują zapytania, które szukają określonych stron.
Platformy SQL Server i Azure SQL oferują dynamiczne widoki zarządzania, które umożliwiają wykrywanie fragmentacji w obiektach. Najczęściej używane dynamiczne widoki zarządzania w tym celu to sys.dm_db_index_physical_stats indeksy b-tree i sys.dm_db_column_store_row_group_physical_stats indeksy magazynu kolumn.
Jedną z innych rzeczy jest to, że ponowne kompilowanie indeksu powoduje zaktualizowanie statystyk indeksu, co może jeszcze bardziej pomóc w wydajności. Reorganizacja indeksu nie aktualizuje statystyk.
Firma Microsoft wprowadziła wznawiane operacje ponownego kompilowania indeksów za pomocą programu SQL Server 2017. Opcja Ponownego kompilowania operacji indeksowania zapewnia większą elastyczność w kontrolowaniu czasu, przez jaki operacja ponownego kompilowania może nakładać się na dane wystąpienie. W programie SQL Server 2019 wprowadzono możliwość kontrolowania skojarzonego maksymalnego stopnia równoległości, zapewniając bardziej szczegółową kontrolę administratorom bazy danych.
Statystyki
Podczas dostrajania wydajności w usłudze Azure SQL zrozumienie znaczenia statystyk ma kluczowe znaczenie.
Statystyki są przechowywane w bazie danych użytkownika jako obiekty binarne (obiekty blob). Te obiekty blob zawierają informacje statystyczne dotyczące rozkładu wartości danych w co najmniej jednej kolumnie tabeli lub widoku indeksowanego.
Statystyki zawierają informacje na temat rozkładu wartości danych w kolumnie. Optymalizator zapytań używa statystyk kolumn i indeksów w celu określenia kardynalności, czyli liczby wierszy zwracanych przez zapytanie.
Oszacowania kardynalności są następnie używane przez optymalizator zapytań do generowania planu wykonania. Szacunki kardynalności pomagają również optymalizatorowi określić typ operacji (na przykład wyszukiwanie indeksu lub skanowanie), które mają być używane do pobierania żądanych danych.
Aby wyświetlić listę statystyk zdefiniowanych przez użytkownika z datą ostatniej aktualizacji, uruchom poniższe zapytanie:
SELECT sp.stats_id,
name,
last_updated,
rows,
rows_sampled
FROM sys.stats
CROSS APPLY sys.dm_db_stats_properties(object_id, stats_id) AS sp
WHERE user_created = 1
Tworzenie statystyk
Jeśli masz AUTO_CREATE_STATISTICS opcję ON, optymalizator zapytań domyślnie tworzy statystyki dla indeksowanej kolumny. Optymalizator zapytań tworzy również statystyki dla pojedynczych kolumn w predykatach zapytań.
Te metody zapewniają wysokiej jakości plany zapytań dla większości zapytań. Czasami może być konieczne utworzenie większej liczby statystyk przy użyciu CREATE STATISTICS instrukcji w celu ulepszenia określonych planów zapytań.
Zaleca się, aby opcja AUTO_CREATE_STATISTICS była włączona, ponieważ umożliwi optymalizatorowi zapytań automatyczne tworzenie statystyk dla kolumn predykatów zapytań.
Za każdym razem, gdy wystąpią następujące sytuacje, rozważ utworzenie statystyk:
- Doradca dostrajania aparatu bazy danych sugeruje tworzenie statystyk
- Predykat zapytania zawiera wiele kolumn, które nie znajdują się jeszcze w tym samym indeksie
- Zapytanie wybiera z podzestawu danych
- Zapytanie ma brakujące statystyki
Automatyzacja zadań konserwacji
Usługa Azure SQL udostępnia natywne narzędzia do wykonywania zadań konserwacji bazy danych na potrzeby automatyzacji. Różne narzędzia są dostępne w zależności od platformy, na której działa baza danych.
Program SQL Server na maszynie wirtualnej platformy Azure
Masz dostęp do usług planowania, takich jak agent SQL lub harmonogram zadań systemu Windows. Te narzędzia automatyzacji mogą pomóc w minimalnej liczbie fragmentacji indeksów. W przypadku większych baz danych należy znaleźć równowagę między odbudową i reorganizacją indeksów, aby zapewnić optymalną wydajność. Elastyczność zapewniana przez agenta SQL lub harmonogram zadań umożliwia uruchamianie zadań niestandardowych.
Azure SQL Database
Ze względu na charakter usługi Azure SQL Database nie masz dostępu do agenta programu SQL Server ani harmonogramu zadań systemu Windows. Bez tych usług konserwacja indeksu musi zostać utworzona przy użyciu innych metod. Istnieją trzy sposoby zarządzania operacjami konserwacji dla usługi SQL Database:
Elementy runbook usługi Azure Automation
Zadanie agenta SQL z programu SQL Server na maszynie wirtualnej platformy Azure (zdalne wywołanie)
Zadania elastyczne usługi Azure SQL
Wystąpienie zarządzane Azure SQL
Podobnie jak w przypadku programu SQL Server na maszynie wirtualnej platformy Azure, można zaplanować zadania w wystąpieniu zarządzanym SQL za pomocą agenta programu SQL Server. Użycie programu SQL Server Agent zapewnia elastyczność wykonywania kodu zaprojektowanego w celu zmniejszenia fragmentacji w indeksach w bazie danych.