Creazione di un'istanza SQL Server ad alte prestazioni

Questo tutorial mostra come creare un'istanza VM di Compute Engine in esecuzione SQL Server ottimizzato per le prestazioni. Questo tutorial ti guida nella creando l'istanza e configurando SQL Server per ottenere prestazioni ottimali in Google Cloud. Imparerai a conoscere una serie di opzioni di configurazione per aiutarti a regolare le prestazioni del sistema.

Questo tutorial utilizza SQL Server Standard Edition 2022, pertanto non tutte le opzioni di configurazione presentate in questa guida sono adatte a tutti e non tutte offrono vantaggi di prestazioni evidenti per ogni carico di lavoro.

Obiettivi

  • Configurazione dell'istanza e dei dischi Compute Engine in corso.
  • Configurazione del sistema operativo Windows.
  • Configurazione di SQL Server in corso...

Costi

Questo tutorial utilizza componenti fatturabili di Google Cloud, tra cui:

  • Istanza con memoria elevata di Compute Engine
  • Archiviazione su disco permanente SSD di Compute Engine
  • Spazio di archiviazione su disco SSD locale di Compute Engine
  • Immagine preconfigurata SQL Server Standard

Il Calcolatore prezzi può generare un costo stimata in base all'utilizzo previsto. Il link fornito mostra il costo stima per i prodotti usati in questo tutorial, che può costare oltre 4 dollari (USA) all'ora e oltre 3000 dollari al mese. I nuovi utenti di Google Cloud potrebbero essere idonei per una prova gratuita.

Prima di iniziare

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  3. Make sure that billing is enabled for your Google Cloud project.

  4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  5. Make sure that billing is enabled for your Google Cloud project.

Creazione della VM 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 il le seguenti considerazioni:

  • Un SSD locale offre una posizione ad alte prestazioni per tempdb e il file di pagina di Windows.

    Ci sono alcune considerazioni importanti da notare quando si usa 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. A ripetere l'esecuzione della macchina, devi scollegare l'account i dischi permanenti, creare una nuova istanza con questi dischi e definire un nuovo SSD locale. Dopo l'avvio, dovrai anche formattare il nuovo disco e riavviare. Di conseguenza, non dovresti archiviare definitivamente dati critici su un SSD locale. se non sei pronto a ricrearla.

  • Un disco permanente SSD offre 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, picchi di prestazioni a 40.000 operazioni di lettura al secondo (operazioni) e 30.000 operazioni di scrittura operazioni. La velocità effettiva totale sostenuta per letture e scritture è di 800 MB al secondo e 400 MB al secondo. Queste misurazioni rappresentano una somma di tutte le dischi permanenti collegati alla macchina virtuale, inclusa l'unità C:\. Questo è il motivo per cui dovresti creare un SSD locale per non sovraccaricare tutte le IOPS necessarie 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 di prestazioni.

Creazione di una VM di Compute Engine con dischi

Per creare una VM con SQL Server 2022 Standard preinstallato su Windows Server 2022, segui questi passaggi:

  1. Nella console Google Cloud, vai alla pagina Crea un'istanza.

    Vai a Crea un'istanza

  2. In Nome, inserisci ms-sql-server.

  3. Nella sezione Configurazione macchina, seleziona Uso generico e quindi procedi nel seguente modo:

    1. Nell'elenco Serie, fai clic su N2.
    2. Nell'elenco Tipo di macchina, fai clic su n2-highmem-16 (16 vCPU, 128 GB di memoria).

  4. Nella sezione Disco di avvio, fai clic su Cambia, quindi segui questi passaggi:

    1. Nella scheda Immagini pubbliche, fai clic sull'elenco Sistema operativo e poi seleziona SQL Server su Windows Server.
    2. Nell'elenco Versione, fai clic su SQL Server 2022 Standard su Windows Data center server 2022.
    3. Nell'elenco Tipo di disco di avvio, fai clic su Disco permanente standard.
    4. Nel campo Dimensioni (GB), imposta la dimensione del disco di avvio su 50 GB.
    5. Per salvare la configurazione del disco di avvio, fai clic su Seleziona.

  5. Espandi la sezione Opzioni avanzate e procedi nel seguente modo:

    1. Espandi la sezione Dischi.

    2. Per creare dischi locali, fai clic su Aggiungi SSD locale, quindi esegui le seguenti:

      1. Nell'elenco Interfaccia, seleziona il protocollo che soddisfa i requisiti di prestazioni del sistema.
      2. Nell'elenco Capacità disco, seleziona una capacità del disco che supporti la dimensione prevista di tempdb file.
      3. Per completare la creazione del disco, fai clic su Salva.

    3. Per creare altri dischi, fai clic su Aggiungi nuovo disco.

      1. Non modificare il campo Name (Nome).
      2. Nell'elenco Tipo di disco di origine, seleziona Disco vuoto.
      3. Nell'elenco Tipo di disco, seleziona Disco permanente SSD.
      4. Nel campo Dimensione, inserisci la dimensione del disco che può contenere le dimensioni del database.
      5. Per completare la creazione del secondo disco, fai clic su Salva.

  6. Per creare la VM, fai clic su Crea.

Configurazione di Windows

Ora che hai un'istanza funzionante che esegue SQL Server, connettiti istanza e configurare il sistema operativo Windows. In seguito, imparerai a in una prossima sezione.

Connettiti all'istanza

  1. Nella console Google Cloud, vai alla pagina Istanze VM.

    Vai a Istanze VM

  2. Nella colonna Nome, fai clic sul nome dell'istanza, ms-sql-server.

  3. Nella parte superiore della pagina dei dettagli dell'istanza, fai clic sull'icona Pulsante Imposta password di Windows.

  4. Specifica un nome utente.

  5. Fai clic su Imposta per generare una nuova password per questa istanza Windows.

  6. Prendi nota del nome utente e della password per accedere all'istanza.

  7. Connettiti all'istanza utilizzando RDP.

Configurazione dei volumi dei dischi

Crea e formatta i volumi:

  1. Nel menu Start, cerca "Gestione computer". e aprilo.
  2. Nella sezione Archiviazione, seleziona Gestione disco.
  3. Quando viene richiesto di inizializzare i dischi, accetta le selezioni predefinite e fai clic su OK.

  4. Crea una 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 o nvme_card per un'interfaccia NVMe. Sia gli SSD locali che gli SSD permanenti sono contrassegnati come partizioni di Unallocated.

    1. Se la VM contiene solo un'unità SSD locale, segui questi passaggi:

      1. Nell'elenco delle unità disco, fai clic con il tasto destro del mouse sul disco SSD locale da 374,98 GB e seleziona Nuovo volume semplice.
      2. Nella schermata di benvenuto, fai clic su Avanti per avviare la procedura guidata per i volumi del disco.
      3. Nel passaggio Specifica la dimensione del volume, lascia il valore predefinito per le dimensioni del volume e fai clic su Avanti per continuare.
      4. Nel passaggio Assegna lettera o percorso di unità, scegli P: come lettera dell'unità e fai clic su Avanti per continuare.

      5. Nel passaggio Formatta il volume, imposta Dimensione unità di allocazione su 8192 e inserisci "file di pagina" per l'Etichetta del volume. Fai clic su Avanti per continuare.

        Procedura guidata nuovo volume

      6. Fai clic su Fine per completare la procedura guidata del volume del disco.

    2. Se la VM contiene più unità SSD locali, segui questi passaggi:

      1. Nell'elenco dei dischi, fai clic con il tasto destro del mouse sul primo disco SSD locale da 374,98 GB e seleziona Nuovo volume a strisce.
      2. Nella schermata di benvenuto, fai clic su Avanti per avviare la procedura guidata per i volumi del disco.

      3. 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.

        Aggiungi dischi a strisce

      4. Nel passaggio Assegna lettera o percorso di unità, scegli P: come lettera dell'unità e fai clic su Avanti per continuare.

      5. Nel passaggio Formatta il volume, imposta Dimensione unità di allocazione su 8192 e inserisci "file di pagina" per l'Etichetta del volume. Fai clic su Avanti per continuare.

        Procedura guidata nuovo volume

      6. Fai clic su Fine per completare la procedura guidata del volume del disco.

  5. Ripeti i passaggi precedenti per creare un New Simple Volume per il disco SSD, con le seguenti tre modifiche:

    • Scegli D per la lettera dell'unità.

    • Imposta Allocation unit size (Dimensioni dell'unità di allocazione) su 64k.

      Per maggiori dettagli sulla selezione delle dimensioni dell'unità di allocazione, consulta Best practice per le istanze SQL Server.

    • Inserisci sqldata per Etichetta del 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 su SSD locale, liberando IOPS del disco permanente e migliorando l'accesso della tua memoria virtuale.

  1. Dal menu Start, cerca Visualizza impostazioni di sistema avanzate e quindi apri la finestra di dialogo.
  2. Fai clic sulla scheda Avanzate e nella sezione Rendimento, fai clic su Impostazioni.
  3. Nella sezione Memoria virtuale, fai clic sul pulsante Cambia.
  4. Deseleziona la casella di controllo Gestisci automaticamente le dimensioni del file di paging per tutte le unità. La aver configurato già il file di paging sull'unità C:\ e necessario spostarlo.
  5. Fai clic su C: e poi sul pulsante di opzione Nessun file di paging.
  6. Fai clic sul pulsante Imposta.
  7. Per creare il nuovo file di paging, fai clic sull'unità P:, quindi sul pulsante Pulsante di opzione Dimensioni gestite dal sistema.
  8. Fai clic sul pulsante Imposta.

  9. Fai clic su OK tre volte per uscire dalle proprietà di sistema avanzate.

    L'assistenza Microsoft ha pubblicato ulteriori suggerimenti per la memoria virtuale impostazioni.

Impostare il profilo di potenza

Imposta il profilo di potenza su High-Performance anziché su Balanced.

  1. Nel menu Start, cerca "Scegli un piano di risparmio energetico" e apri le opzioni di risparmio energetico.
  2. Seleziona il pulsante di opzione Prestazioni elevate.
  3. Esci dalla finestra di dialogo.

Configurazione di SQL Server

Utilizza SQL Server Management Studio per eseguire la maggior parte delle attività amministrative. Le immagini preconfigurate per SQL Server sono fornite con Management Studio già installato. Avvia Management Studio e fai clic su Connetti a e connetterti al database predefinito.

Spostamento dei file di dati e 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: sposterai i file nel nuovo Drive di D:\ che hai creato. Ricorda di creare anche tutti i nuovi database sull'unità D:\. Poiché stai utilizzando un'unità SSD, non è necessario archiviare i file di dati e di log su un disco separato partizioni di Compute Engine.

Esistono due modi per spostare l'installazione sul disco secondario: utilizzando il metodo programma di installazione o di spostare 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 sul disco secondario.

Spostare i file manualmente

Sposta i database di sistema e configura SQL Server in modo da salvare i dati e i file di log sullo stesso volume:

  1. Crea una nuova cartella denominata D:\SQLData.
  2. Apri una finestra dei comandi.

  3. Inserisci il comando seguente per concedere l'accesso completo a NT Service\MSSQLSERVER:

    icacls D:\SQLData /Grant "NT Service\MSSQLServer:(OI)(CI)F"

  4. Utilizza Management Studio e le seguenti guide per spostare i database di sistema e modificare le posizioni dei file predefinite per i nuovi database.

  5. Se prevedi di utilizzare le funzionalità di Report Server, sposta il ReportServer e ReportServerTempDB.

di Gemini Advanced.

Dopo aver spostato i file del database di configurazione principale e aver riavviato il dispositivo, devi configurare il sistema in puntare alla nuova posizione per il modello e i database MSDB. Ecco un aiutante script 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:

  1. Utilizza lo snapshot services.msc per arrestare il servizio di database SQL Server.
  2. Utilizza Esplora file di Windows per spostare i file fisici dall'unità C:\ in cui si trovava il database master nella directory D:\SQLData.
  3. Avvia il servizio di database SQL Server.

Impostazione delle autorizzazioni di sistema

Dopo aver spostato i database di sistema, modifica alcune impostazioni aggiuntive, iniziando con le autorizzazioni per l'account utente Windows creato per eseguire SQL Processo server denominato NT Service\MSSQLSERVER.

Concessione dell'autorizzazione Lock Pages in Memory

L'autorizzazione Lock Pages in Memory del criterio di gruppo impedisce a Windows di spostare le pagine nella memoria fisica nella memoria virtuale. Per mantenere l'attività fisica memoria libera e organizzata, Windows cerca di scambiare le pagine meno recenti il file di paging della memoria virtuale su disco.

SQL Server archivia in memoria informazioni importanti, come strutture di tabelle, piani di esecuzione e query memorizzate nella cache. Alcune di queste informazioni cambiano raramente, quindi diventa una destinazione per il file di paging. Se queste informazioni vengono spostate di paging file, le prestazioni di SQL Server possono peggiorare. La concessione dell'autorizzazione Lock Pages in Memory al criterio di gruppo per l'account di servizio SQL Server impedisce che ciò accada per effettuare lo scambio.

Segui questi passaggi:

  1. Fai clic su Avvia, quindi cerca Modifica Criteri di gruppo per aprire la console.
  2. Espandi Criteri relativi ai computer locali > Configurazione computer > Impostazioni di Windows > Impostazioni sicurezza > Norme locali > Assegnazione dei diritti utente.
  3. Cerca e fai doppio clic su Blocca pagine in memoria.
  4. Fai clic su Aggiungi utente o gruppo.
  5. Cerca "NT Service\MSSQLSERVER".
  6. Se vedi più nomi, fai doppio clic sul nome MSSQLSERVER.
  7. Fai clic su OK due volte.
  8. Tieni aperta la console dell'Editor Criteri di gruppo.

Blocca pagine

Concessione dell'autorizzazione Perform volume maintenance tasks

Per impostazione predefinita, quando un'applicazione richiede una porzione di spazio su disco da Windows, il sistema operativo individua un blocco di spazio su disco di dimensioni adeguate azzera l'intero blocco di disco prima di restituirlo all'applicazione. Poiché SQL Server è in grado di far crescere i file e riempire spazio su disco, non è ottimale.

Esiste un'API separata per l'allocazione di spazio su disco a un'applicazione, spesso indicata come inizializzazione istantanea dei file. Purtroppo questa impostazione funziona per i file di dati, ma in una prossima sezione parleremo dei file di log la crescita di Kubernetes. L'inizializzazione istantanea dei file richiede l'account di servizio che esegue SQL Processo del server per disporre di un'altra autorizzazione per i criteri di gruppo, denominata Perform volume maintenance tasks.

  1. Nell'Editor Criteri di gruppo, cerca "Esegui attività di manutenzione del volume".
  2. Aggiungi "NT Service\MSSQLSERVER" dell'account, come hai fatto nella sezione precedente.
  3. Riavvia il processo di SQL Server per attivare entrambe le impostazioni.

Configurazione di tempdb in corso…

Era una best practice per ottimizzare SQL Server Utilizzo della CPU creando un file tempdb per CPU. Tuttavia, poiché la CPU conteggia sono aumentate nel tempo, pertanto se si segue questa linea guida è possibile che il rendimento una diminuzione costante. Come punto di partenza, utilizza 4 file tempdb. Quando misuri le prestazioni del sistema, in rari casi potrebbe essere necessario aumentare numero di file tempdb fino a un massimo di 8.

Puoi eseguire uno script Transact-SQL (T-SQL) in SQL Server Management Studio per sposta i file tempdb in una cartella nel Drive "p:".

  1. Crea la directory p:\tempdb.

  2. Concedi l'accesso di sicurezza completo a "NT Service\MSSQLSERVER" account utente:

    icacls p:\tempdb /Grant "NT Service\MSSQLServer:(OI)(CI)F"

  3. Esegui lo script seguente in SQL Server Management Studio per spostare il file di dati e il file di log di 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

  4. Riavvia SQL Server.

  5. Esegui lo script seguente per modificare le dimensioni del 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, dopo aver eseguito i passaggi precedenti devi rimuovere altri 3 file tempdb:

    ALTER DATABASE [tempdb] REMOVE FILE temp2;
ALTER DATABASE [tempdb] REMOVE FILE temp3;
ALTER DATABASE [tempdb] REMOVE FILE temp4;

  6. Riavvia SQL Server di nuovo.

  7. Elimina i file model, MSDB, master e tempdb dalla posizione originale sul drive C:\.

Hai spostato correttamente i file tempdb nella partizione SSD locale. Questo una mossa comporta alcuni rischi, come già accennato, ma se viene persa per qualche motivo,SQL Server ricrea i file tempdb. Lo spostamento di tempdb ti offre aggiunge prestazioni dell'SSD locale e riduce il numero di IOPS utilizzate sulla dei dischi permanenti standard.

Impostazione di max degree of parallelism

L'impostazione predefinita consigliata per max degree of parallelism è abbinarla al numero di CPU sul server. Tuttavia, c'è un momento in cui l'esecuzione di un in 16 o 32 blocchi paralleli e unendo i risultati è molto più lento rispetto in un unico processo. Se stai utilizzando un'istanza a 16 o 32 core, puoi impostare il valore max degree of parallelism su 8 utilizzando il seguente comando 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

Impostazione di max server memory

Per impostazione predefinita, questo valore è molto elevato, ma ti consigliamo di impostarlo sul numero di megabyte di RAM fisica disponibile, meno un paio di gigabyte per il sistema operativo e il sovraccarico. Il seguente esempio di T-SQL regola max server memory in 100 GB. Modificalo per regolare il valore in modo che corrisponda alla tua istanza. Esamina il Documento sulle opzioni di configurazione del server di memoria del server per ulteriori informazioni.

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 richiedano effetto. Il sistema SQL Server è configurato e puoi creare e iniziare a testare i tuoi carichi di lavoro specifici. Esamina SQL Server Guida alle best practice per ulteriori informazioni sulle attività operative, altre considerazioni e funzionalità di Enterprise Edition.

Esegui la pulizia

Al termine del tutorial, puoi eseguire la pulizia delle risorse che hai creato in modo che smettono di usare la quota e comportano addebiti. Le sezioni seguenti descrivono come eliminare o disattivare queste risorse.

Elimina il progetto

Il modo più semplice per eliminare la fatturazione creato per il tutorial.

Per eliminare il progetto:

  1. In the Google Cloud console, go to the Manage resources page.

    Go to Manage resources

  2. In the project list, select the project that you want to delete, and then click Delete.
  3. In the dialog, type the project ID, and then click Shut down to delete the project.

Eliminazione di istanze

Per eliminare un'istanza di Compute Engine:

  1. In the Google Cloud console, go to the VM instances page.

    Go to VM instances

  2. Select the checkbox for the instance that you want to delete.
  3. To delete the instance, click More actions, click Delete, and then follow the instructions.

Eliminazione dei dischi permanenti in corso...

Per eliminare il Persistent Disk:

  1. Nella console Google Cloud, vai alla pagina Dischi.

    Vai a Dischi

  2. Seleziona la casella di controllo accanto al nome del disco che vuoi eliminare.

  3. Fai clic sul pulsante Elimina nella parte superiore della pagina.

Passaggi successivi