Mises à jour et options de configuration recommandées pour SQL Server avec des charges de travail hautes performances
Cet article contient une liste des améliorations des performances et des options de configuration disponibles pour SQL Server 2012 et versions ultérieures.
Version du produit d’origine : SQL Server 2014, SQL Server 2012
Numéro de la base de connaissances d’origine : 2964518
Appliquer les mises à jour recommandées et améliorer les performances de SQL Server 2014 et SQL Server 2012
Cet article décrit les améliorations et les modifications des performances disponibles pour les versions de SQL Server 2014 et SQL Server 2012 via différentes mises à jour de produits et options de configuration. Vous pouvez envisager d’appliquer ces mises à jour pour améliorer les performances de l’instance de SQL Server. Le degré d’amélioration que vous voyez dépend de différents facteurs qui incluent le modèle de charge de travail, les points de contention, la disposition du processeur (nombre de groupes de processeurs, sockets, nœuds NUMA, cœurs dans un nœud NUMA) et la quantité de mémoire présente dans le système. L’équipe de support SQL Server a utilisé ces mises à jour et modifications de configuration pour obtenir des gains de performances raisonnables pour les charges de travail client qui utilisaient des systèmes matériels qui avaient plusieurs nœuds NUMA et un grand nombre de processeurs. L’équipe du support technique continuera à mettre à jour cet article avec d’autres mises à jour à l’avenir.
Les systèmes haut de gamme Un système haut de gamme a généralement plusieurs sockets, huit cœurs ou plus par socket, et un demi-téraoctet ou plus de mémoire.
Note
Dans SQL Server 2016 et versions ultérieures, la plupart des indicateurs de trace mentionnés dans cet article sont le comportement par défaut et vous n’avez pas à les activer dans ces versions.
Les recommandations sont regroupées en trois tables comme suit :
- Le tableau 1 contient les mises à jour et indicateurs de trace les plus fréquemment recommandés pour l’extensibilité sur les systèmes haut de gamme.
- Le tableau 2 contient des recommandations et des conseils pour le réglage des performances supplémentaires.
- Le tableau 3 contient des correctifs d’extensibilité supplémentaires inclus avec une mise à jour cumulative.
Tableau 1. Mises à jour importantes et indicateurs de trace pour les systèmes haut de gamme
Passez en revue le tableau suivant et activez les indicateurs de trace dans la colonne Indicateur de trace après avoir assuré que votre instance de SQL Server répond aux exigences de la colonne Version applicable et des plages de build.
Note
La version et la build applicables indiquent la mise à jour spécifique dans laquelle l’indicateur de modification ou de trace a été introduit. Si aucune cu n’est spécifiée, toutes les cu dans le sp sont incluses.
Version non applicable et build indique la mise à jour spécifique dans laquelle l’indicateur de modification ou de trace est devenu le comportement par défaut. Par conséquent, l’application de cette mise à jour suffit pour obtenir les avantages.
Important
Lorsque vous activez des correctifs avec des indicateurs de trace dans les environnements Always On, sachez que vous devez activer les indicateurs de correction et de trace sur tous les réplicas qui font partie du groupe de disponibilité.
| Scénario et symptôme à prendre en compte | Indicateur de trace | Versions applicables et plages de build | Versions non applicables et plages de build | Lien article/blog de la Base de connaissances qui fournit plus de détails |
|---|---|---|---|---|
|
T8048 |
|
|
|
|
T8079 | SQL Server 2014 SP2 vers sp/CU actuel |
|
|
|
T9024 | Package de mise à jour cumulative 3 pour SQL Server 2012 Service Pack 1 vers SP2 SQL Server 2014 RTM |
|
CORRECTIF : Valeur de compteur « attentes d’écriture de journal » élevée sur une instance SQL Server 2012 ou SQL Server 2014 |
| Votre instance de SQL Server gère des milliers de réinitialisations de connexion en raison du regroupement de connexions. | T1236 | Package de mise à jour cumulative 9 pour SQL Server 2012 Service Pack 1 vers SP2 Mise à jour cumulative 1 pour SQL Server 2014 |
|
|
|
T1118 |
|
|
Améliorations de la concurrence pour la base de données tempdb REMARQUE Activez l’indicateur de trace et ajoutez plusieurs fichiers de données pour la base de données tempdb. |
|
T1117 |
|
|
Recommandations pour réduire la contention d’allocation dans la base de données tempdb de SQL Server |
Les contentions de blocages lourds SOS_CACHESTORE ou vos plans sont fréquemment supprimés sur les charges de travail de requête ad hoc. |
T174 |
|
Aucun(e) |
|
|
T8032 |
|
Aucun(e) |
|
| Les statistiques existantes ne sont pas fréquemment mises à jour en raison du grand nombre de lignes de la table. | T2371 |
|
Aucun(e) | |
|
T7471 | SQL Server 2014 SP1 CU6 vers sp/CU actuel | Aucun(e) | Amélioration des performances des statistiques de mise à jour avec SQL 2014 &SQL 2016 |
| La commande CHECKDB prend beaucoup de temps pour les bases de données volumineuses. |
|
|
Aucun(e) | |
| La commande CHECKDB prend beaucoup de temps pour les bases de données volumineuses. | T2566 |
|
Aucun(e) |
|
L’exécution de requêtes simultanées d’entrepôt de données qui prennent beaucoup de temps de compilation entraîne RESOURCE_SEMAPHORE_QUERY_COMPILE des attentes. |
T6498 | Package de mise à jour cumulative 6 pour SQL Server 2014 vers SP1 |
|
|
| Par défaut, vous résolvez des problèmes de performances de requête spécifiques. Les correctifs d’optimiseur de performances des requêtes sont désactivés par défaut. | T4199 |
|
Aucun(e) | |
| Vous rencontrez des performances lentes à l’aide d’opérations de requête avec des types de données spatiales. |
|
|
|
|
|
T8075 |
|
|
CORRECTIF : Erreur de mémoire insuffisante lorsque l’espace d’adressage virtuel du processus SQL Server est faible dans SQL Server |
|
T3449 |
|
|
CORRECTIF : La création de base de données SQL Server sur un système avec un grand volume de mémoire prend plus de temps que prévu |
Tableau 2. Considérations générales et bonnes pratiques pour améliorer les performances de votre instance de SQL Server
Passez en revue le contenu de l’article de la Base de connaissances/documentation en ligne sur les ressources et envisagez d’implémenter les instructions de la colonne Actions recommandées.
| Article de la Base de connaissances/Ressource en ligne de la documentation | Actions recommandées |
|---|---|
| Configurer l'option de configuration de serveur max degree of parallelism | Utilisez la procédure stockée sp_configure pour apporter des modifications de configuration pour configurer le degré maximal de parallélisme de l’option de configuration du serveur pour votre instance de SQL Server en fonction de l’article de la Base de connaissances. |
| Limites de capacité de calcul des éditions SQL Server | Êdition Entreprise avec des licences de licence d’accès client (CAL) server + client est limitée à 20 cœurs par instance SQL Server. Il n'existe aucune limite dans le mode de licence Serveur selon le nombre de cœurs. Envisagez de mettre à niveau votre édition de SQL Server vers la référence SKU appropriée pour tirer parti de toutes les ressources matérielles. |
| Performances lentes sur Windows Server lors de l’utilisation du plan d’alimentation « équilibré » | Passez en revue l’article et collaborez avec votre administrateur Windows pour implémenter l’une des solutions indiquées dans la section « Résolution » de l’article. |
| Affectez manuellement des nœuds NUMA aux groupes K. | |
| Optimiser pour les charges de travail ad hoc CONTRAINTE PARAMETERIZATION | Les entrées dans le cache de plan sont supprimées en raison de la croissance d’autres caches ou commis de mémoire. Vous pouvez également rencontrer l’éviction du cache de plan lorsque le cache atteint son nombre maximal d’entrées. Outre l’indicateur de trace 8032 décrit ci-dessus, envisagez l’optimisation de l’option serveur des charges de travail ad hoc et l’option de base de données PARAMETERIZATION FORCÉE. |
| Comment réduire la pagination de la mémoire du pool de mémoires tampons dans la configuration de la mémoire SQL Server et les considérations relatives au dimensionnement dans SQL Server 2012 et versions ultérieures | Affectez le droit utilisateur Activer les pages de verrouillage en mémoire (Windows) au compte de démarrage du service SQL. Découvrez comment activer la fonctionnalité « pages verrouillées » dans SQL Server 2012. Définissez la mémoire maximale du serveur sur environ 90 % de la mémoire physique totale. Assurez-vous que les options de configuration de la mémoire du serveur définissent les comptes de mémoire à partir des seuls nœuds configurés pour utiliser les paramètres de masque d’affinité. |
| SQL Server et pages volumineuses expliquées... Options de paramétrage pour SQL Server lors de l’exécution dans des charges de travail hautes performances | Envisagez d’activer TF 834 si vous avez un serveur avec une grande quantité de mémoire, en particulier avec une charge de travail d’entreposage de données ou analytique. N’oubliez pas que TF 834 n’est pas recommandé si vous utilisez des index columnstore. |
| Description des options « nombre de compartiments de cache de vérification d’accès » et « quota de cache de vérification d’accès » disponibles dans la procédure stockée sp_configure | Utilisez les options de configuration du serveur de contrôle d’accès pour configurer ces valeurs conformément aux recommandations de l’article de la Base de connaissances. Les valeurs recommandées pour les systèmes haut de gamme sont les suivantes : « Nombre de compartiments de cache de vérification d’accès » : 256 « Quota de cache de vérification d’accès » : 1024 |
| Indicateurs de requête ALTER WORKLOAD GROUP Memory grant | Si vous avez de nombreuses requêtes qui épuisent les allocations de mémoire volumineuses, réduisez request_max_memory_grant_percent la valeur par défaut du groupe de charge de travail par défaut dans la configuration resource governor de 25 % à une valeur inférieure. Les nouvelles options d’octroi de mémoire de requête sont disponibles (min_grant_percent et max_grant_percent) dans SQL Server |
| Initialisation instantanée de fichiers | Collaborez avec votre administrateur Windows pour accorder au compte de service SQL Server le droit d’utilisateur « Effectuer des tâches de maintenance en volume » conformément aux informations contenues dans la rubrique Documentation en ligne. |
| Considérations relatives aux paramètres « autogrow » et « autoshrink » dans SQL Server | Vérifiez les paramètres actuels de votre base de données et vérifiez qu’ils sont configurés conformément aux recommandations de l’article de la Base de connaissances. |
| Points de contrôle de base de données (SQL Server) | Envisagez d’activer des points de contrôle indirects sur les bases de données utilisateur pour optimiser le comportement des E/S dans SQL Server 2012 et 2014. |
| CORRECTIF : Synchronisation lente lorsque les disques ont des tailles de secteur différentes pour les fichiers journaux de réplica principal et secondaire dans les environnements de groupe de disponibilité SQL Server et logshipping | Si vous disposez d’un groupe de disponibilité où le journal des transactions sur le réplica principal se trouve sur un disque avec une taille de secteur de 512 octets et que le journal des transactions du réplica secondaire se trouve sur un lecteur avec une taille de secteur 4K, vous risquez d’avoir un problème où la synchronisation est lente. Dans ces cas, l’activation de TF 1800 doit corriger le problème. Pour plus d’informations, consultez l’indicateur de trace 1800. |
| Si votre serveur SQL Server n’est pas déjà lié au processeur et qu’une surcharge de 1,5 % à 2 % est négligeable pour vos charges de travail, nous vous recommandons d’activer TF 7412 comme indicateur de trace de démarrage. Cet indicateur permet un profilage léger dans SQL Server 2014 SP2 ou version ultérieure, ce qui vous permet d’effectuer des dépannages de requêtes dynamiques dans les environnements de production. |
Tableau 3. Correctifs de performances inclus dans une mise à jour cumulative
Passez en revue la description de la colonne Symptômes et appliquez les mises à jour requises dans la colonne Mise à jour requise dans les environnements applicables. Vous pouvez consulter l’article de la Base de connaissances pour plus d’informations sur les problèmes respectifs. Ces recommandations ne vous obligent pas à activer des indicateurs de trace supplémentaires en tant que paramètres de démarrage. L’application de la dernière mise à jour cumulative ou service Pack qui inclut ces correctifs suffit pour bénéficier de l’avantage.
Note
Le nom de la mise à jour cu dans la colonne De mise à jour requise fournit la première mise à jour cumulative de SQL Server qui résout ce problème. Une mise à jour cumulative contient tous les correctifs logiciels et toutes les mises à jour incluses dans la version précédente de la mise à jour SQL Server. Par conséquent, nous vous recommandons d’installer la dernière mise à jour cumulative afin de résoudre les problèmes.
Remarques importantes
Si toutes les conditions du tableau 1 s’appliquent à vous :
- Conseils pour SQL Server 2014 : Appliquez au moins la mise à jour cumulative 1 pour SQL Server 2014 pour RTM et ajoutez « -T8048 -T9024 -T1236 -T1117 -T1118 » à la liste des paramètres de démarrage SQL Server.
- Conseils pour SQL Server 2012 : Appliquez SP2 et ajoutez « -T8048 -T9024 -T1236 -T1117 -T1118 » à la liste des paramètres de démarrage SQL Server.
Pour obtenir des informations générales sur l’utilisation des indicateurs de trace, consultez la rubrique DBCC TRACEON - Indicateurs de trace (Transact-SQL) dans la documentation en ligne de SQL Server.
Vous trouverez plus d’informations sur le nombre de processeurs, la configuration NUMA, etc., dans votre affichage du journal des erreurs SQL Server dans SQL Server Management Studio (SSMS).
Pour rechercher la version de SQL Server, vérifiez les éléments suivants :
Comment déterminer la version et l’édition de SQL Server et de ses composants
References
Comment obtenir le dernier Service Pack pour SQL Server 2012
Où trouver des informations sur les builds de SQL Server les plus récentes
Ressources de la communauté SQL Server sur les mises à jour importantes pour SQL Server
S’applique à
- SQL Server 2014 Enterprise
- SQL Server 2014 Enterprise Core
- SQL Server 2014 Business Intelligence
- SQL Server 2014 Developer
- SQL Server 2014 Standard
- SQL Server 2014 Web
- SQL Server 2014 Express
- SQL Server 2012 Business Intelligence
- SQL Server 2012 Developer
- SQL Server 2012 Enterprise
- SQL Server 2012 Standard
- SQL Server 2012 Web
- SQL Server 2012 Enterprise Core