Esaminiamo una query in Azure SQL Database che può identificare le query parallele e quelle ad alto volume che potrebbero influire significativamente sulle prestazioni.
Oggi affrontiamo la richiesta di un cliente che desidera monitorare e ottimizzare le query che potrebbero avere un significativo impatto sulle prestazioni del suo database in Azure SQL. Ci riferiamo in particolare alle query eseguite in parallelo e a quelle query con un elevato volume di esecuzioni, pur eseguite con un singolo thread (MAXDOP 1). Sveleremo una query che ci aiuta a individuare questi due tipi di problemi.
La query SQL che illustreremo utilizza le Visualizzazioni di Gestione Dinamiche (DMV) in SQL Server per raggruppare e analizzare le statistiche di esecuzione delle query. Prima di addentrarci, è utile scomporre ed esaminare ciascuna parte di questa query:
WITH QueryStats AS ( SELECT query_hash, SUM(total_worker_time) AS total_worker_time, SUM(total_elapsed_time) AS total_elapsed_time, SUM(execution_count) AS execution_count, MAX(max_dop) AS total_dop FROM sys.dm_exec_query_stats GROUP BY query_hash)SELECT qs.query_hash, qs.execution_count, qs.total_worker_time, qs.total_elapsed_time, qs.total_dop, SUBSTRING(st.text, (qs_statement.statement_start_offset/2) + 1, ((CASE qs_statement.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs_statement.statement_end_offset END - qs_statement.statement_start_offset)/2) + 1) AS query_textFROM QueryStats qsCROSS APPLY (SELECT TOP 1 * FROM sys.dm_exec_query_stats qs_statement WHERE qs.query_hash = qs_statement.query_hash) qs_statementCROSS APPLY sys.dm_exec_sql_text(qs_statement.sql_handle) AS stORDER BY qs.total_worker_time DESC;Decifriamo ora i vari componenti della nostra query:
Espressione di Tabella Comune (CTE): QueryStats
In questa parte della query, vengono aggregate le statistiche dalla vista sys.dm_exec_query_stats, che contiene le statistiche di prestazione per i piani di query memorizzati nella cache. La colonna query_hash è un valore hash utilizzato per identificare le query che hanno una struttura simile. Mentre, total_worker_time indica il tempo totale della CPU utilizzato dalla query, total_elapsed_time rappresenta il tempo totale impiegato per l’esecuzione della query. execution_count è il numero di volte che la query è stata eseguita e, infine, max_dop indica il grado massimo di parallelismo utilizzato dalla query.
Query principale
Le colonne qs.query_hash, qs.execution_count, qs.total_worker_time, qs.total_elapsed_time, qs.total_dop sono selezionate dalla CTE QueryStats. Il testo della query viene poi estratto tramite la funzione substring, che estrae una porzione del testo della query da sys.dm_exec_sql_text.
CROSS APPLY
Con qs_statement viene recuperata la riga più rilevante da sys.dm_exec_query_stats dove l’hash della query corrisponde, fornendo informazioni dettagliate per ciascun hash della query. Con st viene invece recuperato il testo SQL della query utilizzando sys.dm_exec_sql_text con la maniglia SQL da qs_statement.
Infine, il risultato finale è ordinato per total_worker_time in ordine decrescente, mostrando in cima le query che richiedono più tempo di CPU.
Come aiuta a individuare le query che impattano sulle prestazioni
Le query parallele, con un alto valore di total_dop indicano un’esecuzione parallela. Sebbene l’esecuzione parallela possa accelerare le singole query, può anche generare contendibilità delle risorse, specialmente se molte query vengono eseguite in parallelo contemporaneamente. Le query ad alto volume, con un elevato valore di execution_count ma con un basso total_dop, sono tipicamente eseguite con MAXDOP 1. Se queste query vengono eseguite frequentemente, possono influenzare in modo significativo le prestazioni generali del database.
Identificando entrambi i tipi di query, gli amministratori del database possono prendere misure come l’ottimizzazione delle query, l’indicizzazione, o l’aggiustamento delle impostazioni del grado di parallelismo per migliorare le prestazioni complessive del database.