none
Problém s optimalizátorem dotazů po upgradu databáze z SQL2008R2 na SQL2016SP1 - často se provádí index scan místo index seek

    Dotaz

  • Dobrý den,

    řeším urgentní problém po upgradu databáze našeho zákazníka z SQL Server 2008 R2 na SQL Server 2016 SP1.

    Kdy u velkého množství dotazů se přestalo používat index seek (používá se index scan) oproti původní verzi SQL Server2008R2.

    Aktuálně se snažíme přepsat aplikaci, tak že přidáváme do dotazů pro databázi různé hinty, které zlepšují vykonávání dotazů:

    • OPTION (USEHINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));  
    • WITH (forceseek)
    • Rušíme parametrické volání SQL dotazů a pokud      to jde dáváme konstanty do WHERE podmínek

    Ze zoufalství jsem zkoušel neúspěšně měnit globální nastavení:

    • LEGACY_CARDINALITY_ESTIMATION = ON
    • PARAMETER_SNIFFING
    • QUERY_OPTIMIZER_HOTFIXES

    Dále jsme bez pozitivního vlivu provedli rebuild indexů, reoganizaci indexů, update statistics (full scan)

    Moc by mě pomohlo, pokud by existoval způsob jak nějakým nastavením donutit SQL2016

    aby používal v dotazech na indexovanými sloupci index SEEK místo SCAN.

    Děkuji

    Petr Novák

    26. září 2017 15:52

Odpovědi

  • Zdravím,

    v první řadě bych si rád ověřil, že došlo ke zřetelnému zhoršení výkonu při použití scan místo seek. Rozhodně totiž není pravidlo, že by seek byl vždy rychlejší než scan. Ovlivnit to může rozsah čtených záznamů, velikost indexu, paralelizace, atp. atp.

    Pokud máte pozitivně potvrzeno, že vnucený seek vám vychází rychleji, ale query optimizer přesto navrhuje scan, potom bych začal u execution planů. Z nich je patrné jaké jsou náklady jednotlivých operací (cost) a z jakých údajů výpočet probíhal (estimated number of rows atp.), což se dá srovnávat s náklady na seek-verzi a hlavně se skutečnými vstupy (Actual Number of Rows, atp.). Dále do toho může vstupovat i paralelizace.

    Každopádně bych se snažil vyhnout hintům a podobným zoufalostem jako čert kříži. To už je lepší využít QueryStore a fixovat execution plan některých dotazů.

    Od rušení parametrického volání a přechod na konstanty bych si také moc nesliboval, spíš naopak.

    Pokud potřebujete dočasně situaci vyřešit v produkci a na testovacím prostředí si to v klidu diagnostikovat, můžete přepnout v database options její compatibility level na starší verzi, třeba SQL2008.

    Pokud správně tuším, odkud vítr vane, pak už na mě máte kontakt (MK) a klidně se mi ozvěte.

    Hezký den


    Robert Haken, Microsoft MVP ASP.NET/IIS, HAVIT, s.r.o., www.havit.cz, http://knowledge-base.havit.cz

    29. září 2017 23:58
    Moderátor

Všechny reakce

  • Zdravím,

    v první řadě bych si rád ověřil, že došlo ke zřetelnému zhoršení výkonu při použití scan místo seek. Rozhodně totiž není pravidlo, že by seek byl vždy rychlejší než scan. Ovlivnit to může rozsah čtených záznamů, velikost indexu, paralelizace, atp. atp.

    Pokud máte pozitivně potvrzeno, že vnucený seek vám vychází rychleji, ale query optimizer přesto navrhuje scan, potom bych začal u execution planů. Z nich je patrné jaké jsou náklady jednotlivých operací (cost) a z jakých údajů výpočet probíhal (estimated number of rows atp.), což se dá srovnávat s náklady na seek-verzi a hlavně se skutečnými vstupy (Actual Number of Rows, atp.). Dále do toho může vstupovat i paralelizace.

    Každopádně bych se snažil vyhnout hintům a podobným zoufalostem jako čert kříži. To už je lepší využít QueryStore a fixovat execution plan některých dotazů.

    Od rušení parametrického volání a přechod na konstanty bych si také moc nesliboval, spíš naopak.

    Pokud potřebujete dočasně situaci vyřešit v produkci a na testovacím prostředí si to v klidu diagnostikovat, můžete přepnout v database options její compatibility level na starší verzi, třeba SQL2008.

    Pokud správně tuším, odkud vítr vane, pak už na mě máte kontakt (MK) a klidně se mi ozvěte.

    Hezký den


    Robert Haken, Microsoft MVP ASP.NET/IIS, HAVIT, s.r.o., www.havit.cz, http://knowledge-base.havit.cz

    29. září 2017 23:58
    Moderátor
  • Dobrý den, tohle zhoršení výkonu u SQL2016 mohu jedině potvrdit.  V mém případě jde o přepnutí mezi SQL2012 a SQL216. Chápal bych drobné rozdíly, ale u mne v některých případech dotazy které na tomtéž stroji, na téže databázi a stejných datech místo řádově desítek vteřin (kompatibilita databáze 110 (2012)) běží desítky minut (kompatibilita 130 (2016)) právě proto, že se sestaví totálně nesmyslný execution plán. To musí být prostě nějaká chyba. Dotaz je napsaný poměrně standardně a pouhé přepnutí kompatibility ho prakticky znefunkční. Tohle mi rozum nebere, chápal bych nějaké drobné rozdíly v řádu jednotek procent...ale toto ??!!. Jak tohle řešit u systému, který je roky vyvíjen a odlaďován postupně od verze SQL2000 až dodnes a obsahuje stovky pohledů, ad hoc dotazů, procedur a funkcí ?. Nikdy žádná nová verze nepřinesla takovouhle potíž. Tohle je snad horší, než přechod na úplně jiný databázový stroj.

    Mluví se o tom...bude to Microsoft nějak řešit ?


    Ludan

    3. listopadu 2017 21:41