SQL Server strumenti di monitoraggio
Nella Parte 1 di questa serie, abbiamo coperto un numero di caratteristiche che in SQL Server per ottimizzare l’utilizzo di risorse. Ad esempio, è possibile regolare il modo in cui i batch di query vengono compilati, configurare la cache del buffer per lo svuotamento a intervalli diversi e creare tabelle ottimizzate per la memoria. Per sfruttare al meglio queste funzionalità è necessario ottenere informazioni dettagliate in tempo reale sullo stato e sulle prestazioni di SQL Server. Qui esamineremo gli strumenti di monitoraggio di SQL Server all’interno delle funzionalità integrate e delle applicazioni di uso comune, tra cui:
- Query T-SQL: usa il linguaggio di query di SQL Server per raccogliere i dati raccolti internamente
- SQL Server Management Studio (SSMS): Ottieni visualizzazioni in tempo reale nel tuo sistema, diagrammi di query T-SQL e report on-demand
- Monitor delle prestazioni: Correlare le metriche di SQL Server con i dati degli host Windows
Gli strumenti di monitoraggio di SQL Server possono aiutarti ad accedere alle metriche di cui abbiamo discusso nella Parte 1. Alcuni di questi strumenti riportano le stesse metriche e potresti preferire un’interfaccia all’altra. Ad esempio, è possibile utilizzare le query Performance Monitor o T-SQL per ottenere metriche dai contatori delle prestazioni di SQL Server. Potresti optare per i grafici in tempo reale del primo rispetto alla possibilità di scrivere il secondo. In questo post, spiegheremo come utilizzare gli strumenti di monitoraggio di SQL Server per ottenere una visione completa dell’infrastruttura del database.
Utilizzo delle query T-SQL
È possibile monitorare SQL Server utilizzando il proprio linguaggio di query, T-SQL, per raccogliere le metriche. Le query T-SQL sono flessibili. È possibile eseguirli con uno strumento di gestione grafica come SSMS o un’utilità da riga di comando come sqlcmd. E dal momento che vengono eseguiti e restituiscono i dati proprio come qualsiasi altra query di database, è possibile incorporarli facilmente in una soluzione di monitoraggio automatizzato locale. In questa sezione, mostreremo come le query T-SQL possono essere un potente strumento per il monitoraggio di SQL Server, sia che si utilizzino viste di gestione dinamica, funzioni integrate, stored procedure o set di raccolta dati di sistema.
Dynamic Management views
SQL Server tiene traccia dei dati relativi al proprio stato e alle proprie prestazioni e rende queste informazioni disponibili tramite Dynamic Management views (DMV). Poiché i DMV vengono visualizzati come tabelle virtuali, si prestano sia a query ad hoc che automatizzate. Alcuni DMV restituiscono il valore corrente di una metrica o di un’impostazione (ad esempio, la dimensione corrente del log delle transazioni in megabyte). Altri, in particolare le metriche per le tariffe all’interno dei contatori delle prestazioni DMV, misurano i valori a intervalli regolari e prendono la differenza tra campioni consecutivi (ad esempio, richieste batch al secondo). È possibile leggere informazioni su DMV specifici nella documentazione di SQL Server.
Se stai monitorando SQL Server con viste di gestione dinamica, probabilmente vorrai interrogare i contatori delle prestazioni DMV, sys.dm_os_performance_counters. Ogni oggetto prestazioni SQL Server (che può rappresentare qualsiasi cosa, da un database alla cache del piano) mantiene il proprio set di contatori delle prestazioni, che si associano a molte delle categorie di metriche discusse nella parte 1: statistiche SQL, blocchi e gestione buffer.
Ad esempio, è possibile interrogare i contatori delle prestazioni DMV per visualizzare i dati dall’oggetto prestazioni buffer manager e limitare i risultati a metriche con valori diversi da zero:
SELECT object_name, counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE object_name="SQLServer:Buffer Manager" AND cntr_value != 0;GOOtterrai un risultato simile a questo (ma con molte più righe!):
object_name counter_name cntr_value---------------------------------------------------------------------------------------SQLServer:Buffer Manager Buffer cache hit ratio 30SQLServer:Buffer Manager Buffer cache hit ratio base 30SQLServer:Buffer Manager Page lookups/sec 11091500SQLServer:Buffer Manager Database pages 5819SQLServer:Buffer Manager Target pages 212992SQLServer:Buffer Manager Integral Controller Slope 10È possibile trovare un elenco delle viste di gestione dinamica disponibili organizzate per categoria qui. All’interno di ogni categoria, le visualizzazioni sono diverse, mentre alcune calcolano le metriche delle prestazioni e i numeri di output, altre riportano nomi e proprietà. È possibile elencare i nodi SQL Server in un cluster, recuperare i dati di utilizzo dell’indice e ottenere statistiche per i piani di esecuzione come il tempo di completamento e l’utilizzo delle risorse. E poiché le viste di gestione dinamica si comportano come tabelle, è possibile utilizzare le funzioni integrate per aggregare e classificare i dati.
È importante controllare la documentazione per qualsiasi DMV che si prevede di utilizzare. Uno dei motivi è che i DMV richiedono autorizzazioni diverse e la documentazione per ogni DMV spiega il livello richiesto. Un altro motivo è che i DMV possono contenere migliaia di righe e le colonne possono cambiare con le nuove versioni di SQL Server.
Funzioni integrate
SQL Server include anche funzioni integrate che consentono di accedere alle informazioni di sistema. A differenza delle viste di gestione dinamica, che restituiscono i dati sotto forma di tabelle virtuali, le funzioni integrate restituiscono i dati di sistema come singoli valori numerici, calcolati dall’ultimo avvio del server. È possibile chiamare ogni funzione incorporata come argomento di un’istruzione SELECT. Ad esempio, è possibile utilizzare la funzione integrata @@connections per restituire la somma delle connessioni riuscite e non riuscite nel tempo:
SELECT @@connections AS "Total Connections";GORiceverai un output simile a:
Total Connections----------------- 1571Le funzioni integrate a volte assomigliano a viste di gestione dinamiche. @@connections è simile al contatore User Connections all’interno dell’oggetto statistiche generali. Ma mentre User Connections tiene traccia del numero di utenti attualmente connessi, @@connections aumenta ogni volta che un utente tenta di accedere (anche se il tentativo non ha esito positivo).
L’unica funzione di statistiche di sistema integrata che non restituisce un singolo valore numerico è sys.fn_virtualfilestats, che restituisce una tabella con i dati sull’I/O del disco per i file di database e restituisce le stesse informazioni della visualizzazione di gestione dinamica sys.dm_io_virtual_file_stats.
Stored procedure di sistema
Nota del redattore: SQL Server utilizza i termini “master” per identificare il suo database primario. Datadog non usa questo termine.
Un’altra funzionalità integrata che è possibile utilizzare per interrogare le metriche è la stored procedure di sistema. La maggior parte delle stored procedure aiutano con le attività amministrative come il collegamento di un database o l’aggiunta di un login, ma alcune stored procedure riportano metriche. Ad esempio, sp_spaceused misura il consumo del disco all’interno di un database. Si chiamano stored procedure di sistema con istruzioni EXEC anziché SELECT. Questo comando chiama il sp_spaceused stored procedure che restituisce informazioni sull’utilizzo del disco come risultato due set (che è, due righe della tabella, ogni riga tra le diverse colonne):
EXEC sp_spaceused;GOL’uscita avrà un formato simile al seguente:
database_name database_size unallocated space--------------- ----------------- ---------------------master 6.00 MB 0.52 MBreserved data index_size unused--------------- ----------------- ------------------ ------------3568 KB 1536 KB 1600 KB 432 KBSistema di raccolta dati imposta
Se si sta utilizzando query T-SQL per raccogliere informazioni da SQL Server, e si desidera essere in grado di memorizzare i dati e generare report, si potrebbe considerare l’utilizzo di SQL Server set di raccolta. Un set di raccolta preleva i dati da una serie di comandi di reporting e viste di gestione dinamiche e li invia a un database dedicato denominato Data Warehouse di gestione.
Il processo si basa su SQL Server Integration Services per automatizzare il compito di interrogare il database e scrivere i risultati nel Data Warehouse di gestione.
Ad esempio, a partire da SQL Server 2008, il set di raccolta utilizzo disco interroga la vista di gestione dinamica sys.dm_io_virtual_file_stat e altre viste come sys.partitionse sys.allocation_units. È inoltre possibile creare un set di raccolta personalizzato che esegue una sequenza di query T-SQL in un lavoro periodico eseguito in background. Ulteriori informazioni sulla configurazione del Data Management Warehouse sono disponibili qui.
SQL Server Management Studio
SQL Server Management Studio (SSMS) è un ambiente grafico che consente di monitorare il sistema in diversi modi:
- Statistiche in tempo reale nel Monitor attività
- Una mappa ricca di dati di una determinata query
- Rapporti che combinano tabelle, grafici e testo in un formato compatibile con la stampante
Per utilizzare SSMS, è necessario scaricarlo su uno degli host, aprire il programma di installazione e seguire le istruzioni. Il software può monitorare istanze remote di SQL Server, incluse eventuali istanze in esecuzione su Linux. Per connettersi a un host, accedere al menu ” File “e fare clic su” Connetti Esplora oggetti.”Nella finestra di dialogo che segue, specificare l’host e la porta nel campo “Nome server”, nel formato 0.0.0.0,0000 (notare la virgola). Seleziona “Autenticazione SQL Server” nel menu a discesa” Autenticazione”e inserisci il nome utente (“Login”) e la password.
Se ti sei connesso correttamente, vedrai la finestra” Esplora oggetti ” popolare con un albero di file che mostra i componenti dell’istanza di SQL Server, inclusi i database. Sarai quindi in grado di monitorare la tua istanza con le funzionalità mostrate di seguito.
Activity Monitor
Activity Monitor consente di visualizzare le metriche di SQL Server in tempo reale, con una galleria di grafici, una panoramica dei processi e statistiche sulle query. Se stai già utilizzando SSMS per attività di gestione come la configurazione di pool di risorse o la creazione di tabelle, Activity Monitor è facile da aggiungere al tuo flusso di lavoro. Per utilizzare il monitor attività, digitare “Ctrl-Alt-A” o fare clic sull’icona all’interno della barra degli strumenti SSMS.
È possibile utilizzare Activity Monitor per ottenere informazioni dettagliate in tempo reale sulla domanda nell’istanza di SQL Server. La sezione “Panoramica” mostra quattro grafici che visualizzano le metriche di lavoro e risorse in tempo reale. Per impostazione predefinita, queste metriche si aggiornano ogni 10 secondi, ma puoi aggiornare l’intervallo di aggiornamento facendo clic con il pulsante destro del mouse sul riquadro “Panoramica”. Mentre l’intervallo di aggiornamento può essere frequente come una volta al secondo, questo viene fornito con il costo delle prestazioni di query di database più frequenti.
Il riquadro” Query costose recenti ” all’interno di Activity Monitor può aiutare a fornire le informazioni necessarie per rendere le query più efficienti. Qui troverai metriche correlate alle query come esecuzioni al minuto, letture fisiche al secondo e il numero di duplicati di un piano di esecuzione all’interno della cache. Se un singolo piano di esecuzione ha un numero elevato di duplicati o esecuzioni al minuto, è possibile aumentare le prestazioni utilizzando i suggerimenti di query come discusso nella Parte 1.
Il Monitor attività fornisce una comoda panoramica di alto livello del database, ma ha i suoi limiti. Per uno, non è possibile regolare le dimensioni dei grafici o le metriche che mostrano. Né è possibile modificare il modo in cui Activity Monitor aggrega le statistiche per le prestazioni delle query o visualizzare i dati oltre la finestra di visualizzazione preimpostata.
Visualizzazione delle query
SSMS consente di ottimizzare le prestazioni delle query consentendo di visualizzare il modo in cui SQL Server esegue i propri piani di query e mostrando l’utilizzo delle risorse associato all’esecuzione di ogni fase di un piano di query. Come abbiamo discusso nella Parte 1, SQL Server compila batch di istruzioni T-SQL utilizzando un ottimizzatore automatico per trasformare il batch in un piano di esecuzione. È possibile ispezionare un piano di esecuzione in SSMS come un diagramma di passi computazionali e scoprire esattamente come l’ottimizzatore ha interpretato il batch. Per visualizzare una query, accedere al riquadro “Query costose recenti” del monitor attività, fare clic destro su una delle query e fare clic su “Mostra piano di esecuzione.”La vista che segue sarà simile a questa:
Se si passa il mouse su un nodo all’interno del diagramma, è possibile visualizzare una breve spiegazione del passaggio rappresentato dal nodo, nonché una rapida lettura del “Costo operatore stimato” del nodo.”Questo valore viene calcolato dall’ottimizzatore SQL Server durante l’esecuzione della query. Poiché il processo di ottimizzazione è automatico, questo ti dà un modo per verificare che i tuoi batch siano stati compilati come previsto. E poiché ogni fase del piano di esecuzione è valutata in base al costo, puoi vedere su quali passaggi dovresti concentrarti se vuoi aumentare le prestazioni.
In questo esempio, possiamo vedere che Compute Scalar (convertire una stringa in un float) è minimo, con costo zero nel piano di esecuzione. L’operazione più costosa è una tecnica di ottimizzazione, lo spooling della tabella, che copia le righe in una tabella temporanea nascosta.
Rapporti
SSMS offre 20 rapporti standard che forniscono un’indagine ad alta quota della distribuzione di SQL Server, che vanno dall’utilizzo delle risorse del database ai dati storici sulle modifiche allo schema e sulla coerenza del database. È possibile trovare una ripartizione dettagliata dei rapporti qui.
I report sono corretti nel layout e nel contenuto: mostrano i dati disponibili nel momento in cui si crea il report, anziché aggiornarli in tempo reale. L’interattività nei rapporti standard è limitata. È possibile ordinare alcune tabelle per colonna ed espanderne altre quando le informazioni sono nidificate. Il layout fisso rende semplice la creazione di stampe o documenti (PDF, Word ed Excel).
Nel 2017, Microsoft ha aggiunto il report Performance Dashboard, che mostra l’utilizzo della CPU, i conteggi correnti delle sessioni utente e altre informazioni di sistema per le istanze di SQL Server.
Per generare un report, fare clic con il pulsante destro del mouse sul nome di un database in Esplora oggetti, passare il mouse su “Report”, quindi su “Report standard” e selezionare un report dal menu.
Se non è possibile trovare la vista necessaria dai report standard SSMS, è possibile creare un report personalizzato. I report personalizzati sono scritti in Report Definition Language (RDL), un’estensione di XML. Dopo aver specificato la struttura di un report personalizzato, è possibile compilarlo dal menu “Report” facendo clic su “Report personalizzati.”Questi rimangono separati dalla lista dei rapporti standard.
Vale anche la pena notare che Microsoft ha sviluppato diversi strumenti per la creazione di report grafici che vanno oltre le funzionalità di SSMS. Power BI può visualizzare i dati da una serie di fonti, tra cui SQL Server, e viene fornito con un set più completo di strumenti di editing visivo. SQL Server Reporting Services (SSRS) è uno strumento di reporting grafico progettato per SQL Server in grado di generare report impaginati, PDF-ready, nonché visualizzazioni di dati per i dispositivi mobili e il web.
Performance Monitor
Windows Performance Monitor consente di visualizzare l’utilizzo delle risorse a livello di sistema dagli host di Windows e consente di correlare queste metriche con i contatori delle prestazioni di SQL Server nei grafici timeseries.
Performance Monitor è integrato nel sistema operativo Windows. Per usarlo, apri la finestra Esegui dal menu Start e inserisci il nome del programma perfmon. Un grafico in tempo reale apparirà nell’albero di navigazione sotto “Strumenti di monitoraggio.”È quindi possibile selezionare i contatori delle prestazioni di SQL Server e le metriche delle risorse di sistema che si desidera tracciare e utilizzare le opzioni per modellare i grafici.
Strumenti di monitoraggio SQL Server in tempo reale più ricchi
In questo post, abbiamo mostrato come utilizzare gli strumenti di monitoraggio SQL Server e le funzionalità integrate per generare panoramiche in tempo reale dei database e per ottenere dati dettagliati e on-demand sullo stato e sulle prestazioni di SQL Server.
Nella prossima parte di questa serie, vi mostreremo come utilizzare Datadog per raccogliere, grafico, e alert in tempo reale e metriche storiche di SQL Server. Ti mostreremo anche come impostare dashboard con visualizzazioni drag-and-drop e correlare le metriche di SQL Server con i dati provenienti dallo stack.
Scrivi una risposta o commento.