SQL Server : Quelques Trace Flags utiles
Pour commencer les traces flags (TF) sont des paramètres avancés (à ne pas mettre dans toutes les mains) de SQL Server. Il n'y a pas vraiment de lite exhaustive de ceux-ci, certains servant à des fins de débogage et d'autres pour activer / désactiver telle ou telle fonctionnalité. Pour la plupart d'entre il est clair qu'ils n'ont pas vocation à être public ou documenté car tout simplement pas stable et pouvant entraîner de grave dommage à vos serveurs.
Cependant, voici une liste de quelques qui peuvent être utiles. Ils sont public et partiellement documenté, utilisable pour la plupart d'entre à condition d'en connaitre les limites.
Pour activer un Trace Flag, 2 options :
TF 610 - SQL 2008 - Porté Serveur uniquement
Active le « minimaly logging » pour des opérations type INSERT / BULK INSERT sur table non vide, à condition d'utiliser le hint TABLOCK sur la table de destination. C'est une nouveauté de SQL Server 2008 qui est maintenant supporté par Microsoft, elle met les opérations de type INSERT au même niveau que les SELECT INTO ou les BULK INSERT sur table vide à condition d'être en mode « SIMPLE » ou « BULK LOGGED » sur votre base de données, ces opérations auront donc un impact moindre sur le journal de transaction.
INSERT dbo.MaTable WITH(TABLOCK)
(col1, col2)
SELECT col1, col2 FROM dbo.MonAutreTable
Attention le système disque doit être rapide pour utiliser ce mode, un système disque non optimisé pourrait conduire à une diminution des performances. De plus le moteur ne l'utilise que lorsqu'il le peut.
Même si celui-ci peut s'activer à la voler je vous conseille de le passer au démarrage de votre instance, j'ai en effet constaté des dysfonctionnements quand celui-ci est activé avec le TRACEON.
Scénarios types : Réplication, chargement de fichier dans des tables, copies de données en quantité de table à table
TF 2301 - SQL 2005 / 2008 - Porté serveur et session
http://blogs.msdn.com/ianjo/archive/2006/04/24/582219.aspx
Change le comportement de l'optimiseur de requête en lui demande d'explorer plus de possibilité pour les requêtes complexes. Consomme plus de temps processeur et prolonge le temps de compilation, mais permets l'obtention de plans plus justes.
DBCC TRACEON
(2301)
SELECT
*
FROM dbo.MaTable
-- ...
-- On assume ici une requête très complexe
-- ...
DBCC TRACEOFF
(2301)
Scénarios types : Data Warehouse ou requête de génération de rapports
TF 4618, 4610 - SQL 2005 SP2 - Porté Serveur uniquement
et
TF 4621 - SQL 2005 SP3 - Porté Serveur uniquement
Réglage de la taille du cache du TokenAndPermUserStore. C'est un correctif d'un bug courant en 64 bits qui fait que le serveur se retrouve à cours de CPU parce que le cache est trop gros.
Sous SQL Server 2005 SP2 il est possible de réaliser une limitation statique :
http://blogs.msdn.com/psssql/archive/2008/06/16/query-performance-issues-associated-with-a-large-sized-security-cache.aspx
A partir du Service Pack 3, il valeur peut être spécifié dans la base de registre :
http://support.microsoft.com/kb/959823/
Et sous SQL Server 2008, ces paramètres sont accessibles dans les options de configuration serveur
EXEC
sp_configure
'access check cache bucket count'
EXEC
sp_configure
'access check cache quota'
http://support.microsoft.com/kb/955644/
Scénarios types : Correction d'un bug courant en 64 bits, si vous utilisez des comptes de sécurité non sysadmin (ce qui est fortement consillé).
TF 1118 - SQL 2000 & + - Porté Serveur uniquement
Supprime l'allocation d'extension mixte pour toutes les bases de données. En effet SQL Server lors de la création d'une table lui alloue des pages individuelles (une page fait 8ko) partagées avec d'autres tables dans une même extension (qui est un bloc de 64 ko) et c'est seulement à partir de 64ko que des extensions complètes sont allouées. Ce mécanisme permet d'économiser de l'espace disque, mais peut provoquer, essentiellement dans tempdb des problèmes de contention (2 sessions attende l'une après l'autre de pouvoir créer une table temporaire.)
http://blogs.codes-sources.com/christian/archive/2007/11/23/sql-server-optimisez-tempdb-ou-comment-param-trer-tempdb-pour-des-performances-optimales.aspx
Ce problème est aussi fréquent d'autant que tempdb se révèle de plus utilisé par le moteur de base de données lui-même. Ce TF est quasiment indispensable avec SQL Server 2000, se révèle parfois utile avec SQL Server 2005. Par contre de gros effort ayant été réalisé par les équipes de Microsoft sur ce point dans le SP3 et dans SQL Server 2008, vous n'aurez peut être pas à l'employer sur ces versions.
Ayant pu tester ces derniers temps la version 2008, les gains en termes de contention sur tempdb sont visibles.
Scénarios types : Contention dans tempdb
TF 1449 - SQL 2005 SP2 + CU - Porté Serveur uniquement ?
Relatif au Database Mirroring, permet la connexion indifféremment sur le partenaire ou le miroir tel que définie dans la chaîne de connexion. En effet dans le cas où le couple serveur / base de données ciblé par le partie « failover partner » de la chaîne de connexion n'est pas activé pour la mise en miroir la connexion à ce serveur sera refusé. Ce qui peut être problématique si par exemple le serveur principal n'est plus actif, parce que le matériel est HS et que dans ce cas vous souhaitez peut être désactivé cette mise en miroir.
Ce Trace Flag évitera d'avoir à reconfigurer les chaîne de connexion pour les faire pointer vers le « nouveau » serveur actif le temps de récupérer votre ancien serveur.
Scénarios types : Mise en miroir de 2 serveur avec panne sévère sur le serveur principal et limiter la modification des paramètres de connexion au serveur.
TF 7646 - SQL 2008 - Porté Serveur uniquement
Equivalent du NOLOCK pour les mises à jour des Index FullText. Ce Trace Flag profite des améliorations apporté au moteur de recherche intégral de SQL Server dans cette version 2008 en permettant de lire l'index pendant qu'il est remis à jour.
Il a les mêmes défauts que le NOLOCK : risque de lecture erronée, mais peu sur des serveurs très fréquemment consultés et mis à jour s'avérer très utilise pour supprimer les blocages.
Scénarios types : Utilisation du moteur de recherche intégral, avec suivi automatique des mises à jour et fréquentes consultation de l'index.
TF 7662 - SQL 2008 - Porté Serveur uniquement
Permet de supprimer la dégradation volontaire des algorithmes de recherche intégral (Full Text) sur les recherches à base de données à base de caractères génériques, par exemple : « cod* » (pour codes, codification, etc.). Cette dégradation volontaire est bien visible lors de l'exécution des requêtes et j'ai pu avoir à maintes reprises des requêtes en timeout à cause de cela.
En contrepartie l'activation de ce TF nécessite une bonne quantité de mémoire pour exécuter ce type de requêtes qui génère par nature une grande liste de résultats.
Scénarios types : Amélioration des recherches de texte intégrale (pas les LIKE ca n'a rien à voir) avec caractères génériques.
TF 834 - SQL 2005 / 2008 - Porté Serveur uniquement
Utilisation des Large Page Allocator, permet au processeur d'allouer de plus grande pages de mémoire en 64 bits pour SQL Server. Ceci particulièrement intéressant sur des systèmes ayant beaucoup de mémoire car le processeur doit procéder à la translation des adresses physique en logique et inversement. Même s'il dispose d'un cache pour cela, ce dernier est limité en quantité et une grande quantité de mémoire peut s'avérer être un handicap.
Nécessite que le compte de service de l'instance possède le privilège « Lock Page in Memory » et que les valeurs « min server memory » et « max server memory » du serveur soient renseignées. De plus la totalité de la mémoire demandée est allouée au démarrage de l'instance, ce qui peut rallonger le temps de démarrage de l'instance.
Scénarios types : Optimisation des performances sur des serveurs ayant plus de 64 Go de mémoire en 64 bits.
Cette liste est bien entendue non exhaustive mais peut grandement aider dans certaines situations types.
Bonne configuration…
Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
