Home > Articoli > Detect Heap Tables

Detect Heap Tables

Quando progettiamo una nuova tabella, è molto importante decidere di creare o meno l’indice cluster.

Una tabella che non ha l’indice cluster è chiamata anche tabella HEAP, mentre una tabella su cui insiste un indice cluster è definita clustered table. Se non ci poniamo il problema, di creare o meno il clustered index in fase di progettazione, probabilmente non ci penseremo più fino a quando gli utenti (eventualmente) non si lamenteranno delle performance della nostra soluzione (database o applicativa).

L’esperienza mi ha insegnato che si tende a disegnare correttamente le tabelle fondamentali di un DB, progettando gli indici in funzione delle query che verranno eseguite per inserire, modificare e accedere ai dati. La fase di progettazione e disegno del DB serve proprio a questo. Nelle successive implementazioni, fatte "in corsa", per svariati motivi il più delle volte si trascura la fase di progettazione.

Ho avuto l’occasione di verificare alcune situazioni nelle quali il DB è stato implementato in più step e a più mani, alcune tabelle sprovviste dell’indice cluster (tabelle HEAP) e anche di indici non-cluster, nel corso del tempo, sono passate da pochi record (quelli utilizzati per i test), a diverse centinaia di miglia, se non milioni… con conseguente degrado delle performance nelle operazioni di selezione e aggiornamento dei dati. In questi casi le prestazioni degradano sensibilmente col passare del tempo, fino a diventare inaccettabili, l’intero sistema subisce un rallentamento perché alcune risorse sono accedute con grande spreco di memoria forzando il query optimizer ad accedere alla tabella con operazioni di SCAN anche in presenza di una clausola WHERE.

Fortunatamente, la determinazione delle tabelle HEAP presenti su un DB, è semplice. Quando non conosco a fondo la struttura di un database e ci sono problemi di performance, oltre ad alcune DMV’s utilizzo anche la query riportata di seguito; per ogni tabella HEAP presente nel DB e per ogni partizione utilizzata dalla tabella, esiste una riga nella vista di sistema sys.partitions con index_id = 0.

use [<db_name>];
go

select
  o.name
  ,o.type
  ,o.create_date
  ,p.rows
  ,p.filestream_filegroup_id
  ,p.data_compression
  ,p.data_compression_desc
from
  sys.partitions p
join
  sys.objects o on o.object_id=p.object_id
where
  (p.index_id = 0)
  and (o.type = ‘U’)
order by
  p.rows desc;
go

Credetemi, si scoprono situazioni interessanti, come quella illustrata in figura 1.

Senza aggiungere altro, la situazione in figura 1, potrebbe anche essere corretta, ma se controllando le query che impiegano più risorse (per essere eseguite), scopriamo che la tabella dbo.TEXTRIC è acceduta sistematicamente, centinaia di volte in un giorno, filtrando per la colonna DATARIFERIMENTO di tipo DateTime… allora questa situazione rappresenta una criticità.

Detect Heap Table

Figura 1 – Detect HEAP table

Per completezza è giusto ricordare anche i casi in cui è conveniente utilizzare tabelle HEAP, ovvero:

  • Quando una tabella contiene dati volatili, dove le righe vengono modificate frequentemente. In questi casi l’overhead relativo alla manutenzione dell’indice ha un costo maggiore rispetto al guadagno dovuto alla presenza dell’indice stesso
  • Quando una tabella contiene poche righe (dieci, cento righe). In questi casi la scansione dell’intera tabella (operatore TABLE SCAN nel piano di esecuzione) può essere più preformante rispetto alla manutenzione e all’utilizzo dell’indice
  • Quando una tabella contiene principalmente dati duplicati. Anche in questi casi l’utilizzo dell’operatore TABLE SCAN è più preformante rispetto all’utilizzo dell’operatore INDEX LOOKUP
  • Quando una tabella contiene dati che vengono raramente letti, come ad esempio il log di un meccanismo di auditing. La presenza di un indice, oltre allo spazio occupato introduce un overhead (dovuto alle operazioni di manutenzione) non necessario. 

Conclusioni

L’obiettivo di questo articolo non è quello di bandire l’utilizzo delle tabelle HEAP, ma di verificare la consapevolezza del loro utilizzo. L’uso consapevole è ammesso, non sono ammesse le sviste.

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Automazione delle attività di manutenzione in Azure SQL Database

Introduzione Azure SQL Database non possiede un servizio di schedulazione nativo paragonabile a SQL Agent …