SQL4DBA - SQL per DBA SQL Server

Il DBA (DataBase Administrator) utilizza statement SQL particolari per estrarre le informazioni piu' interessanti sui DB che amministra.
Questa pagina raccoglie e descrive gli script SQL piu' utili ed interessanti per la gestione di un database Microsoft SQL Server.

SQL Server e' il diffusissimo RDBMS di Microsoft ed il suo T-SQL e' molto potente.

Il documento e' volutamente breve e pratico con esempi funzionanti di statement SQL.
Informazioni di dettaglio si possono trovare nella documentazione ufficiale.

Introduzione

SQL Server fornisce un SQL ricco di funzionalita'. Il data dictionary e' molto ampio e spesso le informazioni si possono ricavare in piu' modi da SQL o da interfaccia grafica. Molti sistemisti infatti utilizzano il Microsoft SQL Server Management Studio (SSMS) che fornisce report e strumenti automatici ma nel seguito vedremo come ottenere le informazioni solo con query SQL quindi richiamabili da linea di comando con sqlcmd o da un qualsiasi client.

Gli statement SQL a disposizione di un DBA SQL Server sono moltissimi... in questa pagina faremo una scelta su quelli piu' utili ed interessanti.

Sessioni

Una prima visione sull'utilizzo di una base dati e' quella delle connessioni presenti:

SELECT s.session_id, s.login_name, s.host_name, s.program_name, r.status, r.command, r.sql_handle FROM sys.dm_exec_sessions s LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id WHERE s.session_id <> @@SPID;

SELECT s.session_id, s.login_name, s.host_name, s.program_name, r.status, r.command, t.text AS query_text FROM sys.dm_exec_sessions s INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.session_id <> @@SPID;

Le colonne selezionate sono tra le piu' utili ma naturalmente e' possibile estrarre maggiori dettagli dalle viste dinamiche di SQL Server.
Entrambe le query escludono la sessione corrente dall'elenco delle sessioni ottenute.

Lock

Gli oggetti sulla base dati vengono protetti dall'utilizzo concorrente in SQL Server mediante la gestione dei lock. La query seguente utilizza qualche join per ottenere le query bloccate e da quale processo:

WITH cteBL (session_id, blocking_these) AS (SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s CROSS APPLY (SELECT isnull(convert(varchar(6), er.session_id),'') + ', ' FROM sys.dm_exec_requests as er WHERE er.blocking_session_id = isnull(s.session_id ,0) AND er.blocking_session_id <> 0 FOR XML PATH('') ) AS x (blocking_these) ) SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these, batch_text = t.text, input_buffer = ib.event_info, * FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id INNER JOIN cteBL as bl on s.session_id = bl.session_id OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib WHERE blocking_these is not null or r.blocking_session_id > 0 ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id; -- KILL session_id;

Questa query consente di capire con certezza chi e' il colpevole e chi e' la vittima del lock.

Decidere poi se terminare il colpevole o la vittima e' un'altra questione...
Nota: prima di ammazzare qualcuno... e' importante essere certi di quello che si sta facendo!

Utilizzo di spazio

La gestione dello spazio disco in SQL Server si basa sul file system del sistema ospite. Sono utilizzabili diverse organizzazioni dei dati come struttura interna per soddisfare i piu' specifici requisiti di memorizzazione. Tipicamente le primary key vengono implementati come indici clusterizzati mentre gli altri indici sono indici B-tree secondari.

Un server SQL Server ospita piu' database. Quindi un primo punto di vista e' controllare l'occupazione dei database:

SELECT name, size * 8.0 / 1024 AS SizeMB, physical_name FROM sys.master_files; -- EXEC sp_spaceused;

Analizzando invece gli oggetti contenuti nella base dati corrente e' possibile ottenere i dati di dettaglio. Ecco i 20 oggetti di maggior dimensione:

SELECT TOP 20 TABLE_NAME, (SUM(reserved_page_count) * 8192) / 1024.0 AS Size_KB FROM sys.dm_db_partition_stats, INFORMATION_SCHEMA.TABLES WHERE sys.dm_db_partition_stats.object_id = OBJECT_ID(INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA + '.' + INFORMATION_SCHEMA.TABLES.TABLE_NAME) AND INFORMATION_SCHEMA.TABLES.TABLE_TYPE = 'BASE TABLE' GROUP BY TABLE_NAME ORDER BY Size_KB desc;

Performance

L'ottimizzazione delle performance e' il leitmotiv di ogni DBA. I punti di vista sono moltissimi: individuare gli statement SQL piu' pesanti, ottimizzare i piani di esecuzione degli statement, determinare eventuali bottleneck, effettuare il tuning sui parametri di configurazione, ...
Ecco qualche query di esempio:

SELECT TOP 10 q.query_id, t.query_sql_text, rts.avg_cpu_time FROM sys.query_store_query AS q JOIN sys.query_store_query_text AS t ON q.query_text_id = t.query_text_id JOIN sys.query_store_runtime_stats AS rts ON q.query_id = rts.runtime_stats_id ORDER BY rts.avg_cpu_time DESC; SELECT q.query_id, p.plan_id, t.query_sql_text, qp.query_plan FROM sys.query_store_query AS q JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_query_text AS t ON q.query_text_id = t.query_text_id JOIN sys.query_store_plan AS qp ON p.plan_id = qp.plan_id WHERE q.query_id = 123; SELECT qt.query_sql_text, q.query_id, rs1.avg_duration, rs2.avg_duration, (rs2.avg_duration - rs1.avg_duration) AS delta, rs1.count_executions, rs2.count_executions FROM sys.query_store_query AS q JOIN sys.query_store_query_text AS qt ON q.query_text_id = qt.query_text_id JOIN sys.query_store_runtime_stats AS rs1 ON q.query_id = rs1.runtime_stats_id JOIN sys.query_store_runtime_stats AS rs2 ON q.query_id = rs2.runtime_stats_id WHERE rs1.plan_id <> rs2.plan_id AND rs1.runtime_stats_interval_id < rs2.runtime_stats_interval_id ORDER BY delta DESC; SELECT * FROM sys.configurations;

La prima query permette di individuare le 10 query piu' pesanti utilizzando il Query Store.
La seconda query riporta il query plan di una query specifica.
La terza query riporta le query che sono peggiorate e riportano maggiori differenze nel tempo nell'esecuzione.
SQL Server ha un numero elevato di parametri di configurazione che possono essere modificati per effettuare il tuning della prestazioni.
L'ultima query riporta i parametri presenti.

Naturalmente vi sono decine di ulteriori query utili (eg. per i transaction log: sys.dm_db_log_space_usage), ma per ragioni di spazio non e' possibile elencarle tutte.
Deve essere sottolineato che SSMS e Azure Data Studio offrono report predefiniti che utilizzano query analoghe e sono facilmente interpretabili... ma a noi piace scrivere query complicate in SQL!

Ulteriori informazioni

Un'introduzione a SQL Server si trova in questa paginetta. Maggiori dettagli tecnici sulle diverse versioni di SQL Server e le date di rilascio di ogni versione sono riportate in questo documento.

DBA SQL Scripts