Il Data Collector di SQL Server 2008

English Version

Introduzione 


Il tipico problema di ogni DBA è tenere sotto controllo le installazioni dei propri DB in termini di performance e carichi del server.
Il processo di preparazione di collettori di dati per poter fare analisi è normalmente un task laborioso e spinoso.
Ma se siamo dei fortunati DBA di Database SQL Server 2008, Microsoft ha predisposto uno strumento che fa il lavoro al nostro posto: i data collector per SQL (da non confondere con gli omonimi del performace monitor di Windows).
In questo modo i data collector diventano un potentissimo alleato di tutti i DBA, soprattutto di quelli meno esperti. 

Architettura

Nonostante l'architettura sia abbastanza complessa

Architettura dei DATA Collector: fonte MSDN

l'installazione risulta essere molto semplice in quanto la configurazione e la parametrizzazione è minima, peraltro il data collector è stato disegnato per avere un overhead molto basso sul sistema in fase di raccolta ed analisi dei dati.
Crea dei pacchetti SSIS (SQL Server Integration Service),  lanciati tramite dei JOB  e quindi  l'Agent di SQL Server, che si occupano di raccogliere e memorizzare i dati all'interno di un Data Ware House di gestione (Managemet Data WhareHouse da ora in poi MDW) ; infine rappresenta le informazioni raccolte tramite una serie di Report dinamici linkati tra di loro che ci consentono di analizzare a fondo l'attività ed i trend delle nostre istanze di SQL Server.

Installazione

Apriamo SSMS ed andiamo nella sezione di Gestione, tasto destro su data collector e selzioniamo configura data collector

poi procediamo con la configurazione del MDW:

 Scegliamo dove crearlo e come chiamarlo (io con molta fantasia l'ho chiamato DataCollector).

ATTENZIONE: a regime il Data WhareHouse può arrivare ad occupare fino a 10GB di spazio disco!

Quindi fate attenzione a dove lo posizionate ed alle dimensioni iniziali, incrementiali e massime (se proprio dovete impostarle)

Andando avanti ci viene chiesto di configurare i ruoli e le autorizzazioni, ma se non avete esigenze particolari potete semplicemnte procedere senza selezionare nulla andando completare la prima fase di configurazione

Nuovamente torniamo sulla configurazione

E questa volta scegliamo di impostare la collezione dei dati

e semplicemente selezioniamo il Data Wharehouse creato precendentemente ed una directory in cui parcheggiare i dati raccolti prima che vengano inseriti nel MDW (come vedremo più avanti  i collettori delle query e di sistema raccolgono dati con un'elevata frequenza, ma il caricamento nel DB di Data Wharehouse avviene in maniere differita per evitare un eccessivo overhead dovuto ad un quasi continuo inserimento di dati)   

 Anche qui possiamo procedere e completare la configurazione

A questo i nostri collettori sono finalmente attivati e pian piano il nostro MDW comincerà a popolarsi

I Data Collector

Finita la configurazione i nostri pacchetti SSIS ed i loro Job sono pronti e funzionanti; le proprietà dei collettori che vedrete di seguito sono visualizzate solo a scopo esplicativo, chi non fosse interessato ad approfondire può tranquillamente saltare questo paragrafo.

Come possiamo evincere nell'immagine precedente abbiamo tre tipologie di collettori (4 su SQL 2008 R2):

1. Utilizzo del disco
2. Statistiche sulle Query
3. Attività del Server

 Utilizzo del disco: mantiene traccia della crescita dei file di Database e di Log raccogliendo informazioni da una serie di tabelle e viste di sistema.
Il campionamento avviene ogni 6 ore e viene simultaneamente caricato sul MDW (vista la bassa frequenza). I dati vengono mantenuti per 730 giorni in maniera da consentire un adeguato delineamento dei trend di crescita.

Statistiche sulle Query: raccoglie dati sugli statement SQL ed i relativi piani di esecuzione campionando alla frequenza di 10 secondi alcune viste di gestione dinamica ( Dynamic Management Views, da ora in poi DMV). Il caricamento in questo caso è differito ed avviene ogni 15 minuti. I dati vengono conservati nel MDW per 14 giorni.

Attività del Server: fornisce una visualizzazione sull'attività, l'utilizzo di risorse e la competizione sulle risorse di SQL Server, raccoglie  inoltre dati generali sulle risorse di sistema per consetire l'identificazione di eventuali colli di bottiglia esterni rispetto al motore di DB.  In questo caso i dati vengono raccolti sia dalle DMV che da molti contatori di performance di SQL Sever e di sistema.
Il campionamento avviene ogni 60 secondi ed i dati vengono caricati sul MDW ogni 15 minuti dove vengono conservati per 14 giorni.

Come si evince dalle immagini è possibile, ma assolutamente non necessario, modificare alcuni parametri, come ad esempio il periodo di di ritenzione dei dati nel MDW.

I Report

Se non avete voglia di aspettare le schedulazioni predefinite potete in qualsiasi momento forzare la raccolta ed il caricamento dei dati.

L'analisi dei dati raccolti mettere a dura prova anche un DBA di grande esperienza, e per questo motivo che il secondo, e forse anche il più grande, vantaggio del Data Collector sono i report che mette a disposizione.

Come vedete i report sono in rapporto uno ad uno con i collettori.
Vediamo quindi i dati che ci vengono messi a disposizione e la loro rappresentazione grafica.
Mi scuso per il fatto che tutte le immagini sono prese da un ambiente di test praticamente fermo, in cui l'unico carico è quello del Data Collector, ma questo non deve assolutamente indurre nell'errore di pensare che lo strumento crei un forte overhead sul sistema monitorato. 

 Utilizzo del disco

Visualizza tutti i file di Dati e Log dei Database della nostra istanza in termini di grandezza e crescita. Cliccando sul nome del singolo DB possiamo vedere lo spazio libero e l'elenco di tutti gli eventi correlati alla loro crescita.

Statistiche sulle Query

Innanzitutto visualizza la TOP 10 delle Query relativamente al consumo di risorse (di default la CPU) in formato grafico

ed in forma testuale

Cliccando poi sul singolo statement si entra in una visualizzazione di dettaglio con preziosissime informazioni inerenti le risorse utilizzate e la possbilità di entrare nel dettaglio dei waits

Attività del Server:

questo report  consente di visualizzare dei grafici relativi alle quattro risorse fondamentali in fase di tuning di SQL. Molto interessante è il fatto che ci consente di separare l'attività SQL Server da quella di Sistema.

Inoltre mette in formato grafico, con la possibilità di entrare nel dettaglio tramite un semplice click sia i Wait che le attività più significative in termini di performance tuning del server SQL.

Conclusioni  e note finali


Come abbiamo visto questo strumento coniuga due grandi qualità: semplicità di configurazione e potenza di analisi, quindi sono certo che diventerà presto parte di quasi tutte le vostre installazioni di SQL Server.
Purtroppo SQL Server 2005 non supporta i data collector ma MSSQLDUDE  suggerisce una tecnica per ovviare al problema (ad onor del vero non l'ho mai provata).
Inoltre ricordate che potete sempre esportare i grafici in formato PDF o Excel.

Link:
Data Collector su MSDN
SQL Authority (a cui ho rubato un pò di immagini)