Best practice per le istanze SQL Server

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

Configurazione di Windows

Questa sezione tratta gli argomenti di configurazione su come ottimizzare il 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 altri computer client, configura 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 SQL Serverregola del firewall di Windows. Questa regola è abbastanza libera, quindi ti consigliamo di disattivarla prima di procedere alla produzione del sistema.

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 piccoli computer 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 delle macchine virtuali (VM) sono collegate a una rete progettata da Google che offre elevata capacità e prestazioni. 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 tue 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 Server su Compute Engine e descrive le attività operative per contribuire a mantenere il servizio in funzione senza problemi.

Spostare 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'unità SSD locale per migliorare le IOPS

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

La natura temporanea della tecnologia SSD locale la rende una scelta inadeguata per l'uso con i database e i file importanti critici. Tuttavia, tempdb e il file di paging di Windows sono entrambi file temporanei, quindi sono entrambi ottimi candidati per essere trasferiti su un'unità SSD locale. In questo modo, viene scaricato un numero significativo di operazioni di I/O dai dischi permanenti SSD. Per ulteriori informazioni sulla configurazione, consulta Configurare TempDB.

Elaborazione parallela delle query

Best practice:imposta max degree of parallelism su 8.

L'impostazione predefinita consigliata per max degree of parallelism è impostarla sul numero di CPU sul server. Tuttavia, c'è un punto in cui suddividere una query in 16 o 32 chunk, eseguirli tutti su vCPU diverse e poi riunirli in un unico risultato richiede molto più tempo rispetto a se la query fosse stata eseguita da una sola vCPU. In pratica, 8 è un buon valore predefinito.

Best practice: monitora le attese di CXPACKET e aumenta gradualmentecost threshold for parallelism.

Questa impostazione va di pari passo con max degree of parallelism. Ogni unità rappresenta 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 alcun consiglio specifico per modificare il valore predefinito, vale la pena tenerlo d'occhio e, se necessario, aumentarlo gradualmente di 5 durante il test di carico. Un indicatore chiave che indica che questo valore potrebbe dover essere aumentato è la presenza di attese di CXPACKET. Sebbene la presenza di 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 impostale a livello di singolo database.

I singoli database possono avere esigenze di parallelismo diverse. Puoi impostare queste impostazioni a livello globale e impostare Max DOP a livello di singolo database. Devi osservare i tuoi carichi di lavoro unici, monitorare le attese e poi modificare i valori di conseguenza.

Il sito SQLSkills offre una guida utile sul rendimento che copre 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 la crescita 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 giornaliero medio dei log.

Una delle cause più trascurate di perdita di prestazioni e rallentamenti intermittenti è la crescita non gestita del 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. SQL Server aumenta ogni file di log in base alle sue dimensioni massime e all'impostazione Crescita file.

Quando il file ha raggiunto il limite di dimensioni massime e non può crescere, il sistema genera un errore 9002 e mette il database in modalità di sola lettura. Se il file può crescere, SQL Server espande le dimensioni del file 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 il nuovo spazio vuoto.

Best practice:pianifica backup regolari del log delle transazioni.

Indipendentemente dalle impostazioni di dimensione e crescita massime, pianifica regolarmente 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 rendimento nei periodi di picco del traffico.

Ottimizzazione dei file di log virtuali

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

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 fisiche deve aumentare. Se non hai disattivato la crescita automatica e questa avviene troppo spesso, vengono creati troppi VLF. Questa attività può comportare la frammentazione dei file di log, che è 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 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:defragmenta regolarmente gli indici delle tabelle più modificate.

Gli indici nelle tabelle possono essere frammentati, il che può comportare prestazioni ridotte di qualsiasi query che utilizza questi indici. Una pianificazione di manutenzione regolare dovrebbe includere la riorganizzazione degli indici delle tabelle più modificate. Puoi eseguire il seguente script Transact-SQL per il tuo database per visualizzare gli indici e la loro percentuale di frammentazione. Nei risultati di esempio puoi vedere che l'indicePK_STOCK è frammentato per il 95%. Nel seguente statement "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 scriptALTER di base. Ecco uno script di esempio che stampa le istruzioni ALTER che puoi eseguire per ogni indice delle 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'

Scegli le tabelle del set di risultati con la frammentazione più elevata, quindi esegui queste istruzioni in modo incrementale. Valuta la possibilità di pianificare questo script o uno simile come uno dei tuoi job di manutenzione ordinaria.

Formattazione dei dischi secondari

Best practice:formatta i dischi secondari con un'unità di allocazione di 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, ciascuna di 8 KB. La formattazione di un disco con un'unità di allocazione di 64 KB consente a SQL Server di leggere e scrivere le estensioni in modo più efficiente, aumentando le prestazioni I/O del disco.

Per formattare i dischi secondari con un'unità di allocazione di 64 KB, esegui il seguente comando PowerShell, che cerca tutti i dischi nuovi e non inizializzati in un sistema e li formatta con l'unità di allocazione di 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

Backup in corso

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

Le soluzioni di backup e ripristino di emergenza di Google offrono i seguenti vantaggi per Microsoft SQL Server:

  • Backup incrementale continuo efficiente con recupero point in time effettivo che aiuta a eseguire il backup in meno tempo rispetto ai backup convenzionali, riducendo al contempo l'impatto sui server di produzione. Riduce inoltre il consumo di larghezza di banda e spazio di archiviazione per RPO (Recovery Point Objective) e TCO (Total Cost of Ownership) ridotti.
  • Ripristini di montaggio e migrazione (M&M) per i 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.
  • Pannello di gestione centrale che include funzionalità di monitoraggio, avviso e generazione di report dedicati per tutti i backup.

Ulteriori informazioni:

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 che vuoi 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 data warehouse.

Caricamento collettivo dei dati

Best practice:utilizza un database separato per eseguire il staging e la trasformazione dei dati collettivi 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 potresti raggiungere il limite di IOPS dei dischi permanenti quando esegui caricamenti collettivi.

Esiste un modo semplice per ridurre le operazioni di I/O del disco e il consumo della CPU dei caricamenti collettivi, con il vantaggio aggiuntivo di velocizzare il tempo di esecuzione dei job batch. La soluzione è creare un database completamente separato che utilizzi il modello di recupero Simple, quindi utilizzarlo per l'implementazione e la trasformazione del set di dati collettivo prima di inserirlo nel database di produzione. Puoi anche inserire questo nuovo database su un disco SSD locale, se hai spazio sufficiente. L'utilizzo di un'unità 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 dovrà eseguire il backup di tutte queste operazioni collettive nel log delle transazioni e, pertanto, sarà più piccolo ed 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 rendimento. Questa procedura memorizzata è un'ottima risorsa per valutare la configurazione di SQL Server. Più tardi, leggi 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 a Standard Edition. Se stai eseguendo la migrazione di una licenza esistente su Google Cloud, potresti prendere in considerazione alcune opzioni per le prestazioni.

Utilizzo delle tabelle compresse

Best practice:attiva la compressione di tabelle e 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 le I/O del disco aggiuntive necessarie 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 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 memorizza le pagine pulite. In termini semplici, immagazzina copie dei dati, rispecchiando la loro rappresentazione sul disco. Quando i dati cambiano in memoria, si parla di pagina sporca. Le pagine sporche devono essere svuotate sul disco per salvare le modifiche. Quando il database è più grande della memoria disponibile, viene esercitata una pressione sul pool del buffer e le pagine pulite potrebbero essere eliminate. Quando le pagine pulite vengono eliminate, il sistema deve leggere dal disco la volta successiva che accede ai dati eliminati.

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

Questa tecnica non è quasi veloce come avere abbastanza memoria, ma può darti un modesto aumento del throughput quando la memoria disponibile è bassa. Puoi scoprire di più sulle estensioni del pool di buffer e 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 carichi di lavoro 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, che disattiva 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 carico di lavoro.

Tuttavia, la configurazione del numero di thread per core influisce anche sul rendimento del carico di lavoro. 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 la velocità effettiva complessiva del sistema utilizzando meglio le risorse di base disponibili. Ad esempio, quando un thread è in stato di stallo, l'altro thread può utilizzare il core.

L'impatto esatto sul rendimento dell'SMT su SQL Server dipende dalle caratteristiche del carico di lavoro e dalla piattaforma hardware utilizzata, poiché l'implementazione dell'SMT è diversa tra le generazioni di hardware. I carichi di lavoro con un volume elevato di piccole transazioni, ad esempio i carichi di lavoro OLTP, possono spesso sfruttare la SMT e beneficiare di un aumento delle prestazioni più elevato. Invece, i carichi di lavoro meno parallelizzabili, ad esempio quelli OLAP, traggono meno vantaggio dall'SMT. Sebbene questi pattern siano stati notati in generale, valuta l'impatto sulle prestazioni di SMT in base al carico di lavoro per determinare l'impatto dell'impostazione del numero di thread per core su 1.

La configurazione più conveniente per la maggior parte dei carichi di lavoro 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 è superiore all'aumento del costo della VM più grande.

Esempio: considera che un server SQL è dipiegato 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