Porady na temat diagnozowania „czasem” powolnego zapytania

Mam procedurę składowaną, która zwraca wyniki z widoku indeksowanego za pomocą indeksu obejmującego. Zwykle działa szybko (~ 10 ms), czasem może działać nawet do 8 sekund.

Oto przykład losowego wykonania (uwaga: to nie jest powolne, ale tekst zapytania jest taki sam, z wyjątkiem przekazywanej wartości):

declare @p2 dbo.IdentityType
insert into @p2 values(5710955)
insert into @p2 values(5710896)
insert into @p2 values(5710678)
insert into @p2 values(5710871)
insert into @p2 values(5711103)
insert into @p2 values(6215197)
insert into @p2 values(5710780)

exec ListingSearch_ByLocationAndStatus @statusType=1,@locationIds=@p2

Oto SPROC:

ALTER PROCEDURE [dbo].[ListingSearch_ByLocationAndStatus]
    @LocationIds IdentityType READONLY,
    @StatusType TINYINT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT      -- lots of fields
    FROM        [dbo].[ListingSearchView][a] WITH (NOEXPAND)
    INNER JOIN  @LocationIds [b] ON [a].[LocationId] = [b].[Id]
    WHERE       [a].[StatusType] = @statusType
    OPTION (RECOMPILE);

(uwaga: dodałem OPTION (RECOMPILE)wskazówkę niedawno po kilku radach, ale to nie pomogło.

Oto indeks obejmujący (uwaga: widok ma również indeks klastrowany ListingId, który jest unikalny)

CREATE NONCLUSTERED INDEX [IX_ListingSearchView_ForAPI] ON [dbo].[ListingSearchView]
(
    [LocationId] ASC,
    [StatusType] ASC
)
INCLUDE ( -- all the fields in the query) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Włączyłem śledzenie profilera ze statystykami showplan XML.

Oto powolny (6 sekund) i odpowiedni plan:

Wygląda dokładnie tak, jak się spodziewałem i jest taki sam plan, gdy zapytanie jest szybkie.

Oto powiększenie kosztownej części planu, jeśli to pomaga:

Oto pełny schemat tabel widoku / kopii zapasowej, jeśli to pomaga: https://pastebin.com/wh1sRcbQ

Uwagi:

• Indeksy zostały zdefragmentowane, statystyki są aktualne.
• Pierwotnie zapytanie było wbudowane w widok, ale przeniosłem się do SPROC, aby spróbować ustabilizować. Nie pomogło
• Dodanie WITH OPTION (RECOMPILE);podpowiedzi (nie działało, więc nie może być wąchanie parametru?)
• Inne zapytania w systemie również czasami działają wolno i nie mają też żadnych oczywistych problemów w swoim planie.
• Może być blokowany? Nie jestem pewien, jak to potwierdzić.

Jakieś pomysły na to, co mogę spróbować dalej?

Dzięki

Naprawdę nie sądzę, aby używanie tego OPTION (RECOMPILE)było skutecznym sposobem na wyeliminowanie możliwości wąchania parametrów.

Wykrywanie parametrów ma miejsce, gdy SQL jest mylony z określonym zapytaniem i myśli o swoim nowym, ponieważ widzi nowe parametry. Jest powolny, ponieważ wygenerowanie nowego planu wykonania zajmuje więcej czasu.

Wszystko, co robi ta opcja, zmusza SQL do tworzenia nowego planu za każdym razem, co jest prawie tym samym. Zamiast tego możesz rozważyć dodanie parametrów domyślnych za pomocą tej wskazówki:

OPTION(OPTIMIZE FOR(@LocationIds='xx',@StatusType='xx'))

Wybierając parametry domyślne, należy użyć zestawu reprezentatywnego statystycznie.
Wymusi to stosowanie tego samego planu za każdym razem i wyeliminuje możliwość wąchania parametrów. Gdy to zrobisz i określisz, że to nie pomogło, prawdopodobnie bezpiecznie można odrzucić wąchanie parametrów jako możliwą.

Być może spróbuj wymusić kolejność, więc prawdopodobnie zawsze zaczynasz od mniejszej tabeli (zmiennej). Jest to trudne z widokami ...

    SELECT  -- lots of fields
    FROM    @LocationIds [b] WITH (NOEXPAND)
            INNER JOIN  [dbo].[ListingSearchView][a] WITH (NOEXPAND) 
                ON [a].[LocationId] = [b].[Id]
    WHERE   [a].[StatusType] = @statusType
    OPTION (FORCE ORDER);

lub możesz wymusić sprzężenie pętli, jeśli ogólnie tak chcesz dołączyć zmienną tabeli do widoku, co również wymusi porządek ...

    SELECT  -- lots of fields
    FROM    @LocationIds [b] WITH (NOEXPAND)
            INNER LOOP JOIN  [dbo].[ListingSearchView][a] WITH (NOEXPAND) 
                ON [a].[LocationId] = [b].[Id]
    WHERE   [a].[StatusType] = @statusType
    --leaving this here so you don't get an annoying warning 
    OPTION (FORCE ORDER);

Wpisz nazwę procedury Store w edytorze zapytań, a następnie wybierz proc. Sklepu. nazwa, a następnie wybierz opcję Wyświetl plan wykonania szacunkowego lub kliknij (Ctrl + L). poniżej obrazu tego.

następnie Plany wykonania są wyświetlane tuż obok karty Wiadomości na dole Edytora zapytań. następnie w zielone linie pokaż brakujące dane indeksu i kliknij je prawym przyciskiem myszy. Następnie otwórz nowe zapytanie w nowej karcie, a następnie utwórz INDEKS. wtedy Twoje zapytanie działa szybko.

Więc użyłem tej metody do zdiagnozowania zapytania, które pracuje wolno. a także istnieje tak wiele zapytań lub metod, których można użyć.

Jeśli uważasz, że problem polega na blokowaniu, zasugeruję, abyś użył optymistycznego poziomu izolacji transakcji Przeczytaj zatwierdzoną migawkę (pamiętaj, że spowoduje to obciążenie twojego tempDB).

ODNIESIENIE: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

Jeśli problem nie polega na blokowaniu odczytu / zapisu, możesz spróbować dodać indeksy do swojego widoku (najlepszy wybór indeksów zależy od selektywności danych)

CREATE NONCLUSTERED INDEX IX_ListingSearchView (LocationID, StatusType) INCLUDE (other columns...)