Monitorare i componenti di SQL Server

  • Articolo

Si applica a:SQL Server

Il monitoraggio è importante perché SQL Server fornisce un servizio in un ambiente dinamico. i dati nell'applicazione cambiano, il tipo di accesso richiesto dagli utenti cambia, la modalità di connessione degli utenti cambia. I tipi di applicazioni che accedono a SQL Server possono anche cambiare, ma SQL Server gestisce automaticamente le risorse a livello di sistema, ad esempio memoria e spazio su disco, per ridurre al minimo la necessità di un'ampia ottimizzazione manuale a livello di sistema. Il monitoraggio consente agli amministratori di identificare le tendenze delle prestazioni per determinare i casi in cui è necessario apportare modifiche.

Per monitorare in modo efficace qualsiasi componente di SQL Server:

  1. Determinare gli obiettivi del monitoraggio.
  2. Selezionare lo strumento appropriato.
  3. Identificare i componenti di cui eseguire il monitoraggio.
  4. Selezionare la metrica per tali componenti.
  5. Eseguire il monitoraggio del server.
  6. Analizzare i dati.

I singoli passaggi sono descritti in dettaglio di seguito.

Individuazione degli obiettivi del monitoraggio

Per monitorare in modo efficace 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 determinato il motivo per cui si esegue il monitoraggio, è consigliabile selezionare gli strumenti appropriati per quel tipo di monitoraggio. Il sistema operativo Windows e SQL Server forniscono un set completo di strumenti per monitorare i server in ambienti a elevato utilizzo 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:

  • Avviare Monitor prestazioni (Windows) che consente di raccogliere e visualizzare dati in tempo reale su attività quali memoria, disco e utilizzo del processore.
  • Avvisi e registri di prestazioni
  • Gestione attività

Per ulteriori informazioni sugli strumenti di Windows Server o Windows, vedere la documentazione di Windows.

SQL Server offre gli strumenti seguenti per il monitoraggio dei componenti di SQL Server:

Importante

Traccia SQL e SQL Server Profiler sono deprecati. Anche lo spazio dei nomi Microsoft.SqlServer.Management.Trace che contiene gli oggetti Trace e Replay di Microsoft SQL Server è deprecato.

Questa funzionalità verrà rimossa nelle versioni future di SQL Server. Evitare di usare questa funzionalità in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata.

In alternativa, usare Eventi estesi. Per altre informazioni sulla panoramica degli eventi estesi, vedere Guida introduttiva: Eventi estesi e Uso del profiler XEvent di SSMS.

Nota

SQL Server Profiler per i carichi di lavoro di Analysis Services NON è deprecato e continuerà a essere supportato.

Per altre informazioni sugli strumenti di monitoraggio di SQL Server, vedere strumenti di Monitor prestazioni e ottimizzazione.

Identificazione dei componenti di cui eseguire il monitoraggio

Il terzo passaggio per il monitoraggio di un'istanza di SQL Server consiste nell'identificare i componenti monitorati. Ad esempio, se si usa SQL Server Profiler per tracciare un server, è possibile definire la traccia per raccogliere dati su eventi specifici. È anche possibile escludere eventi che non si applicano alla situazione.

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 le metriche usati per monitorare il server.

Di seguito viene descritto il processo di acquisizione dei dati di evento e di utilizzo di tali dati.

  1. 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 altre informazioni sul filtro delle tracce degli eventi estesi, vedere Avvio rapido: Eventi estesi.

    Per altre informazioni sul filtro della traccia SQL, vedere Impostare un filtro di traccia (Transact-SQL) e filtrare gli eventi in una traccia (SQL Server Profiler).For more information on filtering SQL Trace, see Set a Trace Filter (Transact-SQL) and Filter Events in a Trace (SQL Server Profiler).

  2. Eseguire il monitoraggio degli eventi (acquisizione).

    Non appena è abilitato, il monitoraggio attivo 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, vengono acquisiti dati evento quali le letture e le scritture su disco. Questi dati verranno quindi visualizzati sullo schermo. Per altre informazioni, vedere Monitorare l'utilizzo delle risorse (Monitor prestazioni).

  3. Salvare i dati evento acquisiti.

    Il salvataggio dei dati di evento acquisiti consente di poterli analizzare in un secondo momento. I dati evento acquisiti vengono salvati in un file che può essere ricaricato e analizzato nello strumento in cui è stato creato in origine. Il salvataggio dei dati degli eventi acquisiti è importante quando si crea una baseline di prestazioni. I dati di riferimento per le prestazioni vengono salvati e utilizzati per il confronto con dati evento più recenti allo scopo di verificare se le prestazioni sono ottimali.

    Gli eventi estesi consentono di salvare i dati dell'evento in un file dell'evento, contatore degli eventi, istogramma e buffer circolare. Per altre informazioni, vedere Destinazioni per gli eventi estesi.

    I dati degli eventi di traccia SQL possono anche essere riprodotti usando l'utilità Riesecuzione distribuita o SQL Server Profiler. SQL Server Profiler consente di salvare i dati degli eventi in un file o in una tabella di SQL Server. Per altre informazioni, vedere Modelli e autorizzazioni di SQL Server Profiler.

  4. 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 un monitoraggio frequente del numero di deadlock e degli utenti interessati, è possibile creare un modello per la definizione di tali eventi, dati evento e filtri evento, salvare il modello e quindi riapplicare il filtro in occasione del successivo monitoraggio dei deadlock.

    La definizione di una sessione Eventi estesi è un modello che può essere inserito nello script e usato nuovamente. Per creare e gestire le sessioni, vedere Gestire sessioni di eventi in Esplora oggetti. Management Studio XEvent Profiler fornisce già modelli pronti per l'uso. Per altre informazioni, vedere Usare il profiler XEvent di SQL Server Management Studio.

    A questo scopo, SQL Server Profiler usa modelli di traccia. Per altre informazioni, vedere Impostare le impostazioni predefinite delle definizioni di traccia (SQL Server Profiler) e Creare un modello di traccia (SQL Server Profiler).

    Suggerimento

    Una definizione di traccia SQL può essere convertita in una sessione Eventi estesi. Per altre informazioni, vedere Convertire uno script di Traccia SQL esistente in una sessione Eventi estesi.

  5. Analizzare i dati evento acquisiti.

    Ai fini dell'analisi, i dati evento acquisiti e salvati vengono caricati nell'applicazione utilizzata per l'acquisizione.

    Ad esempio, una traccia evento estesa acquisita può essere ricaricata in SQL Server Management Studio per la visualizzazione e l'analisi. Per altre informazioni, vedere Visualizzare i dati degli eventi in SQL Server Management Studio.

    I dati di traccia SQL possono essere ricaricati in SQL Server Profiler per la visualizzazione e l'analisi. Per altre informazioni, vedere Visualizzare e analizzare le tracce con 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 usare l'Ottimizzazione guidata motore di database per analizzare una traccia acquisita da eventi estesi o SQL Server Profiler e impostare raccomandazioni sugli indici in base ai risultati.

  6. Riprodurre i dati di evento acquisiti (facoltativo).

    In tal modo è possibile creare una copia di prova dell'ambiente di database da cui sono stati acquisiti i dati e quindi ripetere gli eventi acquisiti così come si sono verificati nel sistema reale. Questa funzionalità è disponibile solo con l'utilità Riesecuzione distribuita o 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 per 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 altre informazioni, vedere Riprodurre le tracce.