Discussion :

[Romain.pelissier]

Bonjour,
J'ai quelques questions au sujet des jobs d'optimisation sous SQLServer 2000 et j'espere que vous pourrez m'aider a resoudre un petit probleme...
Je dis tout de suite que je ne suis pas un expert sous SQLServer 2000...

Voila, j'ai une bd qui a une taille de de 120 Go (109 utilisé, 11 Libre) de data et 660 Mo de Log (25 utilisé, 534 libre).
La taille du fichier de data est limité.

J'ai un job de maintenance qui fait le backup de cette bd et aussi un jib d'optimisation
- Réorganiser les pages d'index et les données
- Modifier l'espace libre par pourcentage de page à : 10%

J'avoue que je ne sais pas vraiment ce que veux dire ce setting mais ce que je comprend c'est que les index sont detruits puis recréés et optimisé de maniere a ce qu'ils puissent grossir. Je me trompe peut etre mais dans ce cas, merci de me dire ce que cela veux dire ou bien ce que cela est censé faire ;-)

Voila, le job d'optimisation se termine toujours avec une erreurs du style manque de place:

-Could not allocate space for object '(SYSTEM table id: -178721486)' in database 'RealSecureDB' because the 'PRIMARY' filegroup is full.

Il semble que le job veuille grossir la bd pour les index (fichier de data) mais le le peux pas prace que le filegourp PRIMARY est plein ...

C'est bien .. mais comment faire pour eviter ce probleme et permettre a mon job d'optimisation de fonctionner correctement?

Merci pour toutes les informations que vous voudrez bien me donner a ce sujet.

[rudib]

Bonjour,

J'ai un job de maintenance qui fait le backup de cette bd et aussi un jib d'optimisation
- Réorganiser les pages d'index et les données
- Modifier l'espace libre par pourcentage de page à : 10%

J'avoue que je ne sais pas vraiment ce que veux dire ce setting mais ce que je comprend c'est que les index sont detruits puis recréés et optimisé de maniere a ce qu'ils puissent grossir. Je me trompe peut etre mais dans ce cas, merci de me dire ce que cela veux dire ou bien ce que cela est censé faire ;-)

Réorganiser signifie défragmenter, restructurer l'index pour qu'il soit plus compact.
Modifier l'espace libre ... signifie changer le FillFactor, afin de laisser de l'espace libre pour de nouvelles valeurs de clé dans l'index, aisni éviter de la fragmentation, cela implique aussi la réorganisation, ou la reconstruction de l'index.

Pour permettre cette réorganisation/reconstruction, tu dois avoir de l'espace libre dans ton fichier de données. Icic il n'y en a manifestement pas assez pour exécuter la tâche, simplement.

[Romain.pelissier]

Merci pour ces explications!.
Saurais de combien il faudrait que j'augmente la taille de ma bd (data) afin de permettre au job d'optimisation de se terminer correctement?
Merci

[rudib]

Le seul moyen que j'aie de le savoir, c'est par magie incantatoire. Non, tu peux faire une estimation avec la taille de tes index, ou le mettre en grossissement automatique pour l'occasion, pour voir où ça te mène.

[Romain.pelissier]

HAHA !
Je pensais qu'il y avait peut etre une methode pour le calculer. Je suis un peu frileux a l'idee de laisser grossir la bd ... elle fait deja 120 Go et l'espace disque libre est de 160 Go ...
Je veux bien la laisser grossir mais il faudrais ensuite que la taille du data soit shrinké automatiquement pour eviter de grossir dand de trop grosse proprotion ... le job de maintenance peux il faire tout cela sans bobo?

Merci pour les conseils.

[rudib]

Evite le SHRINK, tu vas fragmenter ton disque. Sur Sybase, et à lépoque de SQL Server 6.5, on devait donner des tailles fixes, et on se débrouillait avec. Je crois que je regrette ce temps.

Si tu as d'autres disques, tu peux créer des groupes de fichiers pour déplacer les objets de ta base (comme les index) sur une autre partition

[Romain.pelissier]

Merci,
Pourrais tu me dire comment faire si je voulais deplacer les index dans un autre filegroup et donc un autre disque?

Si l'option du shrink n'est pas la plus recommandée alors possible que je peux y aller pas a pas, c'est a dire augmenter la taille de la bd et voir si le job d'optimisation fonctionne avec la nouvelle taille ... qu'en pense tu?
L'ideal ne serait il pas d'avoir les donnée sur un disque et les index sur un autre (ou un autre filegroup) et de gerer leur taille independement?
Je pensais faire la chose suivante :
- avoir un fichier data (filegroup) pour les data
- un fichier pour les log
- un autre fichier (filegroup) pour les index

Qu'en penses tu?

[rudib]

C'est une bonne idée.

Pour ça : tu vas dans es propriétés de ta base, tuu crées un nouveau fichier, et tu lui donnes un nouveau nom de filegroup. Il te créera le tout.

Puis, tu recrées tes index sur le filegroup :

Code :

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

CREATE INDEX ... WITH DROP_EXISTING ON ton_nouveau_filegroup

Attention : à faire dans une fenêtre administrative (période de basee activité)

[Romain.pelissier]

Merci
Avec cette commande, il va créé l'index en supprimant l'ancien, sur un nouveau filegroup donc, c'est bien ca?

Et ... y a t il un moyen d'avoir le script de creation des indexes pour l'ensemble des tables d'une bd donnée? (C'est ce que je suis en train de regarder)

EDIT :

Pour lister les indexes d'une table, j'ai trouvé ca : http://databases.aspfaq.com/schema-t...-database.html

[rudib]

Tu peux générer ces scripts via SQL-DMO. Exemple ici http://www.babaluga.com/doku.php/pro...on_python-ruby

[Romain.pelissier]

Merci,
Donc pour resumer, le mieux pour effectuer le job d'optimisation des index serait d'avoir un autre filegroup (si possible sur un autre disque) et de 'deplacer' les indexes sur celui ci.
On se retrouverais avec 2 filegroup pour les 'data' et un autre pour le log.

Ou as tu d'autres idees pour effectuer le job d'optimisation de facon ca ce qu'il marche?

Merci encore pour ton aide

[Romain.pelissier]

hmmm je n'ai pas encore proceder a la manipulation de creation d'un nouveau filegroup et deplacement des indexes.

J'ai essayé d'augmenter la taille d'expansion de la bd et de combien elle doit augmenter. Actuellement, la taille est de 150 Go Total et 40 Go libre dans le filegroup, le max size est configuré pour 150 Go et le grossissement se fait par pas de 1Go mais ...
Toujours la meme erreur de filegroup is full... comprend pas ... avec 40Go de libre dans le filegroup le job d'optimisation plante encore .... la faute aux indexes?
Dois je oublier mon job d'optimisation? J'avoue que je ne sais pas trop quoi faire ....
Je me disais que pour regler peut etre mon probleme temporairement serait de creer un filegroup pour les indexs les plus gros ... Dans ce cas la, l'optimisation marcherais possiblement mieux.

Ton avis?

[kagemaru]

bonjour,

quelle est la taille de la table sur laquelle le reindex plante ? Est-ce toujours la même ?

Code :

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

sp_spaceused <table>

[Romain.pelissier]

Salut,
Malheureusement je n'ai pas l'information .. tout ce que j'ai c'est :
'Could not allocate space for object '(SYSTEM table id: -200327630)' in database 'RealSecureDB' because the 'PRIMARY' filegroup is full.' et l'id change a chaque fois ...

Mais en regardant la taille de la table la plus grande dans la bd j'ai ca (je n'ai mis que quelques unes des plus grosse table):

Code :

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

1
2
3
4
5
Name			Rows		Reserved	data		index_size	unused
Observances		12329438   	3462144 KB	1216592 KB	2229864 KB	15688 KB
DailyAttackHistory	3266628    	147304 KB	78752 KB	68496 KB	56 KB	
SensorData1		77082237   	26948560 KB	22061736 KB	4886704 KB	120 KB
SensorDataAVP1		999334625  	82567040 KB	82195000 KB	359720 KB	12320 KB

[kagemaru]

Vous avez une table de 82567040 KB, donc 80 Go (SensorDataAVP1).
Les 40 Go de libres ne sont pas suffisants, SQL Server faisant une duplication de la table puis recréation d'index avec suppression de l'ancienne...
Plusieurs solutions sont possibles (agrandir le fichier MDF actuel, ajouter un 2eme fichier au PRIMARY, créer un 2eme filegroup dans lequel la table sera déplacée ....)

Il faut avoir suffisamment d'espace disque également. Ce genre d'opération beaucoup de log.

[Romain.pelissier]

Merci pour l'eclaircissement. Donc d'apres toi quelle serait la meilleur methode?

[kagemaru]

Le mieux serait de défragmenter les tables qui ont vraiment besoin de l'être : le problème est que le job SQL Server traite toutes les tables sans distinction.

La commande DBCC SHOWCONTIG peut te permettre de connaître le degré de fragmentation. Ensuite il suffit de créer un script SQL qui traitera les tables ciblées.
http://technet.microsoft.com/fr-fr/l.../ms175008.aspx

Reste-t-il beaucoup d'espace disque libre sur le serveur ou seulement les 40 go que tu mentionnes au début du fil ?

[Romain.pelissier]

Les fichiers de data et de log sont sur un disque ou il reste 134 Go. L'espace libre dans le filegroup data de la bd es tde 40 go pour l'instant.

Pour L'optimisation, le script de MS parle des indexes mais pas des tables ... de plus est ce que le job d'optimisation des indexes de mon plan de maintenance ne fait pas cette operation? Sinon, quand devrais je faire la defragmentation?

[Romain.pelissier]

Je viens de trouver quelques ressources web sur la question de la fragmentation, je pense que cela peux interesser des personnes alors je les postes ici.

• Lien1
• Lien2
• Lien3
• Lien4

J'aime particuliere le Lien1 parce que il donne un script utile et explique la fragmentation.

Pour ma part, il semble que mes 10 plus grosses tables ne sont pas fragmentées:

DBCC SHOWCONTIG scanning 'SensorDataAVP1' table...
Table: 'SensorDataAVP1' (1842105603); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 10276331
- Extents Scanned..............................: 1290358
- Extent Switches..............................: 1290495
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.54% [1284542:1290496]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 7.87%
- Avg. Bytes Free per Page.....................: 458.5
- Avg. Page Density (full).....................: 94.34%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHOWCONTIG scanning 'SensorData1' table...
Table: 'SensorData1' (1810105489); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 2758045
- Extents Scanned..............................: 345491
- Extent Switches..............................: 345490
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.79% [344756:345491]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 12.55%
- Avg. Bytes Free per Page.....................: 666.8
- Avg. Page Density (full).....................: 91.76%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHOWCONTIG scanning 'Observances' table...
Table: 'Observances' (530100929); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 152109
- Extents Scanned..............................: 19120
- Extent Switches..............................: 19145
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.31% [19014:19146]
- Logical Scan Fragmentation ..................: 0.01%
- Extent Scan Fragmentation ...................: 1.42%
- Avg. Bytes Free per Page.....................: 799.6
- Avg. Page Density (full).....................: 90.12%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHOWCONTIG scanning 'DailyAttackHistory' table...
Table: 'DailyAttackHistory' (1061578820); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 9847
- Extents Scanned..............................: 1241
- Extent Switches..............................: 1249
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.48% [1231:1250]
- Logical Scan Fragmentation ..................: 0.09%
- Extent Scan Fragmentation ...................: 0.81%
- Avg. Bytes Free per Page.....................: 796.9
- Avg. Page Density (full).....................: 90.15%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHOWCONTIG scanning 'NetworkInterface' table...
Table: 'NetworkInterface' (338100245); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 19192
- Extents Scanned..............................: 2411
- Extent Switches..............................: 2410
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.50% [2399:2411]
- Logical Scan Fragmentation ..................: 0.02%
- Extent Scan Fragmentation ...................: 76.57%
- Avg. Bytes Free per Page.....................: 775.5
- Avg. Page Density (full).....................: 90.42%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHOWCONTIG scanning 'SensorDataResponse1' table...
Table: 'SensorDataResponse1' (2034106287); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 8738
- Extents Scanned..............................: 1123
- Extent Switches..............................: 1123
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 97.24% [1093:1124]
- Logical Scan Fragmentation ..................: 0.57%
- Extent Scan Fragmentation ...................: 65.18%
- Avg. Bytes Free per Page.....................: 283.0
- Avg. Page Density (full).....................: 96.50%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHOWCONTIG scanning 'MessageLog' table...
Table: 'MessageLog' (274100017); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 7741
- Extents Scanned..............................: 977
- Extent Switches..............................: 993
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 97.38% [968:994]
- Logical Scan Fragmentation ..................: 0.25%
- Extent Scan Fragmentation ...................: 89.46%
- Avg. Bytes Free per Page.....................: 733.9
- Avg. Page Density (full).....................: 90.93%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHOWCONTIG scanning 'CheckPlatforms' table...
Table: 'CheckPlatforms' (661577395); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 600
- Extents Scanned..............................: 76
- Extent Switches..............................: 75
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.68% [75:76]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 807.3
- Avg. Page Density (full).....................: 90.03%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHOWCONTIG scanning 'SensorDataAVP2' table...
Table: 'SensorDataAVP2' (553769030); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 1607
- Extents Scanned..............................: 202
- Extent Switches..............................: 201
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.50% [201:202]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.50%
- Avg. Bytes Free per Page.....................: 747.8
- Avg. Page Density (full).....................: 90.76%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHOWCONTIG scanning 'CheckOSGroup' table...
Table: 'CheckOSGroup' (629577281); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 448
- Extents Scanned..............................: 57
- Extent Switches..............................: 56
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.25% [56:57]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 805.1
- Avg. Page Density (full).....................: 90.05%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Je vais surement augmenter la taille du filegroup de data pour que l'espace libre disponible soit plus grand que la taille de la plus grande table.
Je posterais le resultat sous peu.

[kagemaru]

en effet, si vous faites le ratio "embêtements"/gain je pense que ça ne vaut vraiment pas le coup, les tables étant très peu fragmentées à tout niveau.