Cos’è e come fare monitoraggio di Microsoft SQL Server

Sappiamo che il software noto come SQL Server è fondamentale per la gestione dati di un’infrastruttura IT: il relativo monitoraggio deve essere parte integrante del lavoro da svolgere. Infatti, avere sotto controllo il lavoro svolto da un database può fare la differenza in termini di efficienza, sicurezza e performance.

Un responsabile IT deve essere in grado di gestire tutti i processi interni dei sistemi informativi aziendali per monitorare SQL Server di Microsoft in modo adeguato. Quali sono i contorni di queste attività?

Perché dobbiamo monitorare SQL server?

Prima di capire come procedere con il monitoraggio di SQL Server di Microsoft, in modo da ottimizzare ogni passaggio, dobbiamo capire quali sono i motivi che ci spingono verso queste operazioni.

Il primo punto è semplice: grazie a queste attività di monitoring – che consistono in misurazioni periodiche di alcuni benchmark di riferimento – possiamo garantire massima efficienza all’intero database. 

Con relativa ottimizzazione delle risorse per evitare eventi anche gravi. Le condizioni in cui si procede al monitoraggio del database spesso riguardano la presa in carico di un lavoro da parte di un responsabile IT che si ritrova a dover gestire un DB non ottimizzato, basato su impostazioni di default nel migliore dei casi.

Oppure è abbandonato da tempo. Questo chiaramente riguarda qualsiasi Database Management System, ma nello specifico vogliamo concentrarci sul buon monitoraggio di SQL Server. Tutto ciò per evitare blocchi dell’infrastruttura, corruzione e perdita dei dati nel database, degrado delle prestazioni.

Monitor usage, logs, query expensive

Di sicuro la scelta non solo degli strumenti adeguati – dopo vedremo qualche dettaglio in più – ma anche delle attività utili è fondamentale per un buon lavoro di monitoraggio di SQL Server. Uno dei punti fondamentali?

Di sicuro mettiamo in evidenza quello del Monitor usage. Per i server Microsoft Windows, si può usare lo strumento Performance Monitor per misurare le prestazioni di SQL Server e altri elementi come memoria, cache, thread e processi. A ciascuno di questi oggetti è associato un set di contatori che misurano 

• Device usage.
• Queue length.
• Delay.

Tra i diversi vantaggi di questo strumento hai la possibilità di esportare dati da grafici, registri, avvisi e report in fogli di calcolo o applicazioni di database per stampare o portare verso un’elaborazione differente.

Un ulteriore controllo in termini di monitoring è quello degli errori di log: SQL Server registra determinati eventi di sistema indicando automaticamente l’ora e le informazioni tecniche. Cosa significa questo?

Puoi usare i log degli errori di SQL Server per risolvere i problemi relativi a eventuali problemi tecnici. Inoltre puoi monitorare e risolvere slow running query, tipica condizione per chi ha grandi quantità di dati.

Qual è la regola per ognuno di questi punti? Serve competenza ma c’è bisogno anche di strumenti adatti a monitorare, leggere, interpretare i dati. Per poi aiutare ad agire in modo da raggiungere l’ottimizzazione.

Quali sono i migliori strumenti di monitoraggio?

Le soluzioni a disposizione dei tecnici IT per monitorare SQL Server di Microsoft sono tante. Alcune sono interne al software, altre esterne. Quali sono le soluzioni ideali? Ecco qualche idea per ottenere il massimo.

Activity Monitor

Il tool Activity Monitor si trova in SQL Server Management Studio (SSMS), mostra i dati sui processi del software e su come influenzano l’istanza che ti interessa. La finestra può aprire una serie di schede: 

1. Overview
2. Processes
3. Resource Waits
4. Data File I/O
5. Recent Expensive Queries
6. Active Expensive Queries

Activity Monitor – strumento interno a SQL Server di Microsoft –  interroga l’istanza per ottenere i dati che ti servono. Puoi approfondire una o più sezioni per visualizzare diverse informazioni utili.

Query store

Anche Query store è uno strumento di monitoraggio per SQL Server interno al software. Il suo scopo, come ricorda la risorsa ufficiale Microsoft, è ben preciso. Ovvero offrire:

informazioni dettagliate sulla scelta e sulle prestazioni del piano di query per SQL Server, Database SQL di Azure, Istanza gestita di SQL di Azure e Azure Synapse Analytics.

learn.microsoft.com

Attraverso questo tool di monitoring puoi affrontare i problemi di prestazioni. Questo perché Query store trova in tempi ridotti le variazioni delle prestazioni collegate a modifiche nei piani di query. Ciò avviene acquisendo e permettendo la consultazione di piani, statistiche di runtime e cronologia query.

SQL Server Profiler

Ci troviamo di fronte a un’interfaccia pensata per generare e gestire sia le tracce che i relativi risultati. Ma di cosa stiamo parlando esattamente? Su SQL Server, gli eventi vengono salvati su un file di traccia. Il quale può essere analizzato o utilizzato per riprodurre dei percorsi necessari a individuare degli errori da risolvere.

Extended event

Sistema di monitoraggio delle performance di SQL Server che memorizza eventi a livelli differenti – sistema, database, sessione – per consentire analisi, debug, profiling e risoluzione dei problemi. Questo tool di monitoraggio può essere sfruttato con SQL Server Management Studio (SSMS) o script T-SQL e offre una serie di elementi visual come grafici a barre e torte per semplificare la visualizzazione dei dati estrapolati.

Redgate

Oltre ai tool di monitoraggio SQL Server interni, possiamo elencare delle soluzioni di terze parti. Nello specifico, citiamo Redgate come riferimento utile per gli IT Manager interessati a un buon lavoro di analisi:

• delle prestazioni,
• delle query,
• dei problemi tecnici. 

Oltre agli strumenti di diagnosi, alert e monitoring ibrido per database in sede o cloud puoi contare su strumenti di query impact per risolvere blocchi e richieste lunghe. Grazie a un pannello di controllo completo, con un’unica occhiata puoi controllare istanze SQL Server, gruppi di disponibilità, cluster e macchine virtuali.

Redgate è uno strumento per ottimizzare prestazioni, garantire sicurezza e ridurre i rischi che possono colpire SQL Server e PostgreSQL. Questo avviene con analisi e alert per avvisare i responsabili IT.

PRTG 

Un altro strumento esterno per analizzare SQL Server: PRTG viene utilizzato per monitorare i database, trovare i motivi che aumentano i tempi di inattività del server SQL e ottimizzare le prestazioni.

Visualizzando in un attimo tutti i dati necessari al tuo lavoro. Perché aggiungere anche PRTG alla lista? Oltre a essere particolarmente facile da gestire e intuitivo, questo tool monitora in profondità tutti i dati relativi alle query. Monitora diversi tipi di ambienti SQL e identifica le cause di errori raccogliendo dati in tempo reale su utilizzo della CPU, memoria, spazio su disco. In ogni caso hai sempre gli alert che ti avvisano.

Qualche script utile per risolvere e analizzare?

Per concludere, ricordiamo che in molti casi possiamo utilizzare una serie di script quando un SQL Server presenta dei problemi o dei cali di prestazioni. Come riferimento, abbiamo preso qualche consiglio da www.mssqltips.com per creare una piccola library di script.

EXEC sp_whoisactive;

Questo script serve a capire cosa è attivo in un momento ed è fondamentale quando ricevi un alert. Se invece c’è un problema in termine di blocco puoi agire con questo comando:

SELECT * FROM sys.sysprocesses 
WHERE blocked > 0 
OR SPID IN (SELECT Blocked FROM sys.sysprocesses);

Guarda i risultati. La colonna blocked riporta 0 quando uno SPID non è bloccato. Invece puoi utilizzare questa query per registrare la durata prevista della pagina di SQL Server. 

SELECT
  LEFT(counter_name, 25) CounterName, 
  CASE counter_name 
    WHEN 'Stolen pages' THEN cntr_value/128 --8kb pages/128 = MB 
    WHEN 'Stolen Server Memory (KB)' THEN cntr_value/1024 --kb/1024 = MB
    ELSE cntr_value
  END CounterValue_converted_to_MB
FROM
  sys.dm_os_performance_counters
WHERE
  OBJECT_NAME = N'SQLServer:Buffer Manager'
  AND
  counter_name = 'Page life expectancy';

Ancora uno script comodo per risolvere alcuni problemi come quelli legati a un disco pieno. In questi casi potresti avere dei rallentamenti ma il comando da eseguire è questo:

SELECT DISTINCT 
  vs.volume_mount_point Drive
  , vs.logical_volume_name
  , vs.total_bytes/1024/1024/1024 CapacityGB
  , vs.available_bytes/1024/1024/1024 FreeGB
  , CAST(vs.available_bytes * 100. / vs.total_bytes AS DECIMAL(4,1)) FreePct
FROM 
  sys.master_files mf
  CROSS APPLY 
  sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs;

Monitoring database hostati on premise e cloud

Esiste una differenza tra il lavoro di monitoring tra queste soluzioni, ovvero tra database in sede o in cloud? Di sicuro nel primo caso, essendo una soluzione in sede e gestita dal tuo IT Service interno, hai massimo controllo ma anche tutto l’onere del monitoraggio. 

Con i servizi in cloud, di un’applicazione SAAS (Software as a Service), puoi delegare il tutto rinunciando però alla tua personalizzazione e alla gestione interna.

Nel caso del servizio di SQL Server in cloud, il motore database è gestito dal provider tuttavia le problematiche di performance, la manutenzione e il monitoraggio sono comunque in carico al reparto IT interno o, se presente, al database administrator.

Servizio di DBA remoto

IT Impresa fornisce un servizio di monitoraggio proattivo che prevede la risposta agli eventi e l’ottimizzazione delle performance.

Un database è sempre in crescita e va mantenuto performante nel tempo, richiedi subito il servizio di DBA remoto e ci preoccuperemo noi del tuo database.