Zapytanie T-SQL przy użyciu zupełnie innego planu w zależności od liczby wierszy, które aktualizuję

Mam instrukcję SQL UPDATE z klauzulą ​​„TOP (X)”, a wiersz, w którym aktualizuję wartości, ma około 4 miliardów wierszy. Kiedy używam „TOP (10)”, otrzymuję jeden plan wykonania, który wykonuje się niemal natychmiast, ale kiedy używam „TOP (50)” lub większego, zapytanie nigdy (przynajmniej nie podczas oczekiwania) kończy się i używa zupełnie innego planu wykonania. Mniejsze zapytanie korzysta z bardzo prostego planu z parą wyszukiwań indeksu i zagnieżdżonego łączenia pętli, w którym dokładnie to samo zapytanie (z inną liczbą wierszy w klauzuli TOP instrukcji UPDATE) używa planu, który obejmuje dwa różne wyszukiwania indeksu , szpula stołu, równoległość i kilka innych złożoności.

Użyłem opcji „OPCJA (PLAN UŻYCIA ...)”, aby zmusić ją do użycia planu wykonania wygenerowanego przez mniejsze zapytanie - kiedy to robię, mogę zaktualizować nawet 100 000 wierszy w ciągu kilku sekund. Wiem, że plan zapytań jest dobry, ale SQL Server sam wybierze ten plan tylko wtedy, gdy zaangażowana jest tylko niewielka liczba wierszy - każda dość duża liczba wierszy w mojej aktualizacji spowoduje, że plan nie będzie optymalny.

Pomyślałem, że winą może być paralelizm, więc postawiłem MAXDOP 1na zapytanie, ale bez skutku - ten krok zniknął, ale zły wybór / wydajność nie. Pobiegłem także sp_updatestatsdziś rano, aby upewnić się, że to nie jest przyczyna.

Załączam dwa plany wykonania - krótszy jest również szybszy. Dodatkowo, oto pytanie, o które pytamy (warto zauważyć, że dołączony przeze mnie WYBÓR wydaje się być szybki w przypadku małej i dużej liczby wierszy):

    update top (10000) FactSubscriberUsage3
               set AccountID = sma.CustomerID
    --select top 50 f.AccountID, sma.CustomerID
      from FactSubscriberUsage3 f
      join dimTime t
        on f.TimeID = t.TimeID
      join #mac sma
        on f.macid = sma.macid
       and t.TimeValue between sma.StartDate and sma.enddate 
     where f.AccountID = 0 --There's a filtered index on the table for this

Oto szybki plan :

A oto wolniejszy :

Czy jest coś oczywistego w sposobie konfigurowania zapytania lub w planie wykonania, pod warunkiem, że podda się złemu wyborowi dokonywanemu przez silnik zapytań? W razie potrzeby mogę również dołączyć definicje tabel i zdefiniowane na nich indeksy.

Dla tych, którzy poprosili o wersję obiektów bazy danych opartą wyłącznie na statystykach: nawet nie zdawałem sobie sprawy, że możesz to zrobić, ale ma to pełny sens! Próbowałem wygenerować skrypty dla bazy danych zawierającej tylko statystyki, aby inni mogli sami przetestować plany wykonania, ale mogę wygenerować generowanie statystyk / histogramów na moim przefiltrowanym indeksie (wygląda na to, że występuje błąd składniowy w skrypcie), więc jestem pech tam Próbowałem usunąć filtr, a plany zapytań były bliskie, ale nie dokładnie takie same, i nie chcę wysyłać nikogo w pogoń za gęsią skórką.

Aktualizacja i kilka bardziej kompletnych planów wykonania: Po pierwsze, Eksplorator planów SQL Sentry to niesamowite narzędzie. Nie wiedziałem nawet, że istnieje, dopóki nie przejrzałem innych pytań dotyczących planu zapytań w tej witrynie i miałem sporo do powiedzenia na temat tego, jak działały moje zapytania. Chociaż nie jestem pewien, jak rozwiązać problem, dali do zrozumienia, na czym polega problem.

Oto podsumowanie dla 10, 100 i 1000 wierszy - możesz zobaczyć, że zapytanie 1000 wierszy jest bardzo dalekie od innych:

Widać, że trzecie zapytanie ma absurdalną liczbę odczytów, więc oczywiście robi coś zupełnie innego. Oto szacowany plan wykonania z liczbą wierszy. Szacunkowy plan wykonania na 1000 wierszy:

A oto rzeczywiste wyniki planu wykonania (nawiasem mówiąc, „nigdy nie kończy”, okazuje się, że miałem na myśli „kończy się za godzinę”). Rzeczywisty plan wykonania z 1000 wierszy

Pierwszą rzeczą, jaką zauważyłem było to, że zamiast ciągnąc 60 tys wiersze z tabeli dimTime jak oczekuje, to rzeczywiście ciągnąc 1,6 mld, z pensjonatów . Patrząc na moje zapytanie, nie jestem pewien, jak wyciąga tyle wierszy z tabeli dimTime. Operator BETWEEN, którego używam, zapewnia, że ​​pobieram właściwy rekord z #mac na podstawie rekordu czasu w tabeli faktów. Jednak po dodaniu wiersza do klauzuli WHERE, w której filtruję t.TimeValue (lub t.TimeID) do pojedynczej wartości, mogę z powodzeniem zaktualizować 100 000 wierszy w ciągu kilku sekund. W wyniku tego, jak wyjaśniono w dołączonych planach wykonania, oczywiste jest, że problem stanowi mój harmonogram, ale nie jestem pewien, jak zmieniłbym kryteria łączenia, aby obejść ten problem i zachować dokładność . jakieś pomysły?

Dla porównania, tutaj plan (z liczbą wierszy) dla aktualizacji 100 wierszy. Widać, że osiąga ten sam indeks i wciąż ma mnóstwo wierszy, ale nie zbliża się do tej samej wielkości problemu. Wykonanie 100 wierszy z liczbą wierszy :

Indeks na dimTime się zmienia. Szybszy plan korzysta z indeksu _dta. Po pierwsze, upewnij się, że nie jest oznaczony jako indeks hipotetyczny w sys.indexes.

Myślisz, że możesz ominąć jakąś parametryzację, używając tabeli #mac do filtrowania, zamiast podawać tylko daty początkowe / końcowe takie, GDZIE t.TimeValue między @StartDate i @enddate. Pozbądź się tabeli temp.

Bez dodatkowych informacji na temat liczby wierszy w planie, moim wstępnym zaleceniem jest ustalenie prawidłowej kolejności łączenia w zapytaniu i wymuszenie jej użycia OPTION (FORCE ORDER). Wymuszaj kolejność dołączania pierwszego planu.