Overview sul livello di compatibilità
La caratteristica di un database denominata "livello di compatibilità" (compatibility level) è stata progettata per consentire di utilizzare database creati su versioni precedenti di SQL Server in favore di una migrazione ad una versione più recente. È sicuramente una delle impostazioni più importanti di un database ed è spesso ignorata o non configurata correttamente, come vediamo dai nostri Health Check o tramite l'utilizzo di SQL Catcher. Se usata consapevolmente, può infatti portare ad inaspettati benefici in termini di performance.
Cos'è il livello di compatibilità di un database
Si tratta, come appena accennato, di una configurazione a livello di singolo database che "decide" quali funzionalità di SQL Server, in termini di linguaggio e di ottimizzazioni, vengono utilizzate dal motore. Modificare il valore di questo parametro può cambiare radicalmente, ad esempio, il piano di esecuzione di una query o rendere inutilizzabile il codice di una stored procedure perché al suo interno è utilizzato un comando deprecato nella versione di SQL Server che stiamo utilizzando.
Possiamo modificare facilmente questa impostazione a caldo, senza interruzione per gli utenti, da Management Studio, nella tab Options delle proprietà del database:
Oppure utilizzare il linguaggio T-SQL per ottenere lo stesso risultato:
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 150
Una cosa curiosa, che avrete già notato, è che i codici che definiscono questo livello non sono allineati con la nomenclatura "commerciale" di SQL Server ma seguono la sua numerazione interna. Ad esempio, SQL Server 2012 internamente è definito come versione 11.x ed il suo livello di compatibilità è 110. Qui trovate un elenco completo di tutte le versioni di SQL Server dalla 2000 alla 2019 ed i livelli di compatibilità supportati.
| Product | Database Engine Version | Default Compatibility Level Designation | Supported Compatibility Level Values |
|---|---|---|---|
| SQL Server 2019 (15.x) | 15 | 150 | 150, 140, 130, 120, 110, 100 |
| SQL Server 2017 (14.x) | 14 | 140 | 140, 130, 120, 110, 100 |
| Azure SQL Database | 12 | 150 | 150, 140, 130, 120, 110, 100 |
| Azure SQL Database Managed Instance | 12 | 150 | 150, 140, 130, 120, 110, 100 |
| SQL Server 2016 (13.x) | 13 | 130 | 130, 120, 110, 100 |
| SQL Server 2014 (12.x) | 12 | 120 | 120, 110, 100 |
| SQL Server 2012 (11.x) | 11 | 110 | 110, 100, 90 |
| SQL Server 2008 R2 | 10.5 | 100 | 100, 90, 80 |
| SQL Server 2008 | 10 | 100 | 100, 90, 80 |
| SQL Server 2005 (9.x) | 9 | 90 | 90, 80 |
| SQL Server 2000 (8.x) | 8 | 80 | 80 |
Creazione di un nuovo database
Quando creiamo un nuovo database utente, il suo livello di compatibilità sarà impostato a quello della versione di SQL Server sulla quale stiamo lavorando. In realtà, il nuovo database viene creato facendo un clone del database di sistema "model". Potremmo cambiare il livello di compatibilità di questo database per fare in modo che tutti i nuovi database utente che creeremo da ora in avanti utilizzino questo livello. Questo concetto vale anche per molte altre proprietà che possiamo impostare a livello di singolo db.
Restore di un database e attach
Se effettuiamo il restore di un database, ad esempio da SQL Server 2014 a SQL Server 2019, il livello di compatibilità del database sarà mantenuto alla versione 120. Questo comportamento è "by design" in quanto si è voluto garantire un livello di confidenza da parte dell'utente sul fatto che le applicazioni e tutti gli oggetti presenti all'interno del database continuino a comportarsi come se fosse all'interno di SQL Server 2014.
L'unica eccezione a questa regola avviene quando effettuiamo il restore di un database che originariamente ha un livello di compatibilità troppo basso per essere supportato dalla versione che stiamo utilizzando di SQL Server. In questo caso il livello di compatibilità del database, dopo il restore, sarò portato alla versione minima supportata.
Stesso comportamento vale per l'operazione di "attach" di un database.
Nota importante: esiste un altro valore che definisce la versione dei file del database e che non va confuso con il livello di compatibilità. Quando effettuiamo il restore di un database su un SQL Server sul quale gira una versione più recente rispetto a quella di appartenenza originaria del database, la versione dei file viene aggiornata e non potremo più tornare indietro ad una versione precedente.
Il numero magico: 120
Le cose cominciano a diventare interessanti quando passiamo ad un livello di compatibilità di 120 (SQL Server 2014) o superiore. Con questa versione, infatti, è stato introdotto il nuovo "cardinality estimator" (CE), componente fondamentale durante l'esecuzione di una query perché predice il numero di record restituito da ogni singolo passaggio durante l'esecuzione della query stessa. Ma di questo parleremo in un prossimo articolo.
Conclusioni sul livello di compatibilità
Come detto all'inizio del post, agendo su questo parametro, i risultati, in termini di esecuzione della query, potrebbero essere sorprendentemente positivi. Ma attenzione ad abusare nell'utilizzo e modificare la proprietà senza farsi tanti problemi, perché come nella stragrande maggioranza dei casi, prima di portare in produzione una modifica di questo tipo è buona abitudine, se non doverosa, effettuare dei test di regressione per capire l'impatto, positivo o negativo che sia.
Leggi anche: il livello di compatibilità di un database, parte seconda.
di Cristiano Gasparotto, pubblicato il 6 novembre 2020
