Datafly.pro

Pour rappel, un traceflag, ou en français "indicateur de trace" ou "drapeau de trace" est un paramètre transmis au serveur SQL, permettant de configurer un comportement particulier, comme par exemple le renvoi d’indications de diagnostic ou une modification du comportement de l’optimiseur.

Les indicateurs de trace se configurent :

• Soit comme paramètre de démarrage du serveur, activé globalement à l’aide de l’option de ligne de commande –T  (se configure via le gestionnaire de configuration SQL Server)
• Soit dans la session utilisateur avec la commande DBCC TRACEON, activé pour la session, ou si le deuxième argument de cette commande est passé à -1 pour toute l’instance.
• Soit pour une requête individuel avec l’indicateur QUERYTRACEON

Allons- y de notre petite liste.

Avant d’activer un traceflag en production, il faut savoir ce que vous faites ! Une bonne partie des indicateurs existants ne sont pas officiellement documentés : leur utilisation reste entièrement à vos risques et périls et doit être réservée à des environnements de test ! La liste des traceflags documentés : https://technet.microsoft.com/en-us/library/ms188396.aspx 

• 1117 : force l’ensemble des fichiers à s’agrandir simultanément lors d’un incrément de croissance. Etendue globale. Avec SQL Server 2016 ce paramètre est activé par défaut pour la base tempdb, et peut être configuré base par base et pour chaque filegroup avec la commande ALTER DATABASE MODIFY FILEGROUP AUTOGROWTH_ALL_FILES | AUTOGROWTH_SINGLE_FILE.

• 1118 : SQL Server stocke les 8 premières pages d’une même table dans des extensions mixtes. Pour réduire la contention sur les pages d’allocation SGAM, ce TF force l’allocation uniquement dans des extensions uniformes. Etendue globale. KB : https://support.microsoft.com/en-us/kb/328551. Dans SQL 2016, ce TF est activé par défaut pour toutes les bases de données et se configure avec la commande ALTER DATABASE SET MIXED_PAGE_ALLOCATION

• 3226 : Empêche SQL Serveur d’écrire un message dans l’errorlog en cas de sauvegarde réussie. Bien utile en cas de sauvegardes fréquentes qui polluent le journal d’erreurs. Etendue globale.

• 2371 : modifie l’algorithme de recalcul automatique des statistiques de colonnes et d’index. Le seuil est dynamique en fonction de la taille de la table. Fonctionnement par défaut avec SQL 2016. Etendue globale.

• 3604 : Permet d’afficher les résultats de certaines commandes (comme DBCC PAGE ou DBCC IND) dans la fenêtre de messages. Le TF 3605 force l’envoi vers l’errorlog.

• 610 : active une journalisation minimale pour l’insertion de données pour optimiser les performances avec certains scénarios de chargement. Lire https://technet.microsoft.com/en-us/library/dd425070%28v=sql.100%29.aspx Etendue globale ou session.

• 4199 : active les hotfixes de l’optimiseur inactivés (destinés à être activés dans une version future). Lire https://support.microsoft.com/en-us/kb/974006 . Dans SQL 2016, il peut être configuré avec la commande ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES

• 9481 : utilisable pour une base en compatibilité 2014 ou 2016, force l’estimateur de cardinalité à fonctionner sur le modèle de SQL 2012. Etendue globale, session ou requête.

• 2312 : à l’inverse du précédent, utilisable pour une base en compatibilité 2012 ou inférieure (sur un serveur 2014 ou 2016), force l’estimateur de cardinalité à fonctionner sur le modèle de SQL 2014/2016. Etendue globale, session ou requête.

• 2453 : pour améliorer l'estimation de cardinalité quand on utilise des variables de type table dans des jointures. Depuis 2012 SP2 ou 2014 CU3. A utiliser avec précaution. Lire https://support.microsoft.com/en-us/kb/2952444

• 2389 et 2390 : permet à l’optimiseur de mieux estimer les cardinalités sur des colonnes dont les valeurs sont ascendantes. Lire https://blogs.msdn.microsoft.com/ianjo/2006/04/24/ascending-keys-and-auto-quick-corrected-statistics/

• 9130 : permet de mettre en évidence dans le plan d'exécution graphique les filtres résiduels. Lire : http://www.theboreddba.com/Categories/FunWithFlags/Revealing-Predicates-in-Execution-Plans-%28TF-9130%29.aspx

• 8666 : non documenté, donne la liste des statistiques utilisées par l’optimiseur au moment de la compilation. Lire : http://blog.developpez.com/elsuket/p12023/moteur-de-base-de-donnees-sql-server/quelles-statistiques-loptimiseur-a-t-il-utilisees-pour-calculer-le-plan-de-requete#more-559

• 9204 et 9292 : Permettent de renvoyer la liste des statistiques utilisées par l’optimiseur. Statistiques considérées avec le 9292 et statistiques utilisées pour calculer le plan avec le 9204. Lire : http://sqlblog.com/blogs/paul_white/archive/2011/09/21/how-to-find-the-statistics-used-to-compile-an-execution-plan.aspx

• 4137 : force l’estimateur de cardinalités à considérer des prédicats multiples comme directement corrélés (force la sélectivité minimum). Jusqu’à SQL Server 2012. Lire https://support.microsoft.com/en-us/kb/2658214

• 9471 et 9472 : utilisable à partir de SQL 2014, force le comportement de l’optimiseur pour des prédicats multiples. Force la sélectivité minimum avec le 9471 et l’indépendance des prédicats avec le 9472. Lire : https://sqlperformance.com/2014/01/sql-plan/cardinality-estimation-for-multiple-predicates

• 4136 : désactive le « parameter sniffing » : toutes les requêtes sont compilées commes si elles utilisaient l’indicateur OPTIMIZE FOR UNKNOWN. Avec SQL Server 2016, se configure avec la commande ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING. Lire : http://www.benjaminnevarez.com/2010/08/disabling-parameter-sniffing/

• 1204 et 1222 : affichent les informations sur les deadlocks dans l’errorlog. 1204 donne l’information par nœud, 1222 par process et ressource en XML. Couramment utilisés.

• 1200 : uniquement pour des fins de démonstration, car extrêmement verbeux, il affiche chaque verrou posé. A utiliser globalemment avec le TF 3604

• 1224 et 1211 : désactivent l’escalade de verrous. Le TF 1211 force la désactivation jusqu’à épuisement de la mémoire…