Attenzione a modificare la struttura di una tabella con il Table Designer del Management Studio di SQL

Lo confesso, anche io non presto molta attenzione alle “Options” del SQL Management Studio (SSMS), principalmente perché uso sempre gli script TSQL per modificare (e non solo) la struttura di una tabella, ma la cosa può essere potenzialmente pericolosa per cui ho ritenuto interessante creare questo nuovo post per il nostro blog.

Dunque, lo scenario è quello relativo ad una eventuale modifica della struttura di una tabella, magari molto grossa in termini di spazio occupato e numero di righe contenute, per la quale il DBA sceglie di usare l’interfaccia grafica del “SQL Server Management Studio (SSMS)”, nello specifico il “Table Designer”. Quest è una semplice tabella a 2 campi con 1000 record banali inseriti:

 

Ora proviamo ad aggiungere una colonna altrettanto banale (“valore2” di tipo INT “nullable) alla suddetta tabella:

 

 

Ok, fin qui tutto bene, la colonna è stata aggiunta istantaneamente ed i valori per tutte le righe sono state impostati ovviamente a “NULL”:

Proviamo ora una cosa diversa, cioè la rimozione della stessa colonna, ancora tutto bene, nessun problema e prendendo una trace con il Profiler, la console di SQL tra le altre cose esegue il comando che ci si aspetta:

                ALTER TABLE dbo.T1 DROP COLUMN valore2

 

Proviamo ora a rimuovere l’attributo “Allow Nulls” dalla colonna “valore2”, sempre dal “Table Designer”, non appena clicchiamo per salvare la modifica ecco quello che compare sullo schermo:

Perché appare questo messaggio ? Perché il “Table Designer” vuole distruggere questa tabella e ricrearla ?

La risposta alla prima domanda è nelle “Options” del tool, così appaiono di default (almeno su SQL 2008 R2):

 

La risposta alla seconda domanda è che l’interfaccia grafica talvolta non “traduce” la Vostra modifica in rapporto 1:1 con i comandi TSQL che normalmente un DBA utilizzerebbe; per verificare questo disabilitiamo l’opzione cerchiata in rosso e ripetiamo l’operazione, nel frattempo avviamo una semplice trace con il SQL Profiler per vedere cosa accade in background:

Ovviamente nessun messaggio di errore questa volta, ma andando a vedere cosa c’è nella trace del SQL Profiler abbiamo diverse cose molto interessanti:

 

• C’è tutta una lunga serie di query di controllo che l’interfaccia esegue sul design della tabella, gli attributi, le statistiche, le permission, etc. ; fin qui nulla di strano;
• Alla fine delle query precedenti, ecco la prima cosa interessante (evento SQL “SQL:stmtCompleted”):

• A questo punto ho un sospetto terribile, proseguiamo nell’analisi degli eventi ed ecco cosa troviamo, un travaso brutale di dati dalla tabella originaria (T1) a quella temporanea appena creata (Tmp_T1) e per di più con un lock esclusivo a livello di tabella (TABLOCKX) per tutta la durata dell’operazione di lettura dei dati (HOLDLOCK):

 

• Avrete già capito quello che succede, in ogni modo proseguiamo nell’analisi della trace e troviamo:

 

 

A questo punto ricapitoliamo cosa è successo a fronte della modifica alla struttura della tabella:

 

• Il “Table Designer”, non il database engine di SQL, ha creato una tabella di appoggio (non temporanea !) con la stessa struttura della tabella originaria;
• Bloccando la tabella “T1) in maniera esclusiva e continuata, ha copiato i dati sulla tabella di appoggio appena creata;
• E’ stata “droppata” la tabella originaria;
• E’ stata rinominata la tabella di appoggio usando lo stesso nome della tabella originaria;

 

Se non avete colto la pericolosità di questa cosa permettetemi di aiutarVi, immaginate che la tabella “T1” abbia qualche decina o centinaia di milioni di record o che comunque sia di dimensioni ragguardevoli:

 

• La tabella “T1” viene bloccata in maniera esclusiva e nessun client/applicazione può acceddere: per quanto tempo non è dato saperlo, ma se la tabella è grossa e l’attività elevata la cosa si tradurrà in un blocco esteso e non desiderato;
• Il caricamento dei dati nella tabella temporanea comporterà una grossa (enorme ?) crescita del transaction log del database utente: faccio notare che tale tabella non è temporanea, quindi non userà spazio nel TEMPDB, bensì nel database utente, con tutti i problemi del caso;

Un’ultima considerazione: cosa sarebbe accaduto nel caso questa tabella fosse stata riferita da una o più “Foreign Key Contraint” ? L’operazione di “drop” della tabella originaria sarebbe andata in errore oppure il Table Designer avrebbe eseguito ulteriori attività in background ? Bè, per questo magari dovrete aspettare un ulteriore post su questo blog ! :-)

A questo punto, per i DBA non avvezzi all’uso estensivo del TSQL per la modifica della struttura delle tabelle, una domanda sorge spontanea: c’è un modo più efficiente per fare questa operazione ? Ovviamente sì, ed è anche molto semplice:

 

alter table T1 alter column valore char(8000) not null;

Avendo abilitato le statistiche sull’I/O , l’esecuzione (con successo) del suddetto comando, ecco le informazioni:

Table 'T1'. Scan count 1, logical reads 1000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Liscio e lineare, vero ?

 

La lezione di oggi dal titolo: “In produzione, mai modificare la struttura di una tabella con il Table Designer senza aver prima verificato cosa realmente accade in background, specialmente se la tabella è molto grande” è finita, buon lavoro a tutti ed arrivederci al prossimo post.

 

--Igor Pagliai--