Czy można zwiększyć wydajność zapytań w wąskiej tabeli z milionami wierszy?

Mam zapytanie, które obecnie zajmuje średnio 2500 ms. Mój stół jest bardzo wąski, ale jest 44 miliony wierszy. Jakie opcje muszę poprawić, czy jest to tak dobre, jak to możliwe?

Zapytanie

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31'; 

Stół

CREATE TABLE [dbo].[Heartbeats](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DeviceID] [int] NOT NULL,
    [IsPUp] [bit] NOT NULL,
    [IsWebUp] [bit] NOT NULL,
    [IsPingUp] [bit] NOT NULL,
    [DateEntered] [datetime] NOT NULL,
 CONSTRAINT [PK_Heartbeats] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Indeks

CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats] 
(
    [DateEntered] ASC,
    [DeviceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Czy dodanie dodatkowych indeksów pomogłoby? Jeśli tak, to jak by wyglądały? Obecna wydajność jest do zaakceptowania, ponieważ zapytanie jest uruchamiane tylko od czasu do czasu, ale zastanawiam się jako ćwiczenie edukacyjne, czy jest coś, co mogę zrobić, aby przyspieszyć?

AKTUALIZACJA

Gdy zmienię zapytanie, aby użyć podpowiedzi indeksu wymuszenia, zapytanie zostanie wykonane w ciągu 50 ms:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats] WITH(INDEX(CommonQueryIndex))
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' 

Dodanie poprawnie selektywnej klauzuli DeviceID również uderza w zakres 50ms:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' AND DeviceID = 4;

Jeśli dodam ORDER BY [DateEntered], [DeviceID]do pierwotnego zapytania, jestem w zakresie 50ms:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' 
ORDER BY [DateEntered], [DeviceID];

Wszystkie używają indeksu, którego się spodziewałem (CommonQueryIndex), więc przypuszczam, że moje pytanie jest teraz, czy istnieje sposób na wymuszenie użycia tego indeksu w zapytaniach takich jak ten? A może rozmiar mojego stołu wyrzucający optymalizator jest zbyt duży i muszę po prostu skorzystać ORDER BYz podpowiedzi?

Dlaczego optymalizator nie wybiera twojego pierwszego indeksu:

CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats] 
(
    [DateEntered] ASC,
    [DeviceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Jest kwestią selektywności kolumny [DateEntered].

Powiedziałeś nam, że twój stół ma 44 miliony wierszy. rozmiar wiersza to:

4 bajty dla identyfikatora, 4 bajty dla identyfikatora urządzenia, 8 bajtów dla daty i 1 bajt dla 4-bitowych kolumn. to 17 bajtów + 7 bajtów narzut dla (znaczników, bitmapy Null, przesunięcia zmiennej col, liczby kolumn) łącznie 24 bajty na wiersz.

To mniej więcej przekładałoby się na 140 000 stron. Do przechowywania tych 44 milionów wierszy.

Teraz optymalizator może zrobić dwie rzeczy:

1. Może skanować tabelę (skanowanie indeksu klastrowego)
2. Lub może użyć twojego indeksu. W przypadku każdego wiersza w indeksie konieczne będzie wówczas sprawdzenie zakładek w indeksie klastrowym.

Teraz w pewnym momencie po prostu droższe jest wykonywanie wszystkich tych pojedynczych wyszukiwań w indeksie klastrowym dla każdego wpisu indeksu znalezionego w indeksie nieklastrowanym. Próg do tego jest na ogół łączna liczba wyszukiwań powinna przekraczać 25% do 33% całkowitej liczby stron tabeli.

Więc w tym przypadku: 140k / 25% = 35000 rzędów 140k / 33% = 46666 rzędów.

(@RBarryYoung, 35k to 0,08% wszystkich wierszy, a 46666 to 0,10%, więc myślę, że to było zamieszanie)

Więc jeśli twoja klauzula where spowoduje gdzieś pomiędzy 35000 a 46666 wierszy. (Jest to pod klauzulą ​​górną!) Jest bardzo prawdopodobne, że twoja niesklastrowana nie zostanie użyta i zostanie użyte skanowanie indeksu klastrowanego.

Jedynymi dwoma sposobami na zmianę tego są:

1. Uczyń klauzulę where bardziej selektywną. (Jeśli to możliwe)
2. Upuść * i wybierz tylko kilka kolumn, aby użyć indeksu obejmującego.

teraz możesz utworzyć indeks obejmujący, nawet jeśli używasz select *. Hoever, który po prostu tworzy ogromne obciążenie dla twoich wstawek / aktualizacji / usuwa. Musielibyśmy dowiedzieć się więcej o obciążeniu pracą (odczyt vs zapis), aby upewnić się, czy jest to najlepsze rozwiązanie.

Zmiana z datetime na smalldatetime to 16% zmniejszenie rozmiaru indeksu klastrowanego i 24% zmniejszenie rozmiaru indeksu klastrowego.

Czy jest jakiś szczególny powód, dla którego twój PK jest skupiony? Wiele osób robi to, ponieważ domyślnie w ten sposób, lub myślą, że PK muszą być grupowane. Nie więc. Indeksy klastrowe najlepiej nadają się do zapytań o zakres (takich jak ten) lub do klucza obcego tabeli potomnej.

Efektem indeksu klastrowania jest to, że grupuje on wszystkie dane razem, ponieważ dane są przechowywane w węzłach liści b drzewa klastra. Zakładając, że nie pytasz o „zbyt szeroki zakres”, optymalizator będzie dokładnie wiedział, która część drzewa b zawiera dane i nie będzie musiał znaleźć identyfikatora wiersza, a następnie przeskoczyć do miejsca, w którym dane jest (podobnie jak w przypadku indeksu NC). Co to jest „zbyt szeroki” zakres? Śmiesznym przykładem może być prośba o 11 miesięcy danych z tabeli, która zawiera tylko roczne rekordy. Pobieranie danych z jednego dnia nie powinno stanowić problemu, przy założeniu, że statystyki są aktualne. (Chociaż optymalizator może mieć kłopoty, jeśli szukasz wczorajszych danych i nie aktualizowałeś statystyk przez trzy dni).

Ponieważ uruchamiasz zapytanie „SELECT *”, silnik będzie musiał zwrócić wszystkie kolumny w tabeli (nawet jeśli ktoś doda nową, której Twoja aplikacja nie potrzebuje w tym momencie), a więc indeks obejmujący lub indeks z dołączonymi kolumnami nic nie pomoże, jeśli w ogóle. (Jeśli do indeksu dołączasz każdą kolumnę z tabeli, robisz coś źle.) Optymalizator prawdopodobnie zignoruje te indeksy NC.

Co więc robić?

Moją propozycją byłoby upuszczenie indeksu NC, zmianę PK w klastrach na nieklastrowane i utworzenie indeksu klastrowego w [DateEntered]. Prostsze jest lepsze, dopóki nie zostanie udowodnione inaczej.

Tak długo, jak masz tam „*”, jedyną rzeczą, jaką mogłem sobie wyobrazić, która miałaby znaczącą różnicę, była zmiana definicji indeksu na:

CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats] 
(
    [DateEntered] ASC,
    [DeviceID] ASC
)INCLUDE (ID, IsWebUp, IsPingUp, IsPUp)
 WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Jak zauważyłem w komentarzach, powinien używać tego indeksu, ale jeśli nie, możesz go przekonać za pomocą ORDER BY lub wskazówki indeksu.

Patrzę na to trochę inaczej.

• Tak, wiem, że to stary wątek, ale jestem zaintrygowany.

Zrzuciłbym kolumnę datetime - zmień ją na int. Zrób tablicę przeglądową lub przekonwertuj datę.

Zrzuć indeks klastrowany - pozostaw go jako stertę i utwórz indeks nieklastrowany w nowej kolumnie INT reprezentującej datę. tj. dzisiaj byłby 20121015. To zamówienie jest ważne. W zależności od częstotliwości ładowania tabeli, spójrz na tworzenie tego indeksu w kolejności DESC. Koszty utrzymania będą wyższe i będziesz chciał wprowadzić współczynnik wypełnienia lub partycjonowanie. Partycjonowanie pomogłoby również skrócić czas działania.

Na koniec, jeśli możesz użyć SQL 2012, spróbuj użyć SEKWENCJI - będzie on przewyższał tożsamość () dla wstawek.