Créer une instance SQL Server hautes performances

Ce tutoriel montre comment créer une instance de VM Compute Engine exécutant SQL Server optimisée pour la performance. Ce tutoriel vous explique comment créer l'instance, puis configurer SQL Server pour des performances optimales sur Google Cloud. Vous découvrirez les différentes options de configuration disponibles pour vous aider à ajuster les performances du système.

Ce tutoriel utilise SQL Server Standard Edition 2022. De ce fait, certaines des options de configuration présentées dans ce guide ne sont pas nécessairement adaptées à tous les utilisateurs, et certaines options ne fourniront pas nécessairement de gains de performances notables pour toutes les charges de travail.

Objectifs

  • Configurer l'instance et les disques Compute Engine
  • Configurer le système d'exploitation Windows
  • Configurer SQL Server

Coûts

Ce tutoriel fait appel à des composants facturables de Google Cloud, par exemple :

  • Instance Compute Engine à haute capacité de mémoire
  • Stockage Compute Engine sur disque persistant SSD
  • Stockage Compute Engine sur disque SSD local
  • Image pré-configurée de SQL Server Standard

Le simulateur de coût peut générer une estimation des coûts en fonction de votre utilisation prévue. Le lien fourni présente le coût estimé pour les produits utilisés dans ce tutoriel, qui peut représenter plus de 4 dollars américains par heure et plus de 3 000 dollars par mois. Les nouveaux utilisateurs de Google Cloud peuvent bénéficier d'un essai gratuit.

Avant de commencer

  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.

Créer la VM Compute Engine avec des disques

Pour créer une instance SQL Server hautes performances, vous devez d'abord créer une instance de VM avec SQL Server et deux disques persistants.

Considérations sur les disques persistants

Pour sélectionner le type de disques persistants pour votre VM, examinez les considérations suivantes :

  • Un disque SSD local fournit un emplacement hautes performances pour tempdb et le fichier d'échange Windows.

    Vous devez tenir compte de certains points importants lorsque vous utilisez un disque SSD local. Lorsque vous arrêtez votre instance à partir de Windows ou que vous la réinitialisez à l'aide de l'API, le disque SSD local est supprimé. Cette action entraîne l'impossibilité d'amorcer l'instance. Pour redémarrer la machine, vous devez dissocier vos disques persistants, créer une instance contenant ces disques, puis définir un nouveau disque SSD local. Après le démarrage, vous devez également formater le nouveau disque et redémarrer. Par conséquent, vous devez éviter de stocker des données critiques de façon permanente sur un disque SSD local, ou de mettre l'instance hors tension, sauf si vous êtes prêt à la reconstruire.

  • Un disque persistant SSD fournit un stockage hautes performances pour les fichiers de base de données.

    Les performances des disques persistants SSD sont basées sur un calcul utilisant le nombre de processeurs et la taille du disque. Avec 32 processeurs virtuels et un disque de 1 To, les performances atteignent 40 000 opérations de lecture par seconde et 30 000 opérations d'écriture par seconde (ops). En régime continu, le débit total en termes de lectures et d'écritures est, respectivement, de 800 Mo/s et 400 Mo/s. Ces mesures représentent la somme de tous les disques persistants associés à la machine virtuelle, y compris le lecteur C:\. C'est pourquoi il est recommandé de créer un disque SSD local visant à déporter la charge de toutes les IOPS requises par le fichier de pagination, tempdb, les données de préproduction et les sauvegardes.

Pour en savoir plus sur les performances des disques, consultez la section Configurer des disques pour répondre aux exigences de performances.

Créer une VM Compute Engine avec des disques

Pour créer une VM Windows Server 2022 sur laquelle SQL Server 2022 Standard est préinstallé, procédez comme suit :

  1. Accédez à la page Créer une instance dans Google Cloud Console.

    Accéder à la page Créer une instance

  2. Dans le champ Nom, saisissez ms-sql-server.

  3. Dans la section Configuration de la machine, sélectionnez Usage général, puis procédez comme suit :

    1. Dans la liste Série, cliquez sur N2.
    2. Dans la liste Type de machine, cliquez sur n2-highmem-16 (16 processeurs virtuels, 128 Go de mémoire).

  4. Dans la section Disque de démarrage, cliquez sur Modifier, puis procédez comme suit :

    1. Dans l'onglet Images publiques, cliquez sur la liste Système d'exploitation, puis sélectionnez SQL Server sur Windows Server.
    2. Dans la liste Version, sélectionnez SQL Server 2022 Standard sur Windows Server 2022 Datacenter.
    3. Dans la liste Type de disque de démarrage, cliquez sur Disque persistant standard.
    4. Dans le champ Taille (Go), définissez la taille du disque de démarrage sur 50 Go.
    5. Pour enregistrer la configuration du disque de démarrage, cliquez sur Sélectionner.

  5. Développez la section Options avancées et procédez comme suit :

    1. Développez la section Disques.

    2. Pour créer des disques locaux, cliquez sur Ajouter un disque SSD local, puis procédez comme suit :

      1. Dans la liste Interface, sélectionnez le protocole répondant aux exigences de performances de votre système.
      2. Dans la liste Capacité du disque, sélectionnez une capacité de disque compatible avec la taille prévue des fichiers tempdb.
      3. Pour terminer la création de ce disque, cliquez sur Enregistrer.

    3. Pour créer des disques supplémentaires, cliquez sur Ajouter des disques.

      1. Laissez le champ Nom inchangé.
      2. Dans la liste Type de source de disque, sélectionnez Disque vide.
      3. Dans la liste Type de disque, sélectionnez Disque persistant SSD.
      4. Dans le champ Taille, saisissez la taille du disque pouvant accueillir la base de données.
      5. Pour terminer la création du deuxième disque, cliquez sur Enregistrer.

  6. Pour créer la VM, cliquez sur Créer.

Configurer Windows

Maintenant que vous disposez d'une instance de travail exécutant SQL Server, connectez-vous à votre instance et configurez le système d'exploitation Windows. Vous apprendrez à configurer SQL Server un peu plus loin dans une prochaine section.

Se connecter à votre instance

  1. Dans Google Cloud Console, accédez à la page Instances de VM.

    Accéder à la page "Instances de VM"

  2. Dans la colonne Nom, cliquez sur le nom de votre instance, ms-sql-server.

  3. En haut de la page de détails de l'instance, cliquez sur le bouton Définir un mot de passe Windows.

  4. Spécifiez un nom d'utilisateur.

  5. Cliquez sur Définir afin de générer un nouveau mot de passe pour cette instance Windows.

  6. Notez le nom d'utilisateur et le mot de passe pour pouvoir vous connecter à l'instance.

  7. Connectez-vous à votre instance via RDP.

Configurer les volumes de disque

Créez et formatez les volumes :

  1. Dans le menu Démarrer, recherchez "Gestion de l'ordinateur", puis ouvrez la boîte de dialogue correspondante.
  2. Dans la section Stockage, sélectionnez Gestion du disque.
  3. Lorsque vous êtes invité à initialiser les disques, acceptez les sélections par défaut et cliquez sur OK.

  4. Créez une partition pour les disques SSD locaux :

    Pour localiser un disque SSD local, effectuez un clic droit sur celui-ci, puis sélectionnez Propriétés. Le nom du disque SSD local est Google EphemeralDisk pour une interface SCSI ou nvme_card pour une interface NVMe. Les disques SSD locaux et les disques SSD persistants sont marqués comme ayant des partitions Unallocated.

    1. Si la VM ne contient qu'un seul disque SSD local, procédez comme suit :

      1. Sous la liste des disques, effectuez un clic droit sur le disque SSD local de 374,98 Go, puis sélectionnez Nouveau volume simple.
      2. Sur l'écran d'accueil, cliquez sur Suivant pour démarrer l'assistant de volume de disque.
      3. À l'étape Spécifier la taille du volume, conservez la valeur par défaut du volume, puis cliquez sur Suivant pour continuer.
      4. À l'étape Attribuer une lettre de lecteur ou un chemin d'accès, choisissez la lettre P: comme lettre de lecteur, puis cliquez sur Suivant pour continuer.

      5. À l'étape Formater le volume, définissez la Taille d'unité d'allocation à 8192 et saisissez "pagefile" (fichier d'échange) comme Nom de volume. Cliquez sur Suivant pour continuer.

        Assistant Nouveau volume

      6. Cliquez sur Terminer pour finaliser le configuration de l'assistant de volume de disque.

    2. Si la VM contient plusieurs disques SSD locaux, procédez comme suit :

      1. Sous la liste des lecteurs de disques, effectuez un clic droit sur le premier disque SSD local de 374,98 Go, puis sélectionnez Nouveau volume agrégé par bandes.
      2. Sur l'écran d'accueil, cliquez sur Suivant pour démarrer l'assistant de volume de disque.

      3. À l'étape Sélectionner des disques, ajoutez tous les disques disponibles avec une taille de 383,982 Mo à la section "Sélection". Cliquez sur Suivant pour continuer.

        Ajouter des disques agrégés par bandes

      4. À l'étape Attribuer une lettre de lecteur ou un chemin d'accès, choisissez la lettre P: comme lettre de lecteur, puis cliquez sur Suivant pour continuer.

      5. À l'étape Formater le volume, définissez la Taille d'unité d'allocation à 8192 et saisissez "pagefile" (fichier d'échange) comme Nom de volume. Cliquez sur Suivant pour continuer.

        Assistant Nouveau volume

      6. Cliquez sur Terminer pour finaliser le configuration de l'assistant de volume de disque.

  5. Répétez les étapes précédentes pour créer un volume simple pour le disque SSD, en apportant les trois modifications suivantes :

    • Choisissez D: comme lettre de lecteur.

    • Définissez le champ Allocation unit size (Taille d'unité d'allocation) sur 64k.

      Pour en savoir plus sur la sélection d'une taille d'unité d'allocation, consultez la page Bonnes pratiques pour les instances SQL Server.

    • Saisissez sqldata dans le champ Volume label (Nom du volume).

Déplacer le fichier d'échange de Windows

Maintenant que les nouveaux volumes sont créés et installés, déplacez le fichier d'échange de Windows vers le disque SSD local. Cela libère des IOPS de disque persistant et améliore les temps d'accès à votre mémoire virtuelle.

  1. Dans le menu Démarrer, recherchez Afficher les paramètres système avancés, puis ouvrez la boîte de dialogue correspondante.
  2. Cliquez sur l'onglet Avancé et, dans la section Performances, cliquez sur Paramètres.
  3. Dans la section Mémoire virtuelle, cliquez sur le bouton Modifier.
  4. Décochez la case Gestion automatique du fichier d'échange pour les lecteurs. Le système doit avoir déjà configuré votre fichier d'échange sur le lecteur C:\ et vous devez le déplacer.
  5. Cliquez sur C:, puis sur la case d'option Aucun fichier d'échange.
  6. Cliquez sur le bouton Définir.
  7. Pour créer le fichier d'échange, cliquez sur le lecteur P:, puis activez la case d'option Taille gérée par le système.
  8. Cliquez sur le bouton Définir.

  9. Cliquez trois fois sur OK pour quitter les propriétés système avancées.

    L'assistance Microsoft a publié des conseils supplémentaires pour les réglages de mémoire virtuelle.

Définir le profil de gestion d'alimentation

Définissez le profil de gestion d'alimentation sur High-Performance au lieu de Balanced.

  1. Dans le menu Démarrer, recherchez "Choisir un mode de gestion d'alimentation", puis ouvrez la boîte de dialogue correspondante.
  2. Sélectionnez la case d'option Hautes performances.
  3. Quittez la boîte de dialogue.

Configurer SQL Server

Utilisez SQL Server Management Studio pour réaliser l'essentiel des tâches d'administration. Les images préconfigurées pour SQL Server sont livrées avec Management Studio déjà installé. Lancez Management Studio, puis cliquez sur Connexion pour vous connecter à la base de données par défaut.

Déplacer les données et les fichiers journaux

L'image préconfigurée pour SQL Server est fournie avec tout le nécessaire installé sur le lecteur C:\, y compris les bases de données système. Pour optimiser la configuration, déplacez ces fichiers vers le lecteur D:\ que vous venez de créer. Rappelez-vous également que toute nouvelle base de données doit dorénavant être créée sur le lecteur D:\. Étant donné que vous utilisez un SSD, vous n'avez pas besoin de stocker les fichiers de données et les fichiers journaux sur des partitions de disque distinctes.

Il existe deux méthodes pour déplacer l'installation vers le disque secondaire : utiliser le programme d'installation ou déplacer les fichiers manuellement.

Utiliser le programme d'installation

Pour utiliser le programme d'installation, exécutez c:\setup.exe, puis sélectionnez un nouveau chemin d'installation sur votre disque secondaire.

Déplacer les fichiers manuellement

Déplacez les bases de données système et configurez SQL Server pour qu'il enregistre les données et les fichiers journaux sur le même volume :

  1. Créez un dossier nommé D:\SQLData.
  2. Ouvrez une fenêtre de commande.

  3. Entrez la commande suivante pour accorder un accès complet à NT Service\MSSQLSERVER :

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

  4. Utilisez Management Studio ainsi que les guides suivants pour déplacer vos bases de données système et modifier les emplacements de fichiers par défaut pour les nouvelles bases de données.

  5. Si vous prévoyez d'utiliser les fonctionnalités de Serveur de rapports, déplacez également les fichiers ReportServer et ReportServerTempDB.

Après avoir déplacé les fichiers de base de données de configuration principale et redémarré, vous devez configurer le système pour qu'il pointe vers le nouvel emplacement du modèle et des bases de données MSDB. Voici un script auxiliaire à exécuter dans 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' )

Après avoir exécuté ces commandes :

  1. Utilisez le composant logiciel enfichable services.msc pour arrêter le service de base de données SQL Server.
  2. Utilisez l'explorateur de fichiers Windows pour déplacer les fichiers physiques du lecteur C:\ où se trouve la base de données master vers le répertoire D:\SQLData.
  3. Démarrez le service de base de données SQL Server.

Définir les autorisations système

Après avoir déplacé les bases de données système, modifiez certains paramètres supplémentaires, en commençant par les autorisations du compte utilisateur Windows créé pour exécuter votre processus SQL Server, dénommé NT Service\MSSQLSERVER.

Attribuer l'autorisation Lock Pages in Memory

L'autorisation de stratégie de groupe Lock Pages in Memory empêche Windows de déplacer les pages de la mémoire physique vers la mémoire virtuelle. Afin de maintenir la disponibilité et l'organisation de la mémoire physique, Windows essaie de déplacer les pages anciennes, rarement modifiées, vers le fichier d'échange de la mémoire virtuelle sur le disque.

SQL Server stocke en mémoire des informations importantes telles que la structure des tables, les plans d'exécution et les requêtes mises en cache. Certaines de ces informations changent rarement et deviennent donc une cible de choix pour le fichier d'échange. Si ces informations sont déplacées vers le fichier de pagination, les performances de SQL Server peuvent se dégrader. Le fait d'accorder au compte de service de SQL Server l'autorisation de stratégie de groupe Lock Pages in Memory empêche ces échanges en mémoire.

Procédez comme suit :

  1. Cliquez sur Démarrer, puis recherchez Modifier la stratégie de groupe pour ouvrir la console.
  2. Développez Stratégie Ordinateur local > Configuration ordinateur > Paramètres Windows > Paramètres de sécurité > Stratégies locales > Attribution des droits utilisateur.
  3. Recherchez puis double-cliquez sur Verrouiller les pages en mémoire.
  4. Cliquez sur Ajouter un utilisateur ou un groupe.
  5. Recherchez "NT Service\MSSQLSERVER".
  6. Si vous voyez plusieurs noms correspondants, double-cliquez sur le nom MSSQLSERVER.
  7. Cliquez deux fois sur OK.
  8. Gardez la console de l'Éditeur de stratégie de groupe ouverte.

Verrouiller les pages

Attribuer l'autorisation Perform volume maintenance tasks

Par défaut, lorsqu'une application demande à Windows une tranche d'espace disque, le système d'exploitation recherche un fragment d'espace disque de taille appropriée, puis met à zéro la totalité de ce fragment avant de le renvoyer à l'application. Étant donné que SQL Server est capable de faire croître ses fichiers et de remplir l'espace disque disponible, ce comportement n’est pas optimal.

Il existe une API distincte pour l'allocation d'espace disque à une application, que l'on désigne souvent par le nom initialisation instantanée des fichiers. Malheureusement, ce paramètre fonctionne uniquement pour les fichiers de données, mais dans l'une des sections à venir, vous en apprendrez davantage sur la croissance des fichiers journaux. L'initialisation instantanée des fichiers nécessite que le compte de service exécutant le processus SQL Server dispose d'une autre autorisation de stratégie de groupe dénommée Perform volume maintenance tasks.

  1. Dans l'Éditeur de stratégie de groupe, recherchez "Effectuer les tâches de maintenance du volume".
  2. Ajoutez le compte "NT Service\MSSQLSERVER" comme vous l'avez déjà fait à la section précédente.
  3. Redémarrez le processus SQL Server pour activer les deux réglages.

Configurer tempdb

Il était auparavant recommandé d'optimiser l'utilisation du processeur SQL Server en créant un fichier tempdb par processeur. Toutefois, comme le nombre de processeurs a augmenté avec le temps, suivre cette directive peut aujourd'hui entraîner une baisse des performances. Un bon point de départ consiste à utiliser quatre fichiers tempdb. L'évaluation des performances de votre système peut, dans de rares cas, vous conduire à augmenter progressivement le nombre de fichiers tempdb jusqu'à un maximum de huit.

Vous pouvez exécuter un script Transact-SQL (T-SQL) au sein de SQL Server Management Studio pour déplacer les fichiers tempdb vers un dossier du lecteur "p:".

  1. Créez le répertoire p:\tempdb.

  2. Accordez un accès de sécurité complet au compte d'utilisateur "NT Service\MSSQLSERVER" :

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

  3. Exécutez le script suivant dans SQL Server Management Studio afin de déplacer le fichier de données tempdb ainsi que le fichier journal :

    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. Redémarrez SQL Server.

  5. Exécutez le script suivant pour modifier la taille des fichiers et créer trois fichiers de données supplémentaires pour le nouveau dossier 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

    Si vous utilisez SQL Server 2016, vous devez supprimer trois fichiers tempdb supplémentaires après avoir suivi la procédure précédente :

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

  6. Redémarrez SQL Server de nouveau.

  7. Supprimez les fichiers model, MSDB, master et tempdb de l'emplacement d'origine sur le lecteur C:\.

Vous avez correctement déplacé vos fichiers tempdb vers la partition SSD locale. Ce déplacement comporte certains risques, que nous avons mentionnés précédemment. Si toutefois, pour une raison quelconque, les fichiers sont perdus, SQL Server peut reconstruire les fichiers tempdb. Déplacer tempdb vous offre les performances supplémentaires du disque SSD local et réduit les IOPS utilisées sur vos disques persistants.

Configurer max degree of parallelism

La configuration par défaut recommandée pour max degree of parallelism consiste à le faire correspondre au nombre de processeurs sur le serveur. Cependant, il existe un seuil au-delà duquel l'exécution d'une requête en 16 ou 32 blocs parallèles et la fusion des résultats est beaucoup plus lente que son exécution en un seul processus. Si vous utilisez une instance à 16 ou 32 cœurs, vous pouvez définir la valeur max degree of parallelism sur 8 à l'aide du code T-SQL suivant :

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

Configurer max server memory

Ce paramètre est défini par défaut sur un nombre très élevé, mais vous devez le régler sur le nombre de mégaoctets de mémoire RAM physique disponible, moins quelques gigaoctets réservés au système d'exploitation et à la gestion des débordements. L'exemple T-SQL suivant ajuste max server memory à 100 Go. Modifiez-le pour ajuster cette valeur en fonction de votre instance. Pour en savoir plus, consultez la page Options de configuration de la mémoire du serveur.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
exec sp_configure 'max server memory', 100000
GO
RECONFIGURE WITH OVERRIDE
GO

Pour terminer

Redémarrez l'instance une dernière fois pour vous assurer que tous les nouveaux paramètres prennent effet. Votre système SQL Server est configuré et vous êtes prêt à créer vos propres bases de données et à tester vos charges de travail spécifiques. Consultez le Guide des bonnes pratiques SQL Server pour en savoir plus sur les activités opérationnelles, les autres considérations relatives aux performances et les fonctionnalités de l'édition Enterprise.

Effectuer un nettoyage

Une fois le tutoriel terminé, vous pouvez procéder au nettoyage des ressources que vous avez créées afin qu'elles ne soient plus comptabilisées dans votre quota et qu'elles ne vous soient plus facturées. Dans les sections suivantes, nous allons voir comment supprimer ou désactiver ces ressources.

Supprimer le projet

Le moyen le plus simple d'empêcher la facturation est de supprimer le projet que vous avez créé pour ce tutoriel.

Pour supprimer le projet :

  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.

Supprimer des instances

Pour supprimer une instance Compute Engine, procédez comme suit :

  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.

Supprimer les disques persistants

Pour supprimer le disque persistant, procédez comme suit :

  1. Dans la console Google Cloud, accédez à la page Disques.

    Accéder à la page Disques

  2. Cochez la case située à côté du nom du disque à supprimer.

  3. Cliquez sur le bouton Supprimer en haut de la page.

Étapes suivantes