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.