SQL 2008R2 + Datafiles

[davy.g]

Bonjour,

J'ai une base qui est composé de 2 group files : PRIMARY et INDEX.
Chaque group file est composé d'un datafile.

J'ai ajouté 1 datafile supplémentaire à chaque groupfile et relancer un job de recréation des indexs en pensant que mes data allaient être dispatchées entre mes différents datafiles.
Mon 1er datafile du groupe PRIMARY fait 500Go environ. Après le job de recréation d'index mon second datafile ne fait que 2Go.

Comment faire pour que le dispatch de mes datas se fassent de manière sensiblement identique entre mes différents datafiles?

Merci pour votre aide.

[elsuket]

Bonjour,

Je ne suis pas sûr qu'en reconstruisant vos index vous parveniez à répartir les données dans les fichiers de données qui composent votre groupe de fichiers.

La règle c'est qu'il faut que les fichiers fassent très exactement la même taille pour que les données soient réparties de façon à peu près équitable en terme de volume dans les fichiers qui composent le groupe de fichiers.

Il vous faut donc tailler le fichier à la même taille que l'actuel.

@++

[davy.g]

Y a t il un moyen alors de découper ma base de 500Go en 3 datafiles de 200Go chacun pour mon groupe PRIMARY et en faisant la même chose pour mon groupe INDEX ?

Merci.

[elsuket]

Je ne pense pas que cela soit possible pour le groupe de fichiers PRIMARY, s'il ne contient aucun index.
En effet, à la reconstruction d'un index cluster, qui vous aurait permis de déplacer les tables d'un groupe de fichiers à l'autre, on ne peut pas spécifier de fichier : on spécifie seulement le nom du groupe de fichiers, et le moteur de stockage fait le reste ...

En revanche pour le groupe de fichiers INDEX, je pense effectivement qu'ajouter des fichiers de taille identique sera la bonne, mais il vous faudra reconstruire les index pour que cela se fasse.

Je suppose que tous vos fichiers sont sur des axes physiques différents.
Pour connaître la taille occupée par les données dans chacun de vos fichiers, vous pouvez utiliser cette requête.

@++

[davy.g]

Les index cluster se trouvent dans le groupe Primary.
Dans ce groupe j'ai aujourd'hui un datafile de 500Go.

[SQLpro]

Le plus simple est de créer un autre base à côté et de migrer les données dedans.

A +

[elsuket]

Oui mais c'est un peu brutal

Reconstruire les index cluster dans le filegroup INDEX après lui avoir ajouté un second fichier devrait aider

@++

[mikedavem]

Brutal pas tant que cela

En fait il faudra jouer au jeu des vases communiquants pour que les données soient réparties correctement. Le fait de reconstruire l'index dans le même groupe de fichiers en ajoutant un fichier ne changera pas grand chose. Les données sont déjà dans le vase et elles y resteront.

Pour pouvoir bénéficier de l'algorithme de répartition de SQL Server il faudra à chaque fois créer un nouveau groupe de fichiers avec les fichiers désirés ayant la même taille, la même valeur d'expansion et reconstruire les index dans ce nouveau groupe. Ceci est valable pour les données des tables + index non clusters.

Solution un peu moins brutale que celle proposée par SQLPro mais finalement l'idée reste bien la même.

++

[Ptit_Dje]

Bonjour,

Vous pouvez essayer le DBCC shrinkfile (filename, EMPTYFILE).

depuis BOL:

Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.

http://msdn.microsoft.com/en-us/library/ms189493.aspx

Si vous avez de nouveaux fichiers proprement tailles, vos datas devraient bouger toutes seules ou il faut et vous permettre d'enlever le fichier en question.

Preferez un test avant de lancer ca en prod, j'ai jamais essaye encore...

[mikedavem]

Cela ne fonctionnera pas pour le groupe PRIMARY car toutes les données du premier fichier ne pourront pas être déplacés notamment les données concernant les tables systèmes.

De plus vider un fichier via cette méthode ne permet pas de répartir de façon équitable les données dans les fichiers (il faut le savoir )

++

[Ptit_Dje]

Ca pourrait permettre de reduire au maximum le fichier, ensuite de le verouiller (verouiller l'autogrow) et avoir la repartition des donnees plus ou moins egale dans les autres fichiers.

Ou bien alors le shrinker a 200MB comme les autres, ce qui devrait engendrer un mouvement de donnees aussi.

A tester

[Ptit_Dje]

Envoyé par mikedavem

De plus vider un fichier via cette méthode ne permet pas de répartir de façon équitable les données dans les fichiers (il faut le savoir )

Ah bon ?

Quelle est la methode de repartition des donnees dans les fichiers d'un meme filegroup lorsque DBCC shrinkfile est utilise ?
Est ce qu'il y a une doc quelque part ? Ca m'interesserait de lire ca.

[mikedavem]

En fait on ne contrôle pas dans ce cas le remplissage des fichiers.
Pour avoir déjà tester, il n'est pas forcé d'avoir un 1er fichier rempli puis le second avec le reste des données ...

Mais personnellement je trouve beaucoup plus simple dans ce cas de créer des FILEGROUP à côté avec les fichiers correctement dimensionnés et transférer les données d'un FILEGROUP à un autre en reconstruisant les index. Cette méthode est je trouve plus propre car elle permet en même temps d'avoir des index non fragmentés. La méthode SHRINKFILE ne garantit pas cela

++

[Ptit_Dje]

Envoyé par mikedavem

Mais personnellement je trouve beaucoup plus simple dans ce cas de créer des FILEGROUP à côté avec les fichiers correctement dimensionnés et transférer les données d'un FILEGROUP à un autre en reconstruisant les index. Cette méthode est je trouve plus propre car elle permet en même temps d'avoir des index non fragmentés. La méthode SHRINKFILE ne garantit pas cela
++

Plus simple je sais pas - une seule commande pour shrinker c'est plus simple.

Plus propre et maitrise oui, je suis d'accord

[Jinroh77]

D'après tout cela, si l'on souhaite répartir certains objets sur des axes physique distinct, le mieux est alors, non pas de créer plusieurs DataFiles dans un même group, mais plutôt de créer plusieurs GrouFiles (autant que d'axes) et de ne créer qu'un seul DataFile dans chacun ?
On pourrait alors attribuer soit-même la répartition des objets sur les GroupFiles ?

[mikedavem]

L'attribution des objets se fait au niveau du groupefile et pas au niveau des fichiers directement. La réponse est donc oui. Pour contrôler l'attribution des objets il faut créer un FILEGROUP distinct sur chaque axe physique.

Cela peut être utile sur des tables concernés par des "grosses" jointures par exemple. Cela permet de paralléliser l'accès à ces tables et d'augmenter les temps de traitement.

++

[Jinroh77]

Merci pour la précision. Je continue donc un peu Désolé si je reprends des lieux communs. Davy ne m'en voudra pas de squatter un peu son topic

Si l'on affecte un GroupFile particulier, qui ne possède qu'un unique DataFile, à une table, c'est donc toute la table qui se retrouve sur un axe ?
Lors d'une requête utilisant une "grosse" jointure, les accès seront donc plutôt séquentiel ?

Par contre si la table est affectée à un GroupFile possédant plusieurs DataFiles, à force d'utilisation dans de "grosses jointures", est-ce que les données de la table se trouveront réparties entre les différents DataFiles afin de gérer un accès parallélisé aux données ?

[mikedavem]

Envoyé par Jinroh77

Si l'on affecte un GroupFile particulier, qui ne possède qu'un unique DataFile, à une table, c'est donc toute la table qui se retrouve sur un axe ?

Oui

Envoyé par Jinroh77

Lors d'une requête utilisant une "grosse" jointure, les accès seront donc plutôt séquentiel ? Par contre si la table est affectée à un GroupFile possédant plusieurs DataFiles, à force d'utilisation dans de "grosses jointures", est-ce que les données de la table se trouveront réparties entre les différents DataFiles afin de gérer un accès parallélisé aux données ?

Disons que si une table est répartie sur plusieurs fichiers l'accès à ses données pourront être parallélisés (plusieurs têtes de lectures pouvant en même temps récupérer les données) à condition bien sûr d'avoir un axe physique pour chaque fichier.

++

[mikedavem]

Un petit bémole à ce que j'ai pu poster sur le sujet.

La réindexation répartit plus ou moins bien les données à partir du moment où il ne concerne pas le premier fichier du groupe PRIMARY (ajout de 2 fichiers supplémentaires). Il faut bien penser par contre à dimmensionner correctement les fichiers cibles.

Cf billet

++

[elsuket]

Merci pour le billet, très clair et concis

@++