Czy w tym widoku podzielonym na partycje można zmusić optymalizator do wyeliminowania niepotrzebnych tabel?

Testuję różne architektury dla dużych tabel i jedną z sugestii, które widziałem, jest użycie widoku podzielonego na partycje, w którym duży stół jest podzielony na serię mniejszych „podzielonych na partycje” tabel.

1 , 2 , 3 , 4

Testując to podejście, odkryłem coś, co nie ma dla mnie większego sensu. Kiedy filtruję „kolumnę partycjonowania” w widoku faktów, optymalizator szuka tylko odpowiednich tabel. Ponadto, jeśli filtruję tę kolumnę w tabeli wymiarów, optymalizator eliminuje niepotrzebne tabele.

Jeśli jednak odfiltruję jakiś inny aspekt wymiaru, optymalizator będzie szukał wartości PK / CI każdej tabeli podstawowej.

Oto pytania, o których mowa:

select 
    od.[Year], 
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o 
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where o.ObservationDateKey >= 20000101
    and o.ObservationDateKey <= 20051231
group by od.[Year];

select 
    od.[Year], 
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o 
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where od.DateKey >= 20000101
    and od.DateKey <= 20051231
group by od.[Year];

select 
    od.[Year], 
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o 
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where od.[Year] >= 2000 and od.[Year] < 2006
group by od.[Year];

Oto link do sesji SQL Sentry Plan Explorer.

Pracuję nad partycjonowaniem większej tabeli, aby zobaczyć, czy dostanę eliminację partycji, aby zareagować w podobny sposób.

Dostaję eliminację partycji dla (prostego) zapytania, które filtruje aspekt wymiaru.

Tymczasem oto kopia bazy danych przeznaczona wyłącznie do statystyk:

https://gist.github.com/swasheck/9a22bf8a580995d3b2aa

„Stary” estymator liczności otrzymuje tańszy plan, ale wynika to z niższych szacunków liczności przy każdym z (niepotrzebnych) indeksów.

Chciałbym wiedzieć, czy istnieje sposób na to, aby optymalizator użył kolumny klucza podczas filtrowania według innego aspektu wymiaru, aby mógł wyeliminować szukanie w zbędnych tabelach.

Wersja SQL Server:

Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
    Feb 20 2014 20:04:26 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Włącz flagę śledzenia 4199.

Musiałem także wydać:

UPDATE STATISTICS dbo.ObservationDates 
WITH ROWCOUNT = 73049;

aby uzyskać plany pokazane poniżej. Brak danych statystycznych dla tej tabeli. Liczba 73 049 pochodzi z informacji o liczności tabeli w załączniku do Eksploratora planu. Użyłem programu SQL Server 2014 SP1 CU4 (kompilacja 12.0.4436) z dwoma procesorami logicznymi, maksymalną pamięcią ustawioną na 2048 MB i bez flag śledzenia oprócz 4199.

Powinieneś otrzymać plan wykonania z dynamiczną eliminacją partycji:

select 
    od.[Year], 
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o 
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where 
    od.[Year] >= 2000 and od.[Year] < 2006
group by 
    od.[Year]
option (querytraceon 4199);

Fragment planu:

Może to wyglądać gorzej, ale wszystkie filtry są filtrami początkowymi . Przykładowy predykat to:

Podczas iteracji pętli testowany jest predykat początkowy i tylko wtedy, gdy zwraca on wartość true, wykonywane jest wyszukiwanie klastrowane indeksu poniżej. Stąd dynamiczna eliminacja partycji.

Być może nie jest to tak skuteczne jak eliminacja statyczna, szczególnie jeśli plan jest równoległy.

Może trzeba spróbować podpowiedzi jak MAXDOP 1, FAST 1lub FORCESEEKw celu uzyskania tego samego planu. Wybór kalkulatorów kosztów z widokami podzielonymi na partycje (np. Tabele podzielone na partycje) może być trudny.

Chodzi o to, że potrzebujesz planu z filtrami początkowymi, aby uzyskać dynamiczną eliminację partycji za pomocą widoków podzielonych na partycje.

Zapytania z osadzonymi USE PLANwskazówkami: (przez gist.github.com):

• Plan szeregowy
• Plan równoległy

Moja obserwacja zawsze była taka, że ​​musisz wyraźnie określić wartość (lub zakres wartości) kolumny partycji w zapytaniu, aby uzyskać „eliminację tabeli” w widoku podzielonym na partycje. Jest to oparte na doświadczeniu w używaniu partycjonowanych widoków w produkcji od SQL Server 2000 do SQL Server 2014.

SQL Server nie ma koncepcji operatora łączenia w pętli, w którym silnik może dynamicznie celować wyszukiwanie bezpośrednio do odpowiedniej tabeli po wewnętrznej stronie pętli w oparciu o wartość wiersza po zewnętrznej stronie pętli. Jednak, jak wyjaśnia odpowiedź Paula , istnieje możliwość planu z filtrami początkowymi, aby dynamicznie pomijać nieistotne tabele po wewnętrznej stronie pętli w stałym czasie (w przeciwieństwie do logarytmiki poprzez faktyczne wyszukiwanie).

Należy jednak pamiętać, że w przypadku tabel podzielonych na partycje ten rodzaj wyszukiwania (do określonej partycji) jest obsługiwany.

Jeśli nie chcesz używać widoków podzielonych na partycje, inną opcją jest podzielenie zapytania na wiele zapytań, takich jak:

-- Gather than the min/max values for the partition column
DECLARE @minDateKey INT,
        @maxDateKey INT
SELECT @minDateKey = MIN(DateKey),
        @maxDateKey = MAX(DateKey)
FROM dbo.ObservationDates od
WHERE od.[Year] >= 2000 and od.[Year] < 2006

-- Since I have a stats-only copy of the database, simulate having run the query above
-- (You can comment this out since you have the actual data.)
SELECT @minDateKey = 20000101, @maxDateKey = 20051231

-- Adjust the query to use the min/max values of the partition column
-- rather than filtering on a different column in the dimension table
select 
    od.[Year], 
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o 
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
WHERE od.DateKey >= @minDateKey AND od.DateKey <= @maxDateKey
group by od.[Year]
-- Must use OPTION RECOMPILE; otherwise the plan will touch all tables because it
-- must do so in order to be valid for all values of the parameters!
OPTION (RECOMPILE)

To daje następujący plan. Istnieje teraz dodatkowe zapytanie dotyczące tabeli wymiarów, ale zapytanie dotyczące (prawdopodobnie znacznie większej) tabeli faktów jest zoptymalizowane.