Chociaż ten post nie będzie kompletną odpowiedzią z powodu braku informacji, powinien być w stanie skierować Cię we właściwym kierunku lub w inny sposób uzyskać wgląd, który możesz później udostępnić społeczności.
Niestety definicja ta powoduje pogorszenie wydajności w stosunku do poprzedniej sytuacji z tabelą dyskową. Rząd wielkości jest mniej więcej o 10% wyższy (w niektórych przypadkach osiąga 100%, więc podwójny czas).
Przede wszystkim spodziewałem się przewagi w scenariuszach o wysokiej współbieżności, biorąc pod uwagę architekturę bez blokady reklamowaną przez Microsoft. Zamiast tego najgorsze wyniki występują dokładnie wtedy, gdy kilku równoczesnych użytkowników uruchamia kilka zapytań w tabeli.
Jest to niepokojące, ponieważ zdecydowanie nie powinno tak być. Niektóre obciążenia nie są w tabelach pamięci (SQL 2014), a niektóre obciążenia się do tego nadają. W większości sytuacji może wystąpić minimalny wzrost wydajności po prostu przez migrację i wybranie odpowiednich indeksów.
Początkowo myślałem bardzo wąsko o twoich pytaniach dotyczących tego:
Pytania:
- jaki jest właściwy BUCKET_COUNT do ustawienia?
- jakiego rodzaju indeksu powinienem użyć?
- dlaczego wydajność jest gorsza niż w przypadku tabeli opartej na dysku?
Początkowo uważałem, że występuje problem z faktyczną tabelą pamięci i indeksami, które nie są optymalne. Chociaż istnieją pewne problemy z definicją indeksu skrótu zoptymalizowaną pod kątem pamięci, uważam, że prawdziwy problem dotyczy wykorzystywanych zapytań.
-- INSERT (can vary from 10s to 10,000s of records):
INSERT INTO MyTable
SELECT @fixedValue, id2, col1, col2 FROM AnotherTable
Ta wstawka powinna być wyjątkowo szybka, gdyby obejmowała tylko tabelę pamięci. Dotyczy to jednak również tabeli opartej na dysku i podlega wszystkim związanym z tym blokowaniu i blokowaniu. Zatem marnotrawstwo w czasie rzeczywistym dotyczy tabeli opartej na dysku.
Kiedy po załadowaniu danych do pamięci zrobiłem szybki test z wstawieniem 100 000 wierszy z tabeli opartej na dysku - czasy reakcji były poniżej sekundy. Jednak większość danych jest przechowywana tylko przez bardzo krótki czas, mniej niż 20 sekund. To nie daje dużo czasu, aby naprawdę żyć w pamięci podręcznej. Ponadto nie jestem pewien, jak duży AnotherTablejest naprawdę i nie wiem, czy wartości są odczytywane z dysku, czy nie. Na tych odpowiedziach musimy polegać.
Za pomocą zapytania Wybierz:
SELECT id2, col1
FROM MyTable INNER JOIN OtherTbl ON MyTable.id2 = OtherTbl.pk
WHERE id1 = @value
ORDER BY col1
Ponownie jesteśmy na łasce wydajności tabeli opartej na dyskach interop +. Ponadto sortowanie nie jest tanie w indeksach HASH i należy stosować indeks nieklastrowany. Zostało to przywołane w Przewodniku po indeksie, który zamieściłem w komentarzach.
Aby podać pewne fakty oparte na badaniach, załadowałem SearchItemstabelę pamięci do 10 milionów wierszy i AnotherTable100 000, ponieważ nie znałem jej rzeczywistej wielkości ani statystyk. Następnie wykonałem powyższe zapytanie select. Dodatkowo utworzyłem sesję zdarzeń rozszerzonych na wait_completed i umieściłem ją w buforze pierścieniowym. Został wyczyszczony po każdym uruchomieniu. Pobiegłem również, DBCC DROPCLEANBUFFERSaby zasymulować środowisko, w którym wszystkie dane mogą nie znajdować się w pamięci.
Wyniki nie były niczym spektakularnym, gdy patrzy się na nie w próżni. Ponieważ laptop, na którym testuję, korzysta z dysku SSD wyższej jakości, sztucznie obniżyłem wydajność dysku dla używanej maszyny wirtualnej.
Wyniki pojawiły się bez informacji o czekaniu po 5 uruchomieniach zapytania tylko w tabeli opartej na pamięci (usunięcie sprzężenia i brak podpytań). Jest to w zasadzie zgodne z oczekiwaniami.
Jednak przy użyciu oryginalnego zapytania czekałem. W tym przypadku to PAGEIOLATCH_SH ma sens, ponieważ dane są odczytywane z dysku. Ponieważ jestem jedynym użytkownikiem w tym systemie i nie spędziłem czasu na tworzeniu ogromnego środowiska testowego dla wstawek, aktualizacji, usuwania w stosunku do połączonej tabeli, nie spodziewałem się, że nastąpi jakiekolwiek blokowanie lub blokowanie.
W tym przypadku po raz kolejny znaczna część czasu została poświęcona tabeli opartej na dysku.
Na koniec usuń zapytanie. Znalezienie wierszy na podstawie tylko ID1 nie jest wyjątkowo wydajne dzięki indeksowi. Chociaż prawdą jest, że predykaty równości są właściwe dla indeksów mieszających, segment, do którego wpadają dane, jest oparty na całych kolumnach mieszanych. Zatem id1, id2 gdzie id1 = 1, id2 = 2, a id1 = 1, id2 = 3 będzie mieszał różne wiadra, ponieważ hash będzie w poprzek (1,2) i (1,3). Nie będzie to prosty skan zakresu B-Tree, ponieważ indeksy skrótów nie mają takiej samej struktury. Spodziewałbym się wtedy, że nie będzie to idealny wskaźnik dla tej operacji, ale nie spodziewałbym się, że zajmie to rzędy wielkości dłużej niż doświadczenie. Byłbym zainteresowany zobaczeniem wait_info na ten temat.
Przede wszystkim spodziewałem się przewagi w scenariuszach o wysokiej współbieżności, biorąc pod uwagę architekturę bez blokady reklamowaną przez Microsoft. Zamiast tego najgorsze wyniki występują dokładnie wtedy, gdy kilku równoczesnych użytkowników uruchamia kilka zapytań w tabeli.
Chociaż prawdą jest, że zamki są używane dla logicznej spójności, operacje muszą nadal być atomowe. Odbywa się to za pomocą specjalnego operatora porównywania opartego na procesorze (dlatego In-Memory działa tylko z niektórymi [choć prawie wszystkimi procesorami produkowanymi w ciągu ostatnich 4 lat]). Dlatego nie dostajemy wszystkiego za darmo, będzie jeszcze trochę czasu na dokończenie tych operacji.
Kolejną kwestią, o której należy wspomnieć, jest fakt, że w prawie wszystkich zapytaniach wykorzystano interfejs T-SQL (a nie natywnie skompilowane SPROC), które dotykają co najmniej jednej tabeli opartej na dysku. Właśnie dlatego uważam, że ostatecznie nie mamy żadnej zwiększonej wydajności, ponieważ nadal jesteśmy ograniczeni wydajnością tabel opartych na dyskach.
Kontynuacja:
Utwórz rozszerzoną sesję zdarzeń dla wait_completed i podaj znany identyfikator SPID. Uruchom zapytanie i podaj nam dane wyjściowe lub zużyj je wewnętrznie.
Daj nam aktualizację danych wyjściowych z nr 1.
Nie ma magicznej liczby do określenia liczby segmentów dla indeksów skrótów. Zasadniczo, dopóki łyżki nie zostaną całkowicie wypełnione, a łańcuchy rzędów pozostaną poniżej 3 lub 4, wydajność powinna pozostać do zaakceptowania. To trochę jak pytanie: „Na co powinienem ustawić mój plik dziennika?” - będzie zależeć od procesu, bazy danych i typu użycia.
OPTION(OPTIMIZE FOR UNKNOWN)(patrz Wskazówki do tabeli )?