Sterty (tabele bez indeksów klastrowanych)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBaza danych SQL w usłudze Microsoft Fabric

Sterta to tabela bez indeksu klastrowanego. Możesz tworzyć jeden lub więcej indeksów nieklastrowanych na tabelach przechowywanych jako kopica. Sterta przechowuje dane bez określania kolejności. Zazwyczaj kopca początkowo przechowuje dane w kolejności, w jakiej wstawiasz wiersze. Jednakże silnik bazy danych może przenosić dane na stercie w celu optymalnego przechowywania wierszy. W wynikach zapytań nie da się przewidzieć kolejności danych. Aby zagwarantować kolejność wierszy zwracanych ze sterta, użyj klauzuli ORDER BY . Aby określić stały porządek logiczny przechowywania wierszy, stwórz klasterizowany indeks w tabeli, tak aby tabela nie była kopcą.

Kiedy należy używać sterta

Sterta jest idealna do tabel, które często obcinasz i przeładowujesz. Database Engine optymalizuje przestrzeń w stercie, wypełniając najwcześniejszą dostępną przestrzeń.

Rozważ następujące kwestie:

• Znalezienie wolnego miejsca w stercie może być kosztowne, zwłaszcza jeśli dochodzi do wielu usuśnień lub aktualizacji.
• Indeksy klastrowane zapewniają stabilną wydajność dla tabel, których rzadko obcinasz.

W tabelach, które regularnie obrabiasz lub odtwarzasz, takich jak tabele tymczasowe lub staging tabele, użycie sterty jest często bardziej efektywne.

Wybór między użyciem sterta a indeksem klastrowanym może znacząco wpłynąć na wydajność i wydajność bazy danych.

Gdy zapisujesz tabelę jako stertę, identyfikujesz poszczególne wiersze za pomocą 8-bajtowego identyfikatora wiersza (RID), składającego się z numeru pliku, numeru strony danych oraz slotu na stronie (FileID:PageID:SlotID). ID wiersza jest niewielką i wydajną strukturą.

Stosy używaj jako tabel etapowych dla dużych, nieuporządkowanych operacji wstawiania. Ponieważ kopce nie wymuszają ścisłej kolejności wstawiania, operacja wstawiania jest zazwyczaj szybsza niż równoważne wstawianie do klastrowanego indeksu. Jeśli czytasz i przetwarzasz dane z kopca do ostatecznego miejsca, rozważ stworzenie wąskiego, nieklastrowanego indeksu, który obejmuje predykat wyszukiwania, którego używa zapytanie.

Można też używać kopców, gdy zawsze uzyskujesz dostęp do danych przez indeksy nieklastrowane, a RID jest mniejszy niż klucz indeksowy klastrowany.

Jeśli tabela jest kopcem i nie ma żadnych indeksów nieklastrowanych, musisz przeczytać całą tabelę (skanowanie tabeli), aby znaleźć dowolny wiersz. SQL Server nie może bezpośrednio szukać RID na stercie. To zachowanie może być akceptowalne, gdy tabela jest mała.

Kiedy nie należy używać stert

Nie używaj sterty, gdy dane są często zwracane w uporządkowanej kolejności. Klasterizowany indeks w kolumnie sortowania może uniknąć operacji sortowania.

Nie używaj sterty, gdy dane są często grupowane razem. Dane muszą być posortowane przed grupowaniem, a klasterizowany indeks w kolumnie sortowania może uniknąć operacji sortowania.

Nie używaj sterty, gdy zakresy danych są często zapytywane z tabeli. Indeks klastrowany w kolumnie zakresu pozwala uniknąć sortowania całego stosu.

Nie używaj kopca, gdy nie ma indeksów nieklastrowanych, a tabela jest duża. Jedynym zastosowaniem tego projektu jest zwracanie całej zawartości tabeli bez określonej kolejności. W stercie silnik bazy danych odczytuje wszystkie wiersze, aby znaleźć dowolny wiersz.

Nie używaj heap, jeśli często aktualizujesz dane. Jeśli zaktualizujesz rekord, a ta aktualizacja zajmuje więcej miejsca na stronach danych niż obecnie, rekord przenosi się na stronę danych, która ma wystarczająco dużo wolnego miejsca. Ten ruch tworzy przekierowany rekord wskazujący na nową lokalizację danych. Wskaźnik przekierowania jest zapisany na stronie, która wcześniej przechowywała dane, aby wskazać nową fizyczną lokalizację. Ten ruch wprowadza fragmentację w stercie. Gdy silnik bazy danych skanuje stertę, podąża za tymi wskaźnikami. Ta akcja ogranicza wydajność odczytu z przodu i może wiązać się z dodatkowym wejściem/wyjściem, co obniża wydajność skanowania.

Zarządzanie stertami

Aby utworzyć stertę, utwórz tabelę bez indeksu klastrowanego. Jeśli tabela ma już indeks klastrowany, usuń indeks klastrowany, aby przywrócić tabelę do sterty.

Aby usunąć stertę, utwórz indeks klastrowany na stercie.

Aby odbudować stertę i odzyskać zmarnowane miejsce:

• Utwórz indeks klastrowany na stercie, a następnie usuń ten indeks klastrowany.
• Użyj polecenia ALTER TABLE ... REBUILD, aby odbudować stertę.

Identyfikowanie sterty

Poniższe zapytanie zwraca listę stert z bieżącej bazy danych. Lista zawiera:

• Nazwy tabel
• Nazwy schematu
• Liczba wierszy
• Rozmiar tabeli w KB
• Rozmiar indeksu w kb
• Nieużywane miejsce
• Kolumna identyfikująca stertę

SELECT t.name AS 'Your TableName',
       s.name AS 'Your SchemaName',
       p.rows AS 'Number of Rows in Your Table',
       SUM(a.total_pages) * 8 AS 'Total Space of Your Table (KB)',
       SUM(a.used_pages) * 8 AS 'Used Space of Your Table (KB)',
       (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS 'Unused Space of Your Table (KB)',
       CASE
           WHEN i.index_id = 0 THEN 'Yes'
           ELSE 'No'
       END AS 'Is Your Table a Heap?'
FROM sys.tables AS t
     INNER JOIN sys.indexes AS i
         ON t.object_id = i.object_id
     INNER JOIN sys.partitions AS p
         ON i.object_id = p.object_id
        AND i.index_id = p.index_id
     INNER JOIN sys.allocation_units AS a
         ON p.partition_id = a.container_id
     LEFT OUTER JOIN sys.schemas AS s
         ON t.schema_id = s.schema_id
WHERE i.index_id <= 1 -- 0 for Heap, 1 for Clustered Index
GROUP BY t.name, s.name, i.index_id, p.rows
ORDER BY 'Your TableName';

Struktury kopców

Sterta to tabela bez indeksu klastrowanego. Sterty mają jeden wiersz w sys.partitions, z index_id = 0 dla każdej partycji używanej przez stertę. Domyślnie sterta ma jedną partycję. Gdy sterta ma wiele partycji, każda partycja ma strukturę sterty zawierającą dane dla tej konkretnej partycji. Na przykład, jeśli sterta ma cztery partycje, istnieją cztery struktury sterty; jedna w każdej partycji.

W zależności od typów danych w stercie, każda struktura kopca ma jedną lub więcej jednostek alokacji do przechowywania i zarządzania danymi dla konkretnej partycji. Co najmniej każda kopca ma jedną IN_ROW_DATA jednostkę alokacji na każdą partycję. Struktura kopca ma również jedną LOB_DATA jednostkę alokacji na każdą partycję, jeśli zawiera kolumny dużych obiektów (LOB). Posiada także jedną ROW_OVERFLOW_DATA jednostkę alokacji na każdą partycję, jeśli zawiera kolumny o zmiennej długości przekraczające limit 8 060 bajtów w wierszu.

Kolumna first_iam_page w widoku sys.system_internals_allocation_units systemu wskazuje na pierwszą stronę Index Allocation Map (IAM) w łańcuchu stron IAM, które zarządzają przestrzenią przydzieloną do sterty w konkretnej partycji. Program SQL Server używa stron "IAM" do przechodzenia przez stertę. Strony danych i wiersze w nich nie są ułożone w żadnej konkretnej kolejności i nie są powiązane. Jedynym logicznym połączeniem między stronami danych są informacje zarejestrowane na stronach IAM.

Możesz przeprowadzić skanowanie tabel lub odczyty szeregowe sterty, skanując strony IAM, aby znaleźć zakresy, które zawierają strony dla sterpy. Ponieważ IAM reprezentuje zakresy w tej samej kolejności, w jakiej występują w plikach danych, ta struktura oznacza, że skanowanie kopicy szeregowej przebiega kolejno przez każdy plik. Użycie stron IAM do ustawiania sekwencji skanowania oznacza również, że wiersze z kopca zazwyczaj nie są zwracane w kolejności, w jakiej zostały wstawione.

Na poniższej ilustracji pokazano, jak Silnik bazy danych SQL Server używa stron IAM do pobierania wierszy danych z segmentu danych pojedynczej partycji.

Treści powiązane

• CREATE INDEX (Transact-SQL)
• DROP INDEX (Transact-SQL)
• indeksy klastrowane i nieklastrowane