86
Chapitre 4. Optimisation des objets et de la structure de la base de données
tion en cache des tables). À partir de SQL Server 2005, les variables de type table et
les tables temporaires locales de petite taille sont gardées en cache mémoire, et pour
les tables de moins de 8 Mo, la page d’allocation (IAM) et une page de données sont
conservées en mémoire au cas où la table serait recréée.
Attention – Certaines pratiques empêchent SQL Server de cacher les tables temporaires. La modification de sa structure après création par exemple, ou si elles
sont utilisées dans du code SQL dynamique, ou un batch de requêtes plutôt que
dans une procédure stockée.
Si malgré cela, une contention se produit dans tempdb, les performances peuvent
s’en retrouver sérieusement affectées. C’est une situation rare, rencontrée sur des serveurs très sollicités. Pour détecter une contention, vous pouvez surveiller les attentes
à l’aide de la vue de gestion dynamique sys.dm os waiting tasks, qui liste les tâches
en attentes. La contention des structures de données se traduit sur des attentes de
libération de latches sur des pages de PFS ou de SGAM. Les latches sont des verrous
légers de protection d’accès physique (voir section 7.2.1).
SELECT session_id, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type like 'PAGE%LATCH_%' AND resource_description like '2:%'
Des compteurs du moniteur de performances donnent aussi des indications sur les
latches en général. Ils se trouvent dans l’objet de compteurs SQL:Latches (reportezvous à la section 5.3).
Vous pouvez lire ce document pour obtenir plus de détails sur l’optimisation de
tempdb sur http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
Drapeau de trace 1118
Le document mentionné précédemment évoque le drapeau de trace 1118, qui placé globalement, change la méthode d’allocation de nouveaux objets dans tempdb pour éviter la contention
sur les SGAM. Ce drapeau de trace est d’usage délicat, et peut être contre performant. Référezvous à cet article de blog pour une analyse raisonnée : http://sqlblog.com/blogs/linchi_shea/
archive/2007/08/10/reduce-the-contention-on-tempdb-with-trace-flag-1118-take-2.aspx
Taille et utilisation de tempdb
Les objets qui peuvent prendre de la place dans tempdb sont des objets utilisateur
volumineux, comme des tables temporaires. Les tables temporaires sont supprimées
à la fin de la session qui les a créées. Si une session reste ouverte, et qu’une table
temporaire n’a pas été explicitement supprimée à l’aide d’un DROP, elle peut occuper
de l’espace un certain temps. C’est donc une habitude importante à prendre, de
supprimer une table temporaire lorsqu’on n’en a plus besoin. De même, si la table
Chapitre 4. Optimisation des objets et de la structure de la base de données
tion en cache des tables). À partir de SQL Server 2005, les variables de type table et
les tables temporaires locales de petite taille sont gardées en cache mémoire, et pour
les tables de moins de 8 Mo, la page d’allocation (IAM) et une page de données sont
conservées en mémoire au cas où la table serait recréée.
Attention – Certaines pratiques empêchent SQL Server de cacher les tables temporaires. La modification de sa structure après création par exemple, ou si elles
sont utilisées dans du code SQL dynamique, ou un batch de requêtes plutôt que
dans une procédure stockée.
Si malgré cela, une contention se produit dans tempdb, les performances peuvent
s’en retrouver sérieusement affectées. C’est une situation rare, rencontrée sur des serveurs très sollicités. Pour détecter une contention, vous pouvez surveiller les attentes
à l’aide de la vue de gestion dynamique sys.dm os waiting tasks, qui liste les tâches
en attentes. La contention des structures de données se traduit sur des attentes de
libération de latches sur des pages de PFS ou de SGAM. Les latches sont des verrous
légers de protection d’accès physique (voir section 7.2.1).
SELECT session_id, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type like 'PAGE%LATCH_%' AND resource_description like '2:%'
Des compteurs du moniteur de performances donnent aussi des indications sur les
latches en général. Ils se trouvent dans l’objet de compteurs SQL:Latches (reportezvous à la section 5.3).
Vous pouvez lire ce document pour obtenir plus de détails sur l’optimisation de
tempdb sur http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
Drapeau de trace 1118
Le document mentionné précédemment évoque le drapeau de trace 1118, qui placé globalement, change la méthode d’allocation de nouveaux objets dans tempdb pour éviter la contention
sur les SGAM. Ce drapeau de trace est d’usage délicat, et peut être contre performant. Référezvous à cet article de blog pour une analyse raisonnée : http://sqlblog.com/blogs/linchi_shea/
archive/2007/08/10/reduce-the-contention-on-tempdb-with-trace-flag-1118-take-2.aspx
Taille et utilisation de tempdb
Les objets qui peuvent prendre de la place dans tempdb sont des objets utilisateur
volumineux, comme des tables temporaires. Les tables temporaires sont supprimées
à la fin de la session qui les a créées. Si une session reste ouverte, et qu’une table
temporaire n’a pas été explicitement supprimée à l’aide d’un DROP, elle peut occuper
de l’espace un certain temps. C’est donc une habitude importante à prendre, de
supprimer une table temporaire lorsqu’on n’en a plus besoin. De même, si la table