Home > DMV’s for Query Tuning

DMV’s for Query Tuning

Sergio Govoni 8 Dicembre 2009 Senza categoria 2,515 Views

Le Dynamic Management Views and Functions recentemente introdotte in SQL Server 2005 forniscono informazioni sullo stato di salute di un’istanza SQL Server. Permettono di diagnosticare i problemi e di verificare se le performance possono essere migliorate, rappresentano uno strumento utilissimo per chi svolge le attività di database administrator e database developer.

Possiamo suddividere le DMV’s in due tipi:

  • Server-level
  • Database-level

Per interrogare una dynamic management view o una dynamic management function è necessario disporre del permesso di SELECT (sugli oggetti) in aggiunta ai permessi VIEW SERVER STATE e VIEW DATABASE STATE rispettivamente per consultare le DMV’s a livello server e a livello database.

Alcune DMV’s sono dipendenti dalla cache, infatti la percentuale memoria dedicata alla registrazione di execution plans e data buffers fluttua dinamicamente in funzione dello stato del sistema. Quando un qualsiasi comando SQL viene eseguito, l’engine per prima cosa verifica se esiste, nella cache, un piano di esecuzione per lo stesso comando. SQL Server è in grado di riutilizzare l’eventuale piano di esecuzione trovato risparmiando il carico di lavoro relativo alla ricompilazione del comando. Se il piano di esecuzione non esiste, perché ad esempio è stato rimosso dalla cache, SQL Server ne genera uno nuovo che verrà mantenuto in cache fino a quando la relativa zona di memoria non verrà rilasciata.

L’algoritmo che confronta il testo di un nuovo comando con il testo dei comandi in cache, al fine di riutilizzare i piani di esecuzione, richiede che tutti gli oggetti siano completamente qualificati. Per i due comandi di esempio riportati di seguito, solo il secondo statement troverà una corrispondenza con un piano di esecuzione esistente, il primo no!

SELECT * FROM Contact

SELECT * FROM Person.Contact

In SQL Server 2008, le DMV’s sono organizzate nelle seguenti categorie:

In questo articolo parleremo di alcune view e function contenute nelle categorie Execution Related e Index Related, vedremo come metterle in relazione tra loro per ottenere informazioni sulle performance delle query e sui piani di esecuzione.

Ipotizziamo di lavorare con un database di esempio contenete l’elenco telefonico di una città Italiana, la tabella dbo.Utenti rappresenta l’anagrafica (semplificata) degli abbonati. Generiamo il database ElencoTelefonico e la tabella dbo.Utenti con i seguenti comandi:

— Creazione DB
create database [ElencoTelefonico] on primary
(
name= ElencoTelefonico
,filename= ‘C:\ElencoTelefonico_1.mdf’
,size= 20MB
,filegrowth= 10%
)
log on
(
name= ElencoTelefonico_Log
,filename= ‘C:\ElencoTelefonico_Log_1.ldf’
,size= 10MB
,filegrowth= 10%
)

go

use [ElencoTelefonico] go

— Creazione tabella dbo.Utenti
if object_id(‘Utenti’) is not null
drop table dbo.Utenti

create table dbo.Utenti
(
id int identity(1, 1) not null
,nome varchar(128) not null
,cognome varchar(128) not null
,indirizzo varchar(40) default ‘indirizzo’
,citta varchar(40) default ‘citta’
,CAP varchar(5) default ‘CAP’
,codicefiscale varchar(16) not null
,datanascita datetime not null
,telefono varchar(20) not null
,dataregistrazione datetime default getdate()
,dataultimocontatto datetime default getdate()
)

Popoliamo l’anagrafica inserendo i dati di 500.000 abbonati (perdonerete la fantasia, ma l’abbonato Mario Rossi ha davvero tante utenze):

— Popoliamo la tabella dbo.Utenti
declare @i AS int
set @i = 0

while (@i < 500000)
begin
set nocount on

insert into dbo.Utenti
(
nome,
cognome,
codicefiscale,
datanascita,
telefono
)
values
(
‘Mario_’ + ltrim(str(@i)),
‘Rossi_’ + ltrim(str(@i)),
‘MRORSI’ + ltrim(str(@i)),
(select dateadd(hour, @i, ‘19500101’)),
‘059/’ + ltrim(str(@i))
)

set @i = (@i + 1)
set nocount off
end

Puliamo la cache per evitare risultati inquinati dai precedenti comandi di INSERT:

DBCC FREEPROCCACHE;

DBCC FREESYSTEMCACHE(‘ALL’);

L’elenco abbonati viene consultato attraverso un’applicazione che interroga la tabella dbo.Utenti in funzione dei filtri impostati dagli operatori. Ipotizziamo vengano eseguite, più volte, le seguenti query:

— 1
select
nome
,cognome
,datanascita
,telefono
from
dbo.Utenti
where
(cognome = ‘Rossi’)
go

— 2
select
nome
,cognome
,datanascita
,codicefiscale
from
dbo.Utenti
where
(telefono = ‘059/225890’)
go

— 3
select
nome
,cognome
,datanascita
,codicefiscale
from
dbo.Utenti
where
(telefono like ‘059/9%’)
go

— 4
select
nome
,cognome
,datanascita
,codicefiscale
from
dbo.Utenti
where
(telefono like ‘059/4%’)

— 5
select
nome
,cognome
,datanascita
,telefono
from
dbo.Utenti
where
(datanascita between ‘19750619’ and ‘19750630’)

— 6
select
nome
,cognome
,datanascita
,telefono
from
dbo.Utenti
where
(datanascita = ‘19750101 00:00:00.000’)

E’ giunto il momento di analizzare quali query sono state recentemente eseguite e di queste quali sono state le più lente, utilizziamo la DMV sys.dm_exec_query_stats che restituisce informazioni statistiche relative alle performance delle query il cui piano di esecuzione è in cache. Il seguente comando T-SQL restituisce una riga per ogni diversa query eseguita (il cui piano è ancora in cache), estraiamo alcuni dati statistici tra cui il numero di esecuzioni, il tempo totale di impiego della CPU, ecc ordiniamo l’output per il tempo totale impiegato per l’esecuzione del comando:

select
dm_eqs.execution_count
,dm_eqs.total_worker_time
,dm_eqs.total_elapsed_time
,dm_eqs.total_logical_reads
,dm_eqs.total_logical_writes
,dm_eqs.query_hash
,dm_eqs.query_plan_hash
from
sys.dm_exec_query_stats as dm_eqs
order by
dm_eqs.total_elapsed_time desc

Otteniamo il seguente output:

Combiniamo ora la DMV sys.dm_exec_query_stats con la DMF sys.dm_exec_sql_text() per ottenere, oltre ai contatori statistici, anche testo del comando T-SQL. La funzione sys.dm_exec_sql_text() richiede venga passato come parametro di input l’handle del comando SQL, passiamo quindi la colonna sql_handle estratta dalla vista sys.dm_exec_query_stats. Eseguiamo la query:

select
dm_est.text
,dm_eqs.execution_count
,dm_eqs.total_worker_time
,dm_eqs.total_elapsed_time
,dm_eqs.total_logical_reads
,dm_eqs.total_logical_writes
,dm_eqs.query_hash
,dm_eqs.query_plan_hash
from
sys.dm_exec_query_stats as dm_eqs
cross apply
sys.dm_exec_sql_text(dm_eqs.sql_handle) as dm_est
order by
dm_eqs.total_elapsed_time desc

L’output ottenuto indica chiaramente che la query (numero 4) che utilizza l’operatore LIKE sulla colonna [telefono] è quella che impiega più tempo CPU e più tempo di esecuzione delle altre:

Combiniamo ora le due precedenti DMV’s con la DMF sys.dm_exec_query_plan() per ottenere lo Showplan in formato XML relativo al comando T-SQL. La funzione sys.dm_exec_query_plan() richiede venga passato come parametro di input l’handle del piano di esecuzione, passiamo quindi la colonna plan_handle estratta dalla vista sys.dm_exec_query_stats. Eseguiamo la query:

select
dm_est.text
,dm_eqp.query_plan
,dm_eqs.execution_count
,dm_eqs.total_worker_time
,dm_eqs.total_elapsed_time
,dm_eqs.max_elapsed_time
,dm_eqs.total_logical_reads
,dm_eqs.total_logical_writes
,dm_eqs.query_plan_hash
from
sys.dm_exec_query_stats as dm_eqs
cross apply
sys.dm_exec_query_plan(dm_eqs.plan_handle) as dm_eqp
cross apply
sys.dm_exec_sql_text(dm_eqs.sql_handle) as dm_est
order by
dm_eqs.total_elapsed_time desc

L’output contiene la colonna query_plan di tipo XML, il piano di esecuzione in formato grafico è raggiungibile con un click

E come per magia, ecco il piano di esecuzione della query più lenta (la numero 4, quella che utilizza l’operatore LIKE):

I piani di esecuzione suggeriscono la creazione di indici ?

La risposta a questa domanda è Sì. Quando il l’optimizer genera il piano di esecuzione per una query, analizza quali sono gli indici migliori per accedere ai dati con la condizione di filtro specificata. Se non esiste un indice ottimale, l’optimizer registra che in quella particolare situazione non ha trovato indici, questa funzionalità “missing indexes” permette di ottenere informazioni sugli indici mancanti, tali informazioni rappresentano il supporto decisionale per un’eventuale implementazione. Nella visualizzazione del piano di esecuzione (vedi figura precedente), immediatamente sotto al testo della query, osserviamo che SQL Server suggerisce la creazione di un indice non-clustered. Per questo statement, SQL Server consiglia di creare un indice che sarà in grado di coprire completamente la query (covered indexes).

Applicando l’indice consigliato per la colonna [telefono] con l’INCLUDE per le colonne [nome], [cognome], [codicefiscale] e [datanascita], si stima (per la relativa query) un aumento delle performance del 53%. Consultiamo ora la DMV sys.dm_db_missing_index_details che restituisce informazioni dettagliate sulla mancanza di indici:

select
dm_dbmid.index_handle
,dm_dbmid.object_id
,dm_dbmid.equality_columns
,dm_dbmid.inequality_columns
,dm_dbmid.included_columns
,dm_dbmid.statement
from
sys.dm_db_missing_index_details as dm_dbmid

Tra gli indici suggeriti troviamo anche quello indicato nel precedente piano di esecuzione (index_handle = 11).

Conclusioni

Le DMV’s forniscono gratuitamente importanti informazioni sullo stato dell’istanza SQL Server, alcune di esse dipendono dalla cache che a sua volta dipende (dinamicamente) dallo stato del sistema. Può accadere che alcune informazioni vengano rimosse prima che l’amministratore le abbia consultate, per prevenire questo fenomeno è possibile prevedere alcuni job (schedulati) che permettano il salvataggio dei contatori (che interessano) in tabelle fisiche del DB, per la successiva consultazione.

E’ molto importante capire quali handle passare a quali DMV’s (DMF) e quali DMV’s (DMF) accettano un handle.

I suggerimenti relativi agli indici mancanti devono essere testati, ed è bene ricordare che ogni indice ha un costo di manutenzione.

Il tempo impiegato nello studio delle DMV’s è tempo investito bene, ve lo assicuro.

Pulizia DB

drop database [ElencoTelefonico]

 

Tags

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Azure Key Vault e certificati code-signing: Strategie per la conformità post 1° Giugno 2023!

In questi giorni, ho avuto l’opportunità di esplorare il rinnovo di un certificato di code-signing …

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

twenty six − = sixteen

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.

User Group Italiano SQL Server - Via Del Capanno 26, Lodi - P.IVA: 03015800968
Tutti i contenuti (c) UGISS, 2024