Best practice per le istanze SQL Server

Puoi applicare diverse best practice per ottimizzare Compute Engine che eseguono Microsoft SQL Server. Per scoprire come configurare un un'istanza SQL Server ad alte prestazioni, 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 l'IP indirizzi IP dei tuoi 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 regola firewall di Windows SQL Server. Questa regola non è soggetta a restrizioni, quindi ti consigliamo di disattivarla prima del sistema passa in produzione.

Ottimizzazione delle connessioni di rete

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

Nella maggior parte dei sistemi operativi, le impostazioni di rete predefinite sono configurate per su piccoli computer collegati 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 di macchine virtuali (VM) sono collegate progettata da Google che offre capacità e prestazioni elevate. La posizione fisica i server che eseguono le tue istanze Compute Engine sono altamente ottimizzati per sfruttare questa capacità di rete. I driver della rete virtuale vengono ottimizzate, in modo che i valori predefiniti siano sufficienti nella maggior parte dei casi d'uso.

Installazione antivirus in corso...

Best practice: segui le indicazioni Microsoft per antivirus software.

Se esegui Windows, dovresti eseguire 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 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 senza intoppi.

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 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 SSD locale per migliorare le IOPS

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

La natura effimera della tecnologia SSD locale lo rende un cattivo candidato per da usare con i database critici e i file importanti. Tuttavia, tempdb e il file di paging di Windows sono entrambi file temporanei, quindi entrambi sono ottimi candidati a un 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 è la corrispondenza al 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 rappresenta 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 lavoro di CPU e I/O richiesta per eseguire una query con un'esecuzione seriale prima che venga preso in considerazione per un piano di esecuzione parallelo. Il valore predefinito è 5. Anche se non forniamo consigli specifici per la modifica del valore predefinito, che vale la pena tenere d'occhio e, se necessario, aumentarla di 5 in modo incrementale durante test di carico. Un indicatore chiave dell'aumento di questo valore è la presenza di CXPACKET in attesa. Anche se la presenza di CXPACKET attende, indicano necessariamente che questa impostazione deve essere modificata, pertanto è 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. Tu devono 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 mitigarne l'impatto 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 sul tuo accumulo di log giornaliero medio.

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 l'utilizzo del modello di recupero Full, puoi eseguire un ripristino ma i log delle transazioni si riempiono più velocemente. Per impostazione predefinita, quando il file di log delle transazioni è pieno, SQL Server aumenta la dimensione del file da aggiungere di più spazio vuoto per scrivere più transazioni e bloccare tutte le attività sul fino al termine dell'operazione. SQL Server aumenta ogni file di log in base al Dimensioni massime del file e Crescita dei file.

Se il file ha raggiunto il limite di dimensioni massimo e non può crescere, il sistema emette un Errore 9002 e imposta 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 La dimensione predefinita è pari al 10% della dimensione attuale 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). Nuovi Le VLF vengono create ogni volta che il file di log delle transazioni fisiche deve crescere. Se Non hai disattivato la crescita automatica e la crescita avviene troppo spesso vengono create molte VLF. Questa attività può provocare la frammentazione del 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 quanti 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 in base al 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 di seguito per verificare il numero di VLF attualmente presenti nel database. Se ha 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ù sulle VLF su 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 dovresti includere la riorganizzazione degli indici nelle 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

Se gli indici sono troppo frammentati, puoi riorganizzarli utilizzando una Script ALTER. 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 con la più alta frammentazione dall'insieme di risultati e eseguire 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 da 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 da 64 kB consente a SQL Server di leggere scrivere 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: PowerShell, che cerca tutti i dischi nuovi e non inizializzati in un 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: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.
  • 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, compreso il supporto per Cluster del gruppo di disponibilità di SQL Server e più opzioni di ripristino nei vari scenari.
  • Pannello di gestione centrale che include monitoraggio, avvisi e generazione di report dedicati per tutti i tuoi backup.

Ulteriori informazioni:

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 che desideri monitorare e inviarle al e un 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: usa un database separato per organizzare e trasformare i dati in blocco prima di spostarlo 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 potrebbe raggiungere limite di IOPS del disco permanente quando esegui caricamenti collettivi.

Esiste un modo semplice per ridurre l'I/O del disco e il consumo di CPU dovuto al carico collettivo operazioni, con il vantaggio aggiuntivo di accelerare i tempi di esecuzione in batch. La soluzione è creare un database completamente separato che utilizza il modello di recupero Simple, quindi usa quel database per la gestione temporanea trasformare il set di dati collettivo prima di inserirlo nell'ambiente di produzione per configurare un database. Puoi anche inserire il nuovo database su un'unità SSD locale, se spazio a sufficienza. L'uso 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. L'ultimo vantaggio è che il job di backup per i dati di produzione eseguire il backup di tutte quelle operazioni collettive nel log delle transazioni, più piccolo e più veloce.

Convalida della configurazione in corso...

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. Questi dati sono stati archiviati procedura è un'ottima risorsa per valutare la configurazione di SQL Server. Prenditi un po' di tempo per leggere informazioni di configurazione ed esegui la procedura.

Ottimizzazione di SQL Server Enterprise Edition

SQL Server Enterprise Edition offre un lungo elenco di funzionalità aggiuntive Versione Standard. Se stai eseguendo la migrazione di una licenza esistente a Google Cloud, ecco alcune opzioni per le prestazioni valuta la possibilità di implementarlo.

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 utilizzate dal sistema, migliori saranno le sue prestazioni. Istruzioni per la stima e di abilitazione della compressione di tabelle e indici sono disponibili su MSDN sito web.

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, archivia copie dei tuoi dati, eseguendo il mirroring di come appaiono su disco. Quando i dati modifiche 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, esercita una pressione sul pool di buffer e le pagine pulite potrebbero far cadere. 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 consente di eseguire il push di pagine pulite su un SSD locale, anziché rilasciarle. Funziona 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 è tanto veloce quanto avere un numero sufficiente ma può fornire un modesto aumento della velocità effettiva quando è in esaurimento. Puoi scoprire di più sulle estensioni del pool di buffer ed esaminarne dei risultati del benchmarking sui sito.

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 di SQL Server carichi di lavoro

Multi-threading simultaneo (SMT), comunemente nota come tecnologia Hyper-Threading (HTT) sui processori Intel, è una funzionalità che consente a un singolo core della CPU di essere logicamente condivisi come due thread. In Compute Engine, SMT è abilitato sulla maggior parte delle VM per impostazione predefinita, vale a dire che ogni vCPU nella VM viene eseguita su un singolo thread un core CPU fisico è condiviso da due vCPU

In Compute Engine, puoi configurare il numero di thread per core, che di fatto disattiva SMT. Quando il numero di thread per core è impostato su 1, le vCPU non condividono i core della CPU fisica. Questa configurazione significativamente influisce sui costi delle licenze per Windows Server e SQL Server. Quando il numero thread per core è impostato su 1, il numero di vCPU in una VM viene dimezzato, dimezza il numero di licenze Windows Server e SQL Server necessarie. Questo può per ridurre significativamente il costo totale del carico di lavoro.

Tuttavia, la configurazione del numero di thread per core influisce anche sul carico di lavoro delle prestazioni. Le applicazioni scritte per essere multi-thread possono richiedere questa funzionalità suddivide il lavoro di computing in di blocchi parallelizzabili pianificati in più core logici. Questo il caricamento in contemporanea del lavoro spesso aumenta la velocità effettiva complessiva del sistema migliorando utilizzando le 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 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 di 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. Sebbene che questi modelli sono stati notati in generale, valuta la possibilità di valutare dell'SMT sulla base del carico di lavoro per determinare l'impatto della definizione di thread per core a 1.

La configurazione più conveniente per la maggior parte dei carichi di lavoro SQL Server comporta l'impostazione del numero di thread per core su 1. Qualsiasi calo del rendimento 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 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, ovvero significa che 16 vCPU di Windows Server e 16 vCPU di licenze SQL Server necessaria 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