Ulepszenia w podzielonym na partycje tabel i indeksów przetwarzania kwerendy

SQL Server 2008 zwiększa wydajność przetwarzania kwerend na tabelach podzielonym na partycje dla wielu planów równolegle, zmienia sposób plany szeregowe i równoległe są reprezentowane i zwiększa partycjonowanie informacji w obu kompilacji -czas i uruchom -czas wykonanie planów.W tym temacie opisano te udoskonalenia, zawiera wskazówki dotyczące interpretacji planów wykonanie kwerendy tabel podzielonym na partycje i indeksy i zawiera najważniejsze wskazówki dotyczące poprawy wydajności kwerendy obiektów podzielonym na partycje.

Ostrzeżenie

Tabele podzielonym na partycje i indeksy są obsługiwane tylko w SQL Server wersje Enterprise, Developer i oceny.

Poszukiwanie nowych partycji Aware operacji

In SQL Server 2008, the internal representation of a partitioned table is changed so that the table appears to the query processor to be a multicolumn index with PartitionID as the leading column.PartitionID is a hidden computed column used internally to represent the ID of the partition containing a specific row.Załóżmy na przykład, tabela T, określone jako T(a, b, c), jest podzielony na partycje kolumna a, i ma indeks klastrowany kolumna b.W SQL Server 2008, ta tabela partycjonowana jest traktowane wewnętrznie jako tabelę nonpartitioned ze schematem T(PartitionID, a, b, c) i indeks klastrowany na klucz złożony (PartitionID, b).Dzięki temu optymalizator kwerendy do wykonywania operacji na podstawie szukania PartitionID na dowolnej tabela podzielonym na partycje lub indeks.

Partycja eliminacji wykonywane w tym poszukiwania pracy.

Ponadto optymalizator kwerendy jest rozszerzany tak, aby operacja wyszukiwania lub skanowania, z jednym warunkiem może odbywać się na PartitionID (jako kolumna logicznej wiodących) i ewentualnie innych indeksu kolumna klucz, a następnie seek drugiego poziom, inny warunek, można wykonać na jedną lub więcej dodatkowych kolumn dla każdej wartości distinct, spełniającą kwalifikacji dla wyszukiwania pierwszego poziom operacji.Oznacza to, że ta operacja, nazywane pominąć skanowanie, umożliwia optymalizator kwerendy do wykonywania wyszukiwania lub skanowania operacji na podstawie jednego warunku, aby określić dostęp do partycji i w ramach tego operator zwrócić wiersze z te partycje, które spełniają warunek różnych operacji wyszukiwania indeksu drugiego poziom.Na przykład rozważmy następującą kwerendę.

SELECT * FROM T WHERE a < 10 and b = 2;

W tym przykładzie założono tabela T, określone jako T(a, b, c), jest podzielony na partycje kolumna a, i ma indeks klastrowany kolumna b.Granice partycji tabela T są definiowane za pomocą następujących funkcja partycji:

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (3, 7, 10);

Aby rozwiązać kwerendę, procesor kwerend dokonuje pierwszego poziom operacji, aby znaleźć każdej partycji zawierającej wiersze spełniające warunek wyszukiwania T.a < 10.Identyfikuje dostęp do partycji.W ramach każdej partycji zidentyfikowane procesor wykonuje następnie drugiego poziom dążyć do indeks klastrowany kolumna b Aby znaleźć wiersze spełniające warunek T.b = 2 i T.a < 10.

Następujący rysunek jest logiczną reprezentacją Pomiń operację skanowania.Pokazuje tabela T z danymi w kolumnach a i b.Partycje są ponumerowane od 1 do 4 z granice partycji przez pionowe linie przerywane.Operacji wyszukiwania pierwszego poziom na partycje (nie pokazano na ilustracji) stwierdził, że partycje 1, 2 i 3 spełnia warunek wyszukiwania implikowane przez partycjonowanie zdefiniowanych dla tabela i orzeczenie kolumna a.That is, T.a < 10.Ścieżka pokonywany przez drugiego poziom seek część Pomiń operację skanowania jest zilustrowane linii krzywej.W istocie, Pomiń operację skanowania dąży do każdego z tych partycji dla wierszy spełniających warunek b = 2.Całkowity koszt Pomiń operację skanowania jest taka sama, jak ubiega się z trzech oddzielnych indeksu.

Wyświetlanie informacji o partycji wykonanie kwerendy plany

Wykonanie planów kwerend na podzielonym na partycje tabel i indeksów można zbadać za pomocą Transact-SQL zestaw instrukcji zestawu SHOWPLAN_XML lub USTAWIĆ XML statystyki lub przy użyciu wyjścia graficznego wykonanie planu w SQL Server Management Studio.Na przykład, można wyświetlić kompilacji -czas plan wykonania klikając Wyświetlić Plan wykonania szacowany na pasku narzędzi Edytora kwerend i uruchom -czas plan, klikając Obejmują rzeczywiste wykonanie planu.

Narzędzia te można ustalić następujące informacje:

• Operacje takie jak skanowanie, stara wstawia, aktualizacje, scala i usuwa się, że dostępu na partycje, tabel lub indeksy.

• Partycje dostęp przez kwerendę.Na przykład, całkowita liczba partycji dostęp i zakresy ciągłe partycje, które są dostępne są dostępne w Uruchom -czas wykonanie planów.

• Kiedy Pomiń operację skanowania jest używany w operacji wyszukiwania lub skanowania do pobierania danych z jedną lub więcej partycji.

Aby uzyskać więcej informacji o wyświetlaniu wykonanie planów, zobacz Wykonanie planu tematów opisujących.

Ulepszenia informacji o partycji

SQL Server 2008 informacje rozszerzone partycjonowanie dla obu kompilacji -czas i uruchom -czas wykonanie planów.Wykonanie planów teraz zawierają następujące informacje:

• Opcjonalny Partitioned atrybut, który wskazuje, że operator, takich jak seek, skanowania, wstawiania, aktualizacji, seryjna lub usunięcia, jest wykonywana na tabela partycjonowana.

• Nowy SeekPredicateNew element z SeekKeys podelement, który zawiera PartitionID jako wiodącego indeks klucz warunki kolumna i filtru, które określ zakres stara się na PartitionID.Obecność dwóch SeekKeys podelementy wskazuje, że Pomiń operację skanowania na PartitionID jest używana.

• Informacje podsumowujące stanowi całkowita liczba partycji dostęp.Ta informacja jest dostępna tylko w run -czas planów.

Wykazanie wyświetlania informacji wyjściowych graficznego wykonanie planu i dane wyjściowe XML Showplan, rozważmy następującą kwerendę na tabela partycjonowana fact_sales.Ta kwerenda aktualizuje dane w dwie partycje.

UPDATE fact_sales

SET quantity = quantity * 2

WHERE date_id BETWEEN 20080802 AND 20080902;

Na poniższej ilustracji przedstawiono właściwości Indeks klastrowany poszukiwania operator w kompilacji -czas plan wykonania dla tej kwerendy.Aby wyświetlić definicję fact_sales tabela i definicja partycji, zobacz "Przykład" w tym temacie.

Atrybut podzielonym na partycje

Gdy operator, takich jak Indeksu wyszukiwania jest wykonywany na tabela partycjonowana lub indeksu, Partitioned atrybut pojawia się w kompilacji -czas i uruchom -czas plan i jest ustawiona na True (1).Atrybut nie są wyświetlane, gdy jest on zestaw do False (0).

Partitioned atrybut mogą pojawiać się w następujących operatorów fizyczne i logiczne:

• Skanowanie tabeli

• Skanowanie indeksu

• Indeks wyszukiwania

• Wstaw

• Aktualizacja

• Usuń

• Scalanie

Jak pokazano na poprzedniej ilustracji, ten atrybut jest wyświetlany w właściwości operator, w którym jest zdefiniowana.W danych wyjściowych XML Showplan ten atrybut jest wyświetlany jako Partitioned = "1" w RelOp węzła operator, w którym jest zdefiniowana.

Poszukiwanie nowych predykatu

W danych wyjściowych XML Showplan SeekPredicateNew element jest wyświetlany w operator w którym jest zdefiniowana.Może zawierać maksymalnie dwa wystąpienia SeekKeys elementu podrzędnego.Pierwszy SeekKeys element określa operacji poziom identyfikator partycji logicznej indeksu wyszukiwania pierwszego poziom.Oznacza to, że ten seek określa partycje, które muszą być dostępne warunki kwerendy.Drugi SeekKeys element określa część seek drugiego poziom Pomiń operację skanowania, występujący w ramach każdej partycji, w wyniku pierwszego poziom.

Podsumowanie informacji o partycji

W programie wykonywania -czas wykonanie planów zawiera podsumowanie informacji o partycji liczba partycji, dostęp do i tożsamości partycje rzeczywistego dostępu do.Informacja ta umożliwia Zweryfikuj, że prawidłowe partycje są dostępne w kwerendzie i wyeliminowaniu inne partycje z rozpatrzenia.

Podano następujące informacje: Rzeczywista liczba partycji, i dostęp do partycji.

Rzeczywista liczba partycji jest całkowitą liczbą partycji dostęp przez kwerendę.

Partycje Accessed, w danych wyjściowych XML Showplan, jest partycja informacje podsumowujące, które pojawia się w nowej RuntimePartitionSummary element RelOp węzła operator, w którym jest zdefiniowana.Poniższy przykład przedstawia zawartość RuntimePartitionSummary element, wskazując, że dostępne są dwie partycje całkowita (partycje 2 i 3).

<RunTimePartitionSummary>

    <PartitionsAccessed PartitionCount="2" >

        <PartitionRange Start="2" End="3" />

    </PartitionsAccessed>

</RunTimePartitionSummary>

Wyświetlanie informacji o partycji przy użyciu innych metod Showplan

Metody Showplan, SHOWPLAN_ALL, SHOWPLAN_TEXT i statystyki profilu nie Raportuj opisane w tym temacie, z wyjątkiem następujących informacji o partycji.Jako część SEEK predykatu, dostęp do partycji są identyfikowane przez predykat zakres na kolumna obliczana reprezentuje identyfikator partycji.W poniższym przykładzie SEEK predykatu dla Indeks klastrowany poszukiwania operator.Partycje 2 i 3 są dostępne i seek operator filtry wiersze spełniające warunek date_id BETWEEN 20080802 AND 20080902.

|--Clustered Index Seek(OBJECT:([db_sales_test].[dbo].[fact_sales].[ci]),

        SEEK:([PtnId1000] >= (2) AND [PtnId1000] <= (3)

                AND [db_sales_test].[dbo].[fact_sales].[date_id] >= (20080802)

                AND [db_sales_test].[dbo].[fact_sales].[date_id] <= (20080902))

                ORDERED FORWARD)

Interpretowanie wykonanie planów dla stert podzielonym na partycje

W SQL Server 2008, sterty podzielonym na partycje jest traktowane jako logiczne indeksu na partycji identyfikatora.Eliminacja partycji na stercie podzielonym na partycje jest reprezentowany w plan wykonania jako Skanowanie tabeli operator z predykat SEEK na identyfikator partycji.Showplan informacje można znaleźć w poniższym przykładzie:

|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)

Interpretowanie wykonanie planów dla sprzężeń współistniejącego wdrożenia wersji

Kolokacja łączyć może wystąpić, jeśli dwie tabele są odseparowane, przy zastosowaniu tej samej lub równoważnych funkcja oraz partycjonowanie kolumn z obu stron łączyć partycjonowanie są określone w warunek łączyć kwerendy.optymalizator kwerendy można wygenerować planu, gdzie partycje każdej tabela mają równy partycji identyfikatory są sprzężone oddzielnie.Sprzężenia współistniejącego wdrożenia wersji może być szybciej niż sprzężeń collocated, ponieważ mogą one wymagają mniej pamięci i przetwarzania czas.Optymalizator wybiera plan collocated lub współistniejącego wdrożenia wersji planu, szacunkowy koszt.

W współistniejącego wdrożenia wersji planu Zagnieżdżone pętle łączyć odczytuje jedną lub więcej sprzężonej tabela lub indeksu partycji z wewnętrznej stronie.Numery w Scan stała podmioty reprezentujące liczby partycji.

Gdy równoległe plany dla collocated łączyćs są generowane dla tabel podzielonym na partycje lub indeksy, równoległości prostych operator znajduje się między Scan stałej i Zagnieżdżone pętle łączyć operatorów.przypadek wielu wątki na zewnętrznej stronie łączyć każdego odczytu i pracować na inną partycję.

Poniższej ilustracji przedstawiono plan kwerend równoległych współistniejącego wdrożenia wersji łączyć.

Równoległe strategii wykonanie kwerendy dla obiektów podzielonym na partycje

Procesor kwerend używa strategii wykonywanie równoległe kwerend, które wybierać obiekty podzielonym na partycje.Jako część strategii realizacji procesor kwerend określa partycje tabela wymagane do kwerendy i proporcji wątków przydzielić do każdej partycji.W większości przypadków procesor kwerend przydziela równą bądź prawie równą liczbę wątków każdej partycji i wykonuje kwerendę równolegle na partycje.Następujące ustępy wyjaśnić alokacji wątek bardziej szczegółowo.

Jeśli liczba wątków jest mniejsza niż liczba partycji, procesor kwerend przypisuje każdy wątek na inną partycję początkowo pozostawiając jedną lub więcej partycji bez przypisanego wątku.Po zakończeniu wykonywania na partycji wątku procesor kwerend przypisuje go do next partition aż Pojedynczy wątek został przypisany każdej partycji.Jest to jedyny przypadek , w którym procesor kwerend reallocates wątków inne partycje.

Jeśli liczba wątków jest równa liczbie partycji, procesor kwerend przypisuje każdej partycji jeden wątek.Po zakończeniu wątek ponownym alokowaniu nie na inną partycję.

Jeśli liczba wątków jest większa niż liczba partycji, procesor kwerend przydziela równą liczbę wątków do każdej partycji.Jeśli liczba wątków nie jest wielokrotnością liczby partycji, procesor kwerend przydziela jeden wątek dodatkowe niektóre partycje do używania wszystkich dostępnych wątków.Należy zauważyć, że jeśli istnieje tylko jedna partycja, wszystkie wątki zostanie przypisany do tej partycji.Na diagramie istnieją cztery partycje i 14 wątków.Każda partycja ma 3 wątków przypisanych i dwie partycje zostały dodatkowe wątek, łącznie 14 przydziały wątek.Po zakończeniu wątek nie jest przypisane na inną partycję.

Mimo że powyższe przykłady sugerują prosty sposób przydzielić wątków, rzeczywistej strategii jest bardziej złożone i kont innych zmiennych, które występują podczas wykonywania kwerendy.Na przykład, jeśli tabela jest podzielony na partycje i indeks klastrowany kolumna i kwerenda ma klauzula predykatu WHERE A IN (13, 17, 25), procesor kwerend przyzna jeden lub więcej wątków do każdego z tych trzech poszukuje wartości (A=13, A=17, i A=25) zamiast każdej partycji tabeli.Jest tylko wykonać kwerendy w partycjach, zawierających te wartości, a jeśli wszystkie te poszukiwania predykaty zdarzyć się w tej samej tabela partycji, wszystkich wątków przypisanych do tej samej tabela partycji.

Aby inny przykład, załóżmy, że tabela ma cztery partycje kolumna a punktami granicę (10, 20, 30), indeksu kolumna B, a kwerenda ma predykatu klauzula WHERE B IN (50, 100, 150). ponieważ partycji tabeli są oparte na wartości a, wartości b może wystąpić w dowolnej tabeli partycji.W związku z tym procesor kwerend będzie dążyć dla każdego z trzech wartości B (50, 100, 150) w każdej z czterech tabela partycji.Procesor kwerend przypisze wątków proporcjonalnie, dzięki czemu możliwe wykonać każdego skanowanie 12 kwerendy równolegle.

Tabela partycji na podstawie kolumna a	Poszukuje kolumna b w każdej tabela partycji
Tabela partycji 1: A < 10	B = 50, B = 100, B = 150
Tabela partycji 2: A >= 10 I A < 20	B = 50, B = 100, B = 150
Tabela partycji 3: A >= 20 I A < 30	B = 50, B = 100, B = 150
Tabela partycji 4: A >= 30	B = 50, B = 100, B = 150

Najważniejsze wskazówki

Aby poprawić wydajność kwerendy, które dostęp do dużej ilości danych z dużych tabel podzielonym na partycje i indeksów, firma Microsoft zaleca następujące wskazówki:

• Każda partycja rozłożony na wielu dyskach.

• Jeśli to możliwe, używać serwera z głównej pamięci, aby dopasowanie często uzyskuje dostęp do partycji lub wszystkich partycji w pamięci, aby zmniejszyć koszt We/Wy.

• Jeśli dane kwerendy nie będzie dopasowanie w pamięci, Kompresuj tabel i indeksów.Zmniejszy to koszt We/Wy.

• Użycie serwera z szybkich procesorów i tyle cores procesora, jak można sobie, aby wykorzystać możliwości przetwarzania równoległego kwerendy.

• Upewnij się, serwer ma wystarczające przepustowość kontrolera We/Wy.

• Tworzenie indeks klastrowany na każdym dużych tabela partycjonowana korzystać optymalizacje skanowania B-drzewo.

• Postępuj zgodnie z najlepszymi sposobami w Białej Księdze "załadunku luzem dane do tabeli Partitioned," Kiedy luzem ładowanie danych do tabel podzielonym na partycje.

Przykład

Poniższy przykład tworzy testowej bazy danych, zawierający pojedynczą tabela z siedmiu partycjami.Umożliwia wyświetlanie informacji partycjonowanie dla obu kompilacji - narzędzia opisane wcześniej podczas wykonywania kwerendy w tym przykładzieczas i uruchom -czas planów.

Ostrzeżenie

W tym przykładzie wstawia 1 miliona wierszy do tabela.Uruchamianie w tym przykładzie może potrwać kilka minut, w zależności od sprzętu.Przed wykonaniem tego przykładu, sprawdź, czy więcej niż 1,5 GB miejsca na dysku.

USE master;
GO
IF DB_ID (N'db_sales_test') IS NOT NULL
    DROP DATABASE db_sales_test;
GO
CREATE DATABASE db_sales_test;
GO
USE db_sales_test;
GO
CREATE PARTITION FUNCTION [pf_range_fact](int) AS RANGE RIGHT FOR VALUES 
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
GO
CREATE PARTITION SCHEME [ps_fact_sales] AS PARTITION [pf_range_fact] 
ALL TO ([PRIMARY]);
GO
CREATE TABLE fact_sales(date_id int, product_id int, store_id int, 
    quantity int, unit_price numeric(7,2), other_data char(1000))
ON ps_fact_sales(date_id);
GO
CREATE CLUSTERED INDEX ci ON fact_sales(date_id);
GO
PRINT 'Loading...';
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i<1000000)
BEGIN
    INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
GO
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
BEGIN
    INSERT INTO fact_sales VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
PRINT 'Done.';
GO
-- Two-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080802 AND 20080902
GROUP BY date_id ;
GO
SET STATISTICS XML OFF;
GO
-- Single-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080801 AND 20080831
GROUP BY date_id;
GO
SET STATISTICS XML OFF;
GO

Zobacz także

Odwołanie

Wyświetlanie graficznego wykonanie planów (SQL Server Management Studio)

DBCC TRACEON (Transact-SQL)

Koncepcje

Podzielonym na partycje tabel i indeksów

Wyświetlanie wykonanie planów przy użyciu opcji zestaw Showplan (Transact-SQL)