Best practice per le istanze SQL Server

Per ottimizzare le istanze Compute Engine che eseguono Microsoft SQL Server puoi applicare diverse best practice. Per scoprire come configurare un'istanza SQL Server a prestazioni elevate, leggi Creazione di un'istanza SQL Server ad alte prestazioni.

Configurazione di Windows

Questa sezione descrive i processi di configurazione relativi all'ottimizzazione del sistema operativo Microsoft Windows per le prestazioni di SQL Server quando viene eseguito su Compute Engine.

Configurazione del firewall di Windows

Best practice: utilizza il firewall avanzato di Windows Server e specifica gli indirizzi IP dei computer client.

Il firewall avanzato di Windows è un componente di sicurezza importante in Windows Server. Quando configuri l'ambiente SQL Server in modo che possa connettersi al database da altre macchine client, imposta il firewall in modo da consentire il traffico in entrata:

netsh advfirewall firewall add rule name="SQL Access" ^
dir=in action=allow ^
program="%programfiles%\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" ^
remoteip=LOCAL_SUBNET

Quando utilizzi questa regola firewall, è buona norma specificare l'indirizzo IP delle tue macchine client. Specifica un elenco di indirizzi IP delimitati da virgole senza spazi vuoti per il parametro remoteip al posto di LOCAL_SUBNET. Inoltre, tieni presente che il percorso per il parametro program potrebbe cambiare a seconda della versione di SQL Server in uso.

L'immagine dell'applicazione SQL Server include una regola firewall SQL Server su Windows. Questa regola non presenta troppe restrizioni, quindi ti consigliamo di disattivarla prima che il sistema entri in funzione.

Ottimizzazione delle connessioni di rete

Best practice: utilizza le impostazioni di rete predefinite del sistema operativo.

Le impostazioni di rete predefinite sulla maggior parte dei sistemi operativi sono configurate per le connessioni su computer di piccole dimensioni connessi a reti moderatamente veloci. In genere queste impostazioni sono sufficienti. Inoltre, i valori predefiniti conservativi assicurano che il traffico di rete non sovraccarichi la rete e i computer connessi.

In Compute Engine, le istanze di macchine virtuali (VM) sono collegate a una rete progettata da Google che offre capacità e prestazioni elevate. I server fisici su cui vengono eseguite le istanze Compute Engine sono altamente ottimizzati per sfruttare questa capacità di rete. Anche i driver di rete virtuale nelle istanze sono ottimizzati, il che rende i valori predefiniti sufficienti per la maggior parte dei casi d'uso.

Installazione dell'antivirus

Best practice: segui le indicazioni di Microsoft per il software antivirus.

Se utilizzi Windows, devi avere installato un software antivirus. I malware e i virus informatici rappresentano un rischio significativo per qualsiasi sistema connesso a una rete e il software antivirus è un semplice passaggio di mitigazione che puoi utilizzare per proteggere i tuoi dati. Tuttavia, se il software antivirus non è configurato correttamente, può influire negativamente sulle prestazioni del database. Microsoft fornisce consigli su come scegliere un software antivirus.

Ottimizzazione per le prestazioni e la stabilità

Questa sezione fornisce informazioni su come ottimizzare le prestazioni di SQL su Compute Engine e descrive le attività operative per contribuire a mantenere il servizio in funzione senza problemi.

Spostamento di file di dati e file di log su un nuovo disco

Best practice: utilizza un disco permanente SSD separato per i file di log e di dati.

Per impostazione predefinita, l'immagine preconfigurata per SQL Server include tutto ciò che è installato sul disco permanente di avvio, che viene montato come unità C:. Valuta la possibilità di collegare un disco permanente SSD secondario e di spostare i file di log e di dati sul nuovo disco.

Utilizzo di un SSD locale per migliorare le IOPS

Best practice: crea nuove istanze SQL Server con uno o più SSD locali per archiviare tempdb e i file di paging di Windows.

La natura effimera della tecnologia SSD locale la rende una scelta inadeguata per l'utilizzo con database e file importanti di natura critica. tempdb e il file di paging di Windows sono, però, file temporanei, quindi sono entrambi ottimi candidati per essere spostati su un SSD locale. In questo modo, viene trasferito un numero significativo di operazioni di I/O dai tuoi dischi permanenti SSD. Per ulteriori informazioni su questa configurazione, vedi Configurazione di TempDB.

Elaborazione parallela delle query

Best practice: imposta max degree of parallelism su 8.

Per impostare max degree of parallelism, ti consigliamo di farlo corrispondere al numero di CPU sul server. Tuttavia, si arriva a un momento in cui suddividere una query in 16 o 32 chunk, eseguire il tutto su vCPU diverse e poi riunirlo in un unico risultato richiede molto più tempo rispetto all'eseguire la query su una sola vCPU. In pratica, 8 è un valore predefinito ottimale.

Best practice: monitora le attese CXPACKET e aumenta gradualmente cost threshold for parallelism.

Questa impostazione è strettamente correlata a max degree of parallelism. Ciascuna unità costituisce una combinazione di attività di CPU e I/O necessarie per eseguire una query con un piano di esecuzione seriale prima che venga presa in considerazione per un piano di esecuzione parallelo. Il valore predefinito è 5. Anche se non forniamo alcuna raccomandazione specifica per modificare il valore predefinito, è importante monitorarlo e, se necessario, aumentarlo gradualmente di 5 durante il test di carico. Un indicatore chiave che segnala la necessità di aumentare questo valore è la presenza di attese CXPACKET. Sebbene la presenza di attese CXPACKET non implichi necessariamente che questa impostazione debba essere modificata, è un buon punto di partenza.

Best practice: monitora i diversi tipi di attesa e regola le impostazioni di elaborazione parallela globale o configurale a livello di singolo database.

I singoli database possono avere esigenze di parallelismo diverse. Puoi configurare queste impostazioni a livello globale e impostare Max DOP a livello di singolo database. Devi osservare i workload univoci, monitorare le attese e regolare di conseguenza i valori.

Il sito SQLSkills offre una guida utile sulle prestazioni e descrive le statistiche di attesa all'interno del database. Seguire questa guida può aiutarti a capire cosa è in attesa e come ridurre i ritardi.

Gestione dei log delle transazioni

Best practice: monitora l'aumento delle dimensioni del log delle transazioni sul tuo sistema. Valuta la possibilità di disattivare l'aumento automatico e di impostare il file di log su una dimensione fissa, in base all'accumulo medio giornaliero del log.

Una delle cause più sottovalutate di perdita di prestazioni e rallentamenti intermittenti è l'aumento non gestito delle dimensioni dei log delle transazioni. Quando il database è configurato per utilizzare il modello di recupero Full, puoi eseguire un ripristino in qualsiasi momento, ma i log delle transazioni si riempiono più velocemente. Per impostazione predefinita, quando il file del log delle transazioni è pieno, SQL Server aumenta le dimensioni del file per aggiungere più spazio vuoto per scrivere più transazioni e blocca tutte le attività sul database fino al termine dell'operazione. SQL Server aumenta ogni file di log in base alle sue dimensioni massime e all'impostazione Crescita file.

Quando il file raggiunge il limite di dimensioni massime e non può più crescere, il sistema genera un errore 9002 e imposta il database in modalità di sola lettura. Se il file può crescere, SQL Server espande le sue dimensioni e azzera lo spazio vuoto. Il valore predefinito per l'impostazione Crescita file è pari al 10% delle dimensioni correnti del file di log. Questa non è un'impostazione predefinita ottimale per le prestazioni perché, più il file cresce, più tempo occorre per creare spazio vuoto nuovo.

Best practice: pianifica backup regolari dei log delle transazioni.

Indipendentemente dalle impostazioni di dimensione e crescita massime, pianifica regolarmente il backup dei log delle transazioni, che, per impostazione predefinita, tronca le vecchie voci di log e consente al sistema di riutilizzare lo spazio file esistente. Questa semplice attività di manutenzione può aiutarti a evitare cali di prestazioni nei periodi di picco del traffico.

Ottimizzazione dei file di log virtuali

Best practice: monitora la crescita del file di log virtuale e intervieni per evitarne la frammentazione.

Il file del log delle transazioni fisico è suddiviso in file di log virtuali (VLF). I nuovi VLF vengono creati ogni volta che il file del log delle transazioni fisico deve aumentare le proprie dimensioni. Se non hai disattivato la crescita automatica e questa avviene troppo frequentemente, viene creato un numero eccessivo di VLF. Questa attività può comportare la frammentazione dei file di log, un fenomeno simile alla frammentazione del disco, e può influire negativamente sulle prestazioni.

SQL Server 2014 ha introdotto un algoritmo più efficiente per determinare il numero di VLF da creare durante la crescita automatica. In genere, se la crescita è inferiore a 1/8 della dimensione del file di log corrente, SQL Server crea un VLF all'interno del nuovo segmento. In precedenza, venivano creati 8 VLF per una crescita compresa tra 64 MB e 1 GB e 16 VLF per una crescita superiore a 1 GB. Puoi utilizzare lo script TSQL riportato di seguito per controllare quanti VLF sono attualmente presenti nel tuo database. Se questo contiene migliaia di file, prendi in considerazione la possibilità di ridurre manualmente le dimensioni del file di log.

--Check VLFs substitute your database name below
USE YOUR_DB
DECLARE @vlf_count INT
DBCC LOGINFO
SET @vlf_count = @@ROWCOUNT
SELECT VLFs = @vlf_count

Puoi scoprire di più sui VLF sul sito web di Brent Ozar.

Evitare la frammentazione dell'indice

Best practice: deframmenta regolarmente gli indici nelle tabelle in cui è sono state applicate numerose modifiche.

Gli indici nelle tabelle possono essere frammentati, il che può comportare prestazioni ridotte di qualsiasi query che li utilizza. Una pianificazione di manutenzione regolare dovrebbe includere la riorganizzazione degli indici nelle tabelle in cui sono state applicate numerose modifiche. Per visualizzare gli indici e la loro percentuale di frammentazione, puoi eseguire il seguente script Transact-SQL per il tuo database. Nei risultati dell'esempio puoi notare che l'indice PK_STOCK è frammentato per il 95%. Nell'istruzione "SELECT", sostituire "YOUR_DB" con il nome del database:

SELECT stats.index_id as id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'YOUR_DB'), NULL, NULL, NULL, NULL) AS stats
    JOIN sys.indexes AS indx ON stats.object_id = indx.object_id
      AND stats.index_id = indx.index_id AND name IS NOT NULL;

RESULTS
-------------------------------
Id    name          avg_fragmentation_in_percent
-------------------------------
1 ORDERS_I1 0
2 ORDERS_I2 0
1 ORDER_LINE_I1 0.01
1 PK_STOCK95.5529819557039
1 PK_WAREHOUSE0.8

Quando gli indici sono troppo frammentati, puoi riorganizzarli utilizzando uno script ALTER di base. Ecco uno script di esempio che stampa le istruzioni ALTER che puoi eseguire per ogni indice presente nelle tue tabelle:

SELECT
'ALTER INDEX ALL ON ' + table_name + ' REORGANIZE;
GO'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'YOUR_DB'

Seleziona le tabelle dal set di risultati con la frammentazione più elevata ed esegui queste istruzioni in modo incrementale. Valuta la possibilità di pianificare questo script o uno simile come uno dei tuoi job di manutenzione regolari.

Formattazione dei dischi secondari

Best practice: formatta i dischi secondari con un'unità di allocazione da 64 KB.

SQL Server archivia i dati in unità di archiviazione chiamate estensioni. Le estensioni hanno una dimensione di 64 KB e sono costituite da otto pagine di memoria contigue da 8 KB. La formattazione di un disco con un'unità di allocazione da 64 KB consente a SQL Server di leggere e scrivere le estensioni in modo più efficiente, aumentando le prestazioni I/O dal disco.

Per formattare i dischi secondari con un'unità di allocazione da 64 KB, esegui il comando PowerShell, che cerca tutti i dischi nuovi e non inizializzati all'interno di un sistema e li formatta con l'unità di allocazione da 64 KB:

Get-Disk | Where-Object {$_.PartitionStyle -eq 'RAW'} | Initialize-Disk -PartitionStyle GPT -PassThru | New-Partition -AssignDriveLetter -UseMaximumSize | Format-Volume -FileSystem NTFS -AllocationUnitSize 65536 -Confirm:$FALSE

Esecuzione del backup

Best practice: per una protezione ottimale, esegui regolarmente il backup dei dati utilizzando le soluzioni di backup e disaster recovery di Google. Ti consigliamo di eseguire il backup dei dati almeno una volta al giorno.

Le soluzioni di backup e disaster recovery di Google offrono i seguenti vantaggi per Microsoft SQL Server:

  • Backup efficiente e incrementale continuo con recupero point in time effettivo che aiuta a eseguire il backup in meno tempo rispetto ai backup convenzionali, riducendo anche l'impatto sui server di produzione. Inoltre, consente di diminuire il consumo della larghezza di banda e dello spazio di archiviazione riducendo il Recovery Point Objective (RPO) e i costi totali di proprietà (Total Cost of Ownership, TCO).
  • Ripristini di montaggio e migrazione (M&M) per backup archiviati in Cloud Storage per un RTO basso
  • Integrazione completa con le funzionalità di SQL Server, incluso il supporto per i cluster di gruppi di disponibilità SQL Server e più opzioni di recupero in diversi scenari
  • Riquadro di gestione centrale che include funzionalità di monitoraggio, avviso e generazione di report dedicati per tutti i backup

Scopri di più:

Monitoraggio

Best practice: utilizza Cloud Monitoring.

Puoi installare l'agente Cloud Monitoring per Microsoft Windows per inviare diversi punti dati di monitoraggio al sistema Cloud Monitoring.

Utilizzando le funzionalità di raccolta dei dati, puoi perfezionare le informazioni da monitorare e inviarle al data warehouse di gestione integrato. Il data warehouse di gestione può essere eseguito sullo stesso server che stai monitorando oppure i dati possono essere trasmessi in streaming a un'altra istanza di SQL Server che esegue il servizio.

Caricamento dei dati in blocco

Best practice: utilizza un database separato per preparare e trasformare i dati in blocco prima di spostarli sui server di produzione.

È probabile che tu debba caricare grandi quantità di dati nel tuo sistema almeno una volta, se non regolarmente. Si tratta di un'operazione che richiede molte risorse e, nell'eseguire caricamenti in blocco, potresti raggiungere il limite di IOPS del disco permanente.

Esiste un modo semplice per ridurre i consumi di I/O del disco e della CPU dei caricamenti in blocco, con il vantaggio aggiuntivo di velocizzare il tempo di esecuzione dei job batch. La soluzione è creare prima un database completamente separato che utilizzi il modello di recupero Simple e dopo impiegare quello stesso database per la preparazione e la trasformazione del set di dati in blocco prima che questo venga inserito nel database di produzione. Se hai spazio sufficiente, puoi inoltre inserire questo nuovo database su un drive SSD locale. L'utilizzo di un SSD locale per il database di recupero riduce il consumo di risorse delle operazioni collettive e il tempo necessario per completare i job. Il vantaggio finale è che il job di backup per i dati di produzione non deve eseguire il backup di tutte queste operazioni collettive nel log delle transazioni e, pertanto, le sue dimensioni saranno più esigue e verrà eseguito più velocemente.

Convalida della configurazione

Best practice: testa la configurazione per verificare che funzioni come previsto.

Ogni volta che configuri un nuovo sistema, devi pianificare la convalida della configurazione ed eseguire alcuni test di prestazioni. Questa stored procedure è un'ottima risorsa per valutare la configurazione di SQL Server. Dedica del tempo a leggere i dettagli sui flag di configurazione ed esegui la procedura.

Ottimizzazione di SQL Server Enterprise Edition

SQL Server Enterprise Edition offre un lungo elenco di funzionalità aggiuntive rispetto alla versione Standard Edition. Se stai eseguendo la migrazione di una licenza esistente a Google Cloud, ci sono alcune opzioni relative alle prestazioni che potresti implementare.

Utilizzo delle tabelle compresse

Best practice: attiva la compressione delle tabelle e degli indici.

Potrebbe sembrare controintuitivo che la compressione delle tabelle possa migliorare le prestazioni del sistema, ma nella maggior parte dei casi è così. Il compromesso è l'utilizzo di una piccola quantità di cicli della CPU per comprimere i dati ed eliminare l'I/O del disco aggiuntivo necessario per leggere e scrivere i blocchi più grandi. In genere, più basso è il livello di I/O del disco utilizzato dal sistema, migliori sono le prestazioni. Le istruzioni per stimare e attivare la compressione di tabelle e indici sono disponibili sul sito web di MSDN.

Attivazione dell'estensione del pool di buffer

Best practice: utilizza l'estensione del pool di buffer per velocizzare l'accesso ai dati.

Il pool di buffer è il luogo in cui il sistema archivia le pagine pulite. In termini semplici, archivia copie dei dati e riporta la loro rappresentazione sul disco. Quando i dati cambiano in memoria, si parla di pagina modificata. Per salvare le modifiche, sulle pagine modificate deve essere eseguito il flush su disco. Quando il database è più grande della memoria disponibile, viene esercitata una pressione sul pool di buffer e le pagine pulite potrebbero essere eliminate. Quando le pagine pulite vengono eliminate, il sistema deve leggere dal disco il momento in cui è possibile accedere ai dati eliminati.

La funzionalità di estensione del pool di buffer ti consente di inviare, anziché eliminare, pagine pulite a un'unità SSD locale,. Questa funziona come la memoria virtuale, ovvero tramite sostituzione, il che ti consente di accedere alle pagine pulite sull'SSD locale, un processo più rapido rispetto al recupero dei dati sul disco normale.

Questa tecnica non è veloce quanto quella di disporre di una memoria sufficiente, ma può offrire un modesto aumento del throughput quando la memoria disponibile viene ridotta. Puoi scoprire di più sulle estensioni del pool di buffer ed esaminare alcuni risultati di benchmarking sul sito di Brent Ozar.

Ottimizzazione delle licenze SQL Server

Multi-threading simultaneo (SMT)

Best practice: imposta il numero di thread per core su 1 per la maggior parte dei workload SQL Server

Il multi-threading simultaneo (SMT), comunemente noto come tecnologia Hyper-Threading (HTT) sui processori Intel, è una funzionalità che consente di condividere logicamente un singolo core della CPU come due thread. In Compute Engine, l'SMT è abilitato per impostazione predefinita sulla maggior parte delle VM, il che significa che ogni vCPU nella VM viene eseguita su un singolo thread e ogni core della CPU fisica è condiviso da due vCPU.

In Compute Engine, puoi configurare il numero di thread per core, il che consente di disattivare efficacemente l'SMT. Quando il numero di thread per core è impostato su 1, le vCPU non condividono i core della CPU fisica. Questa configurazione influisce notevolmente sui costi delle licenze per Windows Server e SQL Server. Quando il numero di thread per core è impostato su 1, il numero di vCPU in una VM viene dimezzato, il che dimezza anche il numero di licenze Windows Server e SQL Server richieste. Ciò può ridurre notevolmente il costo totale del workload.

Tuttavia, la configurazione del numero di thread per core influisce anche sulla prestazione del workload. Le applicazioni scritte per essere multi-thread possono sfruttare questa funzionalità suddividendo il lavoro di calcolo in blocchi parallelizzabili più piccoli che vengono pianificati su più core logici. Questa parallelizzazione del lavoro spesso aumenta il throughput complessivo del sistema utilizzando meglio le risorse dei core disponibili. Ad esempio, quando un thread è in stato di stallo, l'altro può utilizzare il core.

L'impatto esatto sulle prestazioni dell'SMT su SQL Server dipende dalle caratteristiche del workload e dalla piattaforma hardware utilizzata, poiché l'implementazione dell'SMT varia in base alle generazioni di hardware. I workload con un volume elevato di piccole transazioni, ad esempio workload OLTP, possono spesso sfruttare la SMT e beneficiare di un ulteriore aumento delle prestazioni. Al contrario, i workload meno parallelizzabili, ad esempio quelli OLAP, traggono meno vantaggio dall'SMT. Sebbene in generale si notino queste sequenze, valuta l'impatto sulle prestazioni di SMT in base a ciascun workload per determinare le conseguenze che derivano dall'impostare il numero di thread per core su 1.

La configurazione più conveniente per la maggior parte dei workload di SQL Server prevede l'impostazione del numero di thread per core su 1. Qualsiasi calo delle prestazioni può essere compensato utilizzando una VM più grande. Nella maggior parte dei casi, la diminuzione del 50% del costo della licenza è maggiore dell'aumento del costo della VM più grande.

Esempio: considera un SQL Server di cui è stato eseguito il deployment nella configurazione n2-standard-16

Per impostazione predefinita, il numero di core visibili nel sistema operativo è 16, il che significa che sono necessarie 16 vCPU di licenze Windows Server e 16 vCPU di licenze SQL Server per eseguire il server.

PS C:\> Get-WmiObject -Class Win32_processor | Select-Object NumberOfCores, @{Name="Thread(s) per core";Expression={$_.NumberOfLogicalProcessors/$_.NumberOfCores}}

NumberOfCores Thread(s) per core
------------- ------------------
            8                  2

Dopo aver seguito i passaggi per disattivare SMT su SQL Server, la nuova configurazione è:

PS C:\> Get-WmiObject -Class Win32_processor | Select-Object NumberOfCores, @{Name="Thread(s) per core";Expression={$_.NumberOfLogicalProcessors/$_.NumberOfCores}}

NumberOfCores Thread(s) per core
------------- ------------------
            8                  1

Ora che nel sistema operativo sono visibili solo 8 core, il server richiede solo 8 vCPU per l'esecuzione di Windows Server e SQL Server.

Passaggi successivi