Často řešené problémy po přechodu na MS SQL 2014

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.

Obecná doporučení

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í.

Návrat k Compatibility Levelu 110

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í.

Cardinality Estimator

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.

Návrat k Cardinality Estimator v70

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;

Řešené problémy z praxe

Nové indexy

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)