Molti ritengono che avere un server abbastanza potente e una base dati non così grande e critica siano buone ragioni per risparmiare sulla qualità della progettazione.

Eppure il caso di un cliente, che si è ritrovato “in eredità” una base dati mal progettata fin dall’inizio, mi è servito per ricordare ancora una volta che questo ragionamento è davvero pericoloso!

Benché il manager di questa azienda sia una persona lungimirante, si è trovato ad affrontare i problemi derivanti dalla cattiva progettazione effettuata dal suo predecessore.

I risultati che possono venir fuori in situazioni del genere possono essere frustranti sia per te che per il tuo team, il quale potrebbe trovarsi a non poter lavorare a causa di problemi che potevano essere presi in considerazione fin dall’inizio.

Performance significa anche non tener fermi i tuoi collaboratori

Uno dei problemi tipici che si trova ad affrontare un professionista occupandosi di basi dati è quello dei deadlocks.

In gergo, un deadlock è una situazione in cui due o più processi o azioni si bloccano a vicenda, aspettando che uno esegua una certa azione (es. rilasciare il controllo su una risorsa come una tabella, ecc…) che serve all’altro e viceversa

In questo articolo tieni presente che sto parlando dell’ambiente Microsoft SQL Server, e questo vale in particolare quando ti parlerò di strumenti e di alcuni particolari.

Perchè si verificano i deadlock?

Per farla semplice questo accade quando più di una sessione tenta di accedere o modificare la stessa risorsa. Per ragioni di sicurezza sull’integrità dei dati, il motore di SQL Server infatti blocca una o più risorse. Accade con lo scopo che altre sessioni (ad esempio altri utenti collegati al sistema) non possano leggere o scrivere gli stessi dati che qualcun altro sta modificando.

Ad esempio supponiamo che l’utente Rossi e l’utente Verdi, in contemporanea, vogliano accedere alla solita tabella “Listini”.

Supponiamo che l’utente Rossi lanci un comando di aggiornamento sulla tabella Listini che richiede molto, molto tempo: il motore del database “segnalerà” ad altri utenti (che chiamiamo sessioni) che la tabella (o una parte di essa) Listini al momento è bloccata (esistono diversi tipi di blocco, lock, ma non ci interessano al momento per capire cosa accade ad alto livello).

Nel momento in cui l’utente Verdi lancia un altro comando sempre sulla tabella Listini accade che il motore del database lo tiene fermo in attesa che il comando lanciato dall’utente Rossi abbia terminato.

Se questa situazione non si risolve (e succede, ah se succede!) il motore del database, ad un certo punto, deciderà di sopprimere uno dei due comandi.

Quando avviene questa “soppressione” si parla di deadlock, mentre quando si parla delle attese (che magari porteranno ad un deadlock) si parla generalmente di lock acquisiti su una tabella.

Anche un semplice lock però può essere fonte di attese e problemi che non dovrebbero esistere.

Cosa fare per analizzare i lock esistenti al momento sul tuo database?

Scendendo un po’ di più nei tecnicismi, esistono diversi approcci per identificare i lock esistenti.

Un modo per avere evidenza della situazione è quello di utilizzare le viste di sistema come ti riporto di seguito

-- QUERY 1
SELECT
    request_session_id as s_id, 
	resource_type, 
	resource_associated_entity_id,
    request_status, 
	request_mode
FROM sys.dm_tran_locks
WHERE resource_database_id = db_id('NOME_DB')
ORDER BY resource_type;


-- QUERY 2
SELECT 
    object_name(p.object_id) as Resource,
    p.object_id,
    p.hobt_id
FROM sys.partitions p
INNER JOIN sys.dm_tran_locks t on t.resource_associated_entity_id = p.hobt_id
WHERE t.resource_database_id = db_id('NOME_DB');

Come puoi vedere tramite la “QUERY 2” è possibile avere una vista immediata delle risorse che stanno subendo dei lock.

Ma che dire se volessimo avere una visione più completa della situazione attuale?

Partendo dalle solite viste usate nel codice precedente è possibile risalire ai comandi che si stanno attualmente bloccando a vicenda, come puoi vedere nel listato seguente.

-- QUERY 3
SELECT  L.request_session_id AS SPID, 
    DB_NAME(L.resource_database_id) AS DatabaseName,
    O.Name AS LockedObjectName, 
    P.object_id AS LockedObjectId, 
    L.resource_type AS LockedResource, 
    L.request_mode AS LockType,
    ST.text AS SqlStatementText,        
    ES.login_name AS LoginName,
    ES.host_name AS HostName,
    TST.is_user_transaction as IsUserTransaction,
    AT.name as TransactionName,
    CN.auth_scheme as AuthenticationMethod
FROM    
    sys.dm_tran_locks L
    JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
    JOIN sys.objects O ON O.object_id = P.object_id
    JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
    JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
    JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
    JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
    CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id

Queste non sono altro che poche righe di codice utili ad identificare parte del problema, e sopratutto non sono l’unico approccio possibile.

Ad esempio SQL Server ci mette a disposizione degli strumenti per “registrare” eventuali deadlock accaduti nel tempo in modo da poterli analizzare a posteriori, come gli Extended Events.

Una buona progettazione può prevenire questi problemi?

Molti di questi problemi devono essere analizzati su carichi di produzione, in situazioni reali, perché le variabili che intervengono sul sistema sono davvero tante.

Ma è anche vero che una buona progettazione può risolvere la maggior parte di queste occorrenze andando ad ottimizzare quelle parti che un buon professionista sa essere delicate.

Ad esempio fin dalle prime fasi dell’analisi è possibile identificare alcuni fattori che saranno utili se non essenziali ai fini delle performance:

  • La mole dei dati che saranno gestiti nelle tabelle del database
  • Il tasso di crescita dei dati
  • Le query che saranno eseguite per poter implementare in prima approssimazione gli indici (te ne parlerò prossimamente)
  • I piani di isolamento delle transazioni

E non pensare che per migliorare le performance basti implementare tutte le possibili soluzioni! Potresti ottenere i risultati opposti! Il sistema deve sempre rimanere più leggero possibile!

E tu cosa ci guadagni?

Ovviamente la domanda è: ma perché affidarmi ad un professionista? Non sono solo dettagli?

No.

Non sono dettagli perché succede spesso di avere un database che in fase di sviluppo “funziona” correttamente. Ma una volta messo in produzione, con i carichi reali, si verificano rallentamenti tali da impedire il lavoro in azienda. E sicuramente non è il miglior momento per accorgersene.

Ecco perché dovresti prendere in considerazione la possibilità di affidarti ad un professionista del settore, che sappia esattamente dove mettere le mani per evitare che tu passi brutti quarti d’ora.

Nel mio percorso ho raccolto molte informazioni interessanti in merito agli argomenti di cui ti ho parlato in questo articolo, ed ho scritto un libro “Why Your Data Matter”.

Essendo il frutto della mia passione ed esperienza diretta, ho scelto di mettere questo libro gratuitamente a disposizione di tutti gli IT Manager ed i CIO delle aziende che come te vogliono ottenere grandi risultati dalle loro scelte e dal loro lavoro (evitando di trovarsi in situazioni scomode e da risolvere con urgenza).

Ti invito a leggere le prime pagine scaricandole!
Se poi ti piacerà sarò felice di inviartene una copia GRATUITA direttamente nel tuo ufficio.  

Clicca qui per scaricare l’estratto del mio libro (se ti piacerà te lo invierò in formato cartaceo!) ==> il mio libro