Puoi applicare diverse best practice per ottimizzare le istanze di Compute Engine che eseguono Microsoft SQL Server. Per scoprire come configurare un'istanza di SQL Server ad alte prestazioni, consulta Creazione di un'istanza SQL Server ad alte prestazioni.
Configurazione di Windows
Questa sezione tratta gli argomenti di configurazione relativi all'ottimizzazione del sistema operativo Microsoft Windows per le prestazioni di SQL Server durante l'esecuzione su Compute Engine.
Configurazione del firewall di Windows
Best practice: utilizza Windows Server Advanced Firewall e specifica gli indirizzi IP dei computer client.
Windows Advanced Firewall è 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 macchine client. Specifica un elenco delimitato da virgole di indirizzi IP 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 del firewall Windows SQL Server.
Questa regola è piuttosto senza restrizioni, quindi valuta la possibilità 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 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.
Su 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 di Compute Engine sono altamente ottimizzati per sfruttare questa capacità di rete. Vengono ottimizzati anche i driver di rete virtuali nelle tue istanze, il che rende i valori predefiniti sufficienti per la maggior parte dei casi d'uso.
Installazione dell'antivirus in corso...
Best practice: segui le indicazioni di Microsoft per il software antivirus.
Se il sistema operativo è Windows, dovrebbe essere in esecuzione un po' di software antivirus. Malware e virus software rappresentano un rischio significativo per qualsiasi sistema collegato a una rete, mentre il software antivirus è un semplice passaggio per limitare la protezione dei 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 prestazioni e stabilità
Questa sezione fornisce informazioni su come ottimizzare le prestazioni di SQL Server su Compute Engine e descrive le attività operative per garantire un'operatività ottimale.
Spostamento dei file di dati e 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 i file di dati sul nuovo disco.
Utilizzo di un SSD locale per migliorare il numero di IOPS
Best practice: crea nuove istanze SQL Server con uno o più
SSD locali per archiviare i
file di paging tempdb e Windows.
La natura temporanea della tecnologia SSD locale ne fa una candidata
per l'utilizzo con database critici e file importanti. Tuttavia, il file di paging tempdb e quello di Windows sono entrambi file temporanei, quindi entrambi sono ottimi candidati per il passaggio a un'unità SSD locale. Questo comporta l'offload di un numero significativo di operazioni di I/O dai dischi permanenti SSD. Per ulteriori informazioni sulla configurazione, consulta
Configurazione di TempDB.
Elaborazione parallela delle query
Best practice: imposta max degree of parallelism su 8.
L'impostazione predefinita consigliata per max degree of parallelism è di farla corrispondere al numero di CPU sul server. Tuttavia, c'è un momento in cui suddividere una query in 16 o 32 blocchi, eseguendole tutte su vCPU diverse e poi consolidandole di nuovo in un unico risultato, richiede molto più tempo rispetto a quanto accadrebbe se la query venisse eseguita da una sola vCPU. In pratica, 8 rappresenta un buon valore predefinito.
Best practice: monitora le attese 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 che venga considerata per un piano di esecuzione parallelo. Il valore predefinito è 5.
Anche se non offriamo alcun consiglio specifico per modificare il valore predefinito, è consigliabile tenere d'occhio e, se necessario, aumentarlo in modo incrementale di 5 durante il test di carico. Un indicatore chiave che potrebbe dover essere aumentato è la presenza
di attese di CXPACKET. Sebbene la presenza di attese di tipo CXPACKET non indichi necessariamente che questa impostazione deve essere modificata, è un buon punto di partenza.
Best practice: monitora diversi tipi di attesa e regola le impostazioni globali dell'elaborazione parallela o impostale 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 tuoi carichi di lavoro univoci, monitorare le attese e quindi regolare i
valori di conseguenza.
Il sito SQLSkills offre un'utile guida sulle prestazioni che illustra le statistiche di attesa all'interno del database. 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 disabilitare la crescita automatica e di impostare il file di log a dimensioni fisse, in base alla tua media di accumulo giornaliero di log.
Una delle fonti 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 di log delle transazioni è pieno, SQL Server ne aumenta le dimensioni per aggiungere più spazio vuoto per scrivere altre transazioni e blocca tutte le attività nel database fino al termine. SQL Server cresce ogni file di log in base alla rispettiva dimensione massima del file e all'impostazione Crescita file.
Quando il file ha raggiunto il limite di dimensione massima e non può crescere, il sistema genera un errore 9002 e imposta il database in modalità di sola lettura. Se il file può aumentare, SQL Server aumenta le dimensioni del file e azzera gli spazi vuoti. Il valore predefinito dell'impostazione Crescita file è 10% delle dimensioni attuali del file di log. Questa non è un'impostazione predefinita appropriata per le prestazioni perché più grande è il file, maggiore sarà il tempo necessario per creare un nuovo spazio vuoto.
Best practice:pianifica backup regolari del log delle transazioni.
Indipendentemente dalle impostazioni di crescita e dimensioni massime, pianifica regolarmente backup dei log delle transazioni, che, per impostazione predefinita, tronca le voci di log precedenti e consentono al sistema di riutilizzare lo spazio per i file esistente. Questa semplice attività di manutenzione può aiutarti a evitare cali di prestazioni nei momenti di picco del 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 viene segmentato in file di log virtuali (VLF). Vengono creati nuovi valori VLF ogni volta che il file di log delle transazioni fisiche deve crescere. Se non hai disabilitato la crescita automatica e la crescita è troppo frequente, vengono create troppe VLF. Questa attività può causare la frammentazione del file di log, simile a quella del disco e 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 una VLF all'interno del nuovo segmento. In precedenza, sarebbero state create 8 VLF per la crescita tra 64 MB e 1 GB e 16 VLF per la crescita oltre 1 GB. Puoi usare lo script TSQL riportato di seguito per verificare il numero di VLF attualmente presenti nel tuo database. Se contiene migliaia di file, 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.
Come evitare la frammentazione dell'indice
Best practice: deframmenta regolarmente gli indici nelle tabelle più modificate.
Gli indici nelle tabelle possono essere frammentati, il che può comportare prestazioni scadenti per le 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 tuo database al fine di mostrare gli indici e la relativa 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
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 ciascuno degli indici 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'
Dal set di risultati scegli le tabelle con la frammentazione più elevata, quindi esegui queste istruzioni in modo incrementale. Valuta la possibilità di pianificare questo script o uno script simile come uno dei tuoi 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 8 kB. La formattazione di un disco con un'unità di allocazione da 64 kB consente a SQL Server di leggere e scrivere estende l'estensione 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 il seguente comando 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:predisponi un piano per i backup ed esegui regolarmente i backup.
Il sito di Ola Hallengren fornisce un buon punto di partenza per comprendere come implementare un solido piano di backup e manutenzione.
Quando esegui regolarmente backup dei database, fai attenzione a non consumare troppi IOPS del disco permanente. Utilizza l'SSD locale per posizionare temporaneamente i backup ed eseguirne il push in un bucket Cloud Storage.
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 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 su un'altra istanza SQL Server che esegue il warehouse.
Caricamento collettivo dei dati
Best practice: utilizza un database separato per posizionare temporaneamente e trasformare i dati collettivi prima di spostarli nei 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 quando esegui caricamenti collettivi.
Esiste un modo semplice per ridurre il consumo di I/O e della CPU del disco durante le operazioni di caricamento collettivo, con l'ulteriore vantaggio di accelerare i tempi di esecuzione dei job batch. La soluzione consiste nel creare un database completamente separato che utilizza il modello di recupero Simple, quindi utilizzarlo per la gestione temporanea e la trasformazione del set di dati in blocco prima di inserirlo nel database di produzione. Puoi anche inserire questo nuovo database su un'unità SSD locale, se disponi di spazio sufficiente. L'utilizzo di un SSD locale per il database di ripristino 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 le operazioni collettive nel log delle transazioni e, di conseguenza, avrà dimensioni ridotte ed verrà eseguito più velocemente.
Convalida della configurazione in corso...
Best practice: testa la configurazione per verificare che funzioni come previsto.
Ogni volta che configuri un nuovo sistema, ti consigliamo di 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. Prenditi un po' di tempo per leggere informazioni sui flag di configurazione ed esegui la procedura.
Ottimizzazione di SQL Server Enterprise Edition
SQL Server Enterprise Edition ha un lungo elenco di funzionalità aggiuntive rispetto alla versione Standard. Se stai eseguendo la migrazione di una licenza esistente a Google Cloud, potresti valutare di implementare alcune opzioni per le prestazioni.
Utilizzo delle tabelle compresse
Best practice:abilita la compressione di tabelle e indici.
Può sembrare controintuitivo che la compressione delle tabelle possa velocizzare le prestazioni del sistema, ma nella maggior parte dei casi è questo che succede. Il compromesso è l'utilizzo di una piccola quantità di cicli della CPU per comprimere i dati ed eliminare l'I/O extra del disco necessario per leggere e scrivere i blocchi più grandi. In genere, la quantità minore di I/O del disco utilizzata dal sistema, migliori le sue prestazioni. Le istruzioni per stimare e abilitare 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.
Nel pool del buffer il sistema archivia le pagine pulite. In parole povere, archivia copie dei tuoi dati, rispecchiando l'aspetto che appaiono su disco. Quando i dati cambiano in memoria, si parla di pagina sporca. Le pagine con problemi devono essere lavati su disco per salvare le modifiche. Se il database ha dimensioni superiori alla memoria disponibile, si esercita una pressione sul pool di buffer e le pagine pulite potrebbero essere eliminate. Quando le pagine eliminate vengono eliminate, il sistema deve leggere dal disco la prossima volta che accede ai dati eliminati.
La funzionalità di estensione del pool di buffer consente di eseguire il push di pagine pulite su un SSD locale, anziché rilasciarle. Questo processo funziona sulle stesse linee della memoria virtuale, vale a dire mediante lo scambiamento e ti consente di accedere alle pagine pulite sull'SSD locale, una procedura più veloce rispetto al recupero dei dati sul disco normale.
Questa tecnica non è così veloce come avere memoria sufficiente, ma può offrirti un modesto aumento della velocità effettiva quando la tua memoria disponibile è bassa. Puoi scoprire di più sulle estensioni del pool di buffer e consultare alcuni risultati di analisi del benchmark 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 di SQL Server
Il multithreading simultaneo (SMT), comunemente noto come Hyper-Threading Technology (HTT) sui processori Intel, è una funzionalità che consente di condividere logicamente un singolo core della CPU come due thread. In Compute Engine, la modalità 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 della CPU fisica è condiviso da due vCPU.
In Compute Engine, puoi configurare il numero di thread per core, che disattiva la tecnologia SMT. Se il numero di thread per core è impostato su 1, le vCPU non condividono i core fisici della CPU. Questa configurazione ha un impatto significativo 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 si dimezza, il che dimezza anche il numero di licenze Windows Server e SQL Server richieste. Questo può ridurre notevolmente il costo totale del carico di lavoro.
Tuttavia, la configurazione del numero di thread per core influisce anche sulle prestazioni dei carichi di lavoro. Le applicazioni scritte per essere multi-thread possono sfruttare questa funzionalità suddividendo il lavoro di computing in blocchi più piccoli parallelizzabili pianificati su più core logici. Il parallelismo del lavoro spesso aumenta la velocità effettiva complessiva del sistema grazie a un migliore utilizzo delle risorse principali disponibili. Ad esempio, quando un thread è bloccato, l'altro thread può utilizzare il core.
L'impatto esatto sulle prestazioni della SMT su SQL Server dipende dalle caratteristiche dei carichi di lavoro e dalla piattaforma hardware utilizzata perché l'implementazione di SMT varia tra le generazioni di hardware. I carichi di lavoro con un volume elevato di transazioni ridotte, ad esempio i carichi di lavoro OLTP, possono spesso sfruttare la SMT e trarre vantaggio da un aumento delle prestazioni maggiore. Al contrario, i carichi di lavoro meno parallelizzabili, ad esempio quelli OLAP, traggono meno vantaggio dalla SMT. Sebbene questi pattern siano stati notati in generale, ti consigliamo di valutare l'impatto sulle prestazioni della 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. Eventuali cali delle prestazioni possono essere compensati 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: supponiamo che venga eseguito il deployment di un server SQL 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 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 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 richiede solo 8 vCPU per l'esecuzione di Windows Server e SQL Server.
Passaggi successivi
- Creazione di un'istanza SQL Server ad alte prestazioni
- Creazione di istanze SQL Server
- Creazione di istanze Windows