ESO9 wiki

Při přechodu aplikační databáze (DB) z MS SQL Serveru 2012 na verzi 2014 (v ESO9 v5.9) může vlivem nového databázového engine dojít k významnému zpomalení některých datových zdrojů. Tento článek popisuje dosud nalezené problémy a jejich řešení.
Obecnější popis diagnostiky výkonových problémů na straně SQL Serveru najdete zde.

• Od SQL 2014 a zejména od SQL 2016 Microsoft zprovoznil CE(Cardinality Estimator), který může způsobovat problémy viz https://www.sqlconsulting.com/news1701.htm:
• Přechod na vyšší verzi SQL Serveru Je potřeba přechod provádět v následujících krocích:
  • Nejprve vše prověřit v testovacím prostředí s těmito základními kroky:
    • Držet se doporučení od Microsoftu (https://docs.microsoft.com/en-us/sql/database-engine/install-windows/change-the-database-compatibility-mode-and-use-the-query-store?view=sql-server-2017). Informace o Query Store např. zde: https://www.wug.cz/zaznamy/472-SQL-Server-Bootcamp-2017-Dotazy-pod-kontrolou-SQL-Server-Query-Store.
  • Po změně „Compatibility Level“ (krok 4) na požadovanou hodnotu např.120 – SQL 2014 navíc doporučujeme:
    • Všechny objekty znovu překompilovat (nejlépe spuštěním verzového skripty + všech doplňků). Výmaz nakešovaných plánů spouštění datových zdrojů lze provést příkazem:

      DBCC FREEPROCCACHE

    • Přegenerovat všechny statistiky lze provést příkazem:

      EXEC sp_updatestats

    • Přegenerovat všechny indexy lze provést příkazem:

      EXEC spUdrzbaIndexu

  • Problematické objekty poté optimalizovat.
• Přejít do ostrého prostředí a aplikovat stejný postup, jako v testovacím prostředí.

Protože aktuálně v ESO9 Start a PAM nevyužíváme žádnou syntaxi, která by CL 120 vyžadovala, je nejjednodušším postupem pro optimalizaci aplikace přechod zpět na CL 110. Tím se vytvoří dostatečný prostor pro prověřování a optimalizaci jednotlivých Profi objektů, které pod CL 120 vykazovaly zpomalení.

Z hlediska výkonu přináší SQL Server 2014 jednu podstatnou změnu a tou je nová verze tzv.Cardinality Estimatoru, pomocí kterého SQL Server sestavuje plán na vykonávání SQL dotazu. Všechny předchozí verze SQL Serveru (resp. v7.0 - v2012) používaly Cardinality Estimator v70, zatímco SQL Server 2014 používá novou verzi v120. Popis fungování nového Cardinality Estimatoru + sadu doporučení najdete v dokumentaci od firmy Microsoft zde.

Pokud má zákazník ve svých Profi objektech některý, jež vyžaduje CL 120 (a tedy návrat k CL 110 již není možný), lze se vrátit zpět i s verzí Cardinality Estimatoru:

• Na MS SQL Serveru 2014 lze nastavit Cardinality Estimator pouze na úrovni celého serveru příkazem:

  DBCC TRACEON (9481,-1)

• Od MS SQL Serveru 2016 lze nastavit Cardinality Estimator na úrovni jednotlivých databází příkazem:

  ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

Nejsnáze řešitelným problémem jsou neexistující indexy. Následující pohled nad stazkami (zjednodušeno) trval na CL=110 zhruba 1 sec:

CREATE VIEW [dbo].[QSTAZKA]
as
SELECT HDOK.* ,
   UCET_OBD,
   TYPDOK.TYP_DOK,
/* tabulka HDOKADD  */
   HDOKADD.IDHDOKADD AS BTHDOKADD,
   HDOKADD.IDHDOK AS RIDHDOK_BTHDOKADD,
  ...
  SUBJEKT.KOD_SUBJEKTU,
  SUBJEKT.SUBJ_NAZEV,

  CASE WHEN VLSTAVHDOK = 10  THEN 7 ELSE IDHDOK_VZOR END AS MF_IDHDOKVZOR,
  'spPostKopie_STAZKA' as MF_EXECHDOK,
  'spPostKopie_STAZKA_SDOK' AS MF_EXECSDOK,
  0 AS MF_NEANOHIS,
  (SELECT MAX(CIS_DOK) FROM hdok HIS WITH(NOLOCK) WHERE HIS.V_SYM_ZALOHY =HDOK.V_SYM_ZALOHY AND HIS.IDHDOK > HDOK.idhdok) AS MF_CIS_DOK_NEW,
  CONCAT(KOD_CENIK,'-',NAZEV_CENIK) AS CENIK

  FROM HDOK with(NoLock)
  LEFT JOIN ...

Po přechodu na CL=120 se zvedla doba vyhodnocení pohledu nad stejnými daty zhruba na 37 sec. Rozborem Execution Planu se zjistilo, že vnořený select nemá k dispozici správný index a provádí Index Scan nad velkým počtem řádků:

(SELECT MAX(CIS_DOK) FROM hdok HIS WITH(NOLOCK) WHERE HIS.V_SYM_ZALOHY =HDOK.V_SYM_ZALOHY AND HIS.IDHDOK > HDOK.idhdok) AS MF_CIS_DOK_NEW

Po doplnění indexu se dotaz zrychlil na původní cca 1 sec:

CREATE NONCLUSTERED INDEX HDOK_V_SYM_ZALOHY_CIS_DOK ON dbo.HDOK (V_SYM_ZALOHY,CIS_DOK)