SQL Server

Ottimizzazione delle prestazioni delle query in SQL Server

Maciej Pilecki

Panoramica:

• Analisi dei piani di esecuzione
• Ottimizzazione delle query
• Identificazione delle query da ottimizzare

Quando si ottimizza il server di database, è necessario ottimizzare le prestazioni delle singole query. Questo è forse più importante dell'ottimizzazione di altri aspetti dell'installazione server che influiscono sulle prestazioni, come le configurazioni hardware e software.

Anche se il server di database viene eseguito sull'hardware più potente a disposizione, il comportamento anomalo di alcune query può influire in modo negativo sulle prestazioni. Di fatto, anche una sola query difettosa, spesso indicata col termine di "query runaway", può causare gravi problemi alle prestazioni di un database.

Viceversa, le prestazioni complessive del database possono essere migliorate notevolmente ottimizzando una serie di query più dispendiose o eseguite più di frequente. In questo articolo verranno esaminate alcune delle tecniche a disposizione per identificare e ottimizzare le query più dispendiose e meno efficienti in esecuzione su un server.

Analisi dei piani di esecuzione

L'ottimizzazione di una query inizia in genere dall'esame del relativo piano di esecuzione. Il piano di esecuzione descrive la sequenza delle operazioni, fisiche e logiche, che verranno eseguite da SQL ServerTM per soddisfare la query e produrre i risultati desiderati. Il piano di esecuzione viene prodotto da un componente del motore di database, definito Query Optimizer, durante la fase di ottimizzazione dell'elaborazione della query: questo tiene conto di molti fattori diversi, come i predicati di ricerca utilizzati nella query, le tabelle utilizzate e le relative condizioni di join, l'elenco delle colonne restituite e la presenza di indici utili che possono fungere da percorsi di accesso efficienti ai dati.

Nel caso delle query complesse, il numero di tutte le permutazioni possibili può essere elevato, quindi Query Optimizer non valuta tutte le possibilità ma tenta invece di trovare un piano che sia "sufficientemente buono" per una determinata query. Quanto appena detto è dovuto al fatto che l'individuazione di un piano perfetto non sempre è possibile e anche se lo è, il costo della valutazione di tutte le possibilità per trovare il piano perfetto potrebbe non essere compensato dagli eventuali guadagni a livello di prestazioni. Dal punto di vista di un DBA, è importante comprendere il processo e le relative limitazioni.

Esistono vari modi per recuperare un piano di esecuzione per una query:

• Management Studio fornisce le funzionalità di visualizzazione del piano di esecuzione effettivo e del piano di esecuzione stimato, che forniscono una rappresentazione grafica del piano. Queste funzionalità offrono la soluzione più adeguata per l'esame diretto e costituiscono di gran lunga l'approccio più diffuso per visualizzare e analizzare i piani di esecuzione. In questo articolo gli esempi proposti saranno illustrati utilizzando i piani grafici generati in questo modo.
• Varie opzioni SET, quali SHOWPLAN_XML e SHOWPLAN_ALL, restituiscono il piano di esecuzione sotto forma di documento XML che descrive il piano utilizzando uno schema speciale o un set di righe con la descrizione testuale di ciascuna delle operazioni contenute.
• Le classi di evento di SQL Server Profiler, come Showplan XML, consentono di raggruppare piani di esecuzione delle istruzioni raccolte da una traccia.

Anche se una rappresentazione XML del piano di esecuzione può non costituire il formato di lettura più semplice, tale opzione consente di scrivere procedure e utilità in grado di analizzare i piani di esecuzione, alla ricerca di segni che indicano problemi di prestazioni e piani non ottimali. Una rappresentazione XML può anche essere salvata su un file con estensione sqlplan e poi aperta in Management Studio per produrre una rappresentazione grafica. Questi file possono anche essere salvati per analisi successive, eliminando ovviamente la necessità di riprodurre il piano di esecuzione ogni volta che si desidera analizzarlo. Ciò è particolarmente utile per confrontare i piani allo scopo di rilevarne l'evoluzione nel tempo.

Costo di esecuzione stimato

La prima cosa da comprendere dei piani di esecuzione è il modo in cui vengono generati. SQL Server utilizza uno strumento di ottimizzazione delle query basato sul costo, che tenta di generare un piano di esecuzione con il minimo costo stimato. La stima è basata sulle statistiche di distribuzione dei dati che sono disponibili per Query Optimizer quando questo valuta ogni tabella interessata dalla query. Se queste statistiche non sono disponibili o non sono aggiornate, Query Optimizer sarà privo delle informazioni necessarie per il processo di ottimizzazione della query e quindi le stime non saranno corrette. In questi casi, Query Optimizer sceglierà un piano non ottimale stimando per eccesso o per difetto i costi di esecuzione di piani diversi.

Sul costo di esecuzione stimato esistono alcune convinzioni errate. In particolare, spesso le persone presumono che il costo di esecuzione stimato sia un buon indicatore del tempo necessario per l'esecuzione della query e che questa stima consenta di distinguere i piani efficienti da quelli meno efficienti. Ovviamente si tratta di una percezione errata. Innanzitutto, le unità di misura adottate per esprimere il costo stimato sono documentate a sufficienza così come l'eventuale relazione diretta con il tempo di esecuzione. In secondo luogo, trattandosi di una stima che può quindi essere suscettibile di errore, i piani con costi più alti possono a volte essere più efficienti in termini di CPU, I/O e tempo di esecuzione, malgrado le relative stime più alte. Questo è spesso il caso delle query che coinvolgono variabili di tabella, per le quali non sono disponibili statistiche, e quindi Query Optimizer presuppone sempre che una variabile di tabella contenga solo una riga, anche se ne contiene di più. Di conseguenza, Query Optimizer sceglierà un piano basato su una stima imprecisa. Di conseguenza, quando si confrontano i piani di esecuzione delle query, non si deve fare affidamento sul solo costo stimato. È necessario includere invece nell'analisi l'output delle opzioni STATISTICS I/O e STATISTICS TIME per comprendere il costo reale di esecuzione in termini di I/O e tempo della CPU.

Vale la pena citare qui un tipo speciale di piano di esecuzione, definito piano parallelo. Se la query viene eseguita su un server con più di una CPU ed è idonea per la parallelizzazione, è possibile scegliere un piano parallelo. In generale, Query Optimizer prende in considerazione un piano parallelo per una query con un costo superiore a una determinata soglia configurabile. A causa dell'overhead legato alla gestione di più thread di esecuzione paralleli, che comporta un lavoro di distribuzione dei thread, la sincronizzazione e raccolta dei risultati, il costo di esecuzione dei piani paralleli è maggiore e questo si riflette sul relativo costo stimato. Perché quindi sono preferiti rispetto ai piani non paralleli meno costosi? Grazie all'utilizzo della potenza di elaborazione di più CPU, i piani paralleli tendono a produrre risultati più veloci rispetto ai piani standard. A seconda dello scenario specifico, comprendente variabili quali le risorse disponibili e il carico concorrente di altre query, questa situazione può rivelarsi preferibile. In tal caso, è necessario controllare quale delle query è in grado di produrre piani paralleli e il numero di CPU utilizzate da ciascuna. A tal fine, è necessario impostare l'opzione del grado massimo di parallelismo a livello di server ed eseguire l'override a livello della singola query tramite OPTION (MAXDOP n) in base alle esigenze.

Analisi di un piano di esecuzione

Ora verrà esaminata una semplice query, il relativo piano di esecuzione e alcuni metodi per migliorarne le prestazioni. Supponiamo di eseguire questa query utilizzando Management Studio con l'opzione Includi piano di esecuzione effettivo attivata nel database di esempio Adventure Works in SQL Server 2005:

SELECT c.CustomerID, SUM(LineTotal)
FROM Sales.SalesOrderDetail od 
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
JOIN Sales.Customer c ON oh.CustomerID=c.CustomerID
GROUP BY c.CustomerID

Il risultato è il piano esecuzione descritto nella Figura 1. Questa semplice query calcola l'importo totale degli ordini eseguiti da ciascun cliente di Adventure Works. Esaminando il piano di esecuzione, si può notare il modo in cui il motore di database elabora la query e produce il risultato. I piani di esecuzione grafici devono essere letti dall'alto verso il basso e da destra verso sinistra. Ogni icona rappresenta un'operazione logica e fisica eseguita e le frecce indicano il flusso dei dati tra le operazioni. Lo spessore delle frecce rappresenta il numero di righe passate tra le operazioni: più spessa è la freccia, maggiore è il numero di righe coinvolte. Se si posiziona il puntatore su una delle icone di operatore, viene visualizzata una descrizione comando, simile a quella illustrata nella Figura 2, che descrive l'operazione.

Figura 1** Piano di esecuzione di esempio **(Fare clic sull'immagine per ingrandirla)

Figura 2** Dettagli di un'operazione **(Fare clic sull'immagine per ingrandirla)

Esaminando ciascun operatore, è possibile analizzare la sequenza dei passaggi eseguiti:

1. Il motore di database esegue un'operazione Clustered Index Scan sulla tabella Sales.Customer e restituisce la colonna CustomerID per tutte le righe nella tabella.
2. Quindi viene eseguita un'operazione Index Scan, non Clustered Index Scan, su uno degli indici nella tabella Sales.SalesOrderHeader. Questo è un indice sulla colonna CustomerID, ma include implicitamente anche la colonna SalesOrderID, la chiave di clustering della tabella. La scansione restituisce i valori da entrambe le colonne.
3. Viene eseguito il join dell'output di entrambe le scansioni nella colonna CustomerID utilizzando l'operatore fisico Merge Join. Questo è uno dei tre possibili metodi di esecuzione di un'operazione di join logico, veloce ma che richiede che entrambi gli input siano ordinati in una colonna di join. In questo caso, entrambe le operazioni di scansione hanno già restituito le righe ordinate su CustomerID evitando così ulteriori operazioni di ordinamento.
4. Quindi, il motore di database esegue una scansione dell'indice cluster nella tabella Sales.SalesOrderDetail, recuperando i valori di quattro colonne (SalesOrderID, OrderQty, UnitPrice e UnitPriceDiscount) da tutte le righe nella tabella. La stima prevedeva la restituzione di 123.317 righe da questa operazione e questo è il numero effettivamente restituito, come si può notare dalle proprietà Numero stimato di righe e Numero effettivo di righe illustrate nella Figura 2, confermando che la stima era estremamente precisa.
5. Le righe prodotte dalla scansione dell'indice cluster vengono passate al primo operatore Compute Scalar in modo che il valore della colonna calcolata LineTotal può essere calcolato per ogni riga, in base alle colonne OrderQty, UnitPrice e UnitPriceDiscount coinvolti nella formula.
6. Il secondo operatore Compute Scalar applica la funzione ISNULL al risultato del calcolo precedente, come richiesto dalla formula della colonna calcolata. In questo modo viene completato il calcolo della colonna LineTotal e lo restituisce, insieme alla colonna SalesOrderID, all'operatore successivo.
7. L'output dell'operatore Merge Join nel passaggio 3 viene unito con l'output dell'operatore Compute Scalar del passaggio 6, utilizzando l'operatore fisico Hash Match.
8. Viene quindi applicato un altro operatore Hash Match per raggruppare le righe restituite da Merge Join tramite il valore della colonna CustomerID e l'aggregato SUM calcolato della colonna LineTotal.
9. L'ultimo nodo, SELECT, non è un operatore fisico o logico ma un segnaposto che rappresenta i risultati e il costo complessivo della query.

Sul mio computer portatile, il costo stimato di questo piano di esecuzione era di 3,31365, come illustrato nella Figura 3. Eseguita con STATISTICS I/O ON, la query ha riportato l'esecuzione di un totale di 1.388 operazioni di lettura logiche tra le tre tabelle coinvolte. Le percentuali visualizzate sotto ciascuno degli operatori rappresentano il costo di ogni singolo operatore relativo al costo complessivo stimato dell'intero piano di esecuzione. Osservando il piano illustrato nella Figura 1, si può notare che la maggior parte del costo totale dell'intero piano di esecuzione è associata ai tre operatori seguenti: Clustered Index Scan della tabella Sales.SalesOrderDetail e i due operatori Hash Match. Prima di passare alla relativa ottimizzazione, vale però la pena rilevare una modifica molto semplice alla query che consentirà di eliminare del tutto due degli operatori.

Figura 3** Costo di esecuzione totale stimato della query **

Poiché l'unica colonna restituita dalla tabella Sales.Customer è CustomerID e questa colonna è anche inclusa come chiave esterna in Sales.SalesOrderHeaderTable, è possibile eliminare completamente la tabella Customer dalla query senza modificare il significato logico o il risultato prodotto dalla query utilizzando il codice seguente:

SELECT oh.CustomerID, SUM(LineTotal)
FROM Sales.SalesOrderDetail od JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
GROUP BY oh.CustomerID

Questo determina un piano di esecuzione diverso, che è illustrato nella Figura 4.

Figura 4** Il piano di esecuzione dopo l'eliminazione della tabella Customer dalla query **(Fare clic sull'immagine per ingrandirla)

Due operazioni sono state eliminate interamente, Clustered Index Scan sulla tabella Customer e Merge Join tra Customer e SalesOrderHeader, e l'operazione di join Hash Match è stata sostituita dalla più efficiente operazione Merge Join. Tuttavia, per utilizzare Merge Join tra le tabelle SalesOrderHeader e SalesOrderDetail, le righe di entrambe le tabelle dovevano essere restituite secondo l'ordine della colonna di join SalesOrderID. Per ottenere questo risultato, Query Optimizer ha deciso di eseguire un'operazione Clustered Index Scan sulla tabella SalesOrderHeader invece di Index Scan non cluster, che avrebbe comportato un costo inferiore in termini di I/O. Questo è un buon esempio del funzionamento pratico di Query Optimizer: poiché i risparmi derivanti dalla modifica del metodo fisico di esecuzione dell'operazione di join erano superiori al costo di I/O aggiuntivo generato dall'operazione Clustered Index Scan, Query Optimizer ha scelto la combinazione risultante di operatori perché fornivano il costo di esecuzione stimato più basso possibile. Sul mio computer portatile, anche se il numero di letture logiche è aumentato (fino a 1.941), il tempo di CPU utilizzato è stato effettivamente inferiore e il costo di esecuzione stimato di questa query è stato ridotto di circa il 13% (2,89548).

Supponiamo di voler migliorare ulteriormente le prestazioni di questa query. Esaminiamo quindi Clustered Index Scan della tabella SalesOrderHeader, l'operatore più dispendioso nel piano di esecuzione. Poiché per soddisfare la query sono necessarie solo due colonne di questa tabella, è possibile creare un indice non cluster che contiene solo due colonne, sostituendo la scansione dell'intera tabella con una scansione del più piccolo indice non cluster. La definizione dell'indice può essere simile alla seguente:

CREATE INDEX IDX_OrderDetail_OrderID_TotalLine
ON Sales.SalesOrderDetail (SalesOrderID) INCLUDE (LineTotal)

Si noti che l'indice creato comprende una colonna calcolata. A seconda della definizione della colonna calcolata, questo non è sempre possibile.

Dopo la creazione di questo indice e l'esecuzione della stessa query, si ottiene il nuovo piano di esecuzione illustrato nella Figura 5.

Figura 5** Piano di esecuzione ottimizzato **(Fare clic sull'immagine per ingrandirla)

La scansione dell'indice cluster sulla tabella SalesOrderDetail è stata sostituita da una scansione dell'indice non cluster con una riduzione significativa del costo di I/O. È stato eliminato anche uno degli operatori Compute Scalar, in quanto l'indice comprende già un valore calcolato della colonna LineTotal. Il costo del piano di esecuzione stimato è ora 2,28112 e quando viene elaborata la query esegue 1.125 letture logiche.

Indice di copertura

Esercizio sulle query degli ordini clienti

D Di seguito viene proposto l'esercizio sulle query degli ordini clienti: scoprire la definizione di indice, quali colonne deve contenere per diventare l'indice di copertura di questa query e se l'ordine delle colonne nella definizione di indice comporta una differenza nelle prestazioni.

R Ho proposto di scoprire l'indice di copertura ottimale da creare nella tabella Sales.SalesOrderHeader per la query di esempio illustrata nel mio articolo. A tal fine, la prima cosa da osservare è che la query utilizza solo due colonne dalla tabella: CustomerID e SalesOrderID. Se l'articolo è stato letto con attenzione, si noterà che nel caso della tabella SalesOrderHeader, esiste già un indice che copre questa query: si tratta dell'indice su CustomerID che contiene implicitamente la colonna SalesOrderID, la chiave di clustering della tabella.

Ovviamente è stato spiegato anche perché Query Optimizer ha deciso di non utilizzare questo indice. Sì, è possibile imporre a Query Optimizer di utilizzarlo, ma la soluzione sarebbe meno efficiente rispetto al piano esistente che utilizza gli operatori Clustered Index Scan e Merge Join perché Query Optimizer verrebbe obbligato a scegliere tra l'esecuzione di un'operazione di ordinamento aggiuntiva per essere in grado di utilizzare ancora Merge Join o l'utilizzo di un'operazione Hash Join meno efficiente. Il costo di esecuzione stimato di entrambe le opzioni è maggiore rispetto al piano esistente (le prestazioni della versione con l'operazione di ordinamento sarebbero particolarmente scadenti), quindi Query Optimizer non le utilizzerà se non viene imposto diversamente. Pertanto, in questa situazione, l'unico indice che garantirà prestazioni migliori rispetto a Clustered Index Scan è un indice non cluster su SalesOrderID, CustomerID. È tuttavia importante notare che le colonne devono essere nell'ordine esatto:

CREATE INDEX IDX_OrderHeader_SalesOrderID_CustomerID
ON Sales.SalesOrderHeader (SalesOrderID, CustomerID)

Se viene creato questo indice, il piano di esecuzione conterrà l'operatore Index Scan e non Clustered Index Scan. Questa è una differenza significativa. In questo caso, l'indice non cluster contenente solo due colonne è molto più piccolo dell'intera tabella nella forma di indice cluster. Di conseguenza, richiederà un I/O inferiore per leggere i dati necessari.

Quest'esempio dimostra anche l'impatto che l'ordine delle colonne nell'indice può avere sull'utilità per Query Optimizer. Ricordarsi questo concetto quando si progettano indici su più colonne.

L'indice creato su SalesOrderDetail è un esempio del cosiddetto "indice di copertura". Si tratta di un indice non cluster che contiene tutte le colonne necessarie per soddisfare la query, eliminando l'esigenza di eseguire la scansione dell'intera tabella gli operatori Table Scan o Clustered Index Scan. L'indice è sostanzialmente una copia più piccola della tabella, contenente un sottoinsieme di colonne della tabella. Nell'indice sono incluse solo le colonne necessarie per rispondere alle query. In altre parole, l'indice contiene solo ciò che è necessario per "coprire" la query.

La creazione di indici di copertura per le query più frequenti rappresenta una delle tecniche più semplici e diffuse nell'ottimizzazione delle query. È particolarmente utile nelle situazioni in cui la tabella contiene molte colonne ma la query fa riferimento solo ad alcune di esse. Tramite la creazione di uno o più indici di copertura, è possibile migliorare notevolmente le prestazioni delle query interessate, in quanto queste accederanno a una quantità decisamente inferiore di dati con un conseguente numero inferiore di I/O. Esiste, tuttavia, un costo nascosto legato alla manutenzione di indici aggiuntivi durante le operazioni di modifica dei dati (INSERT, UPDATE e DELETE). A seconda dell'ambiente e del rapporto tra le query SELECT e le modifiche dei dati, è necessario giudicare attentamente se tale manutenzione aggiuntiva di indici viene giustificata dai miglioramenti di prestazioni della query.

Non si deve temere di creare indici con più colonne rispetto agli indici con una sola colonna. Questi tendono a essere molto più utili rispetto agli indici con una sola colonna ed è più probabile che Query Optimizer li utilizzi per coprire la query. La maggior parte degli indici di copertura sono indici con più colonne.

Nel caso della query di esempio, esiste ancora un margine di miglioramento e la query può essere ottimizzata ulteriormente inserendo un indice di copertura nella tabella SalesOrderHeader. Ciò elimina l'operatore Clustered Index Scan a favore di un Index Scan non cluster. Questo può costituire il tema di un esercizio per il lettore. Provare a scoprire la definizione di indice: quali colonne deve contenere per diventare l'indice di copertura di questa query e se l'ordine delle colonne nella definizione di indice comporta una differenza di prestazioni. La soluzione è riportata nell'intestazione laterale "Esercizio sulle query degli ordini clienti".

Viste indicizzate

Se le prestazioni della query di esempio sono molto importanti, è possibile fare un ulteriore passo avanti e creare una vista indicizzata che memorizza fisicamente i risultati della query. Esistono alcuni prerequisiti e limitazioni delle viste indicizzate, ma la possibilità di utilizzarne una consente di migliorare notevolmente le prestazioni. Tenere presente che le viste indicizzate comportano un maggiore costo di manutenzione rispetto agli indici standard. Di conseguenza, il loro utilizzo va gestito con attenzione. In tal caso, la definizione dell'indice è simile alla seguente:

CREATE VIEW vTotalCustomerOrders
WITH SCHEMABINDING
AS
SELECT oh.CustomerID, SUM(LineTotal) AS OrdersTotalAmt, COUNT_BIG(*) AS TotalOrderLines
FROM Sales.SalesOrderDetail od 
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
GROUP BY oh.CustomerID 

Si noti l'opzione WITH SCHEMABINDING, che è un prerequisito per la creazione di un indice in una vista di questo tipo, e la funzione COUNT_BIG(*), che è necessaria se la definizione dell'indice contiene una funzione di aggregazione, in questo esempio, SUM. Dopo avere creato questa vista, è possibile creare un indice al suo interno nel modo seguente:

CREATE UNIQUE CLUSTERED INDEX CIX_vTotalCustomerOrders_CustomerID 
ON vTotalCustomerOrders(CustomerID)

Quando si crea questo indice, il risultato della query contenuta nella definizione della vista viene materializzato e memorizzato fisicamente su disco nell'indice. Si noti che tutte le operazioni di modifica dei dati nelle tabelle di base aggiornano quindi automaticamente i valori nella vista in base alla relativa definizione.

Se la query viene eseguita di nuovo, ciò che accadrà dipende dall'edizione di SQL Server utilizzata. Nelle edizioni Enterprise e Developer, Query Optimizer confronterà automaticamente questa query con la definizione della vista indicizzata e utilizzerà quest'ultima invece di eseguire la query delle tabelle di base coinvolte. Nella Figura 6 viene illustrato il piano di esecuzione prodotto in questo caso. È composto da una sola operazione: un'operazione Clustered Index Scan dell'indice che è stato creato nella vista. Il costo di esecuzione stimato è solo 0,09023 ed esegue solo 92 letture logiche.

Figura 6** Piano di esecuzione quando viene utilizzata la vista indicizzata **(Fare clic sull'immagine per ingrandirla)

È possibile creare e utilizzare questa vista indicizzata in altre edizioni di SQL Server, ma per ottenere lo stesso effetto è necessario modificare la query per fare riferimento alla vista direttamente utilizzando l'hint NOEXPAND, come mostrato di seguito:

SELECT CustomerID, OrdersTotalAmt
FROM vTotalCustomerOrders WITH (NOEXPAND)

Come si può vedere, se si utilizzano in modo appropriato, le viste indicizzate possono rivelarsi una funzionalità estremamente efficace. L'utilità maggiore si ha soprattutto per l'ottimizzazione delle query che eseguono aggregazioni su grandi quantità di dati. Se utilizzate nell'edizione Enterprise, possono fornire vantaggi a molte query senza richiedere modifiche al codice.

Identificazione delle query da ottimizzare

Come è possibile identificare le query che vale la pena ottimizzare? In genere è consigliabile concentrare l'attenzione alle query che vengono eseguite spesso: è possibile che il costo di una singola esecuzione non sia molto elevato, ma il costo di esecuzione aggregato può essere molto più alto rispetto a quello di una grande query che viene eseguita raramente. Ciò non vuol dire che le grandi query non devono essere ottimizzate, ma semplicemente che è opportuno concentrare l'attenzione su quelle di utilizzo più frequente. Dunque come vengono identificate queste query?

Sfortunatamente, il metodo più affidabile è un po' complicato e comporta l'esecuzione di una traccia di tutte le query eseguite sul server e il relativo raggruppamento in base alle firme, in altre parole, il testo di una query con i valori effettivi dei parametri sostituiti da segnaposti per identificare lo stesso tipo di query anche se questa è stata eseguita con parametri diversi. Questo è un processo complicato, in quanto le firme delle query sono difficili da generare. Itzik Ben-Gan descrive una soluzione che utilizza funzioni CLR definite dall'utente ed espressioni regolari nel suo libro Inside Microsoft SQL Server 2005: Query T-SQL.

Esiste anche un altro metodo molto più semplice, anche se meno affidabile. È possibile affidarsi alle statistiche che vengono conservate per tutte le query nella cache del piano di esecuzione e sottoporle a query mediante viste di gestione dinamiche. Nella Figura 7 viene illustrata una query di esempio che indica il testo e il piano di esecuzione delle 20 query nella cache con il numero maggiore di letture logiche accumulate. Questa query è estremamente utile per identificare rapidamente le query che generano il maggior numero di letture logiche, anche se vi sono però delle limitazioni. Ovvero, verranno mostrate solo le query i cui piani sono memorizzati nella cache al momento dell'esecuzione della query. Se qualcosa non è memorizzato nella cache, andrà perduto.

Figure 7 Identificazione delle prime 20 query più dispendiose in termini di I/O di lettura

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1), 
qs.execution_count, 
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

Dopo avere identificato le query con prestazioni peggiori, dare un'occhiata ai relativi piani di esecuzione e cercare dei metodi per migliorare le relative prestazioni utilizzando alcune delle tecniche di indicizzazione descritte in questo articolo. In caso di risultati positivi, sarà valsa la pena perdere tanto tempo.

Buona ottimizzazione a tutti!

Maciej Pilecki è mentore presso Solid Quality Mentors, un'organizzazione globale specializzata in attività di formazione, assistenza e consulenza. È Microsoft Certified Trainer (MCT) e SQL Server Most Valuable Professional (MVP), tiene spesso corsi e interventi a conferenze su numerosi aspetti di SQL Server e dello sviluppo di applicazioni.

© 2008 Microsoft Corporation e CMP Media, LLC. Tutti i diritti riservati. È vietata la riproduzione completa o parziale senza autorizzazione.