sqlog

Monitoring utilizzo oggetti SQL Server

Tenere in ordine gli oggetti presenti in un database, avendo cura periodicamente di eliminare tabelle, viste o procedure / funzioni non più utili, è da sempre un operazione un po critica e in alcuni casi anche abbastanza fastidiosa. Qualche giorno fa mi è stato chiesto di gestire una situazione molto simile alla suddetta. Era arrivato il momento di fare pulizia su uno dei database di supporto più grossi. Obbiettivo eliminare oggetti non più utilizzati e fare un censimento di quelli ancora utilizzati (per un eventuale aggiornamento).
Serviva monitorare l'utilizzo del suddetto database, procurando il meno "fastidio" possibile agli utenti.
Il Profiler in questi casi torna davvero utile.

Gli eventi che dobbiamo gestire sono (avendo cura di attivare tutte le colonne, vedere img.):

• Audit Schema Object Access Event
• Audit Database Object Access Event

A questo punto filtriamo solo i dati che riguardano il database che vogliamo monitorare (inserendo un filtro sulla colonna DatabaseName).


A questo punto possiamo generarci un template ScriptSQL (vedere img.) che andremo a parametrizzare correttamente e che quindi scheduleremo per mezzo di uno job.


Generato lo script apriamolo con query analayzer e procediamo con il configurarlo. Quattro sono le procedure che vengono utilizzate (e ben documentate su MSDN che vi invito ad consultare per ulteriori info):

• sp_trace_create: creare il trace
• sp_trace_setevent: impostare gli eventi da traccaire (e relative colonne)
• sp_trace_setfilter: impostare eventuali filtri
• sp_trace_setstatus: attivare / disattivare il trace

Per provare possiamo impostare a 2 il secondo parametro (attivando cosi il rollover del file) e inseriamo il path e relativo nome file del trace che andremo a generare:
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL  (impostazioni originali)
exec @rc = sp_trace_create @TraceID output, 2, N'\\nomeShare\FolderTrace\FileName', @maxfilesize, NULL

Per monitorare lo stato dei vari trace attivi, possiamo ricorrere alla seguente funzione di sistema:

select
    traceid,
    case
        when [property] = 1 then 'Opzioni della traccia'
        when [property] = 2 then 'Nome del file'
        when [property] = 3 then 'Dimensioni massime'
        when [property] = 4 then 'Ora di interruzione'
        when [property] = 5 then 'Stato corrente della traccia'
        else convert(varchar(50), [property]) + ' - ND'
    end
    as [property_descr],
    [property],
    [value]
from fn_trace_getinfo ( 0 )

Il traceid=1 è da considerarsi di sistema.

Vediamo un esempio di script di schedulazione completo:

    --Per prima cosa provvedo a stoppare eventuali trace attivi in precedenza...
    DECLARE @TraceID_ToStop AS INT
    select @TraceID_ToStop = 0
   
    --Recupero il traceid
    select @TraceID_ToStop = isnull(traceid, 0)
    from fn_trace_getinfo ( 0 )
    WHERE CONVERT(NVARCHAR(255), [value]) LIKE '%Profiler_Cambusa_Log_%_.trc' and traceid > 1    --Filtro sul nome file del trace per recuperare eventuali trace attivi.

    if(select @TraceID_ToStop) > 1
    begin
        --Arresto ed elimino trace...
        exec sp_trace_setstatus @TraceID_ToStop, 0
        exec sp_trace_setstatus @TraceID_ToStop, 2
        print 'Stoped TraceID: ' + convert(varchar(50), @TraceID_ToStop)
    end
    --------------------------------------------------------------

    DECLARE @DateNow AS NVARCHAR(500)
    DECLARE @PathFileLog AS NVARCHAR(255)
    SET @DateNow = CONVERT(VARCHAR(255), GETDATE(), 112)
    SET @PathFileLog = 'F:\Profile\Cambusa\Profiler_Cambusa_Log_' + @DateNow + '_'        --Preparo il path e il nome file del trace.
    PRINT 'PathFileLog: ' + @PathFileLog


    -- Create a Queue
    declare @rc int
    declare @TraceID int
    declare @maxfilesize bigint
    set @maxfilesize = 50

    exec @rc = sp_trace_create @TraceID output, 2, @PathFileLog, @maxfilesize, NULL
    if (@rc != 0) goto error

    -- Client side File and Table cannot be scripted

    -- Set the events
    declare @on bit
    set @on = 1
    exec sp_trace_setevent @TraceID, 180, 7, @on
    exec sp_trace_setevent @TraceID, 180, 23, @on
    exec sp_trace_setevent @TraceID, 180, 8, @on
    exec sp_trace_setevent @TraceID, 180, 40, @on
    exec sp_trace_setevent @TraceID, 180, 64, @on
    exec sp_trace_setevent @TraceID, 180, 1, @on
    exec sp_trace_setevent @TraceID, 180, 41, @on
    exec sp_trace_setevent @TraceID, 180, 49, @on
    exec sp_trace_setevent @TraceID, 180, 6, @on
    exec sp_trace_setevent @TraceID, 180, 10, @on
    exec sp_trace_setevent @TraceID, 180, 14, @on
    exec sp_trace_setevent @TraceID, 180, 26, @on
    exec sp_trace_setevent @TraceID, 180, 34, @on
    exec sp_trace_setevent @TraceID, 180, 50, @on
    exec sp_trace_setevent @TraceID, 180, 3, @on
    exec sp_trace_setevent @TraceID, 180, 11, @on
    exec sp_trace_setevent @TraceID, 180, 19, @on
    exec sp_trace_setevent @TraceID, 180, 35, @on
    exec sp_trace_setevent @TraceID, 180, 51, @on
    exec sp_trace_setevent @TraceID, 180, 4, @on
    exec sp_trace_setevent @TraceID, 180, 12, @on
    exec sp_trace_setevent @TraceID, 180, 28, @on
    exec sp_trace_setevent @TraceID, 180, 60, @on
    exec sp_trace_setevent @TraceID, 180, 29, @on
    exec sp_trace_setevent @TraceID, 180, 37, @on
    exec sp_trace_setevent @TraceID, 114, 7, @on
    exec sp_trace_setevent @TraceID, 114, 23, @on
    exec sp_trace_setevent @TraceID, 114, 8, @on
    exec sp_trace_setevent @TraceID, 114, 40, @on
    exec sp_trace_setevent @TraceID, 114, 64, @on
    exec sp_trace_setevent @TraceID, 114, 1, @on
    exec sp_trace_setevent @TraceID, 114, 9, @on
    exec sp_trace_setevent @TraceID, 114, 41, @on
    exec sp_trace_setevent @TraceID, 114, 49, @on
    exec sp_trace_setevent @TraceID, 114, 2, @on
    exec sp_trace_setevent @TraceID, 114, 10, @on
    exec sp_trace_setevent @TraceID, 114, 26, @on
    exec sp_trace_setevent @TraceID, 114, 34, @on
    exec sp_trace_setevent @TraceID, 114, 50, @on
    exec sp_trace_setevent @TraceID, 114, 3, @on
    exec sp_trace_setevent @TraceID, 114, 11, @on
    exec sp_trace_setevent @TraceID, 114, 19, @on
    exec sp_trace_setevent @TraceID, 114, 35, @on
    exec sp_trace_setevent @TraceID, 114, 51, @on
    exec sp_trace_setevent @TraceID, 114, 59, @on
    exec sp_trace_setevent @TraceID, 114, 4, @on
    exec sp_trace_setevent @TraceID, 114, 12, @on
    exec sp_trace_setevent @TraceID, 114, 28, @on
    exec sp_trace_setevent @TraceID, 114, 44, @on
    exec sp_trace_setevent @TraceID, 114, 60, @on
    exec sp_trace_setevent @TraceID, 114, 5, @on
    exec sp_trace_setevent @TraceID, 114, 21, @on
    exec sp_trace_setevent @TraceID, 114, 29, @on
    exec sp_trace_setevent @TraceID, 114, 37, @on
    exec sp_trace_setevent @TraceID, 114, 6, @on
    exec sp_trace_setevent @TraceID, 114, 14, @on


    -- Set the Filters
    declare @intfilter int
    declare @bigintfilter bigint

    exec sp_trace_setfilter @TraceID, 35, 0, 6, N'DbNameTest_1'                           --Filtro solo attività sul seguente db
    exec sp_trace_setfilter @TraceID, 34, 0, 7, N'OggettoDaEscludereDalMonitoring'        --escludo eventuali oggetti

    -- Set the trace status to start
    exec sp_trace_setstatus @TraceID, 1

    -- display trace id for future references
    select TraceID=@TraceID
    goto finish

    error:
    select ErrorCode=@rc

    finish:
    return

Questo script potrebbe essere schedulato giornalmente, ed in automatico provvederà a creare uno o più file di trace con nome file e data di generazione.
Periodicamente potrete quindi aprire il vostro file trace con il profiler ed importarlo in una tabella per eseguire eventuali indagini (vedere img.).

Print | posted on lunedì 11 maggio 2009 17:38 |