sqlog

sql & co
posts - 78, comments - 14, trackbacks - 1

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).



Attiviamo e verifichiamo che il tutto funzioni come previsto.
Magari potrebbe essere necessario raffinare i dati tracciati per mezzo di ulteriori filtri (in questo caso applicare un filtro sulla colonna ObjectName può tornare utile).

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 |

Feedback

Gravatar

# re: Monitoring utilizzo oggetti SQL Server

Due consigli che il corso MOC per la certificazione MCTS impone.
Fai sempre il trace su un file di testo se non vuoi stressare il database, poi potrai sempre importare il trace in SQL con dei comandi di trace import.
Se puoi clona il DB e lancia l' analysis su un nuovo DB close, in questo modo la macchina che ospita il DB di produzione non avra' alcun impatto derivato dalle tue analisi.
:-)
11/05/2009 22:20 | raffaeu
Gravatar

# re: Monitoring utilizzo oggetti SQL Server

ciao

forse non è chiaro il mio post.
Il profiler è servito solo per impostare lo script di SQL ed eseguire i test di correttezza.
Lo script viene schedulato via JOB e il profiler non viene proprio utilizzato ;-)) .
Dato che non viene utilizzato il profiler, l'unico modo che ho per salvare il trace è proprio un file, che tra le altre cose è l'unico modo che le procedure di tracing di sql offrono. Quello di salvare su DB è una funzionalità di profiler, che ripeto io non vado proprio ad usare.

Non solo, io non lo ho scritto, ma tale procedura dovrà girare forse anche per piu di un mese e l'utilizzo di un clone o un devDb non risolve il mio problema (son daccordissimo con te che in mezzora non ricavo un granche).
E' una procedura non molto complessa, ma la difficoltà sta proprio nel configurare opportunamente il profiler per evitare di "ammazzare" il db.
Daltronde indagando un po' di più si fanno scoperte molto interessanti, per esempio le procedure di tracing vengono costantemente utilizzate da SQL per scrivere log di vario tipo.... ;))

ciaooooo
11/05/2009 23:20 | pietro partescano
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET