Tracciamento delle procedure SQL (Tracing Stored Procedures)

In questa guida verrà mostrato come creare un processo di trace che monitori tutte le Stored Procedure invocate su una base dati Microsoft SQL Server, e le relative tecniche di estrazione e lettura delle informazioni di tracciamento ottenute.

Il monitoraggio si suddivide in due fasi: una prima fase di scrittura in cui vengono salvate le informazioni man mano che la base dati viene interrogata, mentre la seconda fase riguarda la lettura delle informazioni raccolte.

Fase 1: Scrittura file di tracciamento

Il primo passaggio è quello di creare la procedura di trace; eseguire lo script SQL riportato di seguito selezionando come target la base dati che si vuole monitorare.
Valorizzare le due variabili @tracefile e @database:

DECLARE @tracefile NVARCHAR(MAX) = N'<trace file path>'
DECLARE @database NVARCHAR(MAX) = N'<database name>'

-- Crea una coda
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
SET @maxfilesize = 10

EXEC @rc = sp_trace_create @TraceID OUTPUT, 2, @tracefile, @maxfilesize, NULL 
IF (@rc != 0) GOTO error

-- Settaggio eventi
DECLARE @on BIT
SET @on = 1
EXEC sp_trace_setevent @TraceID, 43, 6, @on
EXEC sp_trace_setevent @TraceID, 43, 8, @on
EXEC sp_trace_setevent @TraceID, 43, 10, @on
EXEC sp_trace_setevent @TraceID, 43, 11, @on
EXEC sp_trace_setevent @TraceID, 43, 12, @on
EXEC sp_trace_setevent @TraceID, 43, 14, @on
EXEC sp_trace_setevent @TraceID, 43, 34, @on
EXEC sp_trace_setevent @TraceID, 43, 35, @on

-- Settaggio filtri
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT

EXEC sp_trace_setfilter @TraceID, 35, 0, 6, @database

-- Imposta lo stato del tracciamento a 1 per avviarlo
EXEC sp_trace_setstatus @TraceID, 1

-- Mostra l'identificativo del tracciamento per poterlo referenziare in futuro
SELECT TraceID = @TraceID
GOTO finish

error: 
SELECT ErrorCode = @rc

finish: 
GO

<trace file path> = percorso assoluto in cui verrà salvato il file di tracciamento (file .trc).
<database name> = database monitorato.

Eseguendo quindi la procedura, il tracciamento sarà attivo fino ad uno stop manuale.

Fase 2: Lettura file di tracciamento

Per interrogare il trace log file generato, è possibile “parcheggiare” il contenuto del file all’interno di una tabella SQL, facilitandone l’analisi e le interrogazioni.

Utilizzando quindi l’istruzione:

DECLARE @tracefile NVARCHAR(MAX) = N'<trace file path>'
SELECT * INTO trace_table
	FROM fn_trace_gettable(@tracefile, DEFAULT);

dove @tracefile identifica il percorso del file trace log file (.trc), esattamente come impostato nella fase 1. Viene così creata una nuova tabella chiamata trace_table che conterrà tutte le informazioni del file di tracciamento.

Successivamente è possibile effettuare interrogazioni sulla nuova tabella per ottenere le informazioni volute.

Esempio: per ottenere l’elenco delle procedure invocate almeno una volta e relativo timestamp di ultima chiamata:

SELECT x.ObjectName AS StoredProcedureName, MAX(x.StartTime) AS LastCall         
    FROM trace_table x JOIN (
		SELECT p.ObjectName, MAX(StartTime) AS max_StartTime
            FROM trace_table p
			GROUP BY p.ObjectName) y ON y.ObjectName = x.ObjectName 
		AND y.max_StartTime = x.StartTime
	GROUP BY x.ObjectName, x.StartTime

Comandi di supporto per la gestione delle procedure di trace

E’ possibile utilizzare una serie di comandi SQL per gestire i tracciamenti attivi su Microsoft SQL Server. Ecco alcuni esempi:

Ottenere l’elenco dei trace

SELECT * FROM ::fn_trace_getinfo(default)

Terminare uno specifico trace dato un identificativo

EXEC sp_trace_setstatus @traceid, 0

Terminare ed eliminare uno specifico trace dato un identificativo

EXEC sp_trace_setstatus @traceid, 2

Altre informazioni è possibile trovarle tramite la documentazione ufficiale SQL Trace Microsoft.

Leave a Reply

Your email address will not be published. Required fields are marked *