Discussion :

[Philippe Robert]

* Bonjour, *

J'ai lu récemment dans le livre "MCTS 70-432 Implémentation et maintenance de SQL Server 2008" qu'und pratique courante consistait à créer un fichier Tempdb multiple et ce, autant qu'il y a de processeurs.

Dans mon cas, on a parmi nos serveurs, un serveur disposant de 2 quadcore, donc 8 processeurs. J'en déduit donc que tempdb devrait être scindé en 8 groupes (1 mdf et 7 ndf).

Mais dans ce genre de configuration, je ne comprends pas très bien comment SQL décide dans quel groupe il va crééer ses tables temporaires.

Pouvez-vous m'éclairer un peu plus à ce sujet ?

* Merci *

[hmira]

Ce qui compte, c’est le nombre de fichiers et non pas le nombre de groupe de fichiers. Il semble même que tempdb supporte un seul groupe de fichier pour les fichiers de données et un seul groupe de fichier pour les fichiers de log.

Revenons aux fichiers de données. Effectivement, il est recommandé, pour la base tempdb, d’avoir les caractéristiques suivantes :
- Avoir un nombre de fichiers de données égale au nombre de processeurs.
- Les fichiers de données, doivent avoir tous exactement la même taille sur le disque et avoir un FILEGROWTH égale à zéro. (1)
- Les fichiers de données doivent être sur des disques séparés et rapide.

Les recommandations ci-dessus ont pour objectif d’aider à résoudre les problèmes potentiels de performance qui sont dus aux opérations I/O. Augmenter le nombre de fichiers permet d'éviter les contentions sur les allocations de pages (GAM, SGAM, PFS ..).
Ce n'est qu'une recommandation, le nombre de fichiers peut ne pas être le même que le nombre de processeurs.

(1) Le calibrage égal des fichiers de données est essentiel parce que l'algorithme de remplissage proportionnel est basé sur la taille des fichiers. Si les fichiers de données sont créées avec des tailles inégales, l'algorithme de remplissage proportionnel essaie d'utiliser le même fichier ayant la plus grande taille, ce qui engendrerait des contentions et irait à l’encontre du but recherché.

Exemple :
Prenons le cas de 2 Quad-Core, soit l’équivalent de 8 processeurs. Supposons que pour le bon fonctionnement de vos applications, vous avez besoins d’une base tempdb d’une taille égale à 8 Go.

Dans cet exemple, nous allons donc définir 8 fichiers de données de 1 Go chacun.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
ALTER DATABASE tempdb 
   MODIFY FILE (NAME = N'tempdev', SIZE = 1024MB, FILEGROWTH = 0); 
 
ALTER DATABASE tempdb 
   ADD FILE (NAME = N'tempdev2', 
   FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdev2.ndf',
   SIZE = 1024MB, 
   FILEGROWTH = 0) ; 
 
... (et ainsi de suite) ...
 
ALTER DATABASE tempdb 
   ADD FILE (NAME = N'tempdev7', 
   FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdev7.ndf',
   SIZE = 1024MB, 
   FILEGROWTH = 0);

Attention : le fait de définir FILEGROWTH à 0 peut vous poser de gros problème si vous avez mal estimé le volume (ou la taille) de la base tempdb, compte-tenu de la charge du Serveur. En effet, si cette taille est atteinte, aucun fichier de donnée ne pourra s’agrandir et vous imaginez aisément les conséquences catastrophiques !

A+

[mikedavem]

Bonsoir,

Effectivement, cela fait partie des bonnes pratiques mais ce n'est pas forcément obligatoire. Cela dépend fortement de la charge et de la façon dont est utilisée tempdb. (Si la base tempdb est un goulet d'étranglement cela peut vouloir dire aussi que le code de l'application est à revoir ...)

Le fait d'allouer plusieurs fichiers de même taille pour cette base réduit en effet les contentions sur les allocations de page (cf les propos de himra). Il faut également tenir compte des effets négatifs :

- le fait d'avoir plusieurs fichiers implique que plusieurs pages IAM peuvent être allouées à un même objet, ce qui peut provoquer un coût plus important lorsqu'il s'agit de switcher pour accéder aux pages de ce même objet.
- un effort supplémentaire est requis pour gérer ces fichiers d'autre part.

Il est important en tout cas de réserver des disques rapides pour cette base de données et voir même d'allouer un ou plusieurs axes physiques à son unique usage.

++

[hmira]

Merci David (mikedavem) pour ces précisions.

Pour info, pour ceux qui veulent approfondir le sujet, ci-dessous 3 liens de référence :
Working with tempdb in SQL Server 2005
Capacity Planning for tempdb
tempdb and Index Creation

A+