Discussion :

[djjej182]

Bonjour,

Etant de métier chef de projet logiciel et non administrateur de base de données, j'ai malheureusement fait plusieurs erreurs lors de la création d'une base de données sur SQL Server.
L'une d'entre elles a été, lors de la création de la base, d'opter pour la croissance automatique des fichiers et non pour des fichiers de taille fixe.
Après avoir lu bon nombre d'articles, je voulais savoir si le fait de changer ce paramètre et de fixer la taille des fichiers (alors que la base est maintenant en exploitation ) est encore possible sans incidence néfaste?
Merci d'avance

[SQLpro]

Oui, sans problème. Vos fichiers seront très fragmentés au début, puis plus fragmentés à la fin.

Une astuce serait de créer un nouveau fichier dans un nouveau groupe de fichier et de migrer index et table dedans, et le faire devenir file group par défaut.
Puis réduire au strict minimum l'ancien.

Pour migrer un index dans un nouveau groupe de fichier il suffit de de reconstruire par REBUILD en indiquant sa nouvelle destination (ALTER INDEX .... ON MonNouveauFileGroup REBUILD).
La plupart de vos tables doivent avoir une clef primaire, donc un index clustered. En migrant l'index clustered, vous migrez la table elle même, puisque ce type d'index est la table elle même.
Si votre table n'est pas organisée en CLUSTERED, alors c'est qu'elle est en HEAP, et dans ce cas la migration d'une telle table consiste à créer un index clustered sur n'importe quelle colonne puis e supprimer.

Pour réduire un fichier presque vide, vous devez utiliser DBCC SHRINKFILE.

A +

[djjej182]

Merci pour votre réponse. Elle est très claire.

Je voudrais juste avoir quelques précisions supplémentaires :
1/ Lorsque l'on active la croissance automatique, cela veut il dire que le moteur de stockage peut écrire d'un bout à l'autre du disque sans chercher le meilleur emplacement?


2/ Si je suis la bonne pratique que vous m'avez énoncé (création d'un groupe de fichier et migration des tables et index sur ce groupe), cette pratique consiste à allouer directement une partie du disque afin de minimiser (je suppose) les déplacements de la tête de lecture et gagner ainsi en performance.
Donc si je me contente de seulement désactiver la croissance automatique (par exemple je fixe une taille de 1 Go et j'ai actuellement 100 Mo de données), il se peut que les 1 Go que je vais fixer ne soient pas contigus..?

3/Peut être une question bête... Désolé d'avance
Les groupes de fichiers créés sont toujours propres à la base de données et non à l'instance ?
Je pose cette question car j'ai plusieurs bases de données sur une seule instance...si tout ce beau monde est sur le même groupe de fichier (PRIMARY par défaut), c'est mauvais pour la fragmentation....

Merci d'avance et dsl si mes interrogations sont un peu confuses

[SQLpro]

Merci pour votre réponse. Elle est très claire.

Je voudrais juste avoir quelques précisions supplémentaires :
1/ Lorsque l'on active la croissance automatique, cela veut il dire que le moteur de stockage peut écrire d'un bout à l'autre du disque sans chercher le meilleur emplacement?

Non, il recherche TOUJOURS le meilleur emplacement à caque opération de création ou raboutage de fichier.

2/ Si je suis la bonne pratique que vous m'avez énoncé (création d'un groupe de fichier et migration des tables et index sur ce groupe), cette pratique consiste à allouer directement une partie du disque afin de minimiser (je suppose) les déplacements de la tête de lecture et gagner ainsi en performance.
Donc si je me contente de seulement désactiver la croissance automatique (par exemple je fixe une taille de 1 Go et j'ai actuellement 100 Mo de données), il se peut que les 1 Go que je vais fixer ne soient pas contigus..?

Oui, parce que tout ce qui a été pris avant cette opération restera dans l'état.

3/Peut être une question bête... Désolé d'avance
Les groupes de fichiers créés sont toujours propres à la base de données et non à l'instance ?
Je pose cette question car j'ai plusieurs bases de données sur une seule instance...si tout ce beau monde est sur le même groupe de fichier (PRIMARY par défaut), c'est mauvais pour la fragmentation....

Chaque bas à ses propres file group à concurrence de 32 000 FG par base. Chaque FG peut contenir jusqu'à 32 000 fichiers. Chaque instance peut contenir jusqu'à 32 000 bases...

Merci d'avance et dsl si mes interrogations sont un peu confuses

A +

[Adel_mana]

Bonjour je me permet de venir ajouter une petite question d'ordre opérationnelle.
Dans le cadre d'une pbatique de performance j'aimerais basculer une base existante sous sqlserver 2012 et créee avec les options "par defaut" (recovery en mode simple, croissance automatique des fichiers par 10mo).

Dans mon cas les requêtes executées dans la portion de programme en question sont relativement "simples" (select * FROM WHERE champ de la clé composé / insert into() values()) mais effectuées en masse et ce qui coutent le plus sont les operations I/O sur disque.
J'aimerais donc basiquement directement à partir d'un .bak ou d'une base test restaurée a partir de ce bak effectuer cette migration dans une base de taille fixe mais je n'ai pas trouvé de tuto ou d'exemple de commande pour le faire.
Je vois à la creation d'une base comment repartir les fichier de données et de log au sein de differents filegroup mais j'ai plus de mal a rechercher les commande pour déplacer des fichiers existants et je veux évidemment faire en sorte de ne pas conserver la fragmentation initiale des données mais bien les transférer "en les defragmentant" en qqs sortes.

Évidemment je suis conscient que les pbatiques de performance constituent un sujet très vaste touchant à de nombreuse sous-thématiques [hardware et OS (technologie de disque, impact de la virtualisation), modèle de données en lui même ("tables obèses", normalisation-dénormalisation,absence de clés etrangère sur de tres vieux modele historique , type et equilibrage d'index), syntaxe des requête en elle même]
Mais la je veux juste valider/invalider cette piste car quand une base est exploitée avec un ERP bien souvent il n'y a pas de fine tuning du sgbd (pas de compétences chez le client,processus d'installation type qui ne se soucie pas de cet aspect).

Merci par avance.

EDIT
du coup je l'ai experimenté sur certaines tables pour l'instant en deplacant leur index cluster
CREATE UNIQUE CLUSTERED INDEX NOMINDEXCLUSTER
ON dbo.TABLE(CHAMP1,CHAMP2,CHAMP3)
WITH DROP_EXISTING
ON [NEXFILEGROUP]
En le faisant manuellement ssms m'indique que l'index va etre rebuildé donc du coup j'elimine bien toute fragmentation existante ?