Présentation JSS2015 - Le Query Store de SQL Server 2016

#JSS2015

Les journées

SQL Server 2015

Un événement organisé par GUSS

@GUSS_FRANCE

#JSS2015

Les journées

SQL Server 2015

Un événement organisé par GUSS

Session - Le query store, le (nouveau)

meilleur ami du DBA

Benjamin Vesan

Guillaume Nocent

#JSS2015

Merci à nos sponsors

#JSS2015

• Benjamin Vesan – Cap DataMVP SQL Server

DBA depuis 2001 – SQL 6.5

[email protected]

@captain_BV

http://blog.capdata.fr/

• Guillaume Nocent – ViatéaDBA depuis 1999 – SQL 6.0

[email protected]

@gnocent_sql

http://www.dba-sqlserver.fr/

Présentation speakers

mailto:[email protected]

http://blog.capdata.fr/

mailto:[email protected]

http://www.dba-sqlserver.fr/

#JSS2015

• Introduction

• La régression de performance

• Les outils à disposition jusqu’ici

• Le Query Store de SQL Server 2016

• Les limites de la « V1 »

• Quelques cas potentiels d’utilisation

Agenda

#JSS2015

Fonctionnalité à venir dans SQL 2016.

Basé sur plusieurs sources :- Documentation Microsoft

- Présentation PASS et SQLBITS de Conor Cunningham

- Plusieurs articles de blog ou présentations de Microsoft et MVP

- Nos tests réalisés sur SQL Server 2016 CTP3

Introduction

#JSS2015

Une réalité dans la vie de nos bases• Utilisateurs remontent lenteurs

- Sur certaines actions

- À certains moments

• Blocage de la production « plus rien ne répond »

• Crainte face aux changements de version/patches

(logiciels internes ou SQL Server)

La régression de performance

#JSS2015

Les attentes (encore et toujours)• Observer les attentes permettra d’identifier des

goulots d’étranglement (saturation CPU ou disque,

concurrence de verrous, …).

• Une ou plusieurs requêtes pourront apparaître

comme très « consommatrices ».

• Reste à répondre à la question « Qu’est-ce qui a

changé ? »


#JSS2015

Plan d’exécution : instable par nature• Généré lorsqu’aucun plan en cache n’est trouvé

• Dépend :

– des Statistiques sur les objets

– du Modèle Physique de chaque objet

– de la valeur des variables

– de la configuration de la session

– de la configuration de l’instance


#JSS2015

Plan d’exécution : instable par nature• Invalidé lorsque:

– les statistiques sont considérées obsolètes

– le modèle physique d’un objet est modifié

– SQLOS décide de le supprimer de la mémoire

– l’instance redémarre

• Le nouveau plan peut être totalement différent de l’ancien

(effets de seuils, valeurs des variables, stats différentes)

• On ne peut déterminer combien de temps un plan sera utilisé !


#JSS2015

DÉMO 1 – INSTABILITE DES PLANS


#JSS2015

Résumé de la démo n°1

Le Query Store de SQL Server 2016

Démonstration des problématiques d’instabilité de plan : modification

d’une table de démonstration avec une répartition très déséquilibrée

(une ligne contre 113442 selon la valeur choisie).

Selon le premier appel effectué après perte du plan (valeur transmise), le

nouveau plan généré sera très différent et réutilisé les fois suivantes

(même pour d’autres paramètres).

Ensuite, beaucoup de situations peuvent invalider un plan (modification

de + de 20% de la volumétrie, entrainant un auto update stats, même si

c’est rollbacké, ou alter database même pour des paramètres n’affectant

pas l’optimizer).

Possibilité de réorienter un nouveau plan, soit par une directive (« hint »),

soit par plan_guide (forçage d’un hint en externe, ou d’un plan complet).

#JSS2015

MonitoringDepuis la version 2005, les outils sont nombreux pour :

• Identifier un problème « à chaud »

– DMVs

– Rapports SSMS

– Activity Monitor

• Suivre l’activité sur la durée

– Trace profiler ou XEvents

– Trace PerfMon / SQLDiag / RML Utilities (PAL)

– Performance Datawarehouse

Aucun de ces outils n’est orienté « suivi du changement ».

Les outils à disposition jusqu’ici

Branchés en

permanence sur la

production ?

#JSS2015

Actions correctivesDes outils existent, avec différents niveaux de facilité et de risque :

• Directives dans une requête (maxdop/index/jointures/optimize for)

– Nécessite l’accès au code d’une requête

• Plan Guides

– Risqué à long terme

• Recompile

– Surcoût à chaque exécution


#JSS2015

Actions correctivesDes outils existent, avec différents niveaux de facilité et de risque :

• Directives dans une requête (maxdop/index/jointures/optimize for)

– Nécessite l’accès au code d’une requête

• Plan Guides

– Risqué à long terme

• Recompile

– Surcoût à chaque exécution


#JSS2015

Query Store: 1. collecte des informations• Plan d’exécution pour chaque requête lors de la compilation («plan store »)

• Statistiques d’exécution pour toutes les requêtes (« runtime stats store »)

• Agrégat et purge paramétrables

(Présentation QS par Conor Cunningham)


#JSS2015

Query Store: 1. collecte des informations

ALTER DATABASE MaBase SET QUERY_STORE = ON -- OFF

(

OPERATION_MODE = READ_WRITE, -- READ_ONLY

CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),

DATA_FLUSH_INTERVAL_SECONDS = 30,

MAX_STORAGE_SIZE_MB = 1000,

INTERVAL_LENGTH_MINUTES = 5,

SIZE_BASED_CLEANUP_MODE = AUTO, --OFF

QUERY_CAPTURE_MODE = ALL, -- AUTO / NONE

MAX_PLANS_PER_QUERY = 10

)

GO


Collecte de données activée ou nonNombre de jours où l’on conserve

les informations d’une requête

Intervalle d’écriture

(persistance asynchrone)Taille maximale occupée par

le Query Store dans la base

Intervalle d’agrégation des informations

statistiques sur les exécutions

1, 5, 10, 15, 30, 60, ou 1440

Si sur AUTO, dès que l’occupation dépassera 90%,

supprimera les requêtes les moins coûteuses et plus

anciennes, jusqu’à passer sous les 80% d’espace occupéALL : capture tout

AUTO : ignore les requêtes considérées comme mineures

NONE : ne capture que les statistiques d’exécution des

requêtes déjà capturées

Nombre maximal de plans capturés par requête

#JSS2015

Query Store: 2. Restitution des informationsLes DMVs suivantes (nécessitent « VIEW DATABASE STATE »)sys.database_query_store_options

sys.query_context_settings (à utiliser avec sys.dm_exec_plan_attributes)

sys.query_store_plan

sys.query_store_query

sys.query_store_query_text (attention aux valeurs de la colonne « has_restricted_text »)

sys.query_store_runtime_stats

sys.query_store_runtime_stats_interval

Beaucoup d’exemples de requêtes sur l’URL :

Monitoring Performance By Using the Query Store

https://msdn.microsoft.com/en-us/library/dn817826.aspx


https://msdn.microsoft.com/en-us/library/dn817826.aspx

#JSS2015

Query Store: 3. Choix d’un planLes Procédures Stockées :

• sp_query_store_force_plan

(Valide tant que le QS est actif, survit à un redémarrage et une restauration)

• sp_query_store_unforce_plan

Attention, différences entre forçage de plan et plan guide !

Plan guide plus souple (forçage externe hint, création depuis un autre env …), mais

très complexe et fastidieux à implémenter

Query Store force plan ne peut forcer qu’un plan qu’il a déjà rencontré sur une

requête telle qu’elle est écrite !


#JSS2015

DÉMO 2 - ACTIVATION

Usage dans SSMS et gestion

#JSS2015

Résumé de la démo n°2Démonstration de l’activation du Query Store en ligne de commande.

Vérification par requêtes que le Query Store ne renvoie rien si la session de l’utilisateur n’est pas

sur la base où est activé le QS (ex: si on est sur master ou tempdb, même si on requête les objets

d’autres bases).

Rappel du côté Read_Write ou Read_Only (notamment passage en RO si plus de place).

Requête type :SELECT q.query_id, qt.query_text_id, qt.query_sql_text,

SUM(rs.count_executions) AS total_execution_count

FROM sys.query_store_query_text AS qt

JOIN sys.query_store_query AS q

ON qt.query_text_id = q.query_text_id

JOIN sys.query_store_plan AS p

ON q.query_id = p.query_id

JOIN sys.query_store_runtime_stats AS rs

ON p.plan_id = rs.plan_id

GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text

ORDER BY total_execution_count DESC;


#JSS2015

DÉMO 3 - INVESTIGATION


#JSS2015

Résumé de la démo n°3Démonstration d’un cas de parameter sniffing avec localisation facile de la requête incriminée et des deux plans.

Présentation des 4 panneaux du Query Store dans SSMS :

- Overall Resources comsumption (choix des indicateurs/périodes)

- Top resources consumer (choix du critère du top 25, de la métrique remontée, …)

- Regressed queries (périodes : récente et historique, avec remontée des requêtes dont le comportement a changé)

- Tracked queries (obtenue par clic sur une requête, permet de voir côte à côte tous les plans, par exemple)

Proposition de fix par création d’un index (qui crééra un 3ème plan, mais toujours instable à cause de la date trop sélective parfois), ainsi que de l’ajout du hint « optimize for unknown » dans la requête.

Démonstration du Live Query Statistics

Nécessite « SET STATISTICS XML ON » ou « SET STATISTICS PROFILE ON; » (session) / ou activation globale de l’XE query_post_execution_showplan (pb perfs !!!)

Observation d’un cas de sur-itération d'udf et démonstration de son repérage via :

select object_name(FS.object_id), FS.execution_count, FS.last_execution_time, * from sys.dm_exec_function_stats FS

where FS.database_id=db_id() order by FS.execution_count DESC, FS.last_execution_time DESC


#JSS2015

Résumé de la démo n°3 – Captures d’écran


#JSS2015

DÉMO 4 - ACTION


#JSS2015

Résumé de la démo n°4Possibilité de forcer des plans via l’interface d’un simple clic sur le plan déjà capturer que l’on veut

garantir (qui apparaît alors avec une petite coche). Cela ne créé pas de plan guide.

Si on effectue un changement qui rend ce plan impossible à appliquer, on pourra retrouver la

remontée d’erreur via une requête ou le XEvent « query_store_plan_forcing_failed » :

CREATE EVENT SESSION [ForcePlanQueryStore_Failed] ON SERVER

ADD EVENT qds.query_store_plan_forcing_failed

ADD TARGET package0.ring_buffer(SET max_memory=(102400))

WITH (STARTUP_STATE=ON)

GO

select P.last_force_failure_reason_desc, P.*

from sys.query_store_plan P

where P.query_id=45

order by P.last_execution_time DESC

GO


#JSS2015

Résumé de la démo n°4 – Captures d’écran


#JSS2015

• Inconnue sur les éditions qui l’inclueront au-delà de

Standard et Enterprise

• Stocké dans la base du contexte … et pas activable sur

master ou tempdb !

• Db_name harcodé, à une restauration, mais pas à un

renommage

• Supporte in-memory, mais avec restrictions (moins

depuis la CTP3)

Mais … Limites de la « V1 »

#JSS2015

• Le bon plan doit avoir été exécuté pour pouvoir être forcé (comment y parvenir ? Plan_guide ??? ;-) )

• Pas de lien avec l’usager (login/machine/…) qui sont à l’origine du workload (limite potentielle dans les critères de recherche)

• Attention, id de req/plan != ceux des dm_exec_*

Mais possibilité de retrouver à partir du texte de req donné

par le QS via la fn : sys.fn_stmt_sql_handle_from_sql_stmt

Ex :

Mais … Limites de la « V1 »

SELECT qt.query_text_id, q.query_id, qt.query_sql_text, qt.statement_sql_handle, q.context_settings_id, qs.statement_context_idFROM sys.query_store_query_text AS qtINNER JOIN sys.query_store_query AS q

ON qt.query_text_id = q.query_idCROSS APPLY sys.fn_stmt_sql_handle_from_sql_stmt (qt.query_sql_text, null) AS fn_handle_from_stmtINNER JOIN sys.dm_exec_query_stats AS qs

ON fn_handle_from_stmt.statement_sql_handle = qs.statement_sql_handle;

#JSS2015

• « Batch de nuit », valeurs de variables différentes selon la plage horaire

d’exécution d’une même requête

1 plan forcé par plage horaire différente

• Mise en production complexe et peu validée

Changement de Compat / TraceFlag 4199

Effectuer un jeu de test dans le compat level source puis cible, comparer les

résultats (dans SSMS ou via requêtes)

Eviter la régression en forçant les plans des requêtes critiques

• Possibilité d’obtenir facilement une vue complète du comportement

d’une production, à distance par l’intermédiaire d’une simple sauvegarde

Quelques cas potentiels d’utilisation

#JSS2015

Vos questions

Vos remarques

#JSS2015#JSS2015

Les évaluations des sessions,

c’est important !!

http://GUSS.Pro/jss

#JSS2015

Merci à nos volontaires…

#JSS2015#JSS2015

#JSS2015

Scripts SQL des démos :

Technology

Présentation JSS2015 - Le Query Store de SQL Server 2016