Długie wykonywanie procedury MSSQL

Witam.
Mam taki problem.
Procedura pisana w T-SQL pod MSSQL 2014 zwraca 70 kolumn. W pewnych warunkach (większa ilość danych, baza od dłuższego czasu nie reindeksowana) zaczyna się mulić. Dobrze to widać podczas testów procedury wykonywanych w Managenent Studio. Po usunięciu jej i założeniu od nowa pierwsze wykonanie potrafi trwać około 20 sekund. Następne wykonanie to już milisekundy.
Jak udało mi się zdiagnozować przyczyną takiego opóźnienia jest zastosowanie klauzuli ORDER BY w zapytaniu. Wtedy takie opóźnienie występuje.
Co ciekawe sprawdzam czas wykonania zapytania w samej procedurze (zapisuję czas przed i po zapytaniu metodą GETDATE() i pokazuję ich różnicę).
Czas ten wynosi kilka milisekund a czas wykonania całej procedury to 20 sekund. Przed i po wywołaniu tego zapytania w środku procedury nic już się nie wykonuje.
Czy możecie mi wytłumaczyć skąd taka różnica w czasach wykonania?
I jak rozwiązać ten problem?
Dodam że ORDER BY jest wykonywany po 9 polach, a zmniejszenie sortowania do 1 pola skutkuje skróceniem czasu wykonania procedury do dwóch sekund. Chyba że zastosuję pole mało rozróżnialne wtedy czas wykonania nie skraca się.

Próbowałem zapisać wynik do tabeli tymczasowej bez sortowania i wynik zwrócić z tej tabeli i ją posortować ale wtedy podobne opóźnienie daje wpis do tabeli. Niezależnie czy jest to tabela tymczasowa czy zwykła.
Po reindeksacji bazy efekt opóźnienia znika.
Ale niestety nie mogę robić często reindeksacji a efekt opóźnienia pojawia się co jakiś czas, gdy baza ma większą ilość danych.

Czy ktoś może spotkał się już z takim przypadkiem?

Ale niestety nie mogę robić często reindeksacji

Dlaczego?

Sprawdziłeś plan tego problematycznego zapytania? To pierwszy krok, przy sprawdzaniu co jest nie tak.

Jak to sortowanie jest zawsze po tych samych kolumnach i jest to główny problem jakiego doświadczasz, to załóż indeks złożony pod to zapytanie (wrzuć do indeksu te same kolumny, które masz w order by, w tej samej kolejności). Dodawanie i update rekordów zwolni, odczyt powinien ruszyć.
Pozytywne objawy po odbudowie indeksów, to trochę przypadek. Wymuszasz w ten sposób odbudowę statystyk. Jeżeli dane w bazie zmieniają się w sposób dynamiczny (było mało, jest dużo) to optymalizator kosztowy może się pogubić i w takich przypadkach, przynajmniej teoretycznie możesz wspomóc bazę odświeżając statystyki optymalizatora.

Niestety w zapytaniu tym są złączeniu w kilkoma tabelami a sortowanie jest po polach z kilku tabel, więc indeks nie rozwiąże mi problemu.

Poza tym w planie wykonania mam kilka ostrzeżeń o polach będących poza statystykami, a są na nich założone indeksy.

Odpisuj w postach, a nie w komentarzach, bo można się łatwo pogubić. To co najprawdopodobniej robi optymalizator, to wyciągnięcie danych do kursora i sortowanie ich na koniec. Na tym etapie nie jest już w stanie sobie pomóc indeksami. Zobacz, czy nie jesteś w stanie założyć jakiegoś sensownego indeksu na pierwsze kolumny order by, żeby wykonywał końcowe sortowanie na wstępnie posortowanych danych (np. po pierwszych 2 kolumnach.

Zastanów się więc nad indeksami zawierajacymi pola, po których następuje sortowanie. W planie zapytania zwróć uwagę na key lookupy, wtedy indeksy ignorowane przez optymalizator mogą wymagać include na wszystkich polach, co w efekcie może koszmarnie spowolnić edycję danych w tabeli. Być może najprościej będzie posortować dane po stronie klienta?

A szukałeś w google?... https://stackoverflow.com/questions/6223161/why-is-my-sql-server-order-by-slow-despite-the-ordered-column-being-indexed:

@ŁF: Czytałem już ten artykuł ale niestety mi nie pomógł.
Próbowałem już zapisać najpierw dane do tabeli tymczasowej bez sortowania ale wtedy takie samo opóźnienie jest na zapisie do tabeli (duża ilość kolumn w tym kilka varchar'owych).
Co ciekawe gdy dodaję ORDER BY w podzapytaniu to opóźnienia brak (zgodnie z sugestią piotrpo aby dane wstępnie przesortować) ale gdy dodaję sortowanie na zewnętrznym zapytaniu to wszystko siada.

Ciekawa rzecz, którą zauważyłem: pomiędzy pierwszym (długim) uruchomieniem procedury po jej przeładowaniu a drugim (szybkim) nie ma różnicy w planie zapytania. Skąd więc różnica w czasie wykonania?

WojtexProgramista napisał(a):

Ciekawa rzecz, którą zauważyłem: pomiędzy pierwszym (długim) uruchomieniem procedury po jej przeładowaniu a drugim (szybkim) nie ma różnicy w planie zapytania. Skąd więc różnica w czasie wykonania?

Za pierwszym razem optymalizator buduje sobie statystyki wykonania zapytania. Czyli próbuje podejść do sprawy na kilka różnych sposobów, wybiera najlepszy (dla aktualnych danych). Później korzysta z poprzednich wyliczeń. Możliwe, że zwyczajnie w świecie, serwer coś gdzieś sobie kesznął.

Problem rozwiązany.
Przeniosłem sortowanie do podzapytania nie wykonując sortowania na zapytaniu zewnętrznym (co powinno ułożyć prawidłowo dane) i opóźnienie znikło.
Dziękuję wszystkim za pomoc.

chyba nie do końca rozwiązałeś problem:

The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless either the TOP or OFFSET and FETCH clauses are also specified. When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15

Mam wrażenie, że problemem nie jest order by inaczej to stwierdzenie:

Próbowałem już zapisać najpierw dane do tabeli tymczasowej bez sortowania ale wtedy takie samo opóźnienie jest na zapisie do tabeli (duża ilość kolumn w tym kilka varchar'owych).

Byłoby nieprawdziwe.

Nie wiem dokładnie co tam pobierasz, na jak dużych danych pracujesz, ale jeżeli

35 wierszy co odpowiada wynikowi.

To co byś tam nie wstawił w order by to nie potrwa 20 sekund...

Takie dywagacje jakie tu prowadzimy to wróżenie z fusów, na razie wiemy, że maintance bazy leży, dane przyrastaja (ale nie wiemy jak duzo) nie wiemy co pobierasz i tak dalej.
Może problem nie leży w indeksach, a wtym że baza czeka na zwolnienie rekordów, ktore są edytowane w czasie zapytania, jak jednak pisałem szklanej kuli tu nie mamy

@Panczo: Masz rację z klauzulą ORDER BY w podzapytaniach ale jest wyjątek: "...chyba że zastosowano klauzulę TOP lub FOR XML...".
Dodatkowo od siebie dodam, że nie można stosować w takich przypadkach konwencji 100 PERCENT bo wtedy też sortowanie nie działa. Ale wystarczy zastosować formę TOP 2147483647 i sortowanie działa prawidłowo.
Opisywany efekt występuje przy skrajnych parametrach bazy (większa ilość danych i dawno nie reindeksowana baza) i niestety taki efekt otrzymuję (inaczej bym o nim nie pisał).
Ale po zastosowaniu opisanych zmian wszystko ruszyło prawidłowo.

Bardziej chodziło mi o ten fragment:

The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

Czy ja dobrze rozumiem?
OP: Mam problem. Zapytanie raz działa wolno, a raz szybko. Co robić?

Może łatwiej byłoby pokazać to zapytanie, wrzucić DDLe tabel... Wiedzielibyśmy, o czym rozmawiamy.

Ale skoro uprawiamy magię, to skoro zapytanie drastycznie przyspiesza po pielęgnacji indeksów, to może warto sprawdzić, co zmienia się w planie zapytania. Czasem wystarczy wymusić wykorzystanie konkretnego indeksu, żeby zmniejszyć podatność zapytania na fragmentację