Discussion :

[Johann7751]

Bonjour,

J'ai 2 tables dans une même base de données SQL Server 2008.
Ces 2 tables sont identiques. Les colonnes et le type des colonnes sont identiques. Les 2 tables contiennent le même nombre de lignes.
La seule différence est que pour table A j'ai défini une clé primaire composite sur 5 champs de la table. Pas dans la table B.

En comparant ces 2 tables, je me suis rendu compte qu'il y a avait une différence non négligeable entre l'espace occupé par les 2 tables.

Code sql :

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

1
2
3
Use MABASE
EXEC sp_spaceused 'Table_B'
EXEC sp_spaceused 'Table_A'

Résultat Table B

name : Table_B
rows : 266868804
reserved : 14772712 KB
data : 14770808 KB
index_size : 40 KB
unused : 1864 KB



Résultat Table A

name : Table_A
rows : 266868804
reserved : 12759912 KB
data : 12638248 KB
index_size : 119920 KB
unused : 1744 KB

C'est quoi cette différence de 2GB ?

[SQLpro]

Avez vous d'autres index que celui sous-jacent à la clef primaire ? (sp_helpindex)
Si non, c'est tout simplement la place occupée par l'index de la clef primaire
Si oui, c'est la place occupée par l'index primaire et par toutes les références inverses des index autres que la clef primaire (index clustered).

Entre nous définir une clef primaire sur un conglomérat de 5 colonnes est généralement mauvais en terme de performances :
1) cela génère un surcroit de volume non négligeable
2) l'index ne disposant de stats que pour la première colonne, l'optimisation est moins performante.

A +

[darkelend]

Ah tiens, j'en profite pour poser une question en rapport avec ce sujet. Si un des indexs est fragmenté, est ce qu'il prendra plus de place qu'un index non fragmenté ? Je pense que oui car un index fragmenté devrait occuper plus de pages ?

[aieeeuuuuu]

Bonjour,

Il peut aussi y avoir une autre explication : le vide prend de la place !

si par exemple vous avez des colonnes en varchar qui ont été modifiées, par des chaines plus courte, la "place libérée" n'est pas forcément restituée.

Lorsque vous sortez un paquet de pattes de votre placard, vous ne changez pas votre placard pour un placard plus petit : ça vous ferez beaucoup de travail pour ensuite devoir remettre un placard plus grand lors de votre prochaine corvée de courses... Le moteur de base de données fait de même...

La taille occupée par une table dépend donc aussi de son "histoire"...

Dans l'exemple ci-dessous, je crée une table "bien remplie" par des chaines de 8000 caractères, puis je remplace par des chaines vides. La taille occupée par la table reste la même.
En revanche, si je copie son contenu dans une nouvelle table de structure identique, celle-ci occupera moins de place :

Code SQL :

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
 
SET NOCOUNT ON
 
CREATE TABLE T1(
	VAL VARCHAR(8000) NOT NULL
)
 
INSERT INTO T1
	SELECT TOP(10000) REPLICATE('a',8000)
	FROM sys.objects a, sys.objects b, sys.objects c
 
PRINT 'T1 Taile initiale'
EXEC sp_spaceused 'T1', 'true'
 
UPDATE T1
SET VAL = ''
 
PRINT 'T1 Après Update'
EXEC sp_spaceused 'T1', 'true'
 
SELECT VAL
INTO	T2
FROM T1
 
PRINT 'T2'
EXEC sp_spaceused 'T2', 'true'
 
DROP TABLE T1
DROP TABLE T2

Résultat :

T1 Taile initiale

name rows reserved data index_size unused
T1 10000 80072 KB 80000 KB 8 KB 64 KB

T1 Après Update

name rows reserved data index_size unused
T1 10000 80072 KB 80000 KB 8 KB 64 KB

T2

name rows reserved data index_size unused
T2 10000 272 KB 240 KB 8 KB 24 KB

[SQLpro]

Tout insert, update delete fragmente et la fragmentation c'est des trous dans des pages. Donc augmentation de volume.

le fait d'avoir utilisé une clef composite complexe génère beaucoup de trous à chaque mise à jour qu'elle quelle soit.

A +

[Babyneedle]

Tout insert, update delete fragmente et la fragmentation c'est des trous dans des pages. Donc augmentation de volume.

le fait d'avoir utilisé une clef composite complexe génère beaucoup de trous à chaque mise à jour qu'elle quelle soit.

A +

J'aimerais simplement ajouter la précision que la gestion du Fill Factor peut prévenir en tout et en partie le 'bloating' des index par fragmentation.