Best practice per le istanze SQL Server

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

Configurazione di Windows

Questa sezione tratta gli argomenti relativi alla configurazione su come ottimizzare il sistema operativo Microsoft Windows per le prestazioni di SQL Server durante l'esecuzione 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.

Windows Advanced Firewall è un componente di sicurezza importante di Windows Server. Quando imposti il tuo ambiente SQL Server in modo che possa connettersi al database da altre macchine client, configura il firewall per 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, è consigliabile specificare l'indirizzo IP delle macchine client. Specifica un elenco di indirizzi IP delimitato da virgole senza spazi vuoti per il parametro remoteip al posto di LOCAL_SUBNET. Tieni inoltre presente che il percorso del parametro program potrebbe cambiare a seconda della versione di SQL Server che utilizzi.

L'immagine dell'applicazione SQL Server include una regola firewall di Windows SQL Server. Questa regola non è soggetta a restrizioni, quindi ti consigliamo di disattivarla prima che il sistema passi in produzione.

Ottimizzazione delle connessioni di rete

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

Le impostazioni di rete predefinite nella maggior parte dei sistemi operativi sono configurate per le connessioni su computer di piccole dimensioni collegati a reti moderatamente veloci. Queste impostazioni sono in genere 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 che eseguono le tue istanze Compute Engine sono altamente ottimizzati per sfruttare questa capacità di rete. Anche i driver di rete virtuale nelle tue istanze sono ottimizzati, in modo che i valori predefiniti siano sufficienti per la maggior parte dei casi d'uso.

Installazione antivirus in corso...

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

Se esegui Windows, dovresti eseguire un software antivirus. I virus malware e software presentano un rischio significativo per qualsiasi sistema connesso a una rete e il software antivirus è una semplice misura 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 il software antivirus.

Ottimizzare per migliorare prestazioni e stabilità

Questa sezione fornisce informazioni su come ottimizzare le prestazioni di SQL Server su Compute Engine e descrive le attività operative per garantirne il corretto funzionamento.

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

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

Per impostazione predefinita, l'immagine preconfigurata per SQL Server è preinstallata 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 i file di dati nel 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 i file di paging tempdb e di Windows.

La natura temporanea della tecnologia SSD locale non è adatta all'utilizzo con database critici e file importanti. Tuttavia, tempdb e il file di paging di Windows sono entrambi file temporanei, quindi entrambi sono ottimi candidati per il passaggio a un SSD locale. In questo modo, dai dischi permanenti SSD viene scaricato un numero significativo di operazioni di I/O. Per ulteriori informazioni sulla configurazione, consulta Configurazione di TempDB.

Elaborazione delle query in parallelo

Best practice: imposta max degree of parallelism su 8.

L'impostazione predefinita consigliata per max degree of parallelism è la corrispondenza con il numero di CPU sul server. Tuttavia, c'è un momento in cui suddividere una query in 16 o 32 blocchi, eseguirli tutti su vCPU diverse e quindi consolidarlo di nuovo in un unico risultato richiede molto più tempo rispetto all'esecuzione della query con una sola vCPU. In pratica, 8 rappresenta un buon valore predefinito.

Best practice: monitora il tempo di attesa di CXPACKET e aumenta in modo incrementale cost threshold for parallelism.

Questa impostazione va di pari passo con max degree of parallelism. Ogni unità rappresenta una combinazione di lavoro di CPU e I/O necessaria per eseguire una query con un piano di esecuzione seriale prima di essere considerata per un piano di esecuzione parallelo. Il valore predefinito è 5. Anche se non forniamo consigli specifici per la modifica del valore predefinito, vale la pena tenerlo d'occhio e, se necessario, aumentarlo di 5 in modo incrementale durante il test di carico. Un indicatore chiave della necessità di aumentare questo valore è la presenza di CXPACKET di attesa. Anche se la presenza di CXPACKET tempi di attesa non indica necessariamente che questa impostazione debba cambiare, è un buon punto di partenza.

Best practice: monitora diversi tipi di attesa e regola le impostazioni di elaborazione parallela globale o definisci le singole impostazioni 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. Dovresti osservare i carichi di lavoro univoci, monitorare le attese e quindi regolare i valori di conseguenza.

Il sito SQLSkills offre un'utile guida alle prestazioni che illustra le statistiche di attesa all'interno del database. Questa guida può aiutarti a capire cosa rimane 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 disabilitare la crescita automatica e di impostare il file di log su una dimensione fissa, in base all'accumulo di log giornaliero medio.

Una delle fonti più trascurate di perdita di prestazioni e rallentamenti intermittenti è la crescita non gestita del log delle transazioni. Quando il database è configurato in modo da 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 di 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 alla dimensione massima del file e all'impostazione Crescita file.

Se il file ha raggiunto la dimensione massima e non può crescere, il sistema emette un errore 9002 e imposta il database in modalità di sola lettura. Se le dimensioni del file aumentano, SQL Server espande le dimensioni del file e azzera gli spazi vuoti. L'impostazione Crescita file utilizza in modo predefinito il 10% delle dimensioni attuali del file di log. Questa non è una buona impostazione predefinita per le prestazioni, perché più grande cresce il file, più tempo occorre per creare il nuovo spazio vuoto.

Best practice: pianifica backup regolari del log delle transazioni.

Indipendentemente dalle impostazioni massime di dimensione e crescita, pianifica backup dei log delle transazioni regolari che, per impostazione predefinita, troncano le vecchie voci di log e consentono al sistema di riutilizzare lo spazio file esistente. Questa semplice attività di manutenzione può aiutare a evitare cali delle prestazioni nei momenti di picco di traffico.

Ottimizzazione dei file di log virtuali

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

Il file di log delle transazioni fisiche è segmentato in file di log virtuali (VLF). Vengono create nuove VLF ogni volta che il file di log delle transazioni fisiche deve crescere. Se non hai disabilitato la crescita automatica e la crescita avviene troppo spesso, vengono create troppe VLF. Questa attività può comportare la frammentazione del file di log, simile alla frammentazione del disco e che 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 attuale, SQL Server crea un VLF all'interno del nuovo segmento. In precedenza, creava 8 VLF per una crescita compresa tra 64 MB e 1 GB e 16 VLF per una crescita superiore a 1 GB. Puoi usare lo script TSQL riportato di seguito per verificare di quanti VLF è attualmente presente il tuo database. Se ne contiene migliaia, valuta la possibilità di ridurre e ridimensionare manualmente il 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 più modificate.

Gli indici nelle tabelle possono diventare frammentati, il che può portare a uno scarso rendimento delle query che utilizzano questi indici. Una pianificazione di manutenzione regolare dovrebbe includere la riorganizzazione degli indici nelle tabelle più modificate. Puoi eseguire il seguente script Transact-SQL per il database al fine di visualizzare gli indici e la percentuale di frammentazione. Nei risultati di esempio puoi vedere che l'indice PK_STOCK è frammentato al 95%. Nella seguente istruzione "SELECT", sostituisci "YOUR_DB" con il nome del tuo 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

Se 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 ciascuno degli indici delle 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 più alta frammentazione, quindi esegui queste istruzioni in modo incrementale. Potresti pianificare questo o uno script simile come uno dei normali job di manutenzione.

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 composte da otto pagine di memoria contigue di dimensioni pari a 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 di I/O dal disco.

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

Backup in corso...

Best practice:per una protezione ottimale, esegui regolarmente il backup dei tuoi dati utilizzando le soluzioni di backup e ripristino di emergenza di Google. 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 a Microsoft SQL Server:

  • Efficienza di backup incrementale incrementale con recupero point-in-time reale che consente di eseguire il backup in meno tempo rispetto ai backup convenzionali, riducendo l'impatto sui server di produzione. Inoltre, riduce il consumo di larghezza di banda e di spazio di archiviazione per un RPO (Recovery Point Objective) e un costo totale di proprietà (TCO) bassi.
  • Monta ed esegui la migrazione dei recuperi (M&M) per i backup archiviati in Cloud Storage per un RTO ridotto.
  • Integrazione completa con le funzionalità di SQL Server, tra cui il supporto per i cluster del gruppo di disponibilità SQL Server e più opzioni di ripristino in tutti gli scenari.
  • Pannello di gestione centrale che include funzionalità di monitoraggio, avviso e generazione di report dedicate per tutti i backup.

Scopri di più:

Monitoraggio

Best practice: usa 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 dati, puoi ottimizzare 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 flusso su un'altra istanza SQL Server che esegue il data warehouse.

Caricamento collettivo dei dati

Best practice: utilizza un database separato per organizzare 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 potresti raggiungere il limite di IOPS del disco permanente eseguendo caricamenti collettivi.

Esiste un modo semplice per ridurre il consumo di I/O del disco e di CPU durante le operazioni di caricamento in blocco, con il vantaggio aggiuntivo di velocizzare il tempo di esecuzione dei job batch. La soluzione consiste nel creare un database completamente separato che utilizzi il modello di recupero Simple e quindi utilizzarlo per la gestione temporanea e la trasformazione del set di dati collettivo prima di inserirlo nel database di produzione. Puoi anche collocare il nuovo database su un'unità SSD locale, se hai spazio sufficiente. L'uso di un SSD locale per il database di recupero riduce il consumo di risorse per le 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 le operazioni collettive nel log delle transazioni e, di conseguenza, sarà di dimensioni inferiori ed eseguito più velocemente.

Convalida della configurazione in corso...

Best practice: testa la configurazione per verificare che abbia le prestazioni previste.

Ogni volta che configuri un nuovo sistema, dovresti pianificare la convalida della configurazione e l'esecuzione di alcuni test delle prestazioni. Questa procedura archiviata è un'ottima risorsa per valutare la configurazione di SQL Server. Leggi di più 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 a Google Cloud, potresti prendere in considerazione l'implementazione di alcune opzioni di prestazioni.

Utilizzo di tabelle compresse

Best practice: attiva la compressione di tabelle e indici.

Può sembrare controintuitivo che comprimere le tabelle sia in grado di aumentare le prestazioni del sistema ma, nella maggior parte dei casi, è qui che succede. Il compromesso consiste nell'utilizzare 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, minore è l'I/O del disco utilizzato dal sistema, migliori sono le prestazioni. Le istruzioni per stimare e abilitare la compressione di tabelle e indici sono disponibili sul sito web di MSDN.

Abilitazione 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 parole povere, memorizza copie dei tuoi dati, eseguendo il mirroring di come appaiono su disco. Quando i dati cambiano in memoria, viene chiamata pagina "sporca". Per salvare le modifiche, è necessario fare il flush delle pagine su disco. Se il tuo database supera la memoria disponibile, fai pressione sul pool di buffer e le pagine pulite potrebbero andare perse. Quando vengono eliminate le pagine pulite, il sistema deve leggere dal disco al successivo accesso ai dati eliminati.

La funzionalità di estensione del pool di buffer consente di eseguire il push delle pagine pulite a un SSD locale, anziché rilasciarle. Funziona esattamente come la memoria virtuale, ovvero mediante lo swap, e consente di accedere alle pagine pulite sull'SSD locale, una procedura più veloce rispetto al recupero dei dati sul disco normale.

Questa tecnica non è tanto veloce quanto avere memoria sufficiente, ma può fornire un aumento modesto della velocità effettiva quando la memoria disponibile è ridotta. Per ulteriori informazioni sulle estensioni del pool di buffer ed esaminare alcuni risultati di benchmark, consulta il sito di Brent Ozar.

Ottimizzazione delle licenze di 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 CPU come due thread. In Compute Engine, la SMT è abilitata 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 CPU fisico è condiviso da due vCPU.

In Compute Engine, puoi configurare il numero di thread per core, in modo da disattivare di fatto SMT. Se 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. Se il numero di thread per core è impostato su 1, il numero di vCPU in una VM viene dimezzato, dimezzando anche il numero di licenze Windows Server e SQL Server necessarie. Ciò può ridurre notevolmente il costo totale del carico di lavoro.

Tuttavia, la configurazione del numero di thread per core influisce anche sulle prestazioni del carico di lavoro. Le applicazioni scritte per essere multi-thread possono sfruttare questa funzionalità suddividendo il lavoro di calcolo in blocchi paralleli più piccoli pianificati in più core logici. Questa parallelizzazione del lavoro spesso aumenta la velocità effettiva complessiva del sistema mediante un miglioramento delle risorse principali disponibili. Ad esempio, quando un thread è bloccato, l'altro thread può utilizzare il core.

L'impatto esatto sulle prestazioni di SMT su SQL Server dipende dalle caratteristiche dei carichi di lavoro e dalla piattaforma hardware utilizzata, poiché l'implementazione SMT varia da una generazione di hardware all'altra. I carichi di lavoro con un volume elevato di transazioni di piccole dimensioni, ad esempio i carichi di lavoro OLTP, possono spesso sfruttare SMT e trarre vantaggio da un maggiore aumento delle prestazioni. Al contrario, i carichi di lavoro meno paralleli, come 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 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% dei costi delle licenze è maggiore dell'aumento del costo della VM più grande.

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

Per impostazione predefinita, il numero di core visibili nel sistema operativo è 16, il che significa che per eseguire il server sono necessarie 16 vCPU di Windows Server e 16 vCPU di licenze SQL 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 disabilitare 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 ha bisogno solo di 8 vCPU per l'esecuzione di Windows Server e SQL Server.

Passaggi successivi