Trace flags SQL Server pour la performance : référence
Activer, lister et persister les trace flags SQL Server, avec une table des flags courants et leurs précautions.
Liste de référence des trace flags SQL Server fréquemment rencontrés en performance et en dépannage, avec la façon de les activer et ceux qui demandent de la prudence. Voir la documentation Microsoft sur les trace flags pour la liste de référence par version.
Activer, lister et rendre persistants les trace flags
-- Niveau session : désactivé à la fermeture de la session
DBCC TRACEON (3226);
-- Global (-1) : s'applique à toutes les sessions
DBCC TRACEON (1117, 1118, 2371, 3226, 4199, -1);
-- Afficher les trace flags activés globalement
DBCC TRACESTATUS (-1);
Les trace flags activés avec DBCC TRACEON sont effacés au redémarrage de SQL Server. Pour les rendre persistants, les ajouter comme paramètres de démarrage (-T<flag>) dans SQL Server Configuration Manager.
Flags couramment utilisés
La colonne « Prudence » indique si l'activation du flag mérite réflexion.
| Flag | Prudence | Effet |
|---|---|---|
| 460 | Non | Remplace le message « string or binary data would be truncated » par un message plus détaillé (vérifier que l'instance est à jour). |
| 610 | Non | Tente des insertions minimalement journalisées dans les tables indexées, comme avec l'indicateur TABLOCK. |
| 652 | Oui | Désactive la lecture anticipée des pages lors des scans. Dans la plupart des cas, on veut charger les pages en mémoire le plus tôt possible. |
| 661 | Oui | Désactive le nettoyage des enregistrements fantômes, qui supprime définitivement les lignes supprimées. |
| 834 | Peut-être | Utilise de grandes pages pour le buffer pool. Connu pour empêcher ou ralentir fortement le démarrage de SQL Server. |
| 845 | Peut-être | Active le verrouillage de pages en mémoire sur l'édition Standard. |
| 1117 | Non | Fait croître ensemble tous les fichiers d'un filegroup. Surtout utilisé pour tempdb mais touche toutes les bases. |
| 1118 | Non | Supprime les extents mixtes. Surtout utilisé pour réduire la contention sur tempdb. |
| 1204 | Non | Retourne les ressources impliquées dans un deadlock (format texte). |
| 1211 | Oui | Désactive l'escalade de verrous en cas de pression mémoire. Prioritaire sur 1224. |
| 1222 | Non | Retourne les ressources et types de verrous impliqués dans un deadlock au format XML. |
| 1224 | Oui | Désactive l'escalade de verrous selon le nombre de verrous. 1211 est prioritaire. |
| 1236 | Non | Corrige un problème de verrous/spinlocks dans SQL Server 2012/2014. |
| 1806 | Oui | Désactive l'initialisation instantanée des fichiers. |
| 2312 | Peut-être | Force l'estimateur de cardinalité de SQL Server 2014. |
| 2330 | Oui | Désactive la collecte de l'usage des index et des index manquants. Rarement une bonne idée. |
| 2371 | Non | Abaisse le seuil de mise à jour automatique des statistiques selon la taille de la table. Utile pour les très grosses bases. |
| 2467 | Non | Ordonnancement CPU : placer les threads sur le nœud le moins chargé. |
| 2468 | Non | Ordonnancement CPU : placer les threads dans le même nœud. |
| 2549 | Peut-être | Peut aider DBCC CHECKDB quand les fichiers sont répartis sur plusieurs disques ; inutile sinon. |
| 2562 | Peut-être | Exécute DBCC CHECKDB en un seul lot. À éviter si tempdb est un point sensible. |
| 2861 | Peut-être | Conserve les plans à coût nul dans le cache. Souvent activé par des outils de supervision. |
| 3014 | Non | Écrit plus d'informations dans le journal d'erreurs pendant les sauvegardes. |
| 3023 | Non | Active les checksums de sauvegarde quand ils ne peuvent pas l'être via l'outil de sauvegarde. |
| 3042 | Peut-être | Les sauvegardes compressées grandissent progressivement au lieu de préallouer l'espace. Peut ralentir les sauvegardes. |
| 3226 | Non | Évite d'écrire dans le journal d'erreurs chaque sauvegarde réussie. Pratique pour des sauvegardes de log sur des centaines de bases. |
| 3505 | Oui | Désactive les checkpoints automatiques. |
| 3605 | Non | Utilisé avec 1204 et 1222 pour envoyer les informations de deadlock dans le journal d'erreurs. |
| 3608 | Peut-être | Empêche SQL Server de démarrer et récupérer automatiquement toutes les bases sauf master. |
| 3625 | Non | Limite les informations des messages d'erreur pour les utilisateurs non sysadmin, afin d'éviter de divulguer des détails sensibles. |
| 3801 | Probablement | Interdit les instructions USE [database]. Surtout utile dans Azure. |
| 4101-4135 | Non | Les flags commençant par 41 sont en général des correctifs de l'optimiseur. Envisager simplement 4199. |
| 4136 | Probablement | Désactive le parameter sniffing. À réserver à des environnements choisis et surveillés ; à partir de SQL Server 2016, préférer la configuration PARAMETER_SNIFFING au niveau base. |
| 4137 | Probablement | Modifie certaines estimations de cardinalité (requêtes avec beaucoup de AND). |
| 4138 | Oui | Désactive toutes les optimisations de row goal, que l'on souhaite généralement conserver. |
| 4139 | Non | Aide pour les problèmes de clés ascendantes avant SQL Server 2014 (2389 et 2390 peuvent aussi être nécessaires). |
| 4199 | Non | Active les correctifs de l'optimiseur livrés dans les service packs et mises à jour cumulatives qui ont leur propre trace flag. |
| 4616 | Peut-être | Rend les rôles serveur visibles aux applications. |
| 7471 | Peut-être | Autorise plusieurs mises à jour de statistiques concurrentes sur une même table ; risque de deadlocks si des statistiques sont créées et mises à jour simultanément. |
| 7806 | Non | Active la connexion administrateur dédiée sur SQL Server Express. |
| 8015 | Peut-être | SQL Server ignore la configuration NUMA et gère les nœuds comme un seul. |
| 8048 | Peut-être | Passe au partitionnement basé sur NUMA. |
| 8649 | Oui | Ramène le seuil de coût du parallélisme à 0. Indicateur de dépannage pour serveurs de développement uniquement. |
| 9481 | Peut-être | Force l'estimateur de cardinalité antérieur à 2014. |
Groupes de disponibilité (par exemple avec SharePoint)
| Flag | Effet |
|---|---|
| 9567 | Compresse le flux de données des groupes de disponibilité pendant l'amorçage automatique. Peut réduire fortement le temps de transfert mais augmente la charge CPU. |
| 9592 | Active la compression du flux de log pour les groupes de disponibilité avec réplicas synchrones. Désactivé par défaut à cause de la latence supplémentaire ; activé par défaut pour les réplicas asynchrones. |
Le type d'attente WRITELOG est courant et peut causer des retards importants : c'est le temps d'attente de l'écriture du cache du journal sur disque. Activer DELAYED_DURABILITY sur les bases est une option pour le réduire, au prix d'une perte possible des dernières transactions après un incident.
Remarques
- Tester tout trace flag hors production ; les effets et valeurs par défaut dépendent de la version de SQL Server (par exemple, le comportement de 1117 et 1118 est intégré pour tempdb à partir de SQL Server 2016).
- Les flags globaux activés avec
DBCC TRACEONne survivent pas à un redémarrage.