De nouveaux jouets/outils intéressants ont été mis à disposition des professionnels des données. Parmi ces outils, on trouve le magasin de données de requête et l’optimisation automatique. Ces deux outils vont en réalité de pair et fonctionnent très bien ensemble. Comme pour la plupart des nouveaux outils, il y a généralement un manuel d’instructions accompagné d’une section sur la résolution des problèmes liés à l’outil. En plus du manuel, on dispose généralement d’un guide indiquant si l’outil fonctionne selon les spécifications souhaitées ou non.
Grâce aux événements étendus (XE), nous avons accès à un guide qui nous aidera à mieux comprendre si notre nouvel outil fonctionne comme prévu. XE opérationnel fournit plusieurs événements pour nous aider à évaluer l’utilisation de l’optimisation automatique dans SQL Server.
Pour trouver ces événements, nous pouvons simplement exécuter une requête comme celle-ci :
select xo.name, xo.object_type, xo.description
From sys.dm_xe_objects xo
where xo.name like '%autom%tun%'
or xo.description like '%autom%tun%'
order by xo.object_type;Lorsqu’elle est exécutée, cette requête renvoie un ensemble de résultats similaire à celui-ci :
Dans l’ensemble rouge, il y a quatre événements utiles pour le diagnostic et la surveillance de l’optimisation automatique. Ces événements affichent les erreurs, les données de diagnostic (et de performance), les modifications de configuration et les modifications d’état. Par exemple, l’événement de changement d’état se déclenchera lorsque l’optimisation automatique est activée et se déclenchera également lorsque la base de données est démarrée (si la session est en cours d’exécution).
L’événement automatic_tuning_diagnostics se déclenche environ toutes les 30 minutes sur mon serveur pour collecter des données de performance et de diagnostic qui peuvent m’aider à comprendre comment cette fonctionnalité fonctionne pour ma charge de travail dans chaque base de données.
Mis en évidence dans la section verte, il y a quelques cartes qui montrent les différentes valeurs pour la phase ou l’état actuel de l’optimisation automatique pour chaque base de données. On peut afficher ces différentes valeurs avec la requête suivante :
select mv.name,mv.map_key,mv.map_value
From sys.dm_xe_map_values mv
where mv.name in ('aprc_state','aprc_task')
order by mv.name, mv.map_key;Cette requête renvoie ces résultats :
Nous avons vu certains des événements et certaines des cartes en un coup d’œil très rapide. Cela dit, il est temps de tout rassembler et de créer une session.
USE master;
GO
-- Créer la session d'événements
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'AutoTuneMonitor' )
DROP EVENT SESSION AutoTuneMonitor
ON SERVER;
GO
EXECUTE xp_create_subdir 'C:\Database\XE';
GO
CREATE EVENT SESSION [AutoTuneMonitor] ON SERVER
ADD EVENT qds.automatic_tuning_config_change(
ACTION ( sqlserver.database_id,
sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)
)
,ADD EVENT qds.automatic_tuning_diagnostics(
ACTION ( sqlserver.database_id,
sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)
)
,ADD EVENT qds.automatic_tuning_state_change(
ACTION ( sqlserver.database_id,
sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)
)
,ADD EVENT qds.automatic_tuning_error(
ACTION ( sqlserver.database_id,
sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)
)
ADD TARGET package0.event_file(SET filename=N'C:\Database\XE\AutoTuneMonitor.xel')
,ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY=5 SECONDS,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF);
GO
ALTER EVENT SESSION [AutoTuneMonitor] ON SERVER STATE = START;
GOÉtant donné que cette session ne produira aucun résultat sans que le magasin de données de requête ne soit activé et que l’optimisation automatique ne soit configurée pour une base de données, j’ai tout configuré dans une base de données de démonstration et j’ai quelques résultats frais à afficher.
Ici, je montre un exemple de sortie filtrée uniquement pour l’événement de diagnostic. Notez que le phase_code affiche certaines de ces valeurs de carte dont nous avons déjà parlé. Je peux également voir qu’environ toutes les 30 minutes, chaque base de données subit une vérification de diagnostic.
Maintenant, en regardant un autre événement dans cette même session, je peux voir ce qui suit. Le state_code dans cette charge d’événement démontre davantage de valeurs provenant des cartes dont nous avons déjà parlé (CorrectionEnabled et DetectionEnabled). Dans ce cas, l’événement automatic_tuning_state_change s’est déclenché plusieurs fois pour la base de données 6 car cette base de données a été intentionnellement mise hors ligne puis remise en ligne pour tester l’événement.
L’utilisation de ces événements particuliers dans cette session est très légère. Je n’ai pas configuré de prédicat pour aucun des événements car je voulais tout capturer. Bien sûr, le nombre d’événements peut augmenter avec une charge accrue et des scénarios d’utilisation sur différents serveurs.
L’optimisation automatique peut être un outil très utile dans votre boîte à outils pour devenir un DBA de rock-star. Au fur et à mesure que vous affûtez vos compétences avec cet outil, vous aurez besoin d’informations d’utilisation et de diagnostic à portée de main pour vous assurer que tout fonctionne correctement. Cette session d’événements est capable de fournir ces informations de diagnostic et de vous maintenir au top du moteur d’optimisation automatique.
Pour plus d’utilisations des événements étendus, je recommande ma série d’articles conçus pour vous aider à apprendre XE petit à petit. Vous êtes intéressé par la puissance de XE par rapport à Profiler ? Jetez un coup d’œil à celui-ci !
Ceci est le onzième article de la série “12 jours de Noël” 2018. Pour une liste complète des articles, visitez cette page.