Risoluzione dei problemi di prestazioni comuni relativi a indici hash Memory-Optimized

Questo argomento è incentrato sulla risoluzione e l'aggiramento dei problemi comuni relativi agli indici hash.

La ricerca richiede un subset di colonne chiave dell'indice hash

Questione: Gli indici hash richiedono valori per tutte le colonne chiave di indice per calcolare il valore hash e individuare le righe corrispondenti nella tabella hash. Pertanto, se una query include predicati di uguaglianza solo per un subset delle chiavi di indice nella clausola WHERE, SQL Server non può usare una ricerca di indice per individuare le righe corrispondenti ai predicati nella clausola WHERE.

Al contrario, gli indici ordinati come gli indici non cluster basati su disco e gli indici non cluster ottimizzati per la memoria supportano la ricerca di indici su un subset delle colonne chiave di indice, purché siano le colonne iniziali nell'indice.

Sintomo: Ciò comporta una riduzione delle prestazioni, poiché SQL Server deve eseguire analisi di tabella complete anziché una ricerca di indice, che in genere è un'operazione più veloce.

Come risolvere i problemi: Oltre alla riduzione delle prestazioni, l'ispezione dei piani di query mostrerà un'analisi anziché una ricerca di indice. Se la query è abbastanza semplice, l'ispezione del testo della query e della definizione dell'indice mostrerà anche se la ricerca richiede un subset delle colonne chiave di indice.

Si consideri la tabella e la query seguenti:

CREATE TABLE [dbo].[od]  
(  
     o_id INT NOT NULL,  
     od_id INT NOT NULL,  
     p_id INT NOT NULL,  
     CONSTRAINT PK_od PRIMARY KEY NONCLUSTERED HASH (o_id, od_id) WITH (BUCKET_COUNT = 10000)  
)  
WITH (MEMORY_OPTIMIZED = ON)  
  
 SELECT p_id  
 FROM dbo.od  
 WHERE o_id=1  

La tabella ha un indice hash sulle due colonne (o_id, od_id), mentre la query ha un predicato di uguaglianza su (o_id). Poiché la query dispone di predicati di uguaglianza solo in un subset delle colonne chiave di indice, SQL Server non può eseguire un'operazione di ricerca dell'indice usando PK_od; SQL Server deve invece ripristinare un'analisi completa dell'indice.

Soluzioni alternative: Esistono diverse possibili soluzioni alternative. Per esempio:

• Ricreare l'indice come tipo non clusterizzato anziché hash non clusterizzato. L'indice non cluster ottimizzato per la memoria è ordinato e quindi SQL Server può eseguire una ricerca di indice sulle colonne chiave principali dell'indice. La definizione di chiave primaria risultante per l'esempio sarà constraint PK_od primary key nonclustered.

• Modificare la chiave di indice corrente in modo che corrisponda alle colonne nella clausola WHERE.

• Aggiungere un nuovo indice hash corrispondente alle colonne nella clausola WHERE della query. Nell'esempio la definizione della tabella risultante esamina quanto segue:

  CREATE TABLE dbo.od  
   ( o_id INT NOT NULL,  
   od_id INT NOT NULL,  
   p_id INT NOT NULL,  
  
   CONSTRAINT PK_od PRIMARY KEY   
   NONCLUSTERED HASH (o_id,od_id) WITH (BUCKET_COUNT=10000),  
  
   INDEX ix_o_id NONCLUSTERED HASH (o_id) WITH (BUCKET_COUNT=10000)  
  
   ) WITH (MEMORY_OPTIMIZED=ON)  
  

Si noti che un indice hash ottimizzato per la memoria non viene eseguito in modo ottimale se sono presenti molte righe duplicate per un determinato valore della chiave di indice: nell'esempio, se il numero di valori univoci per la colonna o_id è molto inferiore al numero di righe nella tabella, non sarebbe ottimale aggiungere un indice in (o_id); Modificare invece il tipo di indice PK_od da hash a non cluster sarebbe la soluzione migliore. Per altre informazioni, vedere Determinazione del numero di bucket corretto per gli indici hash.

Vedere anche

Indici nelle tabelle Memory-Optimized