Indietro

Prossima

Identificare i piani di query problematici

Completato

Per risolvere i problemi di prestazioni delle query, i DBA dovranno prima di tutto identificare la query problematica, in genere la query che usa la quantità più elevata di risorse di sistema, e quindi recuperare il piano di esecuzione di tale query. Esistono due possibili scenari. Una possibilità è che la query abbia costantemente prestazioni scarse. La presenza coerente di prestazioni scarse può essere causata da problemi diversi, inclusi vincoli delle risorse hardware (anche se questa situazione non influisce in genere su una singola query eseguita in isolamento), una struttura di query non ottimale, le impostazioni di compatibilità del database, indici mancanti o la scelta di un piano non ideale da parte di Query Optimizer. Nel secondo scenario, la query viene eseguita correttamente per alcune esecuzioni, ma non per altre. Questo problema può essere causato da alcuni altri fattori, tra i quali i più comuni sono l'asimmetria dei dati in una query con parametri che ha un piano efficiente per alcune esecuzioni e uno scarso per altre esecuzioni. Gli altri fattori comuni associati a prestazioni delle query non coerenti sono il blocco, in cui una query è in attesa del completamento di un'altra query per ottenere l'accesso a una tabella o una contesa hardware.

Ognuno di questi potenziali problemi verrà ora esaminato in maggiore dettaglio.

Vincoli hardware

In genere, i vincoli hardware non si manifestano con singole esecuzioni di query, ma diventano evidenti quando viene applicato il carico di produzione e sono disponibili un numero limitato di thread della CPU e una quantità limitata di memoria da condividere tra le query. L'eventuale condizione di contesa della CPU sarà in genere rilevabile osservando il contatore di monitoraggio delle prestazioni '% tempo processore', che misura l'utilizzo della CPU del server. Esaminando SQL Server in modo più approfondito è possibile che vengano rilevati i tipi di attesa SOS_SCHEDULER_YIELD e CXPACKET quando il server è sotto pressione per la CPU. Tuttavia, in alcuni casi con scarse prestazioni del sistema di archiviazione, anche le singole esecuzioni di una query altrimenti ottimizzata possono risultare lente. È consigliabile tenere traccia delle prestazioni del sistema di archiviazione a livello di sistema operativo usando i contatori di Performance Monitor 'Media letture disco/sec' e 'Media scritture disco/sec' per misurare il tempo necessario per il completamento di un'operazione di I/O. SQL Server scriverà nel log degli errori se rileva prestazioni di archiviazione scarse (se per il completamento di un'operazione di I/O sono necessari più di 15 secondi). La presenza di una percentuale elevata di attese PAGEIOLATCH_SH nelle statistiche di attesa di SQL Server potrebbe indicare un problema di prestazioni del sistema di archiviazione. In genere, le prestazioni hardware vengono esaminate a un livello elevato, all'inizio del processo di risoluzione dei problemi di prestazioni, perché sono relativamente semplici da valutare.

La maggior parte dei problemi di prestazioni del database può essere attribuita a modelli di query non ottimali, ma in molti casi l'esecuzione di query inefficienti causa una pressione eccessiva sull'hardware. Ad esempio, la mancanza di indici potrebbe determinare un utilizzo elevato di CPU, archiviazione e memoria a causa del recupero di più dati del necessario per elaborare la query. Ti consigliamo di verificare le query non ottimali e ottimizzarle prima di risolvere il problema hardware. L'ottimizzazione delle query sarà l'argomento della prossima sezione.

Costrutti di query non ottimali

I database relazionali offrono prestazioni ottimali per l'esecuzione di operazioni basate su set. Le operazioni basate su set consentono di manipolare i dati (INSERT, UPDATE, DELETE e SELECT) in set. Il lavoro viene eseguito su un set di valori e produce un singolo valore o un set di risultati. L'alternativa alle operazioni basate su set consiste nell'eseguire operazioni basate su righe, usando un cursore o un ciclo while. Questo tipo di elaborazione è noto come elaborazione basata su righe e il suo costo aumenta in modo lineare con il numero di righe interessate. La scala lineare è problematica quando i volumi di dati crescono per un'applicazione.

Sebbene sia importante rilevare l'uso non ottimale delle operazioni basate su righe con cursori o cicli WHILE, esistono altri anti-schemi di SQL Server che occorre essere in grado di riconoscere. Le funzioni con valori di tabella, in particolare le funzioni con valori di tabella con più istruzioni, hanno causato modelli di piano di esecuzione problematici prima di SQL Server 2017. Molti sviluppatori preferiscono usare funzioni con valori di tabella con più istruzioni perché possono eseguire più query all'interno di una singola funzione e aggregare i risultati in una singola tabella. Tuttavia, chiunque scriva codice T-SQL deve essere consapevole dei possibili svantaggi a livello di prestazioni associati all'uso delle funzioni con valori di tabella.

SQL Server offre due tipi di funzioni con valori di tabella, inline e con più istruzioni. Se si usa una funzione con valori di tabella, il motore di database la gestisce come una vista. Le funzioni con valori di tabella con più istruzioni vengono gestite come qualsiasi altra tabella durante l'elaborazione di una query. Dato che le funzioni con valori di tabella sono dinamiche e pertanto SQL Server non ha statistiche su di esse, per la stima del costo del piano di query viene usato un numero fisso di righe. Il numero fisso può andare bene se il numero di righe è ridotto. Tuttavia, se la funzione con valori di tabella restituisce migliaia o milioni di righe, il piano di esecuzione potrebbe risultare inefficiente.

Un altro anti-schema è stato l'uso di funzioni scalari, con problemi di stima ed esecuzione simili. Microsoft ha apportato miglioramenti significativi delle prestazioni con l'introduzione dell'elaborazione di query intelligenti, con i livelli di compatibilità 140 e 150.

SARGability

Il termine SARGable nei database relazionali si riferisce a un predicato (clausola WHERE) in un formato specifico che può usare un indice per accelerare l'esecuzione di una query. I predicati nel formato corretto sono detti argomenti di ricerca, in inglese 'Search Argument" da cui l'acronimo SARG. In SQL Server, l'uso di un SARG significa che l'utilità di ottimizzazione valuterà l'uso di un indice non cluster sulla colonna a cui fa riferimento il SARG per un'operazione SEEK, anziché eseguire la scansione dell'intero indice (o dell'intera tabella) per recuperare un valore.

La presenza di un SARG non garantisce l'uso di un indice per un'operazione SEEK. Gli algoritmi di determinazione dei costi dell'utilità di ottimizzazione possono comunque stabilire che l'indice è troppo costoso, ad esempio se un SARG fa riferimento a una percentuale elevata di righe in una tabella. L'assenza di un SARG significa che l'utilità di ottimizzazione non valuterà un'operazione SEEK su un indice non cluster.

Alcuni esempi di espressioni non SARG, talvolta dette non SARGable, sono quelle che includono una clausola LIKE con un carattere jolly all'inizio della stringa per cui trovare una corrispondenza, ad esempio, WHERE lastName LIKE ‘%SMITH%’. Altri predicati che non sono SARG si verificano quando si usano funzioni su una colonna, ad esempio WHERE CONVERT(CHAR(10), CreateDate,121) = ‘2020-03-22’. Queste query con espressioni non SARGable vengono in genere identificate esaminando i piani di esecuzione per le scansioni di indici o di tabelle, in cui le ricerche devono essere eseguite in altro modo.

Esiste un indice sulla colonna City usato nella clausola WHERE della query e fino a quando viene usato in questo piano di esecuzione, è possibile osservare che l'indice viene sottoposto a scansione, il che significa che l'intero indice viene letto. La funzione LEFT nel predicato rende questa espressione non SARGable. L'utilità di ottimizzazione non valuterà l'uso di una ricerca nell'indice sulla colonna City.

Questa query può essere scritta in modo da usare un predicato SARGable. L'utilità di ottimizzazione valuterebbe quindi un'operazione SEEK sull'indice sulla colonna City. Un operatore SEEK dell'indice, in questo caso, leggerebbe un set di righe molto più piccolo, come illustrato di seguito.

La modifica della funzione LEFT in LIKE genera una ricerca di indice.

Nota

La parola chiave LIKE, in questo caso, non ha un carattere jolly a sinistra, quindi esegue una ricerca delle città che iniziano con M. Se fosse "bilaterale" o iniziasse con un carattere jolly ('%M% o '%M'), non sarebbe SARGable. Si stima che l'operazione SEEK restituisca 1267 righe o circa il 15% della stima per la query con il predicato non SARGable.

Altri anti-schemi di sviluppo del database trattano il database come servizio anziché come archivio dati. L'uso di un database per convertire i dati in JSON, manipolare le stringhe o eseguire calcoli complessi può causare un utilizzo eccessivo della CPU e una maggiore latenza. Le query che tentano di recuperare tutti i record e quindi di eseguire calcoli nel database possono causare un utilizzo eccessivo di CPU e I/O. Idealmente, è consigliabile usare il database per operazioni di accesso ai dati e costrutti di database ottimizzati come l'aggregazione.

Indici mancanti

I problemi di prestazioni più comuni riscontrati dagli amministratori di database sono dovuti alla mancanza di indici utili, a causa dei quali il motore deve leggere molte più pagine del necessario per restituire i risultati di una query. Sebbene gli indici non siano gratuiti in termini di risorse (l'aggiunta di indici aggiuntivi a una tabella può influire sulle prestazioni di scrittura e utilizzare spazio), il miglioramento delle prestazioni offerto può compensare i costi delle risorse aggiuntive in molti casi. Spesso i piani di esecuzione con questi problemi di prestazioni possono essere identificati dall'operatore di query Clustered Index Scan o dalla combinazione di Nonclustered Index Seek e Ricerca chiave, che risulta più indicativo di colonne mancanti in un indice esistente.

Il motore di database tenta di fornire assistenza per questo problema segnalando gli indici mancanti nei piani di esecuzione. I nomi e i dettagli degli indici consigliati sono disponibili tramite una DMV denominata sys.dm_db_missing_index_details. Esistono anche altre DMV in SQL Server come sys.dm_db_index_usage_stats e sys.dm_db_index_operational_stats, che evidenziano l'uso degli indici esistenti.

Potrebbe essere opportuno eliminare un indice non usato da alcuna query nel database. Le DMV e gli avvisi dei piani per gli indici mancanti dovrebbero essere usati solo come punto di partenza per l'ottimizzazione delle query. È importante comprendere quali sono le query chiave e creare gli indici a supporto di tali query. Non è consigliabile creare tutti gli indici mancanti senza valutare gli indici nel contesto reciproco.

Statistiche mancanti e non aggiornate

Si è visto quanto siano importanti le statistiche di colonne e indici per Query Optimizer. È anche importante comprendere le condizioni che possono causare statistiche non aggiornate e il modo in cui questo problema può manifestarsi in SQL Server. L'aggiornamento automatico delle statistiche è attivo per impostazione predefinita nelle offerte di Azure SQL. Prima di SQL Server 2016, il comportamento predefinito per l'aggiornamento automatico delle statistiche prevedeva di non aggiornare le statistiche fino a quando il numero di modifiche apportate alle colonne nell'indice era pari a circa il 20% del numero di righe in una tabella. A causa di questo comportamento, potevano essere introdotte modifiche dei dati sufficientemente significative da influire sulle prestazioni delle query, ma non da aggiornare le statistiche. Tutti i piani che usavano la tabella con i dati modificati sarebbero rimasti basati su statistiche non aggiornate e sarebbero risultati spesso non ottimali.

Prima di SQL Server 2016 era possibile usare il flag di traccia 2371, con il quale il numero necessario di modifiche diventava un valore dinamico, in modo che con l'aumento delle dimensioni della tabella si riduceva la percentuale di modifiche delle righe necessarie per attivare un aggiornamento delle statistiche. Le versioni più recenti di SQL Server, del database SQL di Azure e di Istanza gestita di SQL di Azure supportano questo comportamento per impostazione predefinita. È anche disponibile una funzione a gestione dinamica denominata sys.dm_db_stats_properties, che mostra l'ultimo aggiornamento delle statistiche e il numero di modifiche apportate dall'ultimo aggiornamento, in modo da poter identificare rapidamente le statistiche che potrebbero dover essere aggiornate manualmente.

Scelte non ottimali dell'utilità di ottimizzazione

Anche se Query Optimizer gestisce in modo valido l'ottimizzazione della maggior parte delle query, esistono alcuni casi limite in cui l'utilità di ottimizzazione basata sui costi può prendere decisioni rilevanti non del tutto comprensibili. Esistono diversi modi per risolvere questo problema, tra cui l'uso di hint per le query, flag di traccia, forzatura del piano di esecuzione e altri aggiustamenti con lo scopo di ottenere un piano di query stabile e ottimale. Microsoft può contare su un team di supporto che può fornire assistenza per affrontare questi scenari.

Nell'esempio seguente del database AdventureWorks2017 viene usato un hint per la query per indicare all'utilità di ottimizzazione del database di usare sempre il nome di città Seattle. Questo hint non garantirà il piano di esecuzione ottimale per tutti i valori di città, ma sarà prevedibile. Il valore 'Seattle' per @city_name verrà usato solo durante l'ottimizzazione. Durante l'esecuzione verrà usato il valore effettivo fornito (‘Ascheim’).

DECLARE @city_name nvarchar(30) = 'Ascheim',
        @postal_code nvarchar(15) = 86171;

SELECT * 
FROM Person.Address
WHERE City = @city_name 
      AND PostalCode = @postal_code
OPTION (OPTIMIZE FOR (@city_name = 'Seattle');

Come illustrato nell'esempio, la query usa un hint (la clausola OPTION) per indicare all'utilità di ottimizzazione di usare un valore di variabile specifico per compilare il piano di esecuzione.

Sniffing dei parametri

SQL Server memorizza nella cache i piani di esecuzione delle query per usi futuri. Poiché il processo di recupero del piano di esecuzione è basato sul valore hash di una query, il testo della query deve essere identico per ogni esecuzione della query per il piano memorizzato nella cache da usare. Per supportare più valori nella stessa query, molti sviluppatori usano parametri passati tramite stored procedure, come illustrato nell'esempio seguente:

CREATE PROC GetAccountID (@Param INT)
AS

<other statements in procedure>

SELECT accountid FROM CustomerSales WHERE sales > @Param;

<other statements in procedure>

RETURN;

-- Call the procedure:

EXEC GetAccountID 42;

Le query possono anche essere parametrizzate in modo esplicito tramite la procedura sp_executesql. Tuttavia, la parametrizzazione esplicita delle singole query viene in genere eseguita tramite l'applicazione di una qualche forma di istruzione PREPARE ed EXECUTE (a seconda dell'API). Quando il motore di database esegue tale query per la prima volta, la query verrà ottimizzata in base al valore iniziale del parametro, in questo caso 42. Questo comportamento, detto analisi dei parametri, consente di ridurre il carico di lavoro complessivo di compilazione delle query nel server. Tuttavia, in caso di asimmetria dei dati, le prestazioni delle query potrebbero variare notevolmente.

Ad esempio, nel caso di una tabella con 10 milioni di record, dei quali il 99% ha l'ID 1 e l'altro 1% è rappresentato da numeri univoci, le prestazioni saranno basate sull'ID usato inizialmente per ottimizzare la query. Queste prestazioni sensibilmente fluttuanti sono indicative della presenza di un'asimmetria dei dati e non rappresentano un problema intrinseco dell'analisi dei parametri. Questo comportamento è un problema di prestazioni piuttosto comune di cui è importante tenere conto. È necessario conoscere le opzioni a disposizione per arginare il problema. Esistono diversi modi per risolvere questo problema, ma ognuno presenta pro e contro:

• Usare l'hint RECOMPILE nella query o l'opzione di esecuzione WITH RECOMPILE nelle stored procedure. Questo hint causerà la ricompilazione della query o della procedura per ogni esecuzione, con seguente maggiore utilizzo della CPU sul server, ma userà sempre il valore del parametro corrente.
• È possibile usare l'hint per la query OPTIMIZE FOR UNKNOWN. Questo hint farà in modo che l'utilità di ottimizzazione scelga di non analizzare i parametri e confrontare il valore con l'istogramma dei dati della colonna. Questa opzione non offrirà il migliore piano possibile, ma consentirà un piano di esecuzione coerente.
• Riscrivere la stored procedure o le query aggiungendo la logica per i valori dei parametri in modo da usare RECOMPILE solo per i parametri problematici noti. Nell'esempio seguente, se il parametro SalesPersonID è NULL, la query verrà eseguita con OPTION (RECOMPILE).

CREATE OR ALTER PROCEDURE GetSalesInfo (@SalesPersonID INT = NULL)
AS
DECLARE  @Recompile BIT = 0
         , @SQLString NVARCHAR(500)

SELECT @SQLString = N'SELECT SalesOrderId, OrderDate FROM Sales.SalesOrderHeader WHERE SalesPersonID = @SalesPersonID'

IF @SalesPersonID IS NULL
BEGIN
     SET @Recompile = 1
END

IF @Recompile = 1
BEGIN
    SET @SQLString = @SQLString + N' OPTION(RECOMPILE)'
END

EXEC sp_executesql @SQLString
    ,N'@SalesPersonID INT'
    ,@SalesPersonID = @SalesPersonID
GO

L'esempio precedente è una buona soluzione, ma richiede un lavoro di sviluppo notevole e una conoscenza approfondita della distribuzione dei dati. Potrebbe anche richiedere la manutenzione in caso di modifiche ai dati.

Continua