Ottimizzazione dell'archiviazione database

Completato

Per ottimizzare l'archiviazione del database, è necessario considerare la configurazione di riempimento proporzionale e di tempdb.

Informazioni sulle prestazioni di I/O

Le prestazioni di I/O possono essere fondamentali per un'applicazione di database. Azure SQL astrae l'utente dal posizionamento fisico dei file, ma sono disponibili metodi per assicurarsi di ottenere le prestazioni di I/O necessarie.

Le operazioni di I/O al secondo (IOPS) possono essere importanti per l'applicazione. Assicurarsi di aver scelto il livello di servizio e i vCore adatti per le proprie esigenze di operazioni di I/O al secondo. Informazioni su come misurare le operazioni di I/O al secondo per le query in locale se si esegue la migrazione ad Azure. Se sono presenti restrizioni relative alle operazioni di I/O al secondo, è possibile riscontrare attese di I/O lunghe. Nel modello di acquisto di vCore, è possibile aumentare le prestazioni dei vCore o passare al livello Business Critical o Iperscalabilità se non si dispone di un numero sufficiente di operazioni di I/O al secondo. Per i carichi di lavoro di produzione, quando si usa l’unità di elaborazione di database, è consigliabile passare al livello Premium.

La latenza di I/O è un altro componente chiave per le prestazioni di I/O. Per una latenza di I/O più veloce per il database SQL di Azure, prendere in considerazione business critical o iperscalabilità. Per una latenza di I/O più veloce per Istanza gestita di SQL, passare al livello business critical o aumentare le dimensioni dei file o il numero di file per il database. Per migliorare la latenza del log delle transazioni, può essere necessario usare transazioni con più istruzioni.

File e filegroup

I professionisti di SQL Server spesso usano file e filegroup per migliorare le prestazioni di I/O tramite il posizionamento di file fisici. Azure SQL non consente agli utenti di collocare i file in sistemi di dischi specifici. Azure SQL prevede tuttavia impegni di risorse per le prestazioni di I/O relative a frequenze, operazioni di I/O al secondo e latenze. In questo modo, l'astrazione dell'utente dal posizionamento fisico dei file può essere un vantaggio.

Il database SQL di Azure include un solo file di database (il livello Hyperscale in genere ne prevede diversi) e la dimensione massima viene configurata tramite le interfacce di Azure. Non è disponibile alcuna funzionalità per creare altri file.

L'Istanza gestita di SQL di Azure supporta l'aggiunta di file di database e la configurazione di dimensioni, ma non il posizionamento fisico dei file. È possibile usate il numero di file e le dimensioni dei file per Istanza gestita di SQL per migliorare le prestazioni di I/O. I filegroup definiti dall'utente sono inoltre supportati per Istanza gestita di SQL per scopi di gestibilità.

Descrivere il riempimento proporzionale

Quando si inseriscono 1 gigabyte di dati in un database di SQL Server con due file di dati, è possibile che ogni file aumenti di circa 512 megabyte. Tuttavia, questo non è sempre il caso. SQL Server distribuisce i dati in base alle dimensioni di ogni file. Ad esempio, se entrambi i file di dati sono di 2 gigabyte, i dati verranno distribuiti uniformemente. Ma se un file è di 10 gigabyte e l'altro è di 1 gigabyte, circa 900 MB verrebbero inseriti nel file più grande e 100 MB in quello più piccolo. Questo comportamento è comune in qualsiasi database, ma nel tempdb a elevato utilizzo di scrittura, un modello di scrittura non uniforme può creare un collo di bottiglia nel file più grande, poiché gestisce più scritture.

Configurare Tempdb in SQL Server

SQL Server rileva il numero di CPU disponibili durante l'installazione e configura il numero appropriato di file, fino a otto, con dimensioni pari. Inoltre, i comportamenti dei flag di traccia 1117 e 1118 sono integrati nel motore di database, ma solo per tempdb. Per i carichi di lavoro tempdb-heavy, può essere utile aumentare il numero di file tempdb oltre otto, corrispondente al numero di CPU nel computer.

Le modalità d'uso di tempdb sono le stesse, sia per SQL Server che per Azure SQL. Si noti, tuttavia, che le possibilità di configurazione di tempdb sono diverse, ad esempio il posizionamento dei file, il numero e le dimensioni dei file e le opzioni di configurazione di tempdb.

SQL Server usa tempdb per varie attività oltre all'archiviazione di tabelle temporanee definite dall'utente. Viene usato per le tabelle di lavoro che archivia i risultati intermedi delle query, le operazioni di ordinamento e l'archivio delle versioni per il controllo delle versioni delle righe, tra gli altri scopi. A causa di questo utilizzo esteso, è fondamentale posizionare tempdb nell'archiviazione con latenza più bassa disponibile e configurare correttamente i file di dati.

I file di database di tempdb vengono sempre archiviati automaticamente nelle unità SSD locali, quindi le prestazioni di I/O non dovrebbero rappresentare un problema.

I professionisti di SQL Server spesso usano più di un file di database per partizionare le allocazioni per le tabelle tempdb. Per il database SQL di Azure, il numero di file viene ridimensionato con il numero di vCore (ad esempio, due vCore è uguale a quattro file) con un massimo di 16. Il numero di file non può essere configurato tramite T-SQL in tempdb, ma è possibile farlo modificando l'opzione di distribuzione. Le dimensioni massime di tempdb vengono ridimensionate per numero di vCore. Si ottengono 12 file con Istanza gestita di SQL, indipendentemente dal numero di vCore.

L'opzione MIXED_PAGE_ALLOCATION di database è impostata su OFF ed AUTOGROW_ALL_FILES è impostata su ON. Questi parametri non possono essere configurati, ma, come per SQL Server, sono le impostazioni predefinite consigliate.

Attualmente, la funzionalità di ottimizzazione dei metadati di tempdb in SQL Server 2019, che può alleviare una contesa di latch elevata, non è disponibile nel database SQL di Azure o nell'Istanza gestita di SQL di Azure.

Configurazione del database

In genere, si configura un database con le istruzioni T-SQL ALTER DATABASE e ALTER DATABASE SCOPED CONFIGURATION . Molte delle opzioni di configurazione per le prestazioni sono disponibili per Azure SQL. Per le differenze tra SQL Server, Azure SQL Database e Azure SQL Managed Instance, consultare il riferimento T-SQL per ALTER DATABASE e ALTER DATABASE SCOPED CONFIGURATION.

Nel database SQL di Azure il modello di recupero predefinito è il recupero con registrazione completa, che garantisce che il database possa soddisfare i contratti di servizio di Azure. Ciò significa che la registrazione minima per le operazioni bulk non è supportata, ad eccezione di tempdb, in cui è consentita la registrazione minima.

Configurazione di MAXDOP

Il massimo grado di parallelismo (MAXDOP, Max Degree Of Parallelism) può influire sulle prestazioni delle singole query. SQL Server e l'handle MAXDOP SQL di Azure nello stesso modo. Se MAXDOP è impostato su un valore superiore, vengono usati più thread paralleli per ogni query, accelerando potenzialmente l'esecuzione delle query. Tuttavia, questo maggiore parallelismo richiede risorse di memoria aggiuntive, che possono causare un utilizzo elevato della memoria e influire sulle prestazioni di archiviazione. Ad esempio, quando si comprimono i rowgroup in un columnstore, il parallelismo richiede una quantità maggiore di memoria, che può comportare una pressione di memoria e un taglio di rowgroup.

Viceversa, l'impostazione di MAXDOP su un valore inferiore può ridurre la pressione della memoria, consentendo al sistema di archiviazione di eseguire prestazioni più efficienti. Questo aspetto è importante negli ambienti con risorse di memoria limitate o richieste di archiviazione elevate. Configurando attentamente MAXDOP, è possibile bilanciare le prestazioni delle query e l'efficienza di archiviazione, garantendo un uso ottimale sia della CPU che delle risorse di archiviazione.

È possibile configurare MAXDOP in Azure SQL in modo simile a quanto avviene in SQL Server, usando le tecniche seguenti:

• ALTER DATABASE SCOPED CONFIGURATION per configurare MAXDOP è supportato per Azure SQL.
• La stored procedure sp_configure per il "massimo grado di parallelismo" è supportata per Istanza gestita di SQL.
• MAXDOP Gli hint per la query sono completamente supportati.
• La configurazione MAXDOP con Resource Governor è supportata per Istanza gestita di SQL.