Discussion :

[oadin]

Bonjour,

J'ai rencontré pour la deuxième fois aujourd'hui un soucis avec une de nos bases de donnée client.

Le soucis vient de la base de donnée du client qui prend des tailles non logique je dirais.

Je m'explique, nous installons des serveurs sql (sql server 2005 chez qui j'ai rencontré le problème) avec notre modèle de base de donnée et notre logiciel.
Nous fonctionnons toujours de la même manière en terme de maintenance.

Check database integrity - Update statistique - Rebuild index en plan de maintenance que nous exécutons une fois par semaine.
Cela fonctionne parfaitement chez la plupart de nos clients.

Mais chez deux de nos clients les bases de donnée au bout de qq semaines ont quadruplé de taille, je suis obligé d'exécuté le script suivant pour leur rendre leur taille normal :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
DECLARE @TableName varchar(255) 
 
DECLARE TableCursor CURSOR FOR 
SELECT table_name FROM information_schema.tables 
WHERE table_type = 'base table' 
 
OPEN TableCursor 
 
FETCH NEXT FROM TableCursor INTO @TableName 
WHILE @@FETCH_STATUS = 0 
BEGIN 
DBCC DBREINDEX(@TableName,' ',90) 
FETCH NEXT FROM TableCursor INTO @TableName 
END 
 
CLOSE TableCursor 
 
DEALLOCATE TableCursor
 
DBCC SHRINKDATABASE (XXX', TRUNCATEONLY)
DBCC UPDATEUSAGE ('XXX')

Ce que je cherche a savoir c'est pourquoi ce problème se pose chez certains client et pas d'autre (ce n'est pas toujours nous qui installons le serveur mais je ne vois rien dans la configuration qui pourrait occasionné ce problème) et pourquoi mon plan de maintenance ne résout pas le problème et que je dois le résoudre par script tsql.

Y-a t il qqch que je peux faire pour empecher d'exécuter ce script toute les semaines? Corriger un soucis avec la base de donnée?

[ylarvor]

Ce que je cherche a savoir c'est pourquoi ce problème se pose chez certains client et pas d'autre (ce n'est pas toujours nous qui installons le serveur mais je ne vois rien dans la configuration qui pourrait occasionné ce problème) et pourquoi mon plan de maintenance ne résout pas le problème et que je dois le résoudre par script tsql.

La fragmentation apparait avec des insertions, des mises à jours repete...

Je pense tout simplement que les 2 clients dont la base est fragmentée ont une utilisation intensive en insertion/mise à jour.

[oadin]

bah non justement c'est la tout le mystère de ma question.

Je ne peux pas juger du niveau de mise a jour et de correction apportée dans la base de donnée (ajout suivi de delete) mais par rapport a nous. Ce sont de "petits client".

Nous arrivons a juger le niveau d'un client car l'erp que nous avons développé est avant tout installé chez nous et les clients que je parle sont plus petit en terme d'entreprise que la notre.
Moins de machine de productions, moins de fabrication et moins de vente et pourtant le problème apparait chez eux et pas chez nous.

C'est pourquoi je me demande si ça ne vient pas d'un problème de disque ou d'installation d'instance ou de problème avec la base de donnée elle même. Mais cela ne résulte pas de l'utilisation de la base de donnée.

[ylarvor]

au lieu d'appliquer la reeindexation la prochaine fois, je te conseille d'utiliser les informations de dmv, voir article rudi, pour determiner quel table est fragmentée et à quel niveau.

tu peux aussi essayez de determiner la taille de tes tables et des index associes avant et apres reeindexation pour voir l'influence de la fragmentation sur une ou plusieurs tables...

[SQLpro]

Check database integrity :

inutile si votre base de données voit les pages abimés (torn page detection ou checksum). En effet il n'existe plus de bug connu capable d'endommager les bases SQL Server. Si une erreur est détecté ce sera à l'évidence un problème hardware (défaillance Raid, contrôleur disque ou disque lui même...)

Update statistique :

inutile dans votre cas puisque à l'étape suivante vous faites une reconstruction d'index ce qui implique un recalcul des statistiques...

Rebuild index :

Dans une grosse base, fortement solicitée il faut le faire beaucoup plus souvent (toutes les nuits pas exemple) et surtout sélectionner les index.

Sur la fragmentation : cela ne dépends pas que des mise à jour (INSERT, UPDATE, DELETE) cela dépend fortement du type de données des clefs et des index et de la nature des index (cluster ou heap). par exemple l'uisage immodéré du type VARCHAR pour des clef primaire, des contraintes d"unicité ou des colonnes fréquemment recherchées notamment lorsqu'elles sont fréquemment mises à jour, est une abération !

Quand au SHRINKDATABASE c'est la pire des choses à faire. En effet cela oblige à diminuer la taille des fichiers de la base alors que par nature une base est en expansion... le moteur de stockage devra donc à nouveau faire des opérations de croissance, opération qui sont connues pour provoquer des pertes de performances les plus sévères qui soient, pouvant même conduire à des blocages.
En principe les fichiers d'une base de données ne devrait JAMAIS croitre. Ils devraient avoir une taille conçue pour le volume de la base à terme. Par exemple 3 années d'exploitation.
Si vous voulez des performances il vaudrait mieux que votre base ait été créée avec des fichiers de taille fixe. Cela éviterait toute fragmentation physique des fichiers système et en accélérerait les mises à jour (INSERT, UPDATE notamment) de façon importante.

Pour preuve, voici un test qui vous en dira long...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
-- le répertoire C:\SQLDB\ doit préalablement exister sur votre PC
USE master
GO
 
IF EXISTS(SELECT * FROM sys.DATABASES WHERE name = 'TEST_FICHIER_VAR')
   DROP DATABASE TEST_FICHIER_VAR
GO
 
CREATE DATABASE TEST_FICHIER_VAR
ON PRIMARY ( NAME = DATA,
    FILENAME = 'C:\SQLDB\DATA',
    SIZE = 3MB,
    FILEGROWTH = 1MB)
LOG ON 
   (NAME = JT,
    FILENAME = 'C:\SQLDB\JT',
    SIZE = 1MB,
    FILEGROWTH = 1MB)
GO
USE TEST_FICHIER_VAR
GO
 
CREATE TABLE T (LIGNE VARCHAR(500))
GO
 
DECLARE @T1 DATETIME, @T2 DATETIME, @I INT
SET @T1 = CURRENT_TIMESTAMP
SET @I =1
 
INSERT INTO T SELECT REPLICATE('A', 500);
 
WHILE @I < 100
BEGIN
 
   INSERT INTO T SELECT TOP 3000 * FROM T
 
   SET @I = @I + 1
 
END
 
CHECKPOINT
 
SET @T2 = CURRENT_TIMESTAMP
 
SELECT CAST((@T2 - @T1) AS FLOAT) * 86400.0 AS SECONDE
 
 
USE master
GO
 
IF EXISTS(SELECT * FROM sys.DATABASES WHERE name = 'TEST_FICHIER_VAR')
   DROP DATABASE TEST_FICHIER_VAR
GO
 
CREATE DATABASE TEST_FICHIER_FIX
ON PRIMARY ( NAME = DATA,
    FILENAME = 'C:\SQLDB\DATA2',
    SIZE = 2GB,
    FILEGROWTH = 10%)
LOG ON 
   (NAME = JT,
    FILENAME = 'C:\SQLDB\JT2',
    SIZE = 2GB,
    FILEGROWTH = 10%)
GO
 
USE TEST_FICHIER_FIX
GO
 
 
CREATE TABLE T(LIGNE VARCHAR(500))
GO
 
DECLARE @T1 DATETIME, @T2 DATETIME, @I INT
SET @T1 = CURRENT_TIMESTAMP
SET @I =1
 
INSERT INTO T SELECT REPLICATE('A', 500);
 
WHILE @I < 100
BEGIN
 
   INSERT INTO T SELECT TOP 3000 * FROM T
 
   SET @I = @I + 1
 
END
 
CHECKPOINT
 
SET @T2 = CURRENT_TIMESTAMP
 
SELECT CAST((@T2 - @T1) AS FLOAT) * 86400.0 AS SECONDE
 
USE master;
GO
 
DROP DATABASE TEST_FICHIER_FIX

Testé sur mon portable (2 Go de RAM, Dual Core), c'est édifiant :
Base à fichier variable = 40,14 secondes
Base à fichier fixe = 13,34 secondes


A +

[oadin]

Je suis tout a fait d'accord avec ce qui a été dit sur le défragmentation et le shrinkdatabase.

J'essaye de ne jamais l'utiliser sauf cas particulier. Mais dans le cas présent je n'ai pas le choix.

Pour donner des chiffres :

Chez nous : base de donnée de 40go, forte utilisation et pas d'augmentation dramatique de la taille de la base de donnée en une semaine
Sur serveur de test base de 40go fragmentée, la procédure que j'ai mis plus haut, shrink total résultat après, 39go.
Nous avons une maintenance hebdomadaire également de nos index, nous allons peut être changé suite a l'aide apportée par SQLPro.

Chez un client : base de donnée de 7go, utilisation moyenne (pas plus d'archive d'un coté que l'autre, l'utilisation hebdomadaire est plus fort chez nous) et en fin de semaine la base de donnée fait 28go.
J'utilise le script plus haut et je retombe a 7go et qq. Plan de maintenance identique au notre

Je vais vérifier si le SP2 de sql server 2005 est bien installer mais sinon je ne vois pas d'ou ça vient.
Si il y a une erreur dans la maintenance il devrait se présenter également chez nous. Le modèle de base de donnée est le même que le notre donc je ne pense pas que ça vienne du modèle.

Reste une utilisation fautive de notre logiciel, mais a ce point la, et ça resterait difficile a détecter (je pensais a mettre en place un compteur d'insert/update/delete quitte a ralentir le serveur une semaine, m'en faire une idée)
Il me reste donc le problème hardware (j'y ai pensé aussi).

ps : j'ai fait un shrink sur leur serveur car ils ont 50go de disque dur pour une partie application, sql server, les backups et je sais pas encore trop quoi..... Je sais c'est pas du tout opti mais entre ce que l'on demande aux clients et ce qu'ils font c'est pas toujours pareil