Discussion :

[Jinroh77]

Bonsoir à tous,
J'ai lu quelque part, sur les blogs de développez il me semble qu'il était contre-performant de réaliser un ALTER sur une table (en particulier volumineuse) plutôt que de créer une nouvelle table en y copiant les données.
Dans mes souvenirs, le cas d'un ALTER qui modifiait le type d'une colonne pour la rendre plus large faisait perdre l'ancien espace dans chaque page de données..

Malheureusement je ne retrouve plus le billet qui en parlait... Si votre mémoire est meilleur que la mienne, je suis à la recherche du lien
De plus, est-ce qu'il est possible de retrouver dans une base toutes les tables qui ont pu subir ce genre de modification ?

Merci pour vos souvenirs.

[mikedavem]

J'ai lu quelque part, sur les blogs de développez il me semble qu'il était contre-performant de réaliser un ALTER sur une table (en particulier volumineuse) plutôt que de créer une nouvelle table en y copiant les données.

Tout dépend ce que fait le ALTER.

Malheureusement je ne retrouve plus le billet qui en parlait... Si votre mémoire est meilleur que la mienne, je suis à la recherche du lien

Peut-être ici

++

[Jinroh77]

Tout dépend ce que fait le ALTER.
Peut-être ici
++

Ahhhh je me souvenais bien que je l'avais vu pas loin d'ici. je l'avais même commenté


Dans un des cas, je passe d'un INT à du DECIMAL(19,4) :p
Mais l'idée est aussi de retrouver toutes les tables volumineuses qui ont pu "souffrir" de ces alter.

[Jinroh77]

Je reviens un peu sur ma question...

Je suis en train de chercher la liste des tables qui ont pu subir une opération d'ALTER. Pour cela j'utilise la requête suivante qui me retourne les tables pour lesquelles la somme des taille des colonnes est différente du MAX de l'ofset de départ + la taille de cette même colonne :

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
;
WITH MaxOffsetTable AS (
SELECT
	tb.name	AS TableName
    ,MAX(leaf_offset)	AS DebutDerniereColonne
--    ,column_id			AS IdDerniereColonne
FROM 
	sys.system_internals_partition_columns PC
	INNER JOIN sys.partitions AS P
		ON P.partition_id = PC.partition_id
	INNER JOIN sys.columns AS C
		ON C.column_id = PC.partition_column_id
		AND C.object_id = P.object_id
	INNER JOIN sys.tables tb
		ON tb.object_id = P.object_id
GROUP BY 
	tb.name
),
TotalTable AS (
SELECT
	tb.name	AS TableName
    ,4+ SUM(max_inrow_length)	AS TailleTotale
FROM 
	sys.system_internals_partition_columns PC
	INNER JOIN sys.partitions AS P
		ON P.partition_id = PC.partition_id
	INNER JOIN sys.columns AS C
		ON C.column_id = PC.partition_column_id
		AND C.object_id = P.object_id
	INNER JOIN sys.tables tb
		ON tb.object_id = P.object_id
GROUP BY 
	tb.name
)
select * from (
SELECT
	tb.name	AS TableName
	,SUM(leaf_offset + max_inrow_length)	AS TailleColonnes
	,SUM(TotalTable.TailleTotale)			AS TailleTotale
FROM 
	sys.system_internals_partition_columns PC
	INNER JOIN sys.partitions AS P
		ON P.partition_id = PC.partition_id
	INNER JOIN sys.columns AS C
		ON C.column_id = PC.partition_column_id
		AND C.object_id = P.object_id
	INNER JOIN sys.tables tb
		ON tb.object_id = P.object_id
	INNER JOIN MaxOffsetTable
		ON MaxOffsetTable.TableName = tb.name
		AND MaxOffsetTable.DebutDerniereColonne = leaf_offset
	INNER JOIN TotalTable
		ON TotalTable.TableName = tb.name
GROUP BY 
	tb.name
) t
WHERE 
	TailleColonnes <> TailleTotale

Je me retrouve alors avec un certains nombre de table, MAIS avec la requête suivante que l'on trouve dans le billet, je trouve des tables (la plupart) pour lesquelles le "leaf_ofset" prends des valeurs négatives
Je précise que les colonnes en question sont des VARCHAR, non MAX

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
SELECT
	tb.name	AS TableName,
    C.name AS column_name,
    C.column_id,
    PC.max_inrow_length,
    PC.system_type_id,
    PC.leaf_offset
 
    , PC.partition_id
    , P.*
FROM 
	sys.system_internals_partition_columns PC
	INNER JOIN sys.partitions AS P
		ON P.partition_id = PC.partition_id
		AND P.index_id in (0, 1)
	INNER JOIN sys.columns AS C
		ON C.column_id = PC.partition_column_id
		AND C.object_id = P.object_id
	INNER JOIN sys.tables tb
		ON tb.object_id = P.object_id
WHERE 
	P.object_id = OBJECT_ID('DimIndicateur')

P.S. : J'ai du ajouter un filtre "sys.partitions.index_id in (0, 1)" pour ne pas récupérer les infos des indexs et me trouver alors avec doubons sur les colonnes.

[Jinroh77]

En fait, les -1 sont bien pour le cas des types VARCHAR. De plus, les bit ont également un traitement particulier
Ça devient compliqué pour détecter les tables "fautives"

[mikedavem]

Tu ne pourras pas utiliser la vue système sys.system_internals_partition_columns. La colonne leaf_offset n'est réellement utilisable que pour les types de données fixes car la longueur est prédictible dans ce cas. Avec les types de données variables la longueur est directement dépendante de la donnée donc déterminer à l'avance le leaf_offset n'est pas possible. En revanche les numéros s'incrémentent pour déterminer le nombre de colonnes variables dans ce cas. Par exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
CREATE TABLE T_TEST4
( 
texte1 VARCHAR(50) NOT NULL,
texte2 VARCHAR(50) NOT NULL,
boll BIT NOT NULL,
id SMALLINT NOT NULL 
);
 
INSERT T_TEST4 VALUES (REPLICATE('T', 25), REPLICATE('T', 30), 1, 1);

donnera cela :

Code :

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

1
2
3
4
5
column_name	column_id	max_inrow_length	system_type_id	leaf_offset	max_length
texte1	1	50	167	-1	50
texte2	2	50	167	-2	50
boll	3	1	104	4	1
id	4	2	52	5	2

Une modification de la longueur d'une colonne variable donnera ceci :

Code :

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

1
2
3
ALTER TABLE T_TEST4
ALTER COLUMN texte1 VARCHAR(25); 
GO

...

Code :

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

1
2
3
4
texte2	2	50	167	-2	50
boll	3	1	104	4	1
id	4	2	52	5	2
texte1	1	25	167	-3	25

Tu remarqueras que -1 a disparu pour laisser la place à -3. De manière générale si tes tables possèdent des indexes cluster, le fait de les reconstruire permettra de régler le problème d'espace inutilisé par les changements effectués sur tes colonnes.

++