Questo tutorial mostra come creare un'istanza VM di Compute Engine che esegue SQL Server ottimizzata per le prestazioni. Questo tutorial ti guida nella creazione dell'istanza e nella configurazione di SQL Server per prestazioni ottimali su Google Cloud. Scoprirai una serie di opzioni di configurazione disponibili per aiutarti a regolare le prestazioni del sistema.
Questo tutorial utilizza SQL Server Standard Edition 2022, quindi non tutte le opzioni di configurazione presentate in questa guida funzionano per tutti e non tutte offrono notevoli vantaggi in termini di prestazioni per ogni carico di lavoro.
Obiettivi
- Configurazione dell'istanza e dei dischi Compute Engine.
- Configurazione del sistema operativo Windows.
- È in corso la configurazione di SQL Server.
Costi
Questo tutorial utilizza i componenti fatturabili di Google Cloud, tra cui:
- Istanza Compute Engine con memoria elevata
- Archiviazione su disco permanente SSD di Compute Engine
- Spazio di archiviazione disco SSD locale di Compute Engine
- Immagine preconfigurata SQL Server Standard
Il Calcolatore prezzi può generare una stima dei costi in base all'utilizzo previsto. Il link fornito mostra la stima dei costi dei prodotti utilizzati in questo tutorial, che può costare oltre 4 dollari all'ora e oltre 3000 dollari al mese.
Prima di iniziare
- Accedi al tuo account Google Cloud. Se non conosci Google Cloud, crea un account per valutare le prestazioni dei nostri prodotti in scenari reali. I nuovi clienti ricevono anche 300 $di crediti gratuiti per l'esecuzione, il test e il deployment dei carichi di lavoro.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Assicurati che la fatturazione sia attivata per il tuo progetto Google Cloud.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Assicurati che la fatturazione sia attivata per il tuo progetto Google Cloud.
Creazione della VM di Compute Engine con i dischi
Per creare un'istanza SQL Server ad alte prestazioni, devi prima creare un'istanza VM con SQL Server e due dischi permanenti.
Considerazioni sui dischi permanenti
Per selezionare il tipo di dischi permanenti per la tua VM, esamina le seguenti considerazioni:
Un SSD locale offre una posizione ad alte prestazioni per
tempdb
e il file di paging di Windows.Ci sono alcune considerazioni importanti da tenere presenti quando si utilizza un SSD locale. Quando arresti l'istanza da Windows o la reimposti utilizzando l'API, l'SSD locale viene rimosso. Questa azione rende l'istanza non avviabile. Per riprendere l'esecuzione della macchina, devi scollegare i dischi permanenti, creare una nuova istanza con questi dischi, quindi definire un nuovo SSD locale. Dopo l'avvio dovrai anche formattare il nuovo disco e riavviare. Pertanto, non devi archiviare in modo permanente i dati critici su un SSD locale o spegnere l'istanza, a meno che tu non sia pronto a ricrearla.
Un disco permanente SSD offre spazio di archiviazione ad alte prestazioni per i file di database.
Le prestazioni di Persistent Disk si basano su un calcolo che utilizza il numero di CPU e le dimensioni del disco. Con 32 vCPU e un disco da 1 TB, le prestazioni raggiungono i picchi di 40.000 operazioni di lettura al secondo e 30.000 operazioni di scrittura. La velocità effettiva totale sostenuta per le letture e le scritture è rispettivamente di 800 MB al secondo e 400 MB al secondo. Queste misurazioni rappresentano la somma di tutti i dischi permanenti collegati alla macchina virtuale, inclusa l'unità
C:\
. Ecco perché dovresti creare un SSD locale per trasferire tutte le IOPS necessarie per il file di paging,tempdb
, i dati temporanei e i backup.
Per saperne di più sulle prestazioni dei dischi, consulta Configurare i dischi per soddisfare i requisiti delle prestazioni.
Creazione della VM di Compute Engine con i dischi
Per creare una VM con SQL Server 2022 Standard preinstallato su Windows Server 2022, segui questi passaggi:
Nella console Google Cloud, vai alla pagina Crea un'istanza.
In Nome, inserisci
ms-sql-server
.Nella sezione Configurazione macchina, seleziona Uso generico, quindi procedi nel seguente modo:
- Nell'elenco Serie, fai clic su N2.
- Nell'elenco Tipo di macchina, fai clic su n2-highmem-16 (16vCPU, 128 GB di memoria).
Nella sezione Disco di avvio, fai clic su Cambia, quindi segui questi passaggi:
- Nella scheda Immagini pubbliche, fai clic sull'elenco Sistema operativo, quindi seleziona SQL Server su Windows Server.
- Nell'elenco Versione, fai clic su SQL Server 2022 Standard su Windows Server 2022 Datacenter.
- Nell'elenco Tipo di disco di avvio, fai clic su Disco permanente standard.
- Nel campo Dimensioni (GB), imposta la dimensione del disco di avvio su 50 GB.
- Per salvare la configurazione del disco di avvio, fai clic su Seleziona.
Espandi la sezione Opzioni avanzate e procedi nel seguente modo:
- Espandi la sezione Dischi.
Per creare dischi locali, fai clic su Aggiungi SSD locale, quindi segui questi passaggi:
- Nell'elenco Interfaccia, seleziona il protocollo che soddisfa i requisiti di prestazioni del tuo sistema.
- Nell'elenco Capacità disco, seleziona una capacità del disco che supporti la dimensione prevista di
tempdb
file. - Per completare la creazione del disco, fai clic su Salva.
Per creare altri dischi, fai clic su Aggiungi nuovo disco.
- Lascia invariato il campo Nome.
- Nell'elenco Tipo di disco di origine, seleziona Disco vuoto.
- Nell'elenco Tipo di disco, seleziona Disco permanente SSD.
- Nel campo Dimensioni, inserisci le dimensioni del disco che possono accogliere quelle del database.
- Per completare la creazione del secondo disco, fai clic su Salva.
Per creare la VM, fai clic su Crea.
Configurazione di Windows
Ora che disponi di un'istanza funzionante in cui è in esecuzione SQL Server, connettiti all'istanza e configura il sistema operativo Windows. Dopodiché imparerai a configurare SQL Server in una prossima sezione.
Connettiti all'istanza
Nella console Google Cloud, vai alla pagina Istanze VM.
Nella colonna Nome, fai clic sul nome dell'istanza,
ms-sql-server
.Nella parte superiore della pagina dei dettagli dell'istanza, fai clic sul pulsante Imposta password di Windows.
Specifica un nome utente.
Fai clic su Imposta per generare una nuova password per questa istanza Windows.
Prendi nota del nome utente e della password in modo da poter accedere all'istanza.
Connettiti alla tua istanza utilizzando RDP.
Configurazione dei volumi di disco
Crea e formatta i volumi:
- Nel menu Start, cerca "Gestione computer" e aprilo.
- Nella sezione Archiviazione, seleziona Gestione disco.
- Quando ti viene chiesto di inizializzare i dischi, accetta le selezioni predefinite e fai clic su OK.
Crea partizione per i dischi SSD locali:
Per individuare un disco SSD locale, fai clic con il tasto destro del mouse su un disco e seleziona Proprietà. Il nome delle proprietà del disco SSD locale sarà
Google EphemeralDisk
per un'interfaccia SCSI onvme_card
per un'interfaccia NVMe. Sia gli SSD locali che gli SSD permanenti sono contrassegnati conUnallocated
partizioni.Se la VM contiene solo un'unità SSD locale, segui questi passaggi:
- Nell'elenco delle unità disco, fai clic con il pulsante destro del mouse sul disco SSD locale da 374,98 GB e seleziona Nuovo volume semplice.
- Nella schermata di benvenuto, fai clic su Avanti per avviare la procedura guidata del volume su disco.
- Nel passaggio Specifica le dimensioni del volume, lascia il valore predefinito per la dimensione del volume e fai clic su Avanti per continuare.
- Nel passaggio Assegna lettera o percorso di Drive, scegli P: per la lettera dell'unità e fai clic su Avanti per continuare.
Nel passaggio Formato volume, imposta Dimensioni dell'unità di allocazione su 8192 e inserisci "pagefile" in Etichetta volume. Fai clic su Avanti per continuare.
Fai clic su Fine per completare la procedura guidata del volume su disco.
Se la VM contiene più unità SSD locali, segui questi passaggi:
- Nell'elenco delle unità disco, fai clic con il pulsante destro del mouse sul primo disco SSD locale da 374,98 GB e seleziona Nuovo volume a strisce.
- Nella schermata di benvenuto, fai clic su Avanti per avviare la procedura guidata del volume su disco.
Nel passaggio Seleziona dischi, aggiungi alla sezione Selezionati tutti i dischi disponibili con dimensioni pari a 383.982 MB. Fai clic su Avanti per continuare.
Nel passaggio Assegna lettera o percorso di Drive, scegli P: per la lettera dell'unità e fai clic su Avanti per continuare.
Nel passaggio Formato volume, imposta Dimensioni dell'unità di allocazione su 8192 e inserisci "pagefile" in Etichetta volume. Fai clic su Avanti per continuare.
Fai clic su Fine per completare la procedura guidata del volume su disco.
Ripeti i passaggi precedenti per creare un nuovo volume semplice per il disco SSD, apportando le tre modifiche seguenti:
Scegli D: per la lettera dell'unità.
Imposta Dimensioni unità di allocazione su
64k
.Per maggiori dettagli sulla selezione di una dimensione dell'unità di allocazione, consulta Best practice per le istanze SQL Server.
Inserisci
sqldata
per Etichetta volume.
Spostare il file di paging di Windows
Ora che i nuovi volumi sono stati creati e montati, sposta il file di paging di Windows sull'SSD locale, in modo da liberare IOPS del disco permanente e migliorare il tempo di accesso alla memoria virtuale.
- Nel menu Start, cerca Visualizza impostazioni di sistema avanzate, quindi apri la finestra di dialogo.
- Fai clic sulla scheda Avanzate e nella sezione Rendimento fai clic su Impostazioni.
- Nella sezione Memoria virtuale, fai clic sul pulsante Cambia.
- Deseleziona la casella di controllo Gestisci automaticamente le dimensioni del file di paging per tutti i Drive. Il
sistema dovrebbe aver già configurato il file di paging nell'unità
C:\
e devi spostarlo. - Fai clic su C:, quindi sul pulsante di opzione C:.
- Fai clic sul pulsante Imposta.
- Per creare il nuovo file di paging, fai clic sull'unità P:, quindi sul pulsante di opzione Dimensioni gestite dal sistema.
- Fai clic sul pulsante Imposta.
Fai clic su OK tre volte per uscire dalle proprietà avanzate di sistema.
L'assistenza Microsoft ha pubblicato suggerimenti aggiuntivi per le impostazioni della memoria virtuale.
Impostazione del profilo di accensione
Imposta il profilo di accensione su High-Performance
anziché su Balanced
.
- Nel menu Start, cerca "Scegli un piano di alimentazione" e apri le opzioni di alimentazione.
- Seleziona il pulsante di opzione Alte prestazioni.
- Esci dalla finestra di dialogo.
Configurazione di SQL Server
Utilizza SQL Server Management Studio per eseguire la maggior parte delle attività amministrative. Management Studio è già installato nelle immagini preconfigurate per SQL Server. Avvia Management Studio e fai clic su Connetti per connetterti al database predefinito.
Spostare i dati e i file di log
L'immagine preconfigurata per SQL Server include tutto ciò che è installato sull'unità C:\
, inclusi i database di sistema. Per ottimizzare la configurazione, sposta i file nel nuovo Drive D:\
che hai creato. Ricordati inoltre di creare tutti i nuovi database sull'unità D:\
. Poiché utilizzi un'unità SSD, non è necessario archiviare i file di dati e i file di log su partizioni del disco separate.
Esistono due modi per spostare l'installazione sul disco secondario: utilizzando il programma di installazione o spostando i file manualmente.
Utilizzo del programma di installazione
Per utilizzare il programma di installazione, esegui c:\setup.exe
e seleziona un nuovo percorso di installazione sul disco secondario.
Spostare i file manualmente
Sposta i database di sistema e configura SQL Server per salvare i dati e i file di log sullo stesso volume:
- Crea una nuova cartella denominata
D:\SQLData
. - Apri una finestra dei comandi.
Inserisci il comando seguente per concedere l'accesso completo a
NT Service\MSSQLSERVER
:icacls D:\SQLData /Grant "NT Service\MSSQLServer:(OI)(CI)F"
Utilizza Management Studio e le seguenti guide per spostare i database di sistema e modificare le posizioni predefinite dei file per i nuovi database.
Se prevedi di utilizzare le funzionalità di Report Server, sposta anche i file ReportServer e ReportServerTempDB.
Dopo aver spostato i file del database di configurazione principale e riavviato il sistema, devi configurare il sistema in modo che rimandi alla nuova località per i database del modello e MSDB. Ecco uno script di supporto da eseguire in Management Studio:
ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'D:\SQLData\model.mdf' ) ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'D:\SQLData\modellog.ldf' ) ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = 'D:\SQLData\MSDBData.mdf' ) ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBlog , FILENAME = 'D:\SQLData\MSDBLog.ldf' )
Dopo aver eseguito questi comandi:
- Utilizza lo snapshot
services.msc
per arrestare il servizio di database SQL Server. - Utilizza Esplora file di Windows per spostare i file fisici dall'unità
C:\
in cui si trovava il databasemaster
alla directoryD:\SQLData
. - Avvia il servizio di database SQL Server.
Impostazione delle autorizzazioni di sistema
Dopo aver spostato i database di sistema, modifica alcune impostazioni aggiuntive, a partire dalle autorizzazioni per l'account utente Windows creato per eseguire il processo SQL Server, denominato NT Service\MSSQLSERVER
.
Concessione dell'autorizzazione Lock Pages in Memory
L'autorizzazione Lock Pages in Memory
dei criteri di gruppo impedisce a Windows di spostare le pagine dalla memoria fisica alla memoria virtuale. Per mantenere libera e organizzata la memoria fisica, Windows prova a sostituire le vecchie pagine modificate raramente con il file di paging con memoria virtuale su disco.
SQL Server archivia informazioni importanti in memoria, ad esempio strutture delle tabelle, piani di esecuzione e query memorizzate nella cache. Alcune di queste informazioni cambiano raramente, così diventano un target del file di paging. Se queste informazioni vengono spostate nel file di paging, le prestazioni di SQL Server possono peggiorare. La concessione dell'autorizzazione Lock
Pages in Memory
del criterio di gruppo per l'account di servizio di SQL Server impedisce questo cambio.
Segui questi passaggi:
- Fai clic su Start, quindi cerca Modifica criteri di gruppo per aprire la console.
- Espandi Criteri computer locali > Configurazione computer > Impostazioni di Windows > Impostazioni di sicurezza > Criteri locali > Assegnazione dei diritti utente.
- Cerca e fai doppio clic su Blocca le pagine in memoria.
- Fai clic su Aggiungi utente o gruppo.
- Cerca "NT Service\MSSQLSERVER".
- Se vedi più nomi, fai doppio clic sul nome MSSQLSERVER.
- Fai clic due volte su OK.
- Tieni aperta la console dell'Editor Criteri di gruppo.
Concessione dell'autorizzazione Perform volume maintenance tasks
Per impostazione predefinita, quando un'applicazione richiede una porzione di spazio su disco a Windows, il sistema operativo individua un blocco di spazio su disco di dimensioni appropriate, quindi azzera l'intero blocco di disco prima di restituirlo all'applicazione. SQL Server è in grado di aumentare le dimensioni dei file e di riempire lo spazio su disco, questo comportamento non è ottimale.
Esiste un'API separata per assegnare spazio su disco a un'applicazione, spesso definita inizializzazione file istantanea. Sfortunatamente, questa impostazione funziona solo per i file di dati, ma in una prossima sezione parleremo della crescita dei file di log. L'inizializzazione immediata dei file richiede che l'account di servizio che esegue il processo SQL Server disponga di un'altra autorizzazione dei criteri di gruppo, denominata Perform volume
maintenance tasks
.
- Nell'Editor Criteri di gruppo, cerca "Esegui attività di manutenzione del volume".
- Aggiungi l'account "NT Service\MSSQLSERVER" come hai fatto nella sezione precedente.
- Riavvia il processo SQL Server per attivare entrambe le impostazioni.
Configurazione di tempdb
in corso...
Un tempo era una best practice per ottimizzare l'utilizzo della CPU SQL Server creando un file tempdb
per CPU. Tuttavia, poiché il numero di CPU è aumentato nel tempo, seguire questa linea guida può causare un calo delle prestazioni. Come buon punto di partenza, utilizza 4 file tempdb
. Quando misuri le prestazioni del sistema, in rari casi potrebbe essere necessario aumentare in modo incrementale il numero di file tempdb
fino a un massimo di 8.
Puoi eseguire uno script Transact-SQL (T-SQL) in SQL Server Management Studio per spostare i file tempdb
in una cartella dell'unità "p:".
- Crea la directory
p:\tempdb
. Concedi l'accesso di sicurezza completo all'account utente "NT Service\MSSQLSERVER":
icacls p:\tempdb /Grant "NT Service\MSSQLServer:(OI)(CI)F"
Esegui questo script in SQL Server Management Studio per spostare il file di dati e il file di log
tempdb
:USE master GO ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf') GO ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf') GO
Riavvia SQL Server.
Esegui lo script seguente per modificare le dimensioni dei file e creare tre file di dati aggiuntivi per il nuovo
tempdb
.ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf', SIZE=8GB) ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf' , SIZE = 2GB) ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev1', FILENAME = 'p:\tempdb\tempdev1.ndf' , SIZE = 8GB, FILEGROWTH = 0); ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev2', FILENAME = 'p:\tempdb\tempdev2.ndf' , SIZE = 8GB, FILEGROWTH = 0); ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev3', FILENAME = 'p:\tempdb\tempdev3.ndf' , SIZE = 8GB, FILEGROWTH = 0); GO
Se utilizzi SQL Server 2016, sono presenti altri 3 file
tempdb
da rimuovere dopo aver eseguito i passaggi precedenti:ALTER DATABASE [tempdb] REMOVE FILE temp2; ALTER DATABASE [tempdb] REMOVE FILE temp3; ALTER DATABASE [tempdb] REMOVE FILE temp4;
Riavvia SQL Server.
Elimina i file
model
,MSDB
,master
etempdb
dalla posizione originale sul DriveC:\
.
Hai spostato i tuoi tempdb
file nella partizione SSD locale. Questo spostamento comporta alcuni rischi, menzionati in precedenza, ma se per qualsiasi motivo vengono persi,SQL Server ricostruisce i file tempdb
. Lo spostamento di tempdb
offre le
prestazioni aggiuntive dell'SSD locale e riduce il numero di IOPS utilizzate
sui dischi permanenti.
Configurazione di max degree of parallelism
L'impostazione predefinita consigliata per max degree of parallelism
è la corrispondenza con il numero di CPU sul server. Tuttavia, c'è un momento in cui l'esecuzione di una query in 16 o 32 blocchi paralleli e l'unione dei risultati è molto più lenta rispetto all'esecuzione in un singolo processo. Se utilizzi un'istanza a 16 o 32 core, puoi impostare il valore max degree of parallelism
su 8 utilizzando il seguente T-SQL:
USE master GO EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE WITH OVERRIDE GO EXEC sp_configure 'max degree of parallelism', 8 GO RECONFIGURE WITH OVERRIDE GO
Configurazione di max server memory
Il valore predefinito di questa impostazione è un numero molto alto, ma devi impostarla sul numero di megabyte di RAM fisica disponibile, meno un paio di gigabyte per il sistema operativo e l'overhead. Il seguente esempio T-SQL regola max server memory
su 100 GB. Modificalo per regolare il valore in modo che corrisponda alla tua istanza. Per ulteriori informazioni, consulta il documento sulle opzioni di configurazione del server di memoria server.
EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE WITH OVERRIDE GO exec sp_configure 'max server memory', 100000 GO RECONFIGURE WITH OVERRIDE GO
Completamento in corso
Riavvia l'istanza ancora una volta per assicurarti che tutte le nuove impostazioni vengano applicate. Il sistema SQL Server è configurato ed è tutto pronto per creare i tuoi database e iniziare a testare carichi di lavoro specifici. Consulta la guida alle best practice per SQL Server per ulteriori informazioni su attività operative, altre considerazioni sulle prestazioni e funzionalità di Enterprise Edition.
Esegui la pulizia
Al termine del tutorial, puoi eseguire la pulizia delle risorse che hai creato in modo che smettano di utilizzare la quota e non incorrano in addebiti. Le sezioni seguenti descrivono come eliminare o disattivare queste risorse.
Elimina il progetto
Il modo più semplice per eliminare la fatturazione è eliminare il progetto che hai creato per il tutorial.
Per eliminare il progetto:
- Nella console Google Cloud, vai alla pagina Gestisci risorse.
- Nell'elenco dei progetti, seleziona il progetto che vuoi eliminare, quindi fai clic su Elimina.
- Nella finestra di dialogo, digita l'ID del progetto e fai clic su Chiudi per eliminare il progetto.
Eliminazione di istanze
Per eliminare un'istanza di Compute Engine:
- Nella console Google Cloud, vai alla pagina Istanze VM.
- Seleziona la casella di controllo per l'istanza che vuoi eliminare.
- Per eliminare l'istanza, fai clic su Altre azioni, quindi su Elimina e segui le istruzioni.
Eliminazione dei dischi permanenti
Per eliminare il Persistent Disk:
Nella console Google Cloud, vai alla pagina Dischi.
Seleziona la casella di controllo accanto al nome del disco da eliminare.
Fai clic sul pulsante Elimina nella parte superiore della pagina.
Passaggi successivi
- Esegui il test del caricamento dell'istanza SQL Server.
- Consulta la guida alle best practice per SQL Server.
- Esplora le architetture di riferimento, i diagrammi e le best practice su Google Cloud. Dai un'occhiata al nostro Cloud Architecture Center.