Discussion :

[jporet]

bonjour à tous ,

j'ai une table "table_src" avec des index qui fait + 40 millions de lignes et je dois modifier la structure de la table en ajoutant des colonnes.
j'ai de gros problèmes de performance ça met 4 heures et ça plante car je rencontre des problèmes d'espace disque avec la base tempdb

version sql = sql server 2008 R2 , la mémoire allouée à sql est de 2 Go et il reste 20 Go sur le serveur

voici ce que j'ai fait :
- la base est en mode simple
- j'ai créé une nouvelle table table_dest avec la nouvelle structure avec la clé primaire

SET NOCOUNT ON
insert into dbo.table_dest (...) select .... from dbo.table_src

- après l'insert j'ai créé les index sur la table table_dest.

je ne sais pas si par exemple il est préférable de créer les index sur la table finale avant ou après l'insert
j'aurai voulu faire un test en faisant des insertions par bloc pour maîtriser la taille de la base tempdb mais je ne sais pas comment faire.
au final je ne sais pas trop comment m'y prendre , à part sinon de demander à l'infra d'augmenter la mémoire et l'espace disque du serveur mais ça va prendre des semaines.


merci d'avance pour aide

[SQLpro]

Avec si peu de ram vous n'arriverez jamais. Les sgbdr traitant les données en mémoire il faut que l'intégralité des données de la table puise figurer en mémoire ainsi que la journalisation ... Commencez par mettre au moins 16 go de ramener à votre serveur et vous verrez que vous allez résoudre tous les problèmes : vitesse, temps et crash...

[jporet]

merci pour votre réponse , j'ai déjà fait la demande à notre infra pour avoir plus de mémoire...
mais avant que ça soit fait ça va prendre du temps
j'essaye de trouver une solution en parallèle

[SQLpro]

alors il faut le en splittant la table.

A +

[jporet]

je suis parti sur cette idée

merci pour votre aide

[elsuket]

Bonjour,

Effectivement avec 2Go cela va être difficile. Une fois que vous aurez plus de RAM, les performances (outre cet INSERT) s'en ressentiront immédiatement : les applications qui accèdent à la base de données vont "respirer"

Vous pouvez procéder au peuplement de la nouvelle table par les lignes de l'ancienne en copiant les lignes par lots de 10 000, par exemple :

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
-- Extraction des valeurs de clé primaire subrogée de la table source
SELECT	id
INTO	dbo.table_auxiliaire
FROM	dbo.table_source
 
-- Ceci crée implicitement un index cluster
-- qui va bien supporter les ORDER BY qui suivent
ALTER TABLE dbo.table_auxiliaire
ADD CONSTRAINT PK_table_auxiliaire PRIMARY KEY(id)
 
------------------------------------------------------------
 
DECLARE @i int = 0
	, @dt datetime
	, @msg varchar(1024)
 
WHILE EXISTS
(
	SELECT	*
	FROM	dbo.table_auxiliaire
)
BEGIN
	SELECT	@i += 1
		, @dt = GETDATE();
 
	-- On copie les 10000 lignes suivantes dans la table cible ...
	WITH
		CTE AS
		(
			SELECT	TOP 10000 id
			FROM	dbo.table_auxiliaire
			ORDER	BY id
		)
	INSERT INTO dbo.table_cible
	(
		id
		, les_autres_colonnes
	)
	SELECT		S.id
			, S.les_autres_colonnes
	FROM		CTE AS C
	INNER JOIN	dbo.table_source AS S
				ON C.id = S.id;
 
	-- ... une fois qu'elles sont traitées, on peut les supprimer de la table auxiliaire
	WITH
		CTE AS
		(
			SELECT	TOP 10000 id
			FROM	dbo.table_auxiliaire
			ORDER	BY id
		)
	DELETE FROM	dbo.table_auxiliaire
	FROM		CTE AS C
	INNER JOIN	dbo.table_auxiliaire AS A
				ON C.id = A.id;
 
	-- Sortie dans la console (Onglet Messages de SQL Server Management Studio)
	SET @msg = 'Batch #' + CAST(@i AS varchar(10)) + '	 -	' + CAST(DATEDIFF(millisecond, @dt, GETDATE()) AS varchar(20));
	RAISERROR(@msg, 0, 1) WITH NOWAIT;
END

Et pour suivre le déplacement des lignes :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT		S.name + '.' + T.name AS table_name
		, PS.row_count
FROM		sys.dm_db_partition_stats AS PS
INNER JOIN	sys.tables AS T
			ON T.object_id = PS.object_id
INNER JOIN	sys.schemas AS S
			ON S.schema_id = T.schema_id
WHERE		S.name = 'dbo'
AND		T.name IN
		(
			'table_source'
			, 'table_cible'
			, 'table_auxiliaire'
		)

Il est préférable de créer les index une fois que la nouvelle table est complètement peuplée.

@++