Wymuszone plany na czytelne wtórne

Jeśli plan jest wymuszony na podstawowym w grupie dostępności, to czy jest on stosowany do zapytań uruchamianych na dodatkowym?

Szukam odpowiedzi obejmujących obie możliwości wymuszania planu:

Przeczytałem poniższe, które sugerują, że wymuszone plany QS nie przenoszą się, ale nie mogę znaleźć niczego wiarygodnego w dokumentacji ani niczego w przewodnikach po planach.

Sklep z zapytaniami i grupy dostępności według Erin Stellato
Zapytanie Zachowanie planu wymuszonego przechowywania danych w AlwaysOn Readable Secondary autorstwa Vikas Rana

Rozstrzygające dowody zmuszając byłaby obecność Use Planalbo PlanGuideNamei PlanGuideDBwłaściwości w plan wykonania wtórnej jest.

— Paul White 9
źródło

Wymuszanie planu magazynu zapytań NIE ma wpływu na zapytania pomocnicze

Używanie Query Store do wymuszania planu na urządzeniu podstawowym z pewnością wygląda na to, że wymusza on realizację planu na urządzeniu dodatkowym.

Próbowałem uruchomić zapytanie na serwerze non-prod, a następnie opróżnić magazyn zapytań sp_query_store_flush_db(co było wymagane, aby dane zostały zsynchronizowane z drugim serwerem ). Oto element dodatkowy po lewej stronie (zwróć uwagę na kółko z ostrzeżeniem o byciu „tylko do odczytu”), a element podstawowy po prawej:

Teraz kliknę „Wymuś plan” po prawej stronie, a następnie odśwież oba widoki:

Tak więc „wymuszanie” przynajmniej przenoszone w bazowych tabelach zapytań. Ma to sens, biorąc pod uwagę, że artykuły cytowane w PO wskazują, że wymuszanie zapytań powinno pozostać na miejscu po przełączeniu awaryjnym:

Pytanie: Czy QDS zachowa informacje dotyczące planu FORCED podczas przełączania awaryjnego bazy danych z repliki podstawowej na replikę dodatkową?

Odpowiedź: Tak, informacje o wymuszonym planie przechowują w systemie QDS informacje w tabeli sys.query_store_plan, więc w przypadku przełączenia awaryjnego nadal będzie się tak samo zachowywać w nowej wersji podstawowej.

Ale czy zmuszanie zachowanie rzeczywiście nastąpi? Teraz uruchomię to samo zapytanie na obu serwerach. Na podstawowym, zgodnie z oczekiwaniami, atrybut „UsePlan” znajduje się w pliku XML planu:

<QueryPlan DegreeOfParallelism="1" MemoryGrant="11096" CachedPlanSize="288" CompileTime="82"
           CompileCPU="78" CompileMemory="2104" UsePlan="true">

I w interfejsie użytkownika:

W przypadku serwera pomocniczego (zwróć uwagę na inną nazwę serwera) plan nie został wymuszony . Oto ten sam fragment kodu XML planu:

<QueryPlan DegreeOfParallelism="1" MemoryGrant="11096" CachedPlanSize="288" CompileTime="32" 
           CompileCPU="28" CompileMemory="1656">

Przewodniki po planach NIE wpływają na zapytania pomocnicze

Stworzyłem przewodnik po planie podstawowym, używając tego kodu (nazwy tabel zostały zmienione, aby chronić niewinnych):

EXEC sp_create_plan_guide 
    @name = 'plan-guide-test',
    @stmt = N'SELECT TOP (1000) * 
FROM dbo.TableName t 
WHERE 
    NOT EXISTS 
    (
        SELECT NULL 
        FROM dbo.OtherTable o 
        WHERE t.Id = o.TableName
    );',
    @type = N'SQL',
    @module_or_batch = NULL,
    @hints = N'OPTION (MAXDOP 1)';

Przewodnik po planie był oczywiście skuteczny na podstawowym, o czym świadczy plan wykonania:

<StmtSimple StatementCompId="1" StatementEstRows="1000" ... StatementType="SELECT" 
            PlanGuideDB="..._UAT" PlanGuideName="plan-guide-test" ...>

W tym momencie potwierdziłem, że przewodnik planu został zreplikowany na wtórny.

Uruchamiając to samo zapytanie pomocnicze, w planie wykonania brakuje wszystkich oznak wymuszenia przez przewodnik po planie:

<StmtSimple StatementCompId="1" StatementEstRows="1000" ... StatementType="SELECT" 
            QueryHash="0xECF8A24F126EE77A" QueryPlanHash="0x0E93CF7FEAC1B6EA" 
            RetrievedFromCache="true" SecurityPolicyApplied="false">

— Josh Darnell
źródło