Bonnes pratiques pour les instances SQL Server

Cette page fournit des informations sur l'optimisation des instances de Google Compute Engine qui exécutent Microsoft SQL Server. Pour apprendre à configurer une instance SQL Server hautes performances, consultez ce tutoriel.

Configurer Windows

Cette section couvre les rubriques de configuration du système d'exploitation Microsoft Windows en vue d'optimiser les performances SQL Server, dans le cadre d'une exécution sur Compute Engine.

Configurer le pare-feu Windows

Bonne pratique : Utilisez le pare-feu avancé Windows Server et spécifiez les adresses IP de vos ordinateurs clients.

Le pare-feu avancé Windows est un composant de sécurité important de Windows Server. Lorsque vous configurez votre environnement SQL Server pour pouvoir vous connecter à la base de données à partir d'autres ordinateurs clients, configurez le pare-feu pour autoriser le trafic entrant :

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]

Lorsque vous utilisez cette règle de pare-feu, il est recommandé de spécifier l'adresse IP de vos machines clientes. Pour le paramètre remoteip, spécifiez à la place de [LOCAL_SUBNET] une liste d'adresses IP séparées par des virgules et sans espace. En outre, le chemin d'accès du paramètre program peut varier en fonction de la version de SQL Server que vous utilisez.

L'image de l'application SQL Server inclut une règle de pare-feu Windows SQL Server. Cette règle est illimitée, pensez donc à la désactiver avant que votre système n'entre en production.

Régler les connexions réseau

Bonne pratique : Utilisez les paramètres réseau par défaut du système d'exploitation.

Les paramètres réseau définis par défaut sur la plupart des systèmes d'exploitation sont configurés pour établir des connexions sur des petits ordinateurs, connectés à des réseaux relativement rapides. En général, ces paramètres sont suffisants. De plus, ces valeurs par défaut prudentes évitent au trafic réseau de surcharger le réseau et les ordinateurs connectés.

Sur Compute Engine, les instances de VM sont associées à un réseau conçu par Google, qui offre une capacité et des performances élevées. Les serveurs physiques qui exécutent les instances Compute Engine sont optimisés pour tirer parti de ces capacités réseau. Les pilotes de réseau virtuel dans vos instances sont également optimisés, garantissant l'adéquation des valeurs par défaut à la plupart des cas d'utilisation.

Installer un antivirus

Bonne pratique : Suivez les instructions de Microsoft concernant les logiciels antivirus.

Si vous exploitez Windows, vous devez utiliser un logiciel antivirus. Les logiciels malveillants et les virus représentent un risque important pour tout système connecté à un réseau. Les logiciels antivirus constituent une mesure simple pour atténuer ces risques, vous permettant ainsi de protéger vos données. Toutefois, si le logiciel antivirus n'est pas configuré correctement, il peut avoir un impact négatif sur les performances de votre base de données. Microsoft fournit des conseils pour vous aider à choisir votre logiciel antivirus.

Optimiser les performances et la stabilité

Cette section fournit des informations concernant l'optimisation des performances de SQL Server sur Compute Engine et les activités opérationnelles contribuant à préserver son bon fonctionnement.

Transférer des fichiers de données et des fichiers journaux vers un nouveau disque

Bonne pratique : Utilisez un disque persistant SSD dédié pour les fichiers journaux et les fichiers de données.

Par défaut, l'image préconfigurée pour SQL Server vient s'ajouter à tout ce qui est installé sur le disque persistant de démarrage, qui s'installe en tant que lecteur C:\. Pensez à associer un disque persistant SSD secondaire, et à y transférer les fichiers journaux et les fichiers de données.

Utiliser un disque SSD local pour améliorer les IOPS

Bonne pratique : Créez des instances SQL Server avec un ou plusieurs disques SSD locaux pour stocker les fichiers tempdb et les fichiers de pagination Windows.

En raison de sa nature éphémère, la technologie SSD locale n'est pas idéale pour vos bases de données critiques et vos fichiers importants. En revanche, les fichiers tempdb et les fichiers de pagination Windows sont des fichiers temporaires, ce qui les rend propices à un transfert vers un disque SSD local. Cette opération va décharger de vos disques persistants SSD un grand nombre d'opérations d'E/S. Pour en savoir plus sur la configuration à appliquer, cliquez ici.

Traitement de requête en parallèle

Bonne pratique : Définissez la propriété max degree of parallelism sur 8.

Par défaut, il est recommandé de faire correspondre la propriété max degree of parallelism au nombre de processeurs que compte le serveur. Toutefois, diviser une requête en 16 ou 32 fragments, en les exécutant sur des processeurs virtuels différents puis en les centralisant dans un seul résultat, peut parfois prendre beaucoup plus de temps que si un seul processeur virtuel exécutait la requête. Dans la pratique, 8 est une bonne valeur par défaut.

Bonne pratique : Surveillez les temps d'attente de CXPACKET et augmentez progressivement la propriété cost threshold for parallelism.

Ce réglage va de pair avec la propriété max degree of parallelism. Chaque unité représente une combinaison des charges de travail liées au processeur et aux E/S, qui sont nécessaires pour exécuter une requête avec un plan d'exécution en série, avant de pouvoir être envisagée pour un plan d'exécution en parallèle. La valeur par défaut est 5. Même si nous ne recommandons pas particulièrement de modifier la valeur par défaut, il est utile de la surveiller, et si nécessaire, de l'augmenter progressivement au cours des tests de charge, selon des incréments de 5. La présence de temps d'attente CXPACKET indique clairement qu'il faudrait peut-être augmenter cette valeur. Il s'agit là d'un bon point de départ, même si ces temps d'attente CXPACKET n'indiquent pas nécessairement que le paramètre doit être modifié.

Bonne pratique : Surveillez les différents types de temps d'attente et ajustez les paramètres globaux de traitement en parallèle, ou définissez-les au niveau de chaque base de données.

Les différentes bases de données peuvent avoir des besoins spécifiques en matière de traitement en parallèle. Vous pouvez configurer ces paramètres globalement, et définir la propriété Max DOP au niveau de chaque base de données. Vous devez analyser vos charges de travail uniques, surveiller les temps d'attente et ajuster les valeurs en conséquence.

Le site SQLSkills propose un guide utile sur les performances, qui couvre les statistiques d'attente au sein de la base de données. Consultez ce guide pour mieux comprendre les temps d'attente et apprendre à les atténuer.

Gérer les journaux de transactions

Bonne pratique : Surveillez la croissance du journal des transactions sur votre système. Il peut être judicieux de désactiver la croissance automatique et de définir votre fichier journal sur une taille fixe, qui va dépendre des volumes moyens cumulés chaque jour.

La croissance non gérée du journal des transactions représente l'une des sources les plus sous-estimées de perte de performance et de ralentissements intermittents. Si votre base de données est configurée pour utiliser le modèle de récupération Full, vous pouvez effectuer une restauration à tout moment, même si vos journaux de transactions se remplissent plus rapidement. Par défaut, lorsque le fichier journal des transactions est plein, SQL Server augmente sa taille en vue de l'écriture des transactions, et bloque toute activité sur la base de données jusqu'à ce que l'opération soit terminée. SQL Server augmente chaque fichier journal en fonction des paramètres Taille de fichier maximale et Croissance du fichier.

Lorsque le fichier a atteint sa taille maximale et qu'il ne peut plus augmenter, le système génère une erreur 9002 et définit la base de données en mode lecture seule. Si le fichier peut encore augmenter, SQL Server accroît la taille du fichier et annule l'espace vide. Le paramètre pour la Croissance de fichier est défini par défaut à 10 % de la taille actuelle du fichier journal. Ce n'est pas le paramètre par défaut idéal du point de vue des performances, car plus votre fichier augmente, plus la création d'espace vide est lente.

Bonne pratique : Planifiez des sauvegardes régulières du journal des transactions.

Indépendamment de la taille maximale et des paramètres de croissance, planifiez des sauvegardes régulières du journal des transactions, qui vont tronquer par défaut les anciennes entrées de journal et permettre au système de réutiliser l'espace fichier existant. Cette tâche de maintenance simple peut vous aider à éviter les baisses de performances lors des pics de trafic.

Optimiser les fichiers journaux virtuels

Bonne pratique : Surveillez la croissance du fichier journal virtuel et prenez les mesures nécessaires pour empêcher la fragmentation du fichier journal.

Le fichier journal des transactions physiques est segmenté en fichiers journaux virtuels. De nouveaux fichiers journaux virtuels sont créés chaque fois que la taille du fichier journal des transactions physiques doit être augmentée. Si vous ne désactivez pas la croissance automatique et que cette croissance survient trop souvent, trop de fichiers journaux virtuels seront créés. Cette activité peut entraîner une fragmentation du fichier journal semblable à la fragmentation du disque, avec potentiellement un effet négatif sur les performances.

SQL Server 2014 comprend un algorithme plus efficace permettant de déterminer le nombre de fichiers journaux virtuels à créer lors de la croissance automatique. En général, si la croissance est inférieure à 1/8 de la taille du fichier journal actuel, SQL Server crée un fichier journal virtuel dans ce nouveau segment. Auparavant, il aurait créé 8 fichiers journaux virtuels pour une croissance comprise entre 64 Mo et 1 Go, et 16 fichiers journaux virtuels pour une croissance supérieure à 1 Go. Vous pouvez utiliser le script T-SQL ci-dessous pour vérifier le nombre actuel de fichiers journaux virtuels dans votre base de données. Si celle-ci contient plusieurs milliers de fichiers, nous vous suggérons de réduire et de redimensionner manuellement votre fichier journal.

--Check VLFs substitute your database name below
USE 
DECLARE @vlf_count INT
DBCC LOGINFO
SET @vlf_count = @@ROWCOUNT
SELECT VLFs = @vlf_count

Pour en savoir plus sur les fichiers journaux virtuels, consultez le site de Brent Ozar.

Éviter la fragmentation d'index

Bonne pratique : Défragmentez régulièrement les index de vos tables les plus lourdement modifiées.

Les index de vos tables peuvent être fragmentés, ce qui peut nuire aux performances des requêtes utilisant ces index. Un calendrier de maintenance régulière doit inclure la réorganisation des index de vos tables les plus lourdement modifiées. Vous pouvez exécuter le script T-SQL suivant pour que votre base de données affiche les index et leur pourcentage de fragmentation. Vous pouvez voir dans les résultats de l'exemple que l'index PK_STOCK est fragmenté à 95 %. Dans l'instruction SELECT suivante, remplacez [YOUR_DB] par le nom de votre base de données :

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

Si vos index sont trop fragmentés, vous pouvez les réorganiser avec un simple script ALTER. Voici un exemple de script qui imprimera les instructions ALTER que vous pouvez exécuter pour chacun des index de vos tables.

SELECT
'ALTER INDEX ALL ON ' + table_name + ' REORGANIZE;
GO'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'YOUR_DB'

Choisissez parmi les résultats les tables présentant la fragmentation la plus élevée, puis exécutez ces instructions de manière incrémentielle. Nous vous suggérons d'intégrer ce script ou un script semblable à vos tâches de maintenance habituelles.

Sauvegarder

Bonne pratique : Mettez en place un plan de sauvegarde, dans le cadre duquel vous effectuerez des sauvegardes régulières.

Le site d'Ola Hallengren fournit un bon point de départ pour apprendre à mettre en œuvre un plan de sauvegarde et de maintenance efficace.

Lorsque vous effectuez des sauvegardes de base de données régulières, veillez à ne pas trop consommer d'IOPS de disque persistant. Utilisez le disque SSD local pour organiser vos sauvegardes, puis transférez-les vers un bucket Cloud Storage.

Surveiller

Bonne pratique : Utilisez Stackdriver Monitoring.

Vous pouvez installer l'agent bêta Stackdriver Monitoring pour Microsoft Windows pour envoyer plusieurs points de données de surveillance vers le système Stackdriver.

En utilisant les fonctions de collecte de données, vous pouvez affiner les informations que vous souhaitez surveiller et les envoyer vers l'entrepôt de données de gestion intégré. L'entrepôt de données de gestion peut s'exécuter sur le même serveur que celui que vous surveillez, ou bien les données peuvent être transmises vers une autre instance SQL Server qui exécute l'entrepôt.

Chargement groupé de données

Bonne pratique : Utilisez une base de données distincte pour mettre en forme et transformer des données groupées avant de les transférer vers les serveurs de production.

Vous serez probablement amené au moins une fois, pour ne pas dire régulièrement, à charger de grandes quantités de données dans votre système. Il s'agit d'une opération nécessitant beaucoup de ressources, pouvant aller jusqu'à vous faire atteindre la limite d'IOPS du disque persistant lorsque vous effectuez des chargements groupés.

Il existe un moyen simple de réduire la consommation d'E/S disque et de ressources processeur lors des opérations de chargement groupées, tout en accélérant l'exécution de vos tâches par lots. Cette solution consiste à créer une base de données complètement distincte utilisant le modèle de récupération Simple, puis à utiliser cette base de données pour la préparation et la transformation de l'ensemble de données groupées avant de l'insérer dans votre base de données de production. Vous pouvez également transférer cette nouvelle base de données sur un disque SSD local, si vous avez suffisamment d'espace. Cela aura pour effet de réduire la consommation de ressources de vos opérations groupées, ainsi que le temps nécessaire pour effectuer les tâches. Enfin, votre tâche de sauvegarde pour les données de production n'aura plus besoin de sauvegarder toutes ces opérations groupées dans le journal des transactions, et s'avérera donc moins lourde et plus rapide.

Valider votre configuration

Bonne pratique : Testez votre configuration pour vérifier son bon fonctionnement.

Chaque fois que vous configurez un nouveau système, vous devez planifier la validation de la configuration et l'exécution de tests de performances. Cette procédure stockée est une excellente ressource pour évaluer votre configuration SQL Server. Prenez le temps de découvrir les paramètres de configuration, puis lancez la procédure.

Optimiser SQL Server Enterprise

SQL Server Enterprise offre une longue liste de fonctionnalités supplémentaires par rapport à la version Standard. Si vous procédez à la migration d'une licence existante vers GCP, vous pouvez envisager de déployer certaines options qui vont améliorer les performances.

Utiliser des tables compressées

Bonne pratique : Activez la compression des tables et des index.

Même si cela peut sembler contre-intuitif, la compression de tables augmente la rapidité de votre système, dans la plupart des cas. Cette solution utilise un petit nombre de cycles processeur pour compresser les données et éliminer les E/S disque supplémentaires, nécessaires à la lecture et l'écriture des blocs les plus volumineux. En règle générale, moins votre système utilise d'E/S disque, meilleures sont ses performances. Vous pouvez consulter les instructions concernant l'estimation et l'activation de la compression des tables et des index sur le site MSDN.

Activer l'extension du pool de mémoire tampon

Bonne pratique : Utilisez l'extension du pool de mémoire tampon pour accélérer l'accès aux données.

C'est dans le pool de mémoire tampon que le système stocke les pages propres. En d'autres termes, il stocke des copies de vos données, en dupliquant leur structure sur disque. Lorsque les données sont modifiées en mémoire, cela génère une page sale, qui doit être vidée sur le disque pour que les modifications soient enregistrées. Lorsque la taille de votre base de données est supérieure à la capacité mémoire disponible, cela accroît la pression sur le pool de mémoire tampon et certaines pages propres peuvent s'en trouver supprimées. Or, une fois les pages propres supprimées, le système devra effectuer des accès en lecture sur le disque pour accéder aux données qui ont été supprimées.

La fonction d'extension du pool de mémoire tampon vous permet de transférer les pages propres vers un disque SSD local, au lieu de les supprimer. Elle fonctionne de la même manière que la mémoire virtuelle, c'est-à-dire en effectuant un basculement, et vous donne accès aux pages propres sur le disque SSD local, ce qui est plus rapide que d'aller récupérer les données sur le disque standard.

Même si disposer de suffisamment de mémoire constitue toujours la meilleure solution, cette technique permet d'augmenter légèrement le débit lorsque la mémoire disponible vient à manquer. Pour en savoir plus sur les extensions du pool de mémoire tampon et examiner des résultats d'analyse comparative, consultez le site de Brent Ozar.

Étapes suivantes

Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

Documentation Compute Engine