Sql server: Chiusura automatica database e timeout

Oggi mi è capitato un problema subdolo, alcune query che fino a pochi giorni fa funzionavano regolarmente senza essere particolarmente lente andavano saltuariamente in timeout.

Il motivo non poteva essere un intasamento sul server dal momento che era presente solo personale necessario per l’inventario e anche se le query eseguivano computazioni sui dati relativi all’intero anno in corso non erano particolarmente lente e dall’analisi del piano di esecuzione risultava che utilizzano gli indici in modo corretto.

Il motivo era invece dovuto al fatto che il database relativo ad un magazzino automatico era gestito da un SQL Server 2005 Express e per impostazione predefinita le versioni Express creano i database con l’opzione di Chiusura Automatica abilitata per default.

Con tale opzione abilitata  il database viene chiuso e le risorse corrispondenti vengono liberate dopo la disconnessione dell’ultimo utente. Questo implica però che in situazioni in cui ad operare sul database siano pochi utenti quest’ultimo venga continuamente chiuso e un successivo comando per essere eseguito deve prima attendere che venga riaperto.

In questo scenario ai tempo necessario per l’esecuzione della query occorre aggiungere anche il tempo di apertura del database e quindi paradossalmente con meno utenti che lavorano si rischiano i timeout.

Nel mio caso la soluzione quindi è stata quella di disabilitare la chiusura automatica dal momento che è stato SQL Server Express perché il database ha una dimensione assolutamente inferiore al limite massimo imposto dalla versione Express risparmiando così il costo della licenza di un DBMS.

L’opzione di Chiusura automatica ha invece finalità di portabilità del database, non richieste nel mio caso, come si può vedere dal seguente Panoramica di SQL Server 2005 Express Edition:

La funzionalità di chiusura automatica era disponibile già in SQL 2000 e in SQL Server Express è attivata per impostazione predefinita. Tale funzionalità rilascia
i blocchi a livello di file sui database utente, quando non sono presenti connessioni attive a questi ultimi. Il database è pertanto pronto per lo spostamento o per la copia subito dopo la chiusura dell’applicazione che lo utilizza.

Dal punto di vista dell’utente, tuttavia, la chiusura automatica non funziona sempre, specialmente in caso di arresto imprevisto del client oppure quando si utilizza un pool di connessioni. In caso di arresto imprevisto del client, dopo qualche tempo le connessioni vengono eliminate automaticamente dal meccanismo di timeout.
Se invece è attivato il pooling delle connessioni, quando l’utente richiama la chiusura di una connessione quest’ultima non viene effettivamente chiusa, ma viene restituita al pool. Per chiudere tutte le connessioni è in genere necessario chiudere il dominio applicazione oppure tutte le connessioni aperte. ADO.NET include anche funzioni quali ClearPool, che consentono la reimpostazione manuale.

Per ulteriori informazioni si veda Proprietà database (pagina Opzioni).