Discussion :

[Jinroh77]

Bonjour,
Je suis actuellement chez un client avec un certain nombres de bases de données MS SQL Server 2008, des problèmes dans tous les sens et... pour les bases volumineuses (la plupart) un filegroup découpé en plusieurs fichiers.
Ces fichiers sont simplement limités à 20 Go, mais avec une taille initiale de 5Go et des palliers de 10mo
Il n'y a pas de répartition spécifiques à travers ces fichiers : les index, tables etc. sont toutes dans le même filegroup qui est lui même en 4 fichiers.

Dans leurs arguments il y a une histoire d'avoir autant de fichiers que de cœurs CPU (le précédent serveur en avait 4, maintenant 16 mais ils n'ont rien changé là-dessus).

En quoi cet argument est-il valable ?
Le découpage a pour moi un argument dans le cas où ils sont stockés sur des axes différents, ce qui n'est évidement pas le cas ici.

[SQLpro]

Ce que vous dites est tout à fait exact. Si tous les fichiers sont sur le même axe physique, ceci est plutôt mauvais pour les performances, car SQL Server va créé un plan de requête en parallélisant les accès pour au final les faire en série sur le disque. Le plan sera plus couteux et plus long qu'en mono thread, sauf si toutes les données sont en RAM.

En sus, la recommandation n'est pas I fichier par cœur, mais un fichier par socket !

Enfin, limiter le parallélisme via le masque d'affinité ou MAX DOP est important, car trop de parallélisme nuit globalement aux perfs.

A +

[Jinroh77]

Merci pour la confirmation de ce que je pensais.
Pour la recommandation d'1 fichier par socket (cad 1 CPU quad core = 1 socket donc 1 fichier ?) cela est simplement du au fait que le système attribuera 1 socket par fichier ? donc gérant les accès disque de la même manière que les accès CPU ?

[dbaffaleuf]

En sus, la recommandation n'est pas I fichier par cœur, mais un fichier par socket !

Ah bon ?

[Jinroh77]

Petite précision, qu'en est-il de la TempDB ?
Même comportement ?

Merci.

[SQLpro]

Oui si elle est sollicité, non si elle ne l'est pas...

A +

[SQLpro]

Ah bon ?

extrait sur site MS :

"As a general guideline, create one data file per CPU."

autre extrait :
Lining up the number of data files with CPU’s has scalability advantages for allocation intensive workloads.
* It is recommended to have .25 to 1 data files (per filegroup) for each CPU on the host server.
* This is especially true for TEMPDB where the recommendation is 1 data file per CPU.
* Dual core counts as 2 CPUs; logical procs (hyperthreading) do not.

Du temps ou ceci a été écrit on en était encore au bi core.... Maisntant au exacore.... !

Ceci pour tempdb comme pour les Base de prod.

Cependant on peut monter jusqu'à n file sur n core.... Mais là je ne donne pas ma caution, sauf cas particulier !

Trop de parallélisme tuant le parallélisme !

[Jinroh77]

Oui si elle est sollicité, non si elle ne l'est pas...

A +

Les bases de prod sont alimentées sont par des SP dans tous les sens avec des tables #temp à gogo. Donc oui, plus que sollicité la pauvre TempDB.
Il va falloir négocier avec le dba qui a créé 10 fichiers de 2Go, tous sur le même disque pour la TempDB qui est elle-même avec les base de prod...

Merci pour tout ces détails.

[dbaffaleuf]

extrait sur site MS :

"As a general guideline, create one data file per CPU."

autre extrait :
Lining up the number of data files with CPU’s has scalability advantages for allocation intensive workloads.
* It is recommended to have .25 to 1 data files (per filegroup) for each CPU on the host server.
* This is especially true for TEMPDB where the recommendation is 1 data file per CPU.
* Dual core counts as 2 CPUs; logical procs (hyperthreading) do not.

Du temps ou ceci a été écrit on en était encore au bi core.... Maisntant au exacore.... !

Ceci pour tempdb comme pour les Base de prod.

Cependant on peut monter jusqu'à n file sur n core.... Mais là je ne donne pas ma caution, sauf cas particulier !

Trop de parallélisme tuant le parallélisme !

En fait je me demandais pourquoi tu dis qu'il vaut mieux créer un fichier par socket que par coeur.

Code :

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

1
2
*      It is recommended to have .25 to 1 data files (per filegroup) for each CPU on the host server.
*      Dual core counts as 2 CPUs; logical procs (hyperthreading) do not.[/I]

Donc ce serait plutôt l'inverse non ?

En dehors de ça, je ne vois pas trop l'intérêt de multiplier les fichiers simplement pour coller à une guideline et pas à un réel besoin. Si on constate de la contention sur PFS/GAM/SGAM là d'accord mais sinon bof bof. Il ya eu une discussion sur ce sujet déjà il y a qq jours (http://www.developpez.net/forums/d10...hiers-donnees/). Je remets les liens cités en fin de post pour info:

- http://www.sqlskills.com/BLOGS/PAUL/...-core-box.aspx
- http://www.sqlskills.com/BLOGS/PAUL/...ifference.aspx

[SQLpro]

En fait je me demandais pourquoi tu dis qu'il vaut mieux créer un fichier par socket que par coeur.

parce que à l'époque de ces écrit on avait pas autant de core...

Code :

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

1
2
*      It is recommended to have .25 to 1 data files (per filegroup) for each CPU on the host server.
*      Dual core counts as 2 CPUs; logical procs (hyperthreading) do not.[/I]

.25 = 1 fichier pour 4 CPU ! Non ???

Donc ce serait plutôt l'inverse non ?

A +

[dbaffaleuf]

ah oui alors si .25 d'accord.

[Jinroh77]

Je reviens juste rapidement sur la TempDB...

Le DBA ici, me répond que "outre la répartition sur des axes, la tempdb a besoin de plusieurs fichiers pour un accès en parallèle des processus (éviter les contentions des Pages de type wait PAGELATCH_UP )"

Il est vrai que ici la TempDB est très sollicitée, par plusieurs process en parallèle, qui travaillent à 95% avec des tables #temp.
Mais si l'on reste sur l'idée d'une répartition physique, l'accès à la tempDB, même si elle a besoin de paralléliser, elle aura des problème supplémentaires s'il n'y a pas de répartition physique ???

Le DBA m'explique qu'il a réalisé des essais en production et que cela était plus rapide en plusieurs fichiers (aucun moyen d'avoir plus d'infos sur les tests si ce n'est qu'un temps global de traitements quotidien...).

[dbaffaleuf]

Il faudrait une très grosse charge, donc il faut le mesurer. Celà dit ton DBA a raison. Typiquement les accès dans tempdb sont de nature à créer / détruire rapidement beaucoup d'objets. Il n'y a que 3 pages dans un fichier de données qui stockent l'allocation des pages de données (pages PFS pour Page Free Space, GAM pour Global Allocation Map et S-GAM pour Shared-Global Allocation Map). Ce sont respectivement les pages 1,2 et 3 de chaque fichier de données.

Si 100 sessions viennent créer /détruire rapidement des tables temporaires et quil n'y a qu'un seul fichier, alors ces 3 pages vont vite devenir un point de contention. Donc on créé plusieurs fichiers de données pour créer d'autres points d'entrées pour l'allocation. La contrainte est que chaque fichier doit faire strictement la même taille pour que l'allocation soit répartie et pas toujours sur le même fichier.

Le moyen de constater la contention sur ces pages est de regarder dans les colonnes wait_type / wait_resource de sys.dm_exec_requests, si tu vois qq chose comme :
PAGELATCH_* / 2:1:1 => PFS
PAGELATCH_* / 2:1:2 => GAM
PAGELATCH_* / 2:1:3 => S-GAM

2 est le dbid de tempdb, 1 est le fileid du fichier MDF pour tempdb et 1,2,3 sont les id de page de PFS, GAM et S-GAM.

Je n'ai jamais vu de config ou la charge nécessitait d'avoir des axes distincts pour chaque fichier de tempdb. Déjà créer plusieurs fichiers permet de limiter les problèmes principaux. La remarque se pose davantage pour les autres bases que tempdb.

[Jinroh77]

Merci pour la précision.
On n'est pas encore à 100 session simultanées mais plutôt une dizaine avec des chargements de fortes volumétries passant par la tempDB.

Je vais essayer d'avoir un œil pendant les chargements sur ses indicateurs. Mais vu que la temp est déjà découpée en 8 fichiers, je ne devrai rien trouver.

Reste à trouver l'équilibre entre x fichiers pour limiter la contention sur ces 3 premières pages et le fait de biaiser les plans en lui faisant croire à du parallélisme...

[dbaffaleuf]

Mais vu que la temp est déjà découpée en 8 fichiers, je ne devrai rien trouver.

Il y a des chances oui...

[Jinroh77]

Sur une des autres instances de cette magnifique architecture, je regarde ce qui se passe avec la vue sys.dm_exec_requests et je me retrouve avec un wait_type :
LCK_M_S et wait_ressource : KEY:2xxxxxxxxx(xxxxxxx)

J'en conclut que c'est un lock sur la tempdb ??

Dans les requêtes qui tournent je trouve de longs scripts du genre :

Code :

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

select ...... INTO #tabletemp

avec derrière des tables très volumineuses, des jointures à gogo, du filtre, du group by enfin un peu de tout, très consommateur.

Le fait de vouloir mettre toutes ces données dans le tempdb pourrait la bloquer ?

[dbaffaleuf]

- Est-ce que tu vois KEY:2:1:1, KEY:2:1:2 ou KEY:2:1:3 ? ou bien d'autres valeurs ?
- Quelle valeur de wait_time pour ces attentes ?
- Pour regarder quelles sont les sessions qui utilisent massivement tempdb, cf http://blog.capdata.fr/index.php/sql...s-dans-tempdb/

[Jinroh77]

- c'était plus d'autres valeurs du genre : KEY : 2 : 328936372 (6dbz7897).
- je n'ai plus les valeurs mais dans mes souvenirs c'était plutôt élevé, plusieurs minutes.
- je vais aller voir ton billet pour trouver plus d'info sur ces ressources.

Merci.