Il livello di compatibilità di un database: parte 2 

Nel precedente articolo abbiamo visto cos’è il livello di compatibilità di un database. Oggi vediamo nel dettaglio come utilizzarlo a nostro vantaggio, soprattutto nelle ultime versioni di SQL Server, per migliorare le performance del nostro database.

Con SQL Server 2012 e versioni precedenti, il livello di compatibilità del database veniva utilizzato principalmente per controllare se le nuove funzionalità introdotte con una particolare versione di SQL Server fossero abilitate o meno e viceversa per le vecchie funzionalità non più supportate. Fino alla versione 110, questa configurazione è stata utilizzata anche come metodo per mantenere una migliore compatibilità delle applicazioni con le versioni precedenti di SQL Server. Questa configurazione agisce solo a livello di database e non ha alcun effetto a livello di istanza. Attenzione quindi se, pur mantenendo la compatibilità a livello di database, utilizziamo delle feature a livello di istanza che non esistono più o sono cambiate (ad esempio qualche Dynamic Management View o System Catalog View).

Qualsiasi sia la versione di SQL Server, possiamo classificare tutti questi cambiamenti di comportamento in quattro classi:

• Funzionalità deprecate (deprecated features): si tratta di funzionalità pianificate per essere rimosse in una versione futura di SQL Server, ma che sono ancora disponibili nella versione attuale del prodotto. Ci sono funzionalità deprecate da anni, ma ancora attive!
• Funzionalità non più disponibili (discontinued functionalities): sono rappresentate da funzionalità che esistevano in una versione precedente, ma che non funzioneranno più con la versione attuale;
• Modifiche di rottura (breaking changes): si tratta di modifiche al comportamento di SQL Server che potrebbero interrompere il codice quando viene innalzato il livello di compatibilità da una versione precedente. Ad esempio, una funzione di sistema che richiede parametri aggiuntivi.
• Modifiche al comportamento (behavior changes): rappresentano modifiche che influiscono sul modo con cui si comportano alcune funzionalità nei confronti di SQL Server. I parametri opzionali o le modifiche nelle conversioni di tipo rappresentano ad esempio modifiche del comportamento del linguaggio TSQL.

Nella pagina dedicata della documentazione Microsoft potete trovare un elenco di tutte queste funzionalità per la versione 2019 ed i riferimenti per le versioni precedenti. 

Come accennato nel precedente articolo, il cambiamento più importante nella gestione del livello di compatibilità del database è stato introdotto con SQL Server 2014. Da questa versione (120) è stato infatti modificato in maniera importante un componente del Query Optimizer chiamato “cardinality estimator” (CE). Se avete quindi modificato il livello di compatibilità a 120 e avete SQL Server 2014 aspettatevi che il nuovo CE sia entrato in azione: nella maggior parte dei casi le query vengono eseguite più velocemente, ma purtroppo è abbastanza comune imbattersi in alcune query che presentavano importanti regressioni delle prestazioni con il nuovo componente. Con SQL Server 2014 è possibile disabilitare il nuovo CE utilizzando un “hint” su una specifica query in questo modo (TF 9481).

Nota: da SQL 2014 in poi, ogni nuova versione di SQL Server ha portato con sé anche una nuova versione del CE. Quindi, ad oggi con SQL Server 2019, esistono almeno 5 versioni diverse del componente che possiamo scegliere in base al livello di compatibilità del database che impostiamo.

Con SQL Server 2016, oltre ad un nuovo “cardinality estimator” (CE130), sono state introdotte alcune nuove configurazioni a livello di database tra cui due particolarmente rilevanti per quanto riguarda il livello di compatibilità del database stesso:

-  LEGACY_CARDINALITY ESTIMATION: abilita il vecchio CE (CE70) indipendentemente dall’impostazione del livello di compatibilità del database. È equivalente al trace flag 9481, ma interessa solo il database in questione, non l'intera istanza.

-  QUERY_OPTIMIZER_HOTFIXES: equivale al trace flag 4199 a livello di database. Quando si utilizza il livello di compatibilità 130, SQL Server 2016 abiliterà tutti gli hotfix apportati al Query Optimizer prima di SQL Server 2016 RTM. Se abilitiamo il TF 4199 o il parametro QUERY_OPTIMIZER_HOTFIXES, saranno utilizzati anche tutti gli hotfix di Query Optimizer rilasciati dopo SQL Server 2016 RTM.

 In questo caso è possibile disabilitare il nuovo CE agendo sull’opzione LEGACY_CARDINALITY_ESTIMATION mantenendo inalterato il livello di compatibilità del database.

Quando si utilizza SQL Server 2017 o versioni successive, l'utilizzo del livello di compatibilità del database 140 utilizzerà il nuovo “cardinality estimator” CE140 come impostazione predefinita. Sono ovviamente presenti anche tutte le altre modifiche relative alle prestazioni della versione CE130. Un elenco completo delle features di questa versione può essere consultato qui: Differences between Compatibility Level 130 and Level 140.

SQL Server 2017 ha introdotto anche alcune interessanti funzionalità nell’ambito dell’adaptive query processing, features che sono abilitate di default quando stiamo utilizzando un database con un livello di compatibilità impostato a 140. Queste funzionalità includono batch mode memory grant feedback, batch mode adaptive joins, e interleaved execution.

Con questa versione, sono stati apportati ulteriori miglioramenti alle prestazioni e modifiche del comportamento. Un esempio tra tutti è lo scalar UDF inlining, che trasforma automaticamente, all’interno del piano di esecuzione, le funzioni UDF scalari in espressioni o subqueries. Questa nuova funzionalità rappresenta sicuramente uno dei miglioramenti delle prestazioni più importanti per database che utilizzano in modo estensivo le funzioni scalari. 

Esistono anche diciotto nuove opzioni di configurazione a livello di database che offrono il controllo di più elementi precedentemente interessati da trace flag o dal livello di compatibilità del database.

Un elenco completo delle features di questa versione può essere consultato qui: Differences between Compatibility Level 140 and Level 150.

Data Migration Assistant (DMA) è uno strumento gratuito che ci consente di valutare la configurazione e il codice TSQL di un database SQL Server e identificare eventuali problemi che potremmo incontrare durante la migrazione a una versione più recente del nostro DBMS. Ci consentirà quindi di individuare tutti i problemi di compatibilità che abbiamo discusso precedentemente. Oltre a questo, fa molto altro: vi invito a leggere i dettagli nella documentazione Microsoft su DMA.

DMA è l’evoluzione di un altro strumento, esistito sino ai tempi di SQL Server 2014, denominato Database Upgrade Advisor. 

di Cristiano Gasparotto, pubblicato il 13 novembre 2020.