Discussion :

[ghohm]

Bonjour,

Je vous préviens ... je débute en tant que DBA sur SQL Server (2008 R2) !

J'ai donc cette erreur qui apparaît régulièrement (toutes les semaines ou tous les mois suivant l'activité des développeurs) sur le serveur de Développement que j'ai à gérer.

Lorsque j'ai ce problème, voilà ce que je fais (ce qui fait disparaître cette erreur) :

Code :

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

DBCC FREEPROCCACHE; --<a href="http://msdn.microsoft.com/fr-fr/library/ms174283.aspx" target="_blank">http://msdn.microsoft.com/fr-fr/library/ms174283.aspx</a>

Désormais, j'ai trouvé cet article sur MSDN :
http://msdn.microsoft.com/fr-fr/libr...ql.105%29.aspx

J'ai suivi les conseils et j'ai vérifié la définition des options de configuration de mon serveur : http://msdn.microsoft.com/fr-fr/libr...ql.105%29.aspx.

Code :

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

SELECT * FROM sys.configurations WHERE configuration_id IN (1540, 1543, 1544, 1548) ORDER BY 1; GO

1540 min memory per query (KB) 1024 512 2147483647 1024 minimum memory per query (kBytes) 1 1
1543 min server memory (MB) 0 0 2147483647 16 Minimum size of server memory (MB) 1 1
1544 max server memory (MB) 2147483647 16 2147483647 2147483647 Maximum size of server memory (MB) 1 1
1548 awe enabled 0 0 1 0 AWE enabled in the server 0 1

Si j'ai bien compris il est noté ici que mon serveur dispose au maximum de 2 147 483 647 Mo = 2 097 152 Go = 2048 To = 2 Po ... y aurait pas comme un problème !

Mon serveur dispose en réalité de 16 Go (Windows Server 2008 R2 64 bits), ce qui est déjà pas mal non ?!

Quelles valeurs changer ? et jusqu'à quelle valeur : 16 384 Mo (= 16 Go) ?


Gôm

PS : Mon serveur n'a qu'une seule instance SQL Server.

[ghohm]

Bon apparemment les 2 Po c'est normal. C'est la valeur renseignée par défaut.

http://msdn.microsoft.com/fr-fr/libr...ql.105%29.aspx

Mon problème est donc ailleurs !

Une idée d'où il pourrait se situer ?


Gôm

[ghohm]

Je viens de lire sur le net (parmi mes nombreuses recherches ... comprendre "j'sais plus où" ) que le Gouverneur de ressources pouvaient être la cause de mon problème.

C'est pas ça : il est désactivé et, à priori, n'a jamais été activé.

Cherche encore ...


Gôm

[SQLpro]

Cela peut arriver lorsqu'une insertion ou un update massif dépasse la quantité de RAM...
Avec 16 Go, c'est possible si vos développeurs ont l'imbécilité de charger des fichiers de données faisant justement plus de 16 Go de données ou bien des scripts SQL long de plusieurs centaines de milliers de lignes d'un seule coup.
En effet un import massif = 1 transaction, donc toute les données doivent monter en RAM avant de pouvoir commencer à les intégrer....

Dans ce cas, il faut chercher à saucissonner les transactions.

Autre piste, votre quantité de RAM a été minimisé. Voyez cela vec la procédure sp_configure en nous disant ce que vou voyez dans la colonne Run Value de l'entré Max Server memory.
SI vous êtes en 64 bits un bon réglagge de votre serveur doit être de 12 GO (ce qui laisse 4 Go pour Windows.

Autre possibilité : vous êtes sur un serveur virtuel... et là c'est la merde du fait du pompage de RAM par le ballooning... Désactivez-le et assigner à SQL Server les 12 Go de RAM en positionnant à la fois Max server Memory et Min server Memory à 12 Go.

A +

[ghohm]

Cela peut arriver lorsqu'une insertion ou un update massif dépasse la quantité de RAM...
Avec 16 Go, c'est possible si vos développeurs ont l'imbécilité de charger des fichiers de données faisant justement plus de 16 Go de données ou bien des scripts SQL long de plusieurs centaines de milliers de lignes d'un seule coup.
En effet un import massif = 1 transaction, donc toute les données doivent monter en RAM avant de pouvoir commencer à les intégrer....

Dans ce cas, il faut chercher à saucissonner les transactions.

Ce n'est (heureusement) pas mon cas !

Autre piste, votre quantité de RAM a été minimisé. Voyez cela vec la procédure sp_configure en nous disant ce que vou voyez dans la colonne Run Value de l'entré Max Server memory.
SI vous êtes en 64 bits un bon réglagge de votre serveur doit être de 12 GO (ce qui laisse 4 Go pour Windows.

C'est la valeur par défaut : 2147483647 Mo = 2 Po ... on peut pas dire que ce soit minimisé !

Autre possibilité : vous êtes sur un serveur virtuel... et là c'est la merde du fait du pompage de RAM par le ballooning... Désactivez-le et assigner à SQL Server les 12 Go de RAM en positionnant à la fois Max server Memory et Min server Memory à 12 Go.

Non j'ai bien un serveur physique.

[mikedavem]

Il faudrait pouvoir avoir la requête en question qui vous pose ce problème. L'avez-vous identifié ?

Quel type d'architecture (x86, x64 ...) ? Quel système d'exploitation ? Quelle version de SQL Server ?

++

[ghohm]

Architecture : x64
OS : Windows Server 2008 R2
RAM : 16 Go

Version SQL Server :

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

Je n'ai malheureusement pas identifié quelle requête posait problème. Je n'arrivais même plus à me connecter à Management Studio !

[elsuket]

Bonjour,

Comme vous avez plus de 4GB de RAM et que vous êtes sur une plateforme x64, il serait bon de mettre une valeur correcte pour max server memory, comme vous l'a suggéré SQLPro.
J'aurai aussi conseillé d'activer Lock Pages In Memory dans la stratégie de sécurité locale.

Ensuite comme vous êtes sous SQL Server 2008, vous pouvez utiliser une session d'événements étendus pour capturer la requête qui génère cette erreur :

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
CREATE	EVENT SESSION memoire_insuffisante ON SERVER
ADD	EVENT sqlserver.error_reported
	(
		ACTION
		(
			sqlserver.sql_text
			, sqlserver.client_app_name
			, sqlserver.client_hostname
			, sqlserver.database_id
			, sqlserver.session_nt_username
		)
		WHERE error = 701
	)
ADD	TARGET package0.asynchronous_file_target
	(
		SET FILENAME = 'D:\unDossier\XE\memoire_insuffisante.xel'
		, METADATAFILE = 'D:\unDossier\XE\memoire_insuffisante.xem'
	)
GO

Cette session va capturer toutes les requêtes qui génèrent cette erreur.
Pour la démarrer :

Code :

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

1
2
3
ALTER EVENT SESSION memoire_insuffisante ON SERVER
STATE = START
GO

Et pour dépouiller les fichiers :

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
;WITH
	CTE (event_data_xml) AS
	(	
		SELECT	CAST(event_data AS xml) AS data
		FROM	sys.fn_xe_file_target_read_file
		(
			'D:\unDossier\XE\memoire_insuffisante*.xel'
			, 'D:\unDossier\XE\memoire_insuffisante*.xem'
			, NULL
			, NULL
		)
	)
SELECT	DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), GETDATE()), event_data_xml.value('(/event/@timestamp)[1]', 'datetime')) AS time_stamp
	, event_data_xml.value('(/event/data/value)[1]', 'int') AS message_id
	, event_data_xml.value('(/event/data/value)[2]', 'tinyint') AS severity
	, event_data_xml.value('(/event/data/value)[3]', 'tinyint') AS state
	, event_data_xml.value('(/event/data/value)[5]', 'varchar(max)') AS message_label
	, event_data_xml.value('(/event/action/value)[1]', 'nvarchar(max)') AS sql_text
	, event_data_xml.value('(/event/action/value)[2]', 'sysname') AS application_name
	, event_data_xml.value('(/event/action/value)[5]', 'sysname') AS requesting_login
	, event_data_xml.value('(/event/action/value)[3]', 'sysname') AS client_host_name
FROM	CTE
ORDER	BY time_stamp

Pour supprimer la session une fois la solution trouvée :

Code :

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

1
2
3
4
5
6
ALTER EVENT SESSION memoire_insuffisante ON SERVER
STATE = STOP
GO
 
DROP EVENT SESSION memoire_insuffisante ON SERVER
GO

Notez que cela ne supprime pas les fichiers du dossier D:\unDossier\XE\, ce que vous devrez faire manuellement.

@++

[ghohm]

Bonjour,

Bonjour,

Comme vous avez plus de 4GB de RAM et que vous êtes sur une plateforme x64, il serait bon de mettre une valeur correcte pour max server memory, comme vous l'a suggéré SQLPro.

C'est fait !

Code SQL :

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

1
2
3
4
5
6
7
8
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 12288;
GO
RECONFIGURE;
GO

J'aurai aussi conseillé d'activer Lock Pages In Memory dans la stratégie de sécurité locale.

OK (http://msdn.microsoft.com/fr-fr/libr...ql.105%29.aspx), mais ai-je également besoin d'activer AWE (http://msdn.microsoft.com/fr-fr/library/ms190961.aspx) sachant que je suis sur une architecture 64 bits ?

Cet article (http://blogs.msdn.com/b/support_sql_...ql-server.aspx) laisse sous-entendre que je n'ai pas besoin d'activer AWE avec un x64.

Ensuite comme vous êtes sous SQL Server 2008, vous pouvez utiliser une session d'événements étendus pour capturer la requête qui génère cette erreur :

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
CREATE    EVENT SESSION memoire_insuffisante ON SERVER
ADD    EVENT sqlserver.error_reported
    (
        ACTION
        (
            sqlserver.sql_text
            , sqlserver.client_app_name
            , sqlserver.client_hostname
            , sqlserver.database_id
            , sqlserver.session_nt_username
        )
        WHERE error = 701
    )
ADD    TARGET package0.asynchronous_file_target
    (
        SET FILENAME = 'D:\unDossier\XE\memoire_insuffisante.xel'
        , METADATAFILE = 'D:\unDossier\XE\memoire_insuffisante.xem'
    )
GO

Cette session va capturer toutes les requêtes qui génèrent cette erreur.
Pour la démarrer :

Code :

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

1
2
3
ALTER EVENT SESSION memoire_insuffisante ON SERVER
STATE = START
GO

Et pour dépouiller les fichiers :

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
;WITH
    CTE (event_data_xml) AS
    (    
        SELECT    CAST(event_data AS xml) AS data
        FROM    sys.fn_xe_file_target_read_file
        (
            'D:\unDossier\XE\memoire_insuffisante*.xel'
            , 'D:\unDossier\XE\memoire_insuffisante*.xem'
            , NULL
            , NULL
        )
    )
SELECT    DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), GETDATE()), event_data_xml.value('(/event/@timestamp)[1]', 'datetime')) AS time_stamp
    , event_data_xml.value('(/event/data/value)[1]', 'int') AS message_id
    , event_data_xml.value('(/event/data/value)[2]', 'tinyint') AS severity
    , event_data_xml.value('(/event/data/value)[3]', 'tinyint') AS state
    , event_data_xml.value('(/event/data/value)[5]', 'varchar(max)') AS message_label
    , event_data_xml.value('(/event/action/value)[1]', 'nvarchar(max)') AS sql_text
    , event_data_xml.value('(/event/action/value)[2]', 'sysname') AS application_name
    , event_data_xml.value('(/event/action/value)[5]', 'sysname') AS requesting_login
    , event_data_xml.value('(/event/action/value)[3]', 'sysname') AS client_host_name
FROM    CTE
ORDER    BY time_stamp

Pour supprimer la session une fois la solution trouvée :

Code :

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

1
2
3
4
5
6
ALTER EVENT SESSION memoire_insuffisante ON SERVER
STATE = STOP
GO
 
DROP EVENT SESSION memoire_insuffisante ON SERVER
GO

Notez que cela ne supprime pas les fichiers du dossier D:\unDossier\XE\, ce que vous devrez faire manuellement.

@++

Je mets ça en place tout de suite !

Merciiiiiii !

[ghohm]

J'ai l'erreur suivante lorsque j'essaye de démarrer la session :

Msg 25602, Niveau 17, État 22, Ligne 1
"CE79811F-1A80-40E1-8F5D-7445A3F375E7.package0.asynchronous_file_target", de type cible, a rencontré une erreur de configuration durant son initialisation. Impossible d'ajouter l'objet à la session d'événements.