Esempio: creare un avviso di SQL Server Agent con il provider WMI

Si applica a: SQL Server

Un utilizzo comune del provider di eventi WMI consiste nel creare avvisi di SQL Server Agent in risposta a eventi specifici. Nell'esempio seguente viene presentato un avviso semplice che salva eventi Deadlock Graph XML in una tabella per l'analisi successiva. SQL Server Agent invia una richiesta WQL, riceve eventi WMI ed esegue un processo in risposta all'evento. Si noti che benché diversi oggetti di Service Broker siano interessati dall'elaborazione del messaggio di notifica, il provider di eventi WMI gestisce i dettagli della creazione e della gestione di tali oggetti.

Esempio

Viene innanzitutto creata una tabella nel database AdventureWorks2022 in cui includere l'evento Deadlock Graph. La tabella è costituita da due colonne: la colonna AlertTime contiene la durata di esecuzione dell'avviso, mentre la colonna DeadlockGraph contiene il documento XML che include l'evento Deadlock Graph.

Viene quindi creato l'avviso. Lo script crea innanzitutto il processo che verrà eseguito dall'avviso, aggiunge un passaggio di processo al processo e fa riferimento al processo all'istanza corrente di SQL Server. Lo script crea quindi l'avviso.

Il passaggio del processo recupera la TextData proprietà dell'istanza dell'evento WMI e lo inserisce nella DeadlockGraph colonna della DeadlockEvents tabella. SQL Server converte in modo implicito la stringa in formato XML. Poiché il passaggio del processo usa il sottosistema Transact-SQL, il passaggio del processo non specifica un proxy.

L'avviso esegue il processo ogni volta che viene registrato un evento di traccia Deadlock Graph. Per un avviso WMI, SQL Server Agent crea una query di notifica utilizzando lo spazio dei nomi e l'istruzione WQL specificati. Per questo avviso, SQL Server Agent esegue il monitoraggio dell'istanza predefinita nel computer locale. L'istruzione WQL richiede tutti gli eventi DEADLOCK_GRAPH nell'istanza predefinita. Per modificare l'istanza monitorata dall'avviso, sostituire il nome dell'istanza per MSSQLSERVER in @wmi_namespace per l'avviso.

USE AdventureWorks2022;
GO

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

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

Aggiungere un processo per l'esecuzione dell'avviso.

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

Aggiungere un passaggio del processo che inserisce l'ora corrente e il grafico deadlock nella DeadlockEvents tabella.

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

Impostare il server di processo per il processo sull'istanza corrente di SQL Server.

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

Aggiungere un avviso che risponde a tutti gli DEADLOCK_GRAPH eventi per l'istanza predefinita. Per monitorare i deadlock per un'istanza diversa, passare MSSQLSERVER al nome dell'istanza.

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

Testare l'esempio

Per visualizzare l'esecuzione del processo, provocare un deadlock. In SQL Server Management Studio aprire due schede query SQL e connettere entrambe le query alla stessa istanza. Eseguire lo script seguente in una delle schede delle query. Questo script produce un set di risultati e viene terminato.

USE AdventureWorks2022;
GO

BEGIN TRANSACTION;
GO

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

Eseguire lo script seguente nella seconda scheda della query. Questo script genera un set di risultati e quindi blocca, in attesa di acquisire un blocco su 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

Eseguire lo script seguente nella prima scheda della query. Questo script si blocca, in attesa di acquisire un blocco su Production.Location. Dopo un breve timeout, SQL Server sceglierà questo script o lo script nell'esempio come vittima del deadlock e terminerà la transazione.

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

Dopo avere provocato il deadlock, attendere alcuni momenti prima che SQL Server Agent attivi l'avviso ed esegua il processo. Esaminare il contenuto della tabella DeadlockEvents eseguendo lo script seguente:

SELECT * FROM DeadlockEvents;
GO

La colonna DeadlockGraph deve contenere un documento XML indicante tutte le proprietà dell'evento Deadlock Graph.

Contenuto correlato

• Concetti relativi al provider WMI per gli eventi del server