Exemple : Créer une alerte SQL Server Agent avec le fournisseur WMI

S'applique à : SQL Server

Une façon courante d'utiliser le fournisseur d'événements WMI consiste à créer des alertes de l'Agent SQL Server qui répondent à des événements spécifiques. L'exemple suivant présente une alerte simple qui enregistre les événements du graphique de blocage XML dans une table pour leur analyse ultérieure. L'Agent SQL Server soumet une demande WQL, reçoit des événements WMI et exécute un travail en réponse à l'événement. Remarquez que, bien que plusieurs objets Service Broker soient impliqués dans le traitement du message de notification, le fournisseur d'événements WMI gère les détails de la création et de la gestion de ces objets.

Exemple

En premier lieu, une table est créée dans la base de données AdventureWorks2022 pour contenir l'événement du graphique du blocage. La table contient deux colonnes : la colonne AlertTime contient l'heure à laquelle l'alerte s'exécute et la colonne DeadlockGraph contient le document XML qui inclut le graphique du blocage.

Ensuite, l'alerte est créée. Le script crée d’abord le travail que l’alerte exécutera, ajoute une étape de travail au travail et cible le travail à l’instance actuelle de SQL Server. Le script crée alors l'alerte.

L’étape de travail récupère la TextData propriété de l’instance d’événement WMI et insère cette valeur dans la DeadlockGraph colonne de la DeadlockEvents table. SQL Server convertit implicitement la chaîne au format XML. Étant donné que l’étape de travail utilise le sous-système Transact-SQL, l’étape de travail ne spécifie pas de proxy.

L'alerte exécute le travail chaque fois qu'un événement de trace du graphique du blocage est consigné. Pour une alerte WMI, l'Agent SQL Server crée une requête de notification à l'aide de l'espace de noms et de l'instruction WQL spécifiés. Pour cette alerte, l'Agent SQL Server analyse l'instance par défaut sur l'ordinateur local. L'instruction WQL demande un événement DEADLOCK_GRAPH quelconque dans l'instance par défaut. Pour modifier l'instance que l'alerte surveille, substituez le nom de l'instance pour MSSQLSERVER dans le @wmi_namespace pour l'alerte.

USE AdventureWorks2022;
GO

IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL
DROP TABLE DeadlockEvents;
GO

CREATE TABLE DeadlockEvents (
    AlertTime DATETIME,
    DeadlockGraph XML
);
GO

Ajoutez un travail pour que l’alerte s’exécute.

EXEC msdb.dbo.sp_add_job @job_name = N'Capture Deadlock Graph',
    @enabled = 1,
    @description = N'Job for responding to DEADLOCK_GRAPH events';
GO

Ajoutez une étape de travail qui insère l’heure actuelle et le graphique d’interblocage dans la DeadlockEvents table.

EXEC msdb.dbo.sp_add_jobstep @job_name = N'Capture Deadlock Graph',
    @step_name = N'Insert graph into LogEvents',
    @step_id = 1,
    @on_success_action = 1,
    @on_fail_action = 2,
    @subsystem = N'TSQL',
    @command = N'INSERT INTO DeadlockEvents
                (AlertTime, DeadlockGraph)
                VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData)))'')',
    @database_name = N'AdventureWorks2022';
GO

Définissez le serveur de travaux pour le travail sur l’instance actuelle de SQL Server.

EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture Deadlock Graph';
GO

Ajoutez une alerte qui répond à tous les DEADLOCK_GRAPH événements de l’instance par défaut. Pour surveiller les interblocages d’une autre instance, remplacez MSSQLSERVER le nom de l’instance.

EXEC msdb.dbo.sp_add_alert @name = N'Respond to DEADLOCK_GRAPH',
    @wmi_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
    @wmi_query = N'SELECT * FROM DEADLOCK_GRAPH',
    @job_name = 'Capture Deadlock Graph';
GO

Tester l’exemple

Pour voir le travail s'exécuter, provoquez un blocage. Dans SQL Server Management Studio, ouvrez deux onglets requête SQL et connectez les deux requêtes à la même instance. Exécutez le script ci-dessous sous l'un des onglets de requête. Ce script produit un jeu de résultats et se termine.

USE AdventureWorks2022;
GO

BEGIN TRANSACTION;
GO

SELECT TOP (1) Name
FROM Production.Product WITH (XLOCK);
GO

Exécutez le script suivant dans le deuxième onglet de requête. Ce script produit un jeu de résultats, puis bloque, en attendant d’acquérir un verrou sur Production.Product.

USE AdventureWorks2022;
GO

BEGIN TRANSACTION;
GO

SELECT TOP (1) Name
FROM Production.Location WITH (XLOCK);
GO

SELECT TOP (1) Name
FROM Production.Product WITH (XLOCK);
GO

Exécutez le script suivant dans le premier onglet de requête. Ce script bloque, en attendant d’acquérir un verrou sur Production.Location. Après un court délai d’attente, SQL Server choisit ce script ou le script dans l’exemple comme victime d’interblocage et termine la transaction.

SELECT TOP(1) Name FROM Production.Location WITH (XLOCK);
GO

Après avoir provoqué le blocage, attendez un certain temps que l'Agent SQL Server active l'alerte et exécute le travail. Examinez le contenu de la table DeadlockEvents en exécutant le script suivant :

SELECT * FROM DeadlockEvents;
GO

La colonne DeadlockGraph doit contenir un document XML qui indique toutes les propriétés de l'événement du graphique du blocage.

Contenu connexe

• Concepts du fournisseur WMI pour les événements de serveur