Problème de locks mémoire [Résolu]

[Etienne5685]

Bonjour,

Je dois intégrer 45 Millions d'enregistrements dans une table d'une BDD
Configuration :
WINDOWS 2003 X64
SQL SERVER 2008 R2
18 G de ram (14G pour SQL SERVER)
4 Processeurs

Voici la structure de la requête exécutée:
INSERT INTO baseB.TableA
(Champ1,
Champ2,
....)
SELECT
Champ1,
Champ2,
....
FROM baseA.TableA

Le traitement s'exécute correctement on peut voir les ligne se charger dans la table BaseB.TableA jusqu'a 42 Millions de lignes, après le nombre d'enregistrements redescend à 0 et nous avons l'erreur suivante:

DESCRIPTION: Error: 1204, Severity: 19, State: 1
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

Le probleme se reproduit à 9 millions de lignes sur un serveur plus "petit"
2G de ram
1 proc
Windwos server 2003 32
sql server 2008 r2

test réaliser un select sur toute la table provoque l'erreur suivante:
Une erreur s'est produite lors de l'exécution du lot. Message d'erreur*: Une exception de type 'System.OutOfMemoryException' a été levée.


Comment peut-on empêcher ces problèmes ?



Merci

[elsuket]

Bonjour,

C'est un problème d'escalade de verrous.

Comme votre transaction est longue et que chaque ligne insérée / lue doit être verrouillée pour toute la durée de la transaction, un grand nombre de verrous doivent être gérés par SQL Server.

Je ne me souviens plus des seuils exacts, mais quand SQL Server maintient environ 5,000 verrous de ligne (par exemple), il "escalade" et le remplace par un seul verrou de page, de façon à consommer moins de mémoire.

La contrepartie est que la concurrence sur la table est moins élevée (on bloque plus "large").

Il en va ainsi de suite pour l'extent (groupe de 8 pages), puis la table (n'hésitez pas à me corriger si un verrou au niveau partition est pris, mais je ne crois pas que ce soit le cas).

Chaque verrou coûte 96 octets de mémoire.
La quantité maximale de mémoire allouée aux verrous est de 40% de la mémoire totale allouée à SQL Server.
Donc moins votre serveur a de mémoire, plus vite vous obtenez le problème.

Enfin un verrou ne peut pas être acquis dans certaines conditions si une autre transaction a démarré et lit les données que vous voulez lire également.

La règle générale est : faites en sorte que vos transactions soient courtes.
Ce qui se traduit pour votre cas par un insertion en lots de ligne : vous insérez, mettons, 1 million de lignes, puis encore un million, ...

D'habitude je fais cela pour des UPDATE massifs, mais le principe reste le même : stockez dans une table séparée seulement la clé primaire de la table, en conjonction avec une colonne de type int auto-incrémentée (IDENTITY).

Ensuite vous procédez par lot en faisant une jointure entre les la table qui subit le SELECT et cette table BETWEEN 1 AND 1,000,000, et vous incrémentez pour passer au lot suivant d'un million de lignes.

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
CREATE TABLE cle_primaire
(
	i int IDENTITY NOT NULL
	, pk int
)
GO
 
INSERT	dbo.cle_primaire(pk)
SELECT	pk
FROM	dbo.tableA
GO
 
ALTER TABLE cle_primaire
ADD CONSTRAINT PKcle_primaire PRIMARY KEY (i)
GO
 
DECLARE @i int = 1
	, @batch_size int = 1000000
	, @row_count int = IDENT_CURRENT('dbo.cle_primaire')
 
WHILE	@i <= @row_count
BEGIN
	INSERT INTO baseB.dbo.TableA
	(
		Colonne1
		, Colonne2
		....
	)
	SELECT	Colonne1
		, Colonne2
		....
	FROM	baseA.dbo.TableA AS A
	JOIN	baseA.dbo.cle_primare AS PK
			ON A.pk = PK.pk
	WHERE	PK.i BETWEEN @i AND @i + @batch_size
 
	SET	@i += @batch_size
 
	RAISERROR('%d', 1, 1, @i) WITH NOWAIT -- imprime le nombre de lignes traitées
 
END

@++

[Sergejack]

Je ne connais pas bien la version 2008 (et 2008 R2) mais, il me semble que vous pouvez définir le lock escalation table par table.
Alors peut-être pourriez-vous définir qu'un lock est pris sur toute la table

Envoyé par elsuket

Code :

@++

Quite à faire des jointure autant faire des auto-jointure (et profiter des indexes).

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
WHILE 1 = 1
BEGIN
 INSERT dbo.Table2 (
 pk, c1, c2, ...
 )
 SELECT TOP(1000000)
 pk, c1, c2, ...
 FROM dbo.Table1 AS T1
 WHERE NOT EXISTS (
 SELECT 1
 FROM dbo.Table2 AS T2
 WHERE T2.Pk = T1.Pk
 )
 
 IF @@ROWCOUNT = 0
 BREAK
END

[Etienne5685]

Merci pour votre aide vous avez répondus à mes interrogations

[elsuket]

il me semble que vous pouvez définir le lock escalation table par table.

Certes mais rien ne garantit que SQL Server l'utilisera.
Le seul moyen de le forcer à le faire se fait au niveau de l'instance par le traceflag 1211, ou 1224 pour être moins brutal.
Dans les deux cas, c'est dangereux.

Quite à faire des jointure autant faire des auto-jointure (et profiter des indexes).

Les auto-jointures peuvent coûter cher.
La table que je donne est mono-colonne, et indexée en cluster ...

@++

[iberserk]

Les auto-jointures peuvent coûter cher.

Tout a fait d'accord... dans ce cas précis la solution de elsuket sera plus performante même si elle nécessite la création de la table de 'clé'

[Sergejack]

Je ne suis pas d'accord.
J'ai fait quelques tests et la solution de Elsuket est moins rapide que la mienne, l'overhead causé par la population de la table cle_primaire est trop important.
L'intérêt de sa solution est l'utilisation d'une clause WHERE avec des bornes (sans clé numérique, ce serait pê moins efficace) ce qui est bien plus rapide que la clause EXISTS (ou équivalent) que j'utilise (et indépendamment du fait d'être une auto-jointure).

Mais, toujours dans le cas d'une clé numérique, il existe un moyen intéressant d'optimiser l'approche par bornes d'Elsuket (ps: attention, Elsuket inserait parfois 2 fois le même record).

Voici mon code pour insérer des records par million (au singulier).

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
 
CREATE TABLE bornes (
	ID INT
	, IID INT PRIMARY KEY
)
GO
 
INSERT bornes (
	ID
	, IID
)
SELECT
	ID
	, IID / 1000000 -- 1 million
FROM (
	SELECT
		ID
		, ROW_NUMBER() OVER (ORDER BY ID ASC) AS IID
	FROM table120
) AS T
WHERE IID % 1000000 /* 1 million */ = 0
GO
 
DECLARE @lastID INT
SET @lastID = 0
DECLARE @nextID INT
DECLARE @i INT
SET @i = 1
 
WHILE 1 = 1
BEGIN
	SET @nextID = 2147483647 -- Max value for a INT
 
	SELECT @nextID = ID
	FROM bornes
	WHERE IID = @i
 
 INSERT dbo.Table130 (
 ID, vA, vB
 )
 SELECT
  ID, vA, vB
 FROM dbo.Table120 AS T1
 WHERE WHERE ID BETWEEN @lastID AND @nextID - 1
 
	SET @lastID = @nextID
	SET @i = @i + 1
 
 IF @nextID = 2147483647
 BREAK
END
GO

Sur ce, bien que ne l'ayant pas démonté, je me permet d'affirmer sur base de mes témoignages qu'aucune perte de performance pertinente était à noter lors de l'emploi d'une auto-jointure plutôt qu'une autre jointure conjugué à l'emploi d'un insert (témoignage valant pour SQL Server 2005).