Panoramica del monitoraggio e dell'ottimizzazione delle prestazioni
L'obiettivo del monitoraggio dei database è la valutazione delle prestazioni di un server. Un monitoraggio efficace implica l'esecuzione di snapshot periodici delle prestazioni correnti al fine di isolare i processi che causano problemi, nonché la raccolta continua di dati nel tempo per tenere traccia delle tendenze delle prestazioni. In Microsoft SQL Server e nel sistema operativo Microsoft Windows sono disponibili utilità che consentono di visualizzare la condizione corrente del database e di tenere traccia delle prestazioni in caso di variazioni.
Utilizzare il monitoraggio di SQL Server per effettuare le operazioni seguenti:
Determinare se è possibile migliorare le prestazioni. Il monitoraggio dei tempi di risposta delle query più frequenti consente, ad esempio, di determinare se sono necessarie modifiche alle query o agli indici nelle tabelle.
Valutare le attività degli utenti. Il monitoraggio dei tentativi di connessione a un'istanza di SQL Server consente ad esempio di determinare se il sistema di protezione è adeguatamente impostato e di testare applicazioni e sistemi di sviluppo. Il monitoraggio dell'esecuzione di query SQL consente ad esempio di determinare se le query sono formulate in modo corretto e se producono i risultati previsti.
Risolvere eventuali problemi o eseguire il debug dei componenti di applicazione, ad esempio di stored procedure.
Il monitoraggio è importante perché SQL Server fornisce un servizio in un ambiente dinamico, in cui i dati nell'applicazione cambiano, il tipo di accesso richiesto dagli utenti cambia, la modalità di connessione degli utenti cambia, persino i tipi delle applicazioni che accedono a SQL Server può cambiare. SQL Server è tuttavia in grado di gestire automaticamente le risorse a livello di sistema, quali la memoria e lo spazio su disco in modo da ridurre al minimo la necessità di ingenti interventi di ottimizzazione manuale a livello di sistema. Il monitoraggio consente agli amministratori di identificare le tendenze delle prestazioni e di determinare i casi in cui è necessario apportare modifiche.
Per eseguire in modo efficace il monitoraggio di un qualsiasi componente di SQL Server, effettuare la procedura seguente:
Determinare gli obiettivi del monitoraggio.
Selezionare lo strumento appropriato.
Identificare i componenti di cui eseguire il monitoraggio.
Selezionare la metrica per tali componenti.
Eseguire il monitoraggio del server.
Analizzare i dati.
I singoli passaggi sono descritti in dettaglio di seguito.
Individuazione degli obiettivi del monitoraggio
Per eseguire in modo efficace il monitoraggio di SQL Server, è necessario identificare chiaramente il motivo del monitoraggio. Di seguito sono riportati alcuni dei possibili motivi:
Definire una base di riferimento delle prestazioni.
Identificare le variazioni di prestazioni nel tempo.
Diagnosticare problemi specifici relativi alle prestazioni.
Identificare componenti o processi da ottimizzare.
Confrontare l'effetto di diverse applicazioni client in termini di prestazioni.
Controllare l'attività degli utenti.
Testare un server con carichi diversi.
Testare l'architettura del database.
Testare i piani di manutenzione.
Testare i piani di backup e ripristino.
Stabilire i casi in cui è necessario modificare la configurazione hardware.
Selezione dello strumento appropriato
Dopo aver individuato i motivi per i quali eseguire il monitoraggio, è necessario selezionare gli strumenti appropriati. Nell'ambiente operativo Windows e in SQL Server è incluso un set completo di strumenti per il monitoraggio dei server in ambienti caratterizzati da un elevato numero di transazioni. Tali strumenti consentono di ottenere informazioni sulla condizione di un'istanza di Motore di database di SQL Server o di un'istanza di SQL Server Analysis Services.
Di seguito sono riportati gli strumenti disponibili in Windows per il monitoraggio delle applicazioni in esecuzione in un server:
Monitor di sistema, che consente di raccogliere e visualizzare in tempo reale dati sulle attività, quali utilizzo della memoria, del disco e del processore.
Avvisi e registri di prestazioni.
Task Manager.
Per ulteriori informazioni sugli strumenti di Windows Server o di Windows, consultare la documentazione di Windows.
Di seguito sono riportati gli strumenti disponibili in SQL Server per il monitoraggio di componenti di SQL Server:
Traccia SQL
SQL Server Profiler
Monitor attività di SQL Server Management Studio
Showplan grafico di SQL Server Management Studio
Stored procedure
Comandi DBCC (Database Console Commands)
Funzioni predefinite
Flag di traccia
Per ulteriori informazioni sugli strumenti di monitoraggio di SQL Server, vedere Strumenti per il monitoraggio e l'ottimizzazione delle prestazioni.
Identificazione dei componenti di cui eseguire il monitoraggio
Il terzo passaggio della procedura di monitoraggio di un'istanza di SQL Server consiste nell'identificazione dei componenti monitorati. Se, ad esempio, si utilizza SQL Server Profiler per tracciare un server, è possibile definire la traccia per raccogliere dati su eventi specifici. È inoltre possibile escludere eventi non applicabili alla situazione corrente.
Selezione della metrica per i componenti monitorati
Dopo aver identificato i componenti di cui eseguire il monitoraggio, è necessario determinarne la metrica. Dopo aver selezionato gli eventi da includere in una traccia, è, ad esempio, possibile scegliere di includere solo i dati specifici sugli eventi. Limitando la traccia ai soli dati pertinenti si ridurranno al minimo le risorse di sistema necessarie per eseguire la traccia.
Monitoraggio del server
Per eseguire il monitoraggio del server, utilizzare lo strumento configurato per la raccolta dei dati. Dopo aver definito una traccia, è, ad esempio, possibile eseguire la traccia per raccogliere i dati sugli eventi generati nel server.
Analisi dei dati
Al termine della traccia, analizzare i dati per verificare se sono stati raggiunti gli obiettivi di monitoraggio. In caso contrario, modificare i componenti o la metrica utilizzati per il monitoraggio del server.
Di seguito viene descritto il processo di acquisizione dei dati di evento e di utilizzo di tali dati.
Applicare filtri per limitare i dati di evento raccolti.
La limitazione dei dati di evento consente di concentrarsi sugli eventi pertinenti per lo scenario di monitoraggio. Se, ad esempio, si desidera eseguire il monitoraggio per individuare le query lente, è possibile utilizzare un filtro per monitorare solo le query inviate dall'applicazione, per le quali il tempo di esecuzione è superiore ai 30 secondi in un particolare database. Per ulteriori informazioni, vedere Procedura: Impostazione di un filtro di traccia (Transact-SQL) e Procedura: Filtraggio di eventi in una traccia (SQL Server Profiler).
Eseguire il monitoraggio degli eventi (acquisizione).
Non appena viene attivato, il monitoraggio acquisisce i dati dall'applicazione specificata, dall'istanza di SQL Server o dal sistema operativo. Se, ad esempio, si esegue il monitoraggio dell'attività del disco tramite Monitor di sistema, verranno acquisiti i dati di eventi quali le letture e scritture su disco. Tali dati verranno quindi visualizzati sullo schermo. Per ulteriori informazioni, vedere Monitoraggio dell'utilizzo delle risorse (Monitor di sistema).
Salvare i dati di evento acquisiti.
Il salvataggio dei dati di evento acquisiti consente di poterli analizzare in un secondo momento o persino riprodurre utilizzando SQL Server Profiler. I dati di evento acquisiti vengono salvati in un file che può essere ricaricato e analizzato nello strumento in cui è stato creato. In SQL Server Profiler è possibile salvare i dati di evento in una tabella SQL Server. Il salvataggio dei dati acquisiti è fondamentale per la definizione di dati di riferimento per le prestazioni. I dati di riferimento per le prestazioni vengono salvati e utilizzati per il confronto con dati di evento più recenti allo scopo di verificare se il livello di prestazioni è ottimale. Per ulteriori informazioni, vedere Utilizzo di SQL Server Profiler.
Creare modelli di traccia contenenti le impostazioni specificate per l'acquisizione degli eventi.
I modelli di traccia includono informazioni specifiche sugli eventi stessi, i dati di evento e i filtri utilizzati per l'acquisizione dei dati. È possibile utilizzare tali modelli per eseguire il monitoraggio di un set di eventi specifico in un secondo momento senza ridefinire gli eventi, i dati di evento e i filtri. Se, ad esempio, si desidera eseguire il monitoraggio frequente del numero di blocchi critici (deadlock) e degli utenti coinvolti, è possibile creare un modello contenente la definizione degli eventi, dei dati di evento e dei filtri, salvare il modello e quindi riapplicare il filtro in occasione del successivo monitoraggio dei blocchi critici. A tale scopo, in SQL Server Profiler vengono utilizzati i modelli di traccia. Per ulteriori informazioni, vedere Procedura: Impostazione dei valori predefiniti per una definizione di traccia (SQL Server Profiler) e Procedura: Creazione di un modello di traccia (SQL Server Profiler).
Analizzare i dati di evento acquisiti.
Per consentirne l'analisi, i dati di evento acquisiti e salvati vengono caricati nell'applicazione utilizzata per l'acquisizione. È, ad esempio, possibile caricare nuovamente in SQL Server Profiler una traccia acquisita di SQL Server Profiler per attività di visualizzazione e analisi. Per ulteriori informazioni, vedere Visualizzazione e analisi delle tracce tramite SQL Server Profiler.
L'analisi dei dati di evento consente di individuare gli eventi verificatisi e di determinarne le cause. In base a tali informazioni è possibile apportare modifiche che possono incidere positivamente sulle prestazioni, ad esempio aggiungere memoria, modificare gli indici, risolvere problemi di codifica di istruzioni Transact-SQL o stored procedure e così via, a seconda del tipo di analisi eseguito. È ad esempio possibile utilizzare Ottimizzazione guidata Motore di database per analizzare una traccia acquisita di SQL Server Profiler e creare indicazioni per gli indici sulla base dei risultati. Per ulteriori informazioni, vedere Ottimizzazione della progettazione fisica di database.
Riprodurre i dati di evento acquisiti.
In tal modo è possibile creare una copia di prova dell'ambiente di database dal quale sono stati acquisiti i dati e di ripetere gli eventi acquisiti così come si sono verificati nel sistema reale. Questa funzionalità è disponibile solo in SQL Server Profiler. È possibile riprodurre gli eventi con la stessa velocità originale, nel modo più rapido possibile (per sottoporre il sistema a condizioni estreme) oppure, come è più probabile, un passaggio alla volta per analizzare il sistema dopo ogni singolo evento. La possibilità di analizzare eventi reali in un ambiente di prova consente di evitare danni al sistema di produzione. Per ulteriori informazioni, vedere Riproduzione di tracce.