sys.dm_db_xtp_hash_index_stats (Transact-SQL)

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di Azure

Queste statistiche sono utili per comprendere e ottimizzare i conteggi dei bucket per gli indici hash nelle tabelle ottimizzate per la memoria. Possono anche essere utilizzate per rilevare i casi in cui la chiave di indice ha molti duplicati.

Una lunghezza media della catena elevata indica che è stato eseguito l'hashing nello stesso bucket di molte righe. Questa situazione potrebbe verificarsi:

  • Se il numero di bucket vuoti è insufficiente o le lunghezze media e massima della catena sono simili, è probabile che il numero totale di bucket sia troppo basso. Per questo motivo, viene eseguito l'hashing nello stesso bucket di molte chiavi di indice.

  • Se il numero di bucket vuoti è elevato o la lunghezza massima della catena è elevata rispetto alla lunghezza media della catena, esistono due spiegazioni probabili. Esistono molte righe con valori di chiave di indice duplicati o si verifica un'asimmetria nei valori della chiave. In entrambi i casi, tutte le righe con lo stesso hash del valore della chiave di indice nello stesso bucket, con una lunghezza lunga della catena in tale bucket.

Le lunghezze della catena lunga possono influire significativamente sulle prestazioni di tutte le operazioni DML su singole righe, tra cui SELECT e INSERT. Le catene corte associate a un numero di bucket vuoto elevato indicano un valore bucket_count troppo elevato. Ne consegue un calo delle prestazioni delle analisi degli indici.

Avviso

Questa DMV analizza l'intera tabella. Pertanto, se nel database sono presenti tabelle di grandi dimensioni, sys.dm_db_xtp_hash_index_stats l'esecuzione potrebbe richiedere molto tempo.

Per altre informazioni, vedere Indici hash per tabelle ottimizzate per la memoria.

Nome colonna Tipo Descrizione
object_id int ID oggetto della tabella padre.
xtp_object_id bigint ID della tabella ottimizzata per la memoria.
index_id int ID indice.
total_bucket_count bigint Numero totale di bucket di hash nell'indice.
empty_bucket_count bigint Numero totale di bucket di hash vuoti nell'indice.
avg_chain_length bigint La lunghezza media della riga concatena tutti i bucket di hash nell'indice.
max_chain_length bigint Lunghezza massima delle catene di righe nel bucket di hash.
xtp_object_id bigint ID oggetto OLTP in memoria che corrisponde alla tabella ottimizzata per la memoria.

Autorizzazioni

È richiesta l'autorizzazione VIEW DATABASE STATE per il database.

Autorizzazioni per SQL Server 2022 e versioni successive

È richiesta l'autorizzazione VIEW DATABA edizione Standard PERFORMANCE STATE per il database.

Esempi

R. Risolvere i problemi relativi al numero di bucket dell'indice hash

La query seguente può essere usata per risolvere i problemi relativi al numero di bucket dell'indice hash di una tabella esistente. La query restituisce statistiche sulla percentuale di bucket vuoti e sulla lunghezza della catena per tutti gli indici hash nelle tabelle utente.

  SELECT  
    QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [table],   
    i.name                   as [index],   
    h.total_bucket_count,  
    h.empty_bucket_count,  
    FLOOR((  
      CAST(h.empty_bucket_count as float) /  
        h.total_bucket_count) * 100)  
                             as [empty_bucket_percent],  
    h.avg_chain_length,   
    h.max_chain_length  
  FROM sys.dm_db_xtp_hash_index_stats as h   
  INNER JOIN sys.indexes as i  
            ON h.object_id = i.object_id  
           AND h.index_id  = i.index_id  
    INNER JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
    INNER JOIN sys.tables t on h.object_id=t.object_id
  WHERE ia.type=1
  ORDER BY [table], [index];

Per informazioni dettagliate su come interpretare i risultati di questa query, vedere Risoluzione dei problemi relativi agli indici hash per le tabelle ottimizzate per la memoria.

B. Statistiche sugli indici hash per le tabelle interne

Alcune funzionalità usano tabelle interne che usano indici hash, ad esempio indici columnstore nelle tabelle ottimizzate per la memoria. La query seguente restituisce statistiche per gli indici hash nelle tabelle interne collegate alle tabelle utente.

  SELECT  
    QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [user_table],
    ia.type_desc as [internal_table_type],
    i.name                   as [index],   
    h.total_bucket_count,  
    h.empty_bucket_count,  
    h.avg_chain_length,   
    h.max_chain_length  
  FROM sys.dm_db_xtp_hash_index_stats as h   
  INNER JOIN sys.indexes as i  
            ON h.object_id = i.object_id  
           AND h.index_id  = i.index_id  
    INNER JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
    INNER JOIN sys.tables t on h.object_id=t.object_id
  WHERE ia.type!=1
  ORDER BY [user_table], [internal_table_type], [index];

Il numero di bucket di indice nelle tabelle interne non può essere modificato, pertanto l'output di questa query deve essere considerato solo informativo. Non è necessaria alcuna azione.

Questa query non deve restituire righe, a meno che non si usi una funzionalità che usa indici hash nelle tabelle interne. La tabella ottimizzata per la memoria seguente contiene un indice columnstore. Dopo aver creato questa tabella, verranno visualizzati gli indici hash nelle tabelle interne.

  CREATE TABLE dbo.table_columnstore
  (
      c1 INT NOT NULL PRIMARY KEY NONCLUSTERED,
      INDEX ix_columnstore CLUSTERED COLUMNSTORE
  ) WITH (MEMORY_OPTIMIZED=ON);

Contenuto correlato