WYBIERZ TOP 1 z bardzo dużej tabeli w kolumnie indeksu jest bardzo wolny, ale nie w odwrotnej kolejności („desc”)

Mamy dużą bazę danych, około 1 TB, działającą na serwerze SQL Server 2014 na silnym serwerze. Wszystko działało dobrze przez kilka lat. Około 2 tygodnie temu wykonaliśmy pełną konserwację, która obejmowała: Zainstalowanie wszystkich aktualizacji oprogramowania; odbuduj wszystkie indeksy i kompaktowe pliki DB. Nie spodziewaliśmy się jednak, że na pewnym etapie wykorzystanie procesora przez DB wzrosło o ponad 100% do 150%, gdy rzeczywiste obciążenie było takie samo.

Po wielu problemach rozwinęliśmy to do bardzo prostego zapytania, ale nie mogliśmy znaleźć rozwiązania. Zapytanie jest niezwykle proste:

select top 1 EventID from EventLog with (nolock) order by EventID

To zawsze zajmuje około 1,5 sekundy! Jednak podobne zapytanie z „desc” zawsze zajmuje około 0 ms:

select top 1 EventID from EventLog with (nolock) order by EventID desc

PTable ma około 500 milionów wierszy; EventIDto podstawowa kolumna indeksu klastrowego (uporządkowana ASC) z typem danych bigint (kolumna Tożsamość). Istnieje wiele wątków wstawiających dane do tabeli u góry (większe Identyfikatory zdarzeń), a 1 wątek usuwa dane z dołu (mniejsze Identyfikatory zdarzeń).

W SMSS zweryfikowaliśmy, że dwa zapytania zawsze używają tego samego planu wykonania:

• Skanowanie indeksu klastrowego;

• Szacowane i rzeczywiste numery wierszy wynoszą 1;

• Szacowana i faktyczna liczba egzekucji wynosi 1;

• Szacunkowy koszt we / wy wynosi 8500 (wydaje się, że jest wysoki)

• Jeśli uruchamiane są kolejno, koszt zapytania jest taki sam 50% dla obu.

Zaktualizowałem statystyki indeksu with fullscan, problem nadal występował; Zbudowałem indeks ponownie i problem zniknął na pół dnia, ale wrócił.

Włączyłem statystyki IO z:

set statistics io on

następnie uruchomił dwa zapytania kolejno i znalazł następujące informacje:

(W przypadku pierwszego zapytania, wolne)

Tabela „PTable”. Liczba skanów 1, logiczne odczyty 407670, fizyczne odczyty 0, odczytywanie z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty 0.

(W przypadku drugiego zapytania, szybkie)

Tabela „PTable”. Liczba skanów 1, logiczne odczyty 4, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.

Zwróć uwagę na ogromną różnicę w logicznych odczytach. Indeks jest używany w obu przypadkach.

Fragmentacja indeksu może trochę wyjaśnić, ale uważam, że wpływ jest bardzo niewielki; i problem nigdy wcześniej się nie zdarzył. Innym dowodem jest, jeśli uruchomię zapytanie takie jak:

select * from EventLog with (nolock) where EventID=xxxx   

Nawet jeśli ustawię xxxx na najmniejsze Identyfikatory zdarzeń w tabeli, zapytanie zawsze jest błyskawiczne.

Sprawdziliśmy i nie ma problemu z blokowaniem / blokowaniem.

Uwaga: Właśnie próbowałem uprościć powyższy problem. „PTable” to tak naprawdę „EventLog”; PIDjest EventID.

Otrzymuję ten sam wynik testu bez NOLOCKpodpowiedzi.

Czy ktoś może pomóc?

Bardziej szczegółowe plany wykonania zapytań w XML w następujący sposób:

https://www.brentozar.com/pastetheplan/?id=SJ3eiVnob

https://www.brentozar.com/pastetheplan/?id=r1rOjVhoZ

Nie wydaje mi się, że to ważne, aby podać instrukcję tworzenia tabeli. Jest to stara baza danych i działała idealnie dobrze od dłuższego czasu do czasu konserwacji. Sami przeprowadziliśmy wiele badań i zawęziliśmy je do informacji zawartych w moim pytaniu.

Tabela została utworzona normalnie z EventIDkolumną jako kluczem podstawowym, który jest identitykolumną typu bigint. W tej chwili wydaje mi się, że problemem jest fragmentacja indeksu. Wydawało się, że zaraz po odbudowaniu indeksu problem zniknął na pół dnia; ale dlaczego wrócił tak szybko ...?

Skanowanie indeksu klastrowanego pokazuje 423,723 logiczne odczyty, aby zwrócić pierwszy wiersz, biorąc 1926 ms:

To wydaje się dość dużo, aby zlokalizować pierwszy wiersz w kolejności indeksu.

Najprawdopodobniej twoje zadanie czyszczenia duchów jest daleko w tyle lub zostało zatrzymane. Należy sprawdzić ghost_record_countindeks klastrowany sys.dm_db_index_physical_statsi monitorować zmiany w czasie.

Nakazał skanowania od końca wskaźnika, który widzi stałej aktywności usuwania ma skanować ponad strasznie dużo widmo rekordy zanim znajdzie pierwszy wiersz „żywe”, aby powrócić. To wyjaśnia dodatkowe logiczne odczyty. Wyszukiwanie w dół b-drzewa do najniższej wartości indeksu napotka znacznie mniej rekordów.

Innym czynnikiem wpływającym na wydajność jest to, że samo skanowanie staje się odpowiedzialne za usuwanie rekordów duchów, jak wspomniano w Inside the Storage Engine: Dogłębne czyszczenie duchów przez Paula Randala.

Powinieneś sprawdzić, czy flaga śledzenia 661 (wyłącz czyszczenie duchów) nie jest aktywna.

Rozwiązania

• Może się okazać, że uruchomienie sp_clean_db_free_space zapewnia ulgę.
• Zmiana procesu usuwającego wiersze z tego końca indeksu na PAGLOCKwskazówkę umożliwiłaby natychmiastowe oczyszczenie ducha , co również mogłoby rozwiązać problem.

Jeśli proces czyszczenia duchów został całkowicie zatrzymany, najskuteczniejszym rozwiązaniem jest zwykle ponowne uruchomienie instancji SQL Server. Należy również upewnić się, że program SQL Server uruchamia jedną z najnowszych aktualizacji zbiorczych. Przez lata pojawiło się wiele błędów usuwania duchów.

W twoim konkretnym przypadku:

Okazało się, że przyczyną problemu była inna testowa baza danych na tym samym serwerze. Ta testowa baza danych została przywrócona z „utratą danych” i jest uszkodzona. Co zaskakujące, proces oczyszczania duchów najwyraźniej utknął w tej bazie danych. Po usunięciu tej uszkodzonej bazy danych z SMSS problem sam się rozwiązał (zajęło to dużo czasu i mogło spowodować zawieszenie się DB na krótki czas).