Alter ou Drop et Create [Résolu]

[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]

Envoyé par mikedavem

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 :

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 :

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 :

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 :

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 :

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

...

Code :

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.

++

[Jinroh77]

Merci pour la précision. Du coup pour ces longueur varchar, l'emplacement de démarrage de chacun des champs dépend complètement de la longueur de la valeur ? Il n'y a plus rien de fixe.

Détecter toutes les tables à "problème" devient donc plus compliqué et il vaut mieux alors faire une reconstruction complète de toutes celles-ci.

En terme de performance, pour des tables volumineuses de plusieurs centaines de millions de lignes, pour effectuer une modification de type vaut-il mieux alors faire comme le propose SSMS en reconstruisant une nouvelle table ou plutôt conserver cette commande ALTER et le faire suivre d'un ALTER ?

Sur une base de production, l'avantage de la nouvelle table est de ne pas bloquer l'accès à l'ancienne, alors que le ALTER pose un verrou ?

[mikedavem]

En terme de performance, pour des tables volumineuses de plusieurs centaines de millions de lignes, pour effectuer une modification de type vaut-il mieux alors faire comme le propose SSMS en reconstruisant une nouvelle table ou plutôt conserver cette commande ALTER et le faire suivre d'un ALTER ?

Tout dépend le changement comme je te disais :

Une commande ALTER qui supprime une colonne, ajoute une colonne avec NULL par défaut ou augmente la longueur d'une colonne ne touchera pas aux données et il n'y aura pas besoin de faire une analyse ligne à ligne des données.

Une commande ALTER qui annule l'acceptation des absences de valeurs (NULL) ou diminue la longueur d'une colonne devra examiner et valider l'ensemble des valeurs des colonnes concernées sans pour autant y toucher. Sur une table volumineuse le processus peut prendre du temps.

Enfin d'autres changements impliquent un changement physique au niveau des lignes de données comme l'ajout d'une colonne avec par défaut une valeur non NULL. Dans ce cas, chaque changement de ligne doit être loggé dans le journal des transactions. Bien entendu le processus peut être très long ici et consommateur de ressources.

Sur une base de production, l'avantage de la nouvelle table est de ne pas bloquer l'accès à l'ancienne, alors que le ALTER pose un verrou ?

Effectivement un alter table requier un verrou de type schéma SCH-M

++

[Jinroh77]

Merci pour ces dernières précisions