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 relativi alla configurazione su come ottimizzare le risorse Microsoft Sistema operativo Windows per le prestazioni di SQL Server durante l'esecuzione in 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 in Windows Server. Quando configuri il tuo ambiente SQL Server in modo che possa connettersi il database da altre macchine client, configurare il firewall per 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 delimitato da virgole senza spazi vuoti per il parametro remoteip al posto di LOCAL_SUBNET. Inoltre, tieni presente che il percorso del parametro program potrebbe cambiare a seconda del di SQL Server che utilizzi.

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. Queste impostazioni sono in genere sufficienti. Inoltre, le impostazioni predefinite conservative assicurano che il traffico di rete non sovraccarichi alla rete e ai 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 Microsoft per antivirus software.

Se utilizzi Windows, devi avere installato un software antivirus. I malware e i virus dei software presentano un rischio significativo per qualsiasi sistema collegato a una rete, mentre il software antivirus è una semplice 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 senza intoppi.

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 installato sul disco permanente di avvio, che viene montato come unità "C:". Prendi in considerazione collegamento di un disco permanente SSD secondario e lo spostamento dei 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 di Windows.

La natura temporanea della tecnologia SSD locale la rende una scelta inadeguata per l'utilizzo 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. Questa operazione riduce il carico di un numero significativo di operazioni di I/O dai tuoi dischi permanenti SSD. Per ulteriori informazioni sulla configurazione, vedi 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 è impostarla sul numero di CPU sul server. Tuttavia, c'è un momento in cui interrompere un eseguire query in 16 o 32 blocchi, eseguendoli tutti su vCPU diverse Consolidare tutto in un unico risultato richiede molto più tempo rispetto a quanto accadrebbe se su una vCPU per cui è stata eseguita la query. In pratica, 8 è un buon valore predefinito.

Best practice: monitora il tempo di attesa di CXPACKET e aumentalo in modo incrementale cost 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 dell'aumento di questo valore è la presenza di CXPACKET in attesa. 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 il valore globale le impostazioni di elaborazione parallela o impostarle a livello di singolo database.

I singoli database possono avere esigenze di parallelismo diverse. Puoi impostare queste opzioni 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 un'utile guida alle prestazioni che illustra 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 fonti più trascurate di perdita di prestazioni e rallentamenti è la crescita non gestita del log delle transazioni. Se il database viene configurato per utilizzare il modello di recupero Full, puoi eseguire un ripristino 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ò aumentare, SQL Server espande la dimensione del file e azzera lo spazio vuoto. L'impostazione per File L'impostazione Crescita è pari al 10% delle dimensioni attuali del file di log. Non è un buon segno è l'impostazione predefinita per le prestazioni: più il file cresce, più a lungo necessario per creare il nuovo spazio vuoto.

Best practice: pianifica backup regolari del log delle transazioni.

Indipendentemente dalle impostazioni massime di dimensione e crescita, pianifica una pianificazione regolare backup dei log delle transazioni, che, per impostazione predefinita, tronca le vecchie voci di log e consente al sistema di riutilizzare lo spazio esistente dei file. Questo semplice 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 agisci per prevenire la frammentazione del file di log.

Il file di log delle transazioni fisiche è segmentato 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 la crescita avviene troppo spesso vengono create molte 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 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 sui tuoi cluster nelle tabelle modificate.

Gli indici nelle tabelle possono diventare frammentati, il che può generare le prestazioni delle query che usano 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 database per mostrare gli indici e la percentuale di frammentazione. Nei risultati di esempio puoi vedere che L'indice PK_STOCK è frammentato al 95%. Nel seguente elemento "SELECT" l'Informativa, 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 scriptALTER di base. Ecco uno script di esempio che stampa l'elemento ALTER che puoi eseguire per ciascuna delle tue tabelle indici:

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. Prendi in considerazione la possibilità di programmare questo uno script simile a uno dei normali job di manutenzione.

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 dimensioni sono pari a 64 kB e sono composte da otto pagine di memoria contigue di 8 kB dimensioni. 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 tuoi dati utilizzando le funzionalità di backup e di emergenza di Google di ripristino per una protezione ottimale. Ti consigliamo di eseguire il backup dei dati all'indirizzo 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 incrementale efficiente con recupero point-in-time reale che consente di eseguire il backup in meno tempo rispetto ai backup convenzionali, riducendo al contempo l'impatto sui server di produzione. Inoltre riduce il consumo di larghezza di banda e spazio di archiviazione per un RPO (Recovery Point Objective) e un costo totale di proprietà (TCO) bassi.
  • 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 lo stesso server che stai monitorando oppure i dati possono essere trasmessi in flusso a un'altra Istanza server che esegue il warehouse.

Caricamento collettivo dei dati

Best practice: utilizza un database separato per eseguire il staging e trasformare i 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 il nuovo database su un'unità SSD locale, se spazio a sufficienza. 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 verificarne il rendimento previsto.

Ogni volta che configuri un nuovo sistema, dovresti pianificare di convalidare il configurazione ed esecuzione di alcuni test delle prestazioni. 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 alla versione Standard. Se stai eseguendo la migrazione di una licenza esistente su Google Cloud, potresti prendere in considerazione alcune opzioni per le prestazioni.

Utilizzo di tabelle compresse

Best practice: attiva la compressione di tabelle e indici.

Potrebbe sembrare controintuitivo che la compressione delle tabelle possa rendere il sistema ma, nella maggior parte dei casi, è quello che succede. Il compromesso è l'utilizzo di una piccola quantità di cicli della CPU per comprimere i dati ed eliminare l'I/O del disco in eccesso necessaria per leggere e scrivere i blocchi più grandi. In genere, minore è l'I/O su disco il sistema, migliori sono le sue 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 archivia le pagine pulite. In parole povere, archivia copie dei tuoi dati, eseguendo il mirroring di come appaiono su disco. Quando i dati cambiano in memoria, si parla di pagina sporca. È necessario eseguire il flush delle pagine su disco 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 viene eseguita la pulizia vengono eliminate, il sistema deve leggere dal disco al successivo accesso la perdita di dati.

La funzionalità di estensione del pool di buffer ti consente di inviare pagine pulite a un'unità SSD locale, anziché eliminarle. Questa operazione andrà a buon fine sulla stessa funzione della memoria virtuale, vale a dire mediante lo scambio, consente di accedere alle pagine pulite sull'SSD locale, una velocità sul disco normale per recuperare i dati.

Questa tecnica non è veloce quanto avere abbastanza memoria, ma può offrire un aumento modesto della produttività quando la memoria disponibile è ridotta. Puoi scoprire di più sulle estensioni del pool di buffer ed esaminarne dei risultati del benchmarking sui sito.

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 significativamente incide 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 richiedere questa funzionalità suddivide il lavoro di computing in di blocchi parallelizzabili 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 è bloccato, l'altro thread può utilizzare il core.

L'impatto esatto sulle prestazioni di SMT su SQL Server dipende dal carico di lavoro caratteristiche e la piattaforma hardware usata perché l'implementazione di SMT varia a seconda della generazione di hardware. Carichi di lavoro con un volume elevato transazioni, ad esempio i carichi di lavoro OLTP, possono spesso sfruttare SMT e che potranno trarre vantaggio da un maggiore aumento delle prestazioni. Al contrario, carichi di lavoro parallelizzabili, ad esempio i carichi di lavoro OLAP, traggono meno vantaggio dall'SMT. Anche se questi pattern sono stati notati in generale, valuta l'impatto sul rendimento dell'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% il costo delle licenze è maggiore del costo maggiore 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 la procedura 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 8 vCPU per l'esecuzione di Windows Server e SQL Server.

Passaggi successivi