tempdb : nombres de fichiers

Version imprimable

14/06/2016, 17h35
Bernardos

tempdb : nombres de fichiers

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
14/06/2016, 22h21
elsuket

Bonjour,

Citation:

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.

Citation:

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.

@++ ;)
15/06/2016, 08h31
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/
29/06/2016, 11h55
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

Citation:

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
29/06/2016, 14h05
mikedavem
Hello,

Citation:

Envoyé par lbernard

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:

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
------------------------------------------

Citation:

Envoyé par lbernard

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.

++
29/06/2016, 18h10
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
29/06/2016, 18h43
mikedavem

Citation:

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?

C'est une recommandation mais attention il faut bien la lire ... si et seulement si problème de contention d'allocation alors il faut augmenter petit à petit et monitorer bien sûr.
Il arrive un moment où de toute façon on arrive à un point où l'ajout de fichiers supplémentaires ne permet plus de gain. Tu risques donc perdre en place disque utilisés inutilement, ajouter de l'overhead quant à la gestion du round robin + algorithme de remplissage proportionnel dans les fichiers + activer disque en plus en I/O aléatoire.

Le but est de trouver une bonne balance. Dans ton cas je commencerai par activer le T1118 et ensuite ajouter 8 fichiers et je surveillerai si cela est suffisant (perfmon, DMVs ... peut importe).

++
29/06/2016, 19h19
Bernardos

Citation:

Le but est de trouver une bonne balance. Dans ton cas je commencerai par activer le T1118 et ensuite ajouter 8 fichiers et je surveillerai si cela est suffisant (perfmon, DMVs ... peut importe).

en fait ma question n'était pour une base précisément, c'est plutôt pour affiner ma manière de travailler et de conseiller mes clients.
Là en l’occurrence tu m'as posé la question, j'étais chez un client et donc j'ai lancé la requête chez lui.
C'est un client chez qui je ne vais pas depuis longtemps mais en l’occurrence, il y a 8 fichiers tempdb...sauf que le Flag T1117 n'était pas activer.mais c'est loin d'être le pire de ce qu'ai vu chez lui.
Il y a effectivement pas mal de contention au niveau du tempdb(mais pas que) mais la cause est multiple. les disques sont formaté n'importe comment, il y a un autre tempdb d'une autre instance sur le même disuqe,...
Ce que je ferai c'est que j'activerai le Flag T1118 la prochaine fois que je bosse chez lui et je mesurerai l'impact.

en fait, j'aurais encore une petite question. J'ai l'habitude d'isoler le tempdb sur un disque, le journal des transaction de la base sur un autre, et les données de la base sur un 3ème. dans des cas de luxe je peux isoler le journal des transaction sur un disque isolé mais c'est rare. Que faire de ce fichier ldf ? le placer avec le journal de transaction de la base de données ou le placer avec le tempdb.mdf?

Merci d'avance,
Cordialement,
Loïc
29/06/2016, 22h47
janlouk

Un consultant Microsoft vient chez mon client actuel et nous a conseillé d'activer le T1118, que le seul risque était de perdre un peu d'espace. Mais dans notre cas, il y a casi jamais de fichiers secondaires donc pas d'impact.
29/06/2016, 22h55
janlouk

Citation:

Envoyé par lbernard

en fait, j'aurais encore une petite question. J'ai l'habitude d'isoler le tempdb sur un disque, le journal des transaction de la base sur un autre, et les données de la base sur un 3ème. dans des cas de luxe je peux isoler le journal des transaction sur un disque isolé mais c'est rare. Que faire de ce fichier ldf ? le placer avec le journal de transaction de la base de données ou le placer avec le tempdb.mdf?

Moi perso, je ne comprends pas trop ta question vu que le .ldf est ton journal de transaction.

Ou alors tu ne voulais pas dire le journal de transaction, mais le fichier de données. Donc que faire si on ne sait pas séparer les .mdf et .ldf.

Je dirais que ça dépend si tu es en FULL ou SIMPLE. Je dirais que je laisserais la tempDB seule vu que c'est celle qui est la plus sollicitée. Mais je laisse les experts, David et Nicolas confirmer ou non.
30/06/2016, 00h18
elsuket

Citation:

Mais je laisse les experts, David et Nicolas confirmer ou non.

On n'est pas les seuls, et tous les retours d'expérience sont intéressants.

Pour ma part je ne mets jamais automatiquement plusieurs fichiers à TempDB, et je suis la démarche que décrit David.
Si je vois de la contention d'accès aux pages de traçage de l'allocation dans TempDB (PAGELATCH_XX ou LATCH_XX), j'ajoute 3 fichiers, et je continue de regarder.

Il ne m'est arrivé qu'une seule fois de devoir en ajouter encore 4, mais cela dépend directement de la charge de travail, de la qualité du modèle, des requêtes, des index, etc, ...
Pour ce cas là c'était une base pauvrement modélisée, indexée, avec des requêtes ... comme le modèle : moche.

En ce qui concerne le placement des fichiers, l'idéal est d'avoir des LUN distincts. Ensuite cela dépend de l'instance :

- si elle physique et qu'elle n'héberge qu'une seule base, ou que seule une base seulement sera très utilisée, et que j'ai autant de LUN qu'il en faut idéalement, je vais répartir les fichiers suivant le même schéma
- si elle dispose d'un volume SSD, alors je placerai certainement tous les fichiers de TempDB dessus
- si elle héberge plusieurs bases, je ferai en sorte de mélanger les fichiers de données et du journal des transactions, le premier étant accédé de façon "aléatoire", le deuxième séquentiellement.
- si elle est virtualisée, je vais discuter un moment avec les administrateurs système ;)
- ...

Encore une fois ceci n'est que mon retour d'expérience. Il se peut que vous observiez des résultats meilleurs sur des plateformes configurées différemment ou utilisant un stockage que je ne n'ai tout simplement jamais eu l'occasion d'utiliser. L'idéal, comme bien souvent, est d'avoir une baseline et de tester. On peut réaliser cela avec un test TPC-E et HammerDB, par exemple.

@++ ;)
30/06/2016, 10h27
olivtone

Citation:

Envoyé par elsuket

- si elle héberge plusieurs bases, je ferai en sorte de mélanger les fichiers de données et du journal des transactions, le premier étant accédé de façon "aléatoire", le deuxième séquentiellement.

@++ ;)

salut a toi

alors ca je n'ai jamais compris pourquoi....:D
30/06/2016, 13h19
mikedavem

Citation:

en fait, j'aurais encore une petite question. J'ai l'habitude d'isoler le tempdb sur un disque, le journal des transaction de la base sur un autre, et les données de la base sur un 3ème. dans des cas de luxe je peux isoler le journal des transaction sur un disque isolé mais c'est rare. Que faire de ce fichier ldf ? le placer avec le journal de transaction de la base de données ou le placer avec le tempdb.mdf?

En fait tout va dépendre du sous système disque sous jacent. Placer un fichier sur un disque aujourd'hui ne veut plus vraiment dire grand chose car tout est abstrait ou virtualisé.
La question d'isoler une activité de bases de données par rapport à une autre est toujours une bonne pratique mais tout dépend comment le stockage (il faut comprendre stockage ici depuis le serveur jusqu'aux disques).

Prenons un exemple simple que je rencontre tout le temps maintenant. On installe un serveur qui possède N disques sur lesquels on place nos différents fichiers mdf, ldf et tempdb. Au final on se rend compte très vite que derrière on travaille avec un gros pool de disque côté stockage. Est-ce une mauvaise chose? Pas forcément à partir du moment où la configuration de stockage est capable d'encaisser la charge et délivrer les temps de réponses attendues. D'ailleurs bien souvent on se retrouve avec des SAN faisant du write-back depuis les caches en SSD avec des performances plus que correctes.

Si je prends un autre cas. Je veux isoler tempdb et les fichiers de bases de données mais du point de vue stockage comment cela va se répartir? est-ce que j'aurais 6 disques en RAID10 pour les fichiers de bases de données et 2 disques uniquement pour tempdb? Est-ce que dans ce cas il ne vaudrait mieux pas avoir une grappe RAID avec 8 disques et profiter de l'ensemble pour l'ensemble de mes fichiers ? Après tout nous faisons de l'I/O purement aléatoire et asynchrone que ce soit pour tempdb ou pour mes bases de données utilisateurs ...

Quid de la virtualisation ... comment je configure mes datastore, mes contrôleurs ISCSI pour avoir une bonne performance ... sachant que de toute façon si mon datastore est partagé avec d'autres machines virtuels ou que mon pool de disque au niveau stockage est également partagé avec d'autres, que je fasse de l'I/O aléatoire ou séquentiel .. du point de vue stockage tout sera vu comme de l'I/O aléatoire ...

De mon point de vue ce n'est plus aussi simple de demander 3 disques depuis Windows et d'être sûr d'isoler les différents types d'activités. Il faut compter avec toute l'artillerie qu'il existe derrière.

Citation:

alors ca je n'ai jamais compris pourquoi....

Tout simplement parce que tu mélanges de l'aléatoire avec du séquentiel. L'utilisation du journal dans tempdb est beaucoup plus optimisée que pour les autres bases de données utilisateurs (pas besoin de recovery). Il y a donc généralement beaucoup moins d'activité dans ce fichier que pour les autres bases de données. Mélanger les 2 types d'I/O (aléatoire et async vs séquentiel et sync) dans ce cas peut faire sens sous réserve d'avoir validé par des tests bien entendu.

++
30/06/2016, 15h17
SQLpro

Citation:

Envoyé par elsuket

...
- si elle est virtualisée, je vais discuter un moment avec les administrateurs système ;)

Moi il y a longtemps que je discute plus. J'en tue un au hasard et aux autre je leur impose mon point de vue....

A +
30/06/2016, 19h54
Bernardos

Citation:

Envoyé par janlouk

Moi perso, je ne comprends pas trop ta question vu que le .ldf est ton journal de transaction.

En fait , j'ai peut être pas été très clair. voici le contexte
C: fichier system, répertoire d'installation de sql server, les bases system (hors tempdb)
E: pageFile
F: Tempdb.mdf
G: MaDatbase.mdf
H: MaDataBase.ldf
Dans un environnement généreux, j'ai un disque de plus pour le templog.ldf. c'est déjà rare que j'obtienne tous les disques que je demande.
donc dans le contexte ci-dessus. il me reste le fichier templog.ldf à placer. c'est quoi le mieux ? F,H ou autre?
Mettons nous dans un contexte Physique(et oui ca existe encore;) ) pour simplifier la question.

Moi j'ai toujours eu tendance à le mettre templog.ldf avec le MaDataBase.ldf donc Le H dans mon exemple.
Donc pour faire plus simple et plus clair, pourriez-vous (plusieurs c'est mieux) dire ce que vous feriez et pourquoi?

Merci d'avance

Loïc

ps: je parie que Elsuket va dire F ;)
30/06/2016, 21h57
mikedavem

Citation:

Dans un environnement généreux, j'ai un disque de plus pour le templog.ldf. c'est déjà rare que j'obtienne tous les disques que je demande.
donc dans le contexte ci-dessus. il me reste le fichier templog.ldf à placer. c'est quoi le mieux ? F,H ou autre?
Mettons nous dans un contexte Physique(et oui ca existe encore ) pour simplifier la question.

Est-ce que tu peux préciser ton contexte physique et généreux dans ce cas ? ;)

Quel lien entre tes disques vu depuis Windows et le stockage sous-jacent?
Du DAS ? du SAN? Combien de disques? LUNS? Nombre de paths? Vitesse des paths? RAID utilisé?
As-tu une idée de la performance que ton stockage est capable de fournir?
Quelle activité sur tes fichiers tempdb ou du moins sur ton ldf par rapport aux autres ? As-tu des métriques concrètes?

Sans se poser et répondre à ce genre de questions cela reste de la théorie ou bonne pratique sans fondement.

++
01/07/2016, 08h51
Bernardos

Citation:

Sans se poser et répondre à ce genre de questions cela reste de la théorie ou bonne pratique sans fondement.

On va enlever le "sans fondement", ce que je voudrais c'est justement avoir une série de bonnes pratiques (avec fondement)
il existe encore des serveurs physique qui travaillent sur des disques locaux. j'ai justement le cas chez un de mes clients aujourd'hui. c'est du raid 10 (pour les data, log,tempdb).
j'aimerais que tu (et les autres) répondent à ma question dans ce genre de contexte. et finalement, avoir votre cheminement et vos bonnes pratiques AVEC fondement dans n'importe quel contexte.
qu'est ce qui te fait mettre le templog à un endroit plutôt qu'un autre?
Merci d'avance
Cordialement,

Loïc
01/07/2016, 10h09
mikedavem

Citation:

il existe encore des serveurs physique qui travaillent sur des disques locaux. j'ai justement le cas chez un de mes clients aujourd'hui. c'est du raid 10 (pour les data, log,tempdb).

Tout à fait d'accord et pour pouvoir répondre convenablement il faut le contexte. Une bonne pratique reste toujours contextuelle et à adapter à la situation du client.

Citation:

qu'est ce qui te fait mettre le templog à un endroit plutôt qu'un autre?

Si je reprends ta répartition

C: fichier system, répertoire d'installation de sql server, les bases system (hors tempdb)
E: pageFile
F: Tempdb.mdf --> peut être placé ici
G: MaDatbase.mdf --> peut être placé ici
H: MaDataBase.ldf --> peut être placé ici

Tout dépend la charge sur tempdb log et la performance de ton stockage.
F: et H: semblent être un bon point de départ mais il faudra bien entendu monitorer (perfmon, sys.dm_io_virtual_file_stats ...) si tu n'as aucune idée de la charge I/O et que tu as un doute.

De manière générale je laisse le ldf avec les mdf pour tempdb quand un stockage est dédié à tempdb.
Cependant cela m'est déjà arrivé de placer le ldf vers d'autres disques (mdf ou ldf) dans certains cas car tempdb très utilisé par rapport aux autres bases (du genre 70% tempdb vs 30% pour les autres bases) et en me basant sur les statistiques d'utilisation I/O (IO + latence)

Citation:

votre cheminement et vos bonnes pratiques AVEC fondement dans n'importe quel contexte

Pour ma part, la seule bonne pratique que je peux donner c'est placer les fichiers et monitorer. On peut bien sûr suivre les recommandations Microsoft qui restent valables lorsqu'on a une seule base de données (recommandation contextuelle) . Isoler mdf, ldf et tempdb mais quid des instances multibases? Est-ce que l'on doit placer tous les ldf ensemble comme je le vois chez beaucoup de clients? Pas sûr que ce soit la meilleure pratique dans certains cas en fonction du stockage à disposition et de l'activité.

++