Bonnes pratiques pour les instances SQL Server

---

Vous pouvez adopter plusieurs bonnes pratiques pour optimiser les instances de Compute Engine exécutant Microsoft SQL Server. Pour apprendre à configurer une instance SQL Server hautes performances, consultez la page Créer une instance SQL Server hautes performances.

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.

Remarque : L'évaluation SQL Server dans le gestionnaire de charges de travail permet aux clients d'analyser leurs déploiements SQL Server avec un ensemble de recommandations Google Cloud pour des performances optimales directement depuis la console Google Cloud. Pour en savoir plus, consultez les instructions de configuration de l'agent pour SQL Server.

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 qu'il puisse se connecter à la base de données à partir d'autres machines clientes, configurez le pare-feu de sorte à 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. Spécifiez une liste d'adresses IP séparées par des virgules, et sans espaces, pour le paramètre remoteip à la place de LOCAL_SUBNET. Notez également que le chemin d'accès au 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 machine virtuelle (VM) sont associées à un réseau conçu par Google offrant 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 explique comment optimiser les performances de SQL Server sur Compute Engine et décrit les activités opérationnelles permettant d'assurer 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, consultez la section Configurer TempDB.

Traitement de requête en parallèle

Bonne pratique : Définissez la valeur de max degree of parallelism sur 8.

La configuration par défaut recommandée pour max degree of parallelism consiste à le faire correspondre au nombre de processeurs sur 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 CXPACKET et augmentez progressivement la propriété cost threshold for parallelism.

Ce réglage va de pair avec 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 définir ces paramètres de manière globale et définir Max DOP pour chaque niveau de 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. Lorsque 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, mais 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 accroît 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 Croissance du fichier est défini par défaut sur 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 : Programmez 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 permettent 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 YOUR_DB
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 Transact-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

Lorsque vos index sont trop fragmentés, vous pouvez les réorganiser à l'aide d'un script ALTER de base. Voici un exemple de script qui imprime 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.

Si vous restaurez une base de données depuis un autre emplacement vers Google Cloud, vous devez réorganiser, voire recréer, les index, puis mettre à jour l'utilisation. Remplacez YOUR_DB par le nom de votre base de données :

dbcc updateusage (YOUR_DB) GO USE YOUR_DB
GO

Formater des disques secondaires

Bonne pratique : Formatez des disques secondaires avec une unité d'allocation de 64 ko.

SQL Server stocke les données dans des unités de stockage appelées extents (extensions). Les extensions, d'une taille de 64 ko, sont constituées de huit pages de mémoire contiguës, également d'une taille de 8 ko. Le formatage d'un disque avec une unité d'allocation de 64 ko améliore les opérations de lecture et d'écriture de SQL Server dans les extensions, ce qui augmente les performances E/S du disque.

Pour formater des disques secondaires avec une unité d'allocation de 64 Ko, exécutez la commande PowerShell suivante, qui recherche tous les disques nouveaux et non initialisés dans un système, puis les formate avec l'unité d'allocation de 64 Ko :

Get-Disk | Where-Object {$_.PartitionStyle -eq 'RAW'} | Initialize-Disk -PartitionStyle GPT -PassThru | New-Partition -AssignDriveLetter -UseMaximumSize | Format-Volume -FileSystem NTFS -AllocationUnitSize 65536 -Confirm:$FALSE

Sauvegarder

Bonne pratique : Sauvegardez régulièrement vos données à l'aide des solutions de sauvegarde et de reprise après sinistre de Google pour une protection optimale. Nous vous recommandons de sauvegarder vos données au moins une fois par jour.

Les solutions de sauvegarde et de reprise après sinistre de Google offrent les avantages suivants pour Microsoft SQL Server :

• Sauvegarde permanente incrémentielle efficace avec une véritable récupération à un moment précis qui permet d'effectuer des sauvegardes plus rapidement qu'avec les sauvegardes conventionnelles, tout en réduisant l'impact sur les serveurs de production. Cela réduit également l'utilisation de la bande passante et de l'espace de stockage pour un objectif de reprise après sinistre (RPO) et un coût total de possession (TCO) faibles.
• Récupérations de type installer et migrer (M&M) pour les sauvegardes stockées dans Cloud Storage pour un objectif de temps de récupération (RTO) faible.
• Intégration complète aux fonctionnalités de SQL Server, y compris la prise en charge des clusters de groupes de disponibilité SQL Server et de plusieurs options de récupération dans différents scénarios.
• Volet de gestion central incluant des fonctionnalités de surveillance, d'alerte et de création de rapports dédiées pour toutes vos sauvegardes.

En savoir plus :

• Présentation du service Backup and DR
• Liste des fonctionnalités du service Backup and DR
• Protéger et récupérer des bases de données Microsoft SQL Server
• Tarifs du service Backup and DR
• Simulateur de coût

Surveillance

Bonne pratique : Utilisez Cloud Monitoring.

Vous pouvez installer l'agent Cloud Monitoring pour Microsoft Windows afin d'envoyer plusieurs points de données de surveillance vers le système Cloud Monitoring.

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. La solution consiste à créer une base de données entièrement dédiée utilisant le modèle de récupération Simple, puis à l'utiliser pour la préproduction et la transformation de l'ensemble de données groupées avant de l'intégrer à 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. L'utilisation d'un disque SSD local pour la base de données de récupération réduit la consommation des ressources de vos opérations groupées et le temps nécessaire pour effectuer les tâches. Enfin, la tâche de sauvegarde des données de production n'a pas besoin de sauvegarder toutes ces opérations groupées dans le journal des transactions. Elle sera donc plus petite et plus rapide.

Remarque : Bulk Logged est un autre modèle de récupération qui peut s'avérer utile pour vos opérations de chargement groupé. Il utilise plus d'espace disque, plus d'E/S disque et exige d'appliquer régulièrement des tâches de sauvegarde sur vos journaux de transactions. En revanche, il n'est pas aussi exhaustif que le modèle de récupération complet.

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 migrez une licence existante vers Google Cloud, vous pouvez envisager de mettre en œuvre certaines options de performances.

Utiliser des tables compressées

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

Contrairement à ce que l'on pourrait croire, 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 plus volumineux. En règle générale, moins votre système utilise d'E/S disque, meilleures seront 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.

Remarque : Les options de compression d'index et de table sont également disponibles pour SQL Server Standard Edition 2016 SP1 et versions ultérieures.

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. Ce type de page doit être vidé 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. Une fois les pages propres supprimées, le système doit 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.

Optimiser les licences SQL Server

Multithreading simultané (SMT)

Bonne pratique : Définissez le nombre de threads par cœur sur 1 pour la plupart des charges de travail SQL Server.

Le multithreading simultané (SMT, Simultaneous Multithreading, ou Hyper-Threading Technology) utilisé par les processeurs Intel est une fonctionnalité qui permet de partager logiquement un seul cœur de processeur en deux threads. Sur Compute Engine, le SMT est activé par défaut sur la plupart des VM : chaque processeur virtuel de la VM s'exécute sur un seul thread et chaque cœur de processeur physique est partagé par deux processeurs virtuels.

Sur Compute Engine, vous pouvez configurer le nombre de threads par cœur, ce qui désactive le SMT. Lorsque le nombre de threads par cœur est défini sur 1, les processeurs virtuels ne partagent pas les cœurs de processeur physiques. Cette configuration a un impact significatif sur les coûts de licence de Windows Server et SQL Server. Lorsque le nombre de threads par cœur est défini sur 1, le nombre de processeurs virtuels d'une VM est réduit de moitié, ce qui réduit également le nombre de licences Windows Server et SQL Server requises. Cela peut réduire considérablement le coût total de la charge de travail.

Toutefois, la configuration du nombre de threads par cœur a également une incidence sur les performances de la charge de travail. Les applications écrites pour être multithreads peuvent exploiter cette fonctionnalité en divisant les tâches de calcul en fragments plus petits et chargeables en parallèle, planifiés sur plusieurs cœurs logiques. Cette parallélisation du travail permet souvent d'augmenter le débit global du système grâce à une meilleure utilisation des ressources principales disponibles. Par exemple, lorsqu'un thread est bloqué, l'autre thread peut utiliser le cœur.

L'impact sur les performances du SMT sur SQL Server dépend des caractéristiques de charge de travail et de la plate-forme matérielle utilisée, car l'implémentation du SMT diffère selon les générations matérielles. Les charges de travail ayant un volume élevé de petites transactions (les charges de travail OLTP, par exemple) permettent souvent d'exploiter le SMT et offrent une amélioration des performances plus importante. En revanche, les charges de travail moins faciles à charger en parallèle (par exemple, OLTP) bénéficient moins de la compatibilité SMT. Bien que ces tendances aient été généralement constatées, l'impact sur les performances du SMT doit être évalué par charge de travail pour déterminer l'impact de la définition du nombre de threads par cœur sur 1.

La configuration la plus rentable pour la majorité des charges de travail SQL Server consiste à définir le nombre de threads par cœur sur 1. La baisse des performances peut être compensée par l'utilisation d'une VM de taille plus importante. Dans la plupart des cas, la réduction de 50 % du coût des licences est supérieure à l'augmentation du coût de la plus grande VM.

Exemple : Considérons un serveur SQL Server déployé dans la configuration n2-standard-16

Par défaut, le nombre de cœurs visibles dans le système d'exploitation est de 16, ce qui signifie que les licences pour 16 processeurs virtuels Windows Server et 16 processeurs virtuels SQL Server sont requises pour exécuter le serveur.

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

Après avoir suivi la procédure de désactivation du SMT sur SQL Server, la nouvelle configuration est la suivante :

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

Maintenant que seuls huit cœurs sont visibles dans le système d'exploitation, le serveur ne nécessite que huit processeurs virtuels Windows Server et SQL Server pour s'exécuter.

Étapes suivantes

• Créer une instance SQL Server hautes performances
• Créer des instances SQL Server
• Créez des instances Windows.