SQL Server 2012: Piano d'esecuzione non ottimale

Perchè una query apparentemente ottimizzata non dovrebbe rivelarsi tale? Perchè il query optimizer, che dovrebbe sempre trovare la strada migliore per evadere le nostre query, dovrebbe scegliere un piano d'esecuzione non ottimale se non scadente?

Le motivazioni potrebbero essere molteplici, statistice non aggiornate, indici frammentati etc...

Una delle cause più frequenti che trovo però è causata da una "cattiva" scrittura delle query da parte dell'utente...

Prendiamo questa tabella semplicissima

Use tempdb
Go

create table testExecutionPlan
(
 id int,
 valore Varchar(25) Unique
)
Go

Insert into testExecutionPlan values(1,'1')
Go

Eseguiamo ora questa query molto semplice ma che potrebbe rivelarsi estremamente onerosa per il nostro server.

Select * from testExecutionPlan
 Where valore = 1
Go

ecco il risultato

Perchè dovrebbe rivelarsi molto onerosa ? Dopotutto restituisce solo una riga e stiamo effettuando una ricerca su un campo indicizzato Unique (estremamente selettivo ), quindi l'execution plan dovrebbe essere composto da una Index Seek !!

Questo è l'execution plan prodotto dall'optimizer

Potrebbe risultare estremamente lenta perchè viene implementato un Table Scan !!
In pratica legge ogni riga contenuta in ogni data page che compone la tabella. Se la tabella fosse composta da migliaia di data pages le operazioni di I/O sarebbero numerosissime e causerebbero un notevole utilizzo del disco, di conseguenza rallenterebbero il server....

Ma perchè accade tutto ciò ? Perchè ho scritto la query nel modo sbagliato, sto passando un valore numerico (1 è privo del carattere ' di delimitazione stringa) in una condizione di ricerca su un campo varchar(25).

Come potete vedere il Predicate dell'operatore Table Scan contiene una CONVERT_IMPLICIT che viene applicata alla colonna valore della nostra tabella determinando l'impossibilità di effettuare una Index Seek sulla colonna stessa.

Per risolvere questo problema basta passare i valori di ricerca nel Tipo/formato corretto, nel nostro caso sotto forma di stringa.

Select * from testExecutionPlan
 Where valore = '1'
Go

Ed ecco il nuovo piano di ricerca

In effetti implementa una Index Seek per reperire la riga.
L'operatore RID Lookup è dovuto al fatto che la nostra query richiede tutti i campi della tabella ma l'indice contiene i dati della colonna valore, l'eventuale clustered key della tabella e il HEAP RID (Fileid+PageId+RowId). Nel caso in questione è presente solo il RID dato che la nostra tabella non ha un indice clustered.
Il campo id deve essere reperito direttamente dalla tabella e per accedervi SQL utilizza il RID Loockup dato che la tabella è un Heap (Tabella priva di indice cluster).
Per evitare l'uso del RID Lookup e/o del KEY Lookup dovremmo lavorare direttamente sulla struttura dell'indice/tabella, ma questa è un'altra storia.... ;-)

Attenzione quindi a come passiamo i parametri alle nostre query, potremmo causare delle Scan invece delle Seek, ottenendo potenzialmente performance scadenti.
Occhio anche a come gli ORM compongono le query.

Ciao

Luca