Discussion :

[Bernardos]

Bonjour,
J'ai eu l'habitude de lire qu'il fallait créer 1 fichier par coeur ou d'autres versions disaient 1 fichier par CPU physique avec un maximum de 8.
Déjà je voudrais votre avis sur cette question : par coeur ou par cpu physique?
ensuite, j'ai lu dans la bible francophone du sql (http://sqlserver.developpez.com/livr...is#L2212135920)
qu'il fallait que le degré maximum de parallélisme soit égale aux nombres de fichiers.
Ma lecture est elle exacte?
si on a x fichiers de tempdb et un parallélisme à 1, est ce utile quand même, completement inutile ou carrément dangereux?
Merci d'avance,
cordialement

Loïc

[elsuket]

Bonjour,

J'ai eu l'habitude de lire qu'il fallait créer 1 fichier par cœur ou d'autres versions disaient 1 fichier par CPU physique avec un maximum de 8.

C'est effectivement une bonne pratique. Un CPU peut comporter plusieurs cœurs, et une carte mère peut avoir plusieurs sockets.

Arrêtons nous un moment sur la recommandation d'exploser TempDB sur plusieurs fichiers de données. Toute donnée est stockée dans des pages, dont la taille est de 8 Ko. Elles sont logiquement groupées sous la forme de 8 pages, que l'on appelle étendue : c'est l'unité de travail du moteur de stockage de SQL Server. Pour tracer l'allocation de ces pages, des pages utilitaires sont réparties dans le fichier pour tracer où se trouvent les étendues déjà allouées. Elles stockent pour cela un bitmap qui indique si l'étendue est allouée ou non. Pour simplifier, disons qu'on en trouve une pour toute tranche de 4Go.

TempDB est une base de données très particulière, puisqu'elle peut être utilisée par toutes les requêtes s'exécutant dans le contexte de n'importe laquelle des bases de données hébergées par l'instance. Cette base de données supporte les tables temporaires locales (#) et globales (##), les variables de type TABLE et TVPs (DECLARE @t TABLE), etc, etc, mais aussi les spools, qui peuvent être vus comme des zones de stockage de calculs intermédiaires qui servent à optimiser l'utilisation d'un algorithme de jointure, d'agrégat, de groupement, ... Cette base de données est donc optimisée pour allouer et désallouer des objets très rapidement (en fait par cache + marquage pour réutilisation).

On ne trouve les pages d'allocation que tous les 4Go, et bien souvent les fichiers de données de la base de données TempDB sont plus petits. Donc si le nombre de requêtes ayant recours à de l'allocation d'espace dans TempDB est élevé, il peut en résulter une contention d'accès à ces pages utilitaires. D'où l'idée d'augmenter le nombre de fichiers, pour augmenter la surface d'attaque d'allocation des objets temporaires. En situation de contention d'accès à ces pages, on observe des attentes de type PAGELATCH_XX et LATCH_XX. On doit alors décortiquer la ressource sous-jacente : ceci peut se faire à l'aide de la colonne resource_description de la vue de gestion dynamique sys.dm_os_waiting_tasks, qui montre une valeur dont le masque est 2:[fileID]:[pageID], où 2 est l'identifiant de la base de données TempDB (cf. sys.databases). Si donc plusieurs lignes ont la même valeur de ce masque, il y a fort à parier que l'on ait affaire avec de la contention d'accès à ces pages.

Partant de là, la recommandation d'avoir autant de fichiers de données pour TempDB que le degré maximal de parallélisme est fondé sur le fait qu'alors le moteur "affinitise" un core à un fichier de données, soit pour l'exécution d'une requête qui parallélise, soit pour les requêtes sérialisées. Ces fichiers doivent par ailleurs être tous d'égale taille, de façon à ce que SQL Server puisse les remplir proportionnellement : une nouvelle fois, c'est une optimisation qui permet d'augmenter la surface d'attaque d'allocation des objets temporaires.

si on a x fichiers de tempdb et un parallélisme à 1, est ce utile quand même, completement inutile ou carrément dangereux?

Le fait d'empêcher le parallélisme n'empêche pas SQL Server d'utiliser tous les fichiers alloués à la base de données TempDB.
Pour être plus direct : la parallélisation d'une requête et l'allocation d'espace dans TempDB sont deux fonctionnalités distinctes mais qui interagissent.

En revanche, je pense que désactiver la parallélisation est une mauvaise chose, que ce soit pour une charge de travail de type OLTP ou non : elle peut tout à fait bénéficier à certaines requêtes. Il me semble plus intéressant, et mon expérience me le confirme, d'élever le seuil de coût pour la parallélisation de 5, sa valeur par défaut qui est très basse, à 50 ou plus, par exemple. Pour aller plus finement, on peut capturer répétitivement le coût médian de toutes les requêtes en cache, puis extraire la médiane de celles-ci pour trouver la valeur optimale pour cette option. Et si l'on trouve une requête qui ne devrait pas paralléliser après avoir scruté son plan d'exécution, on peut toujours utiliser l'indicateur de requête OPTION (MAXDOP 1).

Notons que jusqu'à SQL Server 2014 inclus, le degré maximal de parallélisation n'est configurable que pour toute l'instance.
Dès SQL Server 2016, il est configurable au niveau de la base de données.

@++

[janlouk]

Bonjour Loïc,

J'avais +- la même question que toi il y a quelques temps. J'ai vu aussi d'autres postes mais ceux-là je ne les retrouve pas.

http://www.developpez.net/forums/d15...mbre-fichiers/

[Bernardos]

Bonjour,
Je reviens sur ce sujet et me/vous repose la question
Nombre de fichier temps db = le nombre de processeur ou = le nombre de coeur(plafonné à 8) ?
@elsuket tu n'as n'as pas pas vraiment répondu à cette question mais j'avais le sentiment que tu poussais plus vers le nombre de coeur????

d'autre par dans cette discussion je lis

mais nooooon....

avec l’augmentation du nombre de cœurs, on a laissé tomber. La recommandation de Microsoft est maintenant, un fichier par socket.

donc je n'ai pas vraiment de réponse à la question nombre de fichier tempdb= nombre de coeur, nombre de cpu ou nombre de socket?

Question subsidiaire : Faut il ou non activer le trace Flag 1118?
Merci d'avance pour vos avis,
Cordialement,

Loïc

[mikedavem]

Hello,

Bonjour,
Je reviens sur ce sujet et me/vous repose la question
Nombre de fichier temps db = le nombre de processeur ou = le nombre de coeur(plafonné à 8) ?
@elsuket tu n'as n'as pas pas vraiment répondu à cette question mais j'avais le sentiment que tu poussais plus vers le nombre de coeur????

donc je n'ai pas vraiment de réponse à la question nombre de fichier tempdb= nombre de coeur, nombre de cpu ou nombre de socket?

Si tu es dans un environnement virtuel, ce qui va compter c'est le nom de processeurs virtuels que voit ton instance SQL au niveau virtuel (VCPUs). Si tu es en environnement physique, c'est le nombre de processeurs physiques ou logiques (en fonction de ton architecture processeur sockets + cores) que voit ton instance SQL. Au final tu l'auras compris, ce qui compte c'est le nombre de processeurs que tu vois depuis ton instance SQL (ce dernier n'est pas au courant de savoir s'il s'agit de sockets, de cœurs logiques)

Code :

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

1
2
3
select COUNT(*)
from sys.dm_os_schedulers
where status = 'VISIBLE ONLINE'

Une règle générale donnée par Microsoft en cas de contention et qui marche à peu près dans tous les cas

------------------------------------------
Nb processors <= 8 --> 1 file / processor
> 8 --> 8 files + number of data files by multiples of 4
------------------------------------------

Question subsidiaire : Faut il ou non activer le trace Flag 1118?

Dans les faits si tu n'as pas de contention d'allocation de pages constatée dans tempdb je dirais que non mais il n'y a pas vraiment de contre indication à ne pas l'utiliser. Le seul impact est de voir tes bases de données utilisateurs grossirent un peu surtout si tu as beaucoup d'objects < 64KB (T1888 est un trace flag de niveau serveur qui influence l'ensemble des bases de données) . C'est une recommandation que nous donnons chez nous et nous n'avons jamais vraiment constaté de problème à l'utiliser et il faut savoir qu'à partir de SQL Server 2016 les allocations sont toujours uniformes pour tempdb par défaut sans avoir à mettre un quelconque traceflag 1118.

++

[Bernardos]

Merci à tous pour vos réponses.
@mikedavem
le résultat de la requête chez un de mes clients est 32
Donc si je suis ton calcul ca fait 8 + ((32-8)/4 ) = 8+6 =14... y a pas un risque de contre performance lorsqu'on augmente de trop le nombre de fichiers?
il s'agit d'une règle générale ou d'une recommandation?
oui pour 2016 j'avais vu cette info.
Cordialement,
Loïc