Mémoire système insuffisante dans le pool de ressources "internes"
SQL Server 2008 Serveur Lié et ad-hoc INSERTs en cause rapide de la fuite de mémoire qui provoque finalement le serveur de répondre et se termine avec l'erreur suivante:
Msg 701, Level 17, State 123, Server BRECK-PC\SQLEXPRESS, Line 2
There is insufficient system memory in resource pool 'internal' to run this
query.
Location: qxcntxt.cpp:1052
Expression: cref == 0
SPID: 51
Process ID: 1880
Le serveur n'est toujours pas répondre jusqu'à ce que SQL Server est redémarré.
Logiciel en cours d'utilisation:
-
Windows Vista Ultimate 64 bits build 6001 SP1
-
Microsoft SQL Server 2008 (SP1) - 10.0.2734.0 (X64) Sep 11 2009 14:30:58 Copyright (c) de 1988 à 2008 Microsoft Corporation Express Edition with Advanced Services (64-bit) Windows NT 6.0 (Build 6001: Service Pack 1)
-
SAOLEDB.11 pilote à partir de SQL Anywhere 11.0.1.2276
Réglage max server memory (MO) 2048 n'a pas aidé.
Ajoutant divers -valeurs g (par exemple, -g256;) pour le Démarrage du serveur, les Paramètres n'ont pas d'aide.
À l'aide de la commande DBCC FREESYSTEMCACHE ( 'ALL' ), DBCC FREESESSIONCACHE et DBCC FREEPROCCACHE n'a pas aidé.
L'installation de la Cumnulative package de mise à jour 4 pour SQL Server 2008 Service Pack 1 n'a pas aidé, même s'il contenait un correctif à une fuite de mémoire symptôme impliquant Lié l'utilisation du Serveur.
Séparant les SÉLECTIONNER ... ROW_NUMBER() OVER ... requête à partir de l'INSERT n'a pas aidé. L'expérimentation a montré que le complexe SÉLECTIONNEZ n'est pas la cause de la fuite de mémoire, l'INSERTION a.
Modifiant le code à utiliser ad-hoc "INSERT INTO OPENROWSET" syntaxe au lieu d'un serveur lié n'a pas aidé; le code ci-dessous montre le serveur lié à l'utilisation.
L'sysinternals.com Processus d'Explorer utilitaire montre que l'utilisation de la mémoire a été associée avec sqlserver.exe, pas les Dll utilisées par le SQL Anywhere pilote OLEDB SAOLEDB.11.
Noter que le SQL Anywhere version de serveur lié (proxy tables) fonctionne bien, "pull", 1,9 million de lignes à partir d'un Serveur SQL server 2008 table SQL Anywhere 11 base de données en une seule opération. La logique montré ici est une tentative d'utiliser le serveur lié à la fonctionnalité de "pousser" la lignes; même direction, une syntaxe différente.
Le code qui suit; 4G de RAM est épuisé au bout de trois ou quatre exécutions de l'EXÉCUTER copy_mss_t2:
EXEC sys.sp_configure
N'show advanced options',
N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure
N'max server memory (MB)',
N'2048'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure
N'show advanced options',
N'0'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.sp_MSset_oledb_prop
N'SAOLEDB.11',
N'AllowInProcess',
1
GO
sp_addlinkedserver
@server = 'mem',
@srvproduct = 'SQL Anywhere OLE DB Provider',
@provider = 'SAOLEDB.11',
@datasrc = 'mem_PAVILION2'
GO
EXEC master.dbo.sp_serveroption
@server=N'mem',
@optname=N'rpc',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption
@server=N'mem',
@optname=N'rpc out',
@optvalue=N'true'
GO
sp_addlinkedsrvlogin
@rmtsrvname = 'mem',
@useself = 'false',
@locallogin = NULL,
@rmtuser = 'dba',
@rmtpassword = 'sql'
GO
CREATE PROCEDURE copy_mss_t2
@from_row BIGINT,
@to_row BIGINT,
@rows_copied_count BIGINT OUTPUT
AS
SELECT *
INTO #t
FROM ( SELECT *,
ROW_NUMBER()
OVER ( ORDER BY sample_set_number,
connection_number )
AS t2_row_number
FROM mss_t2 ) AS ordered_mss_t2
WHERE ordered_mss_t2.t2_row_number BETWEEN @from_row AND @to_row;
SELECT @rows_copied_count = COUNT(*)
FROM #t;
INSERT INTO mem..dba.sa_t2
SELECT sampling_id,
sample_set_number,
connection_number,
blocker_owner_table_name,
blocker_lock_type,
blocker_owner_name,
blocker_table_name,
blocker_reason,
blocker_row_identifier,
current_engine_version,
page_size,
ApproximateCPUTime,
BlockedOn,
BytesReceived,
BytesSent,
CacheHits,
CacheRead,
"Commit",
DiskRead,
DiskWrite,
FullCompare,
IndAdd,
IndLookup,
Isolation_level,
LastReqTime,
LastStatement,
LockCount,
LockName,
LockTableOID,
LoginTime,
LogWrite,
Name,
NodeAddress,
Prepares,
PrepStmt,
QueryLowMemoryStrategy,
QueryOptimized,
QueryReused,
ReqCountActive,
ReqCountBlockContention,
ReqCountBlockIO,
ReqCountBlockLock,
ReqCountUnscheduled,
ReqStatus,
ReqTimeActive,
ReqTimeBlockContention,
ReqTimeBlockIO,
ReqTimeBlockLock,
ReqTimeUnscheduled,
ReqType,
RequestsReceived,
Rlbk,
RollbackLogPages,
TempFilePages,
TransactionStartTime,
UncommitOp,
Userid,
previous_ApproximateCPUTime,
interval_ApproximateCPUTime,
previous_Commit,
interval_Commit,
previous_Rlbk,
interval_Rlbk
FROM #t;
GO
DECLARE @rows_copied_count BIGINT
EXECUTE copy_mss_t2 1110001, 1120000, @rows_copied_count OUTPUT
SELECT @rows_copied_count
GO
EXECUTE create_linked_server
GO
DECLARE @rows_copied_count BIGINT
EXECUTE copy_mss_t2 1120001, 1130000, @rows_copied_count OUTPUT
SELECT @rows_copied_count
GO
EXECUTE create_linked_server
GO
Ici est le Serveur SQL de la table source, contenant environ 1G de données de 1,9 million de lignes:
CREATE TABLE mss_t2 (
sampling_id BIGINT NOT NULL,
sample_set_number BIGINT NOT NULL,
connection_number BIGINT NOT NULL,
blocker_owner_table_name VARCHAR ( 257 ) NULL,
blocker_lock_type VARCHAR ( 32 ) NULL,
blocker_owner_name VARCHAR ( 128 ) NULL,
blocker_table_name VARCHAR ( 128 ) NULL,
blocker_reason TEXT NULL,
blocker_row_identifier VARCHAR ( 32 ) NULL,
current_engine_version TEXT NOT NULL,
page_size INTEGER NOT NULL,
ApproximateCPUTime DECIMAL ( 30, 6 ) NULL,
BlockedOn BIGINT NULL,
BytesReceived BIGINT NULL,
BytesSent BIGINT NULL,
CacheHits BIGINT NULL,
CacheRead BIGINT NULL,
"Commit" BIGINT NULL,
DiskRead BIGINT NULL,
DiskWrite BIGINT NULL,
FullCompare BIGINT NULL,
IndAdd BIGINT NULL,
IndLookup BIGINT NULL,
Isolation_level BIGINT NULL,
LastReqTime TEXT NOT NULL DEFAULT '1900-01-01',
LastStatement TEXT NULL,
LockCount BIGINT NULL,
LockName BIGINT NULL,
LockTableOID BIGINT NULL,
LoginTime TEXT NOT NULL DEFAULT '1900-01-01',
LogWrite BIGINT NULL,
Name VARCHAR ( 128 ) NULL,
NodeAddress TEXT NULL,
Prepares BIGINT NULL,
PrepStmt BIGINT NULL,
QueryLowMemoryStrategy BIGINT NULL,
QueryOptimized BIGINT NULL,
QueryReused BIGINT NULL,
ReqCountActive BIGINT NULL,
ReqCountBlockContention BIGINT NULL,
ReqCountBlockIO BIGINT NULL,
ReqCountBlockLock BIGINT NULL,
ReqCountUnscheduled BIGINT NULL,
ReqStatus TEXT NULL,
ReqTimeActive DECIMAL ( 30, 6 ) NULL,
ReqTimeBlockContention DECIMAL ( 30, 6 ) NULL,
ReqTimeBlockIO DECIMAL ( 30, 6 ) NULL,
ReqTimeBlockLock DECIMAL ( 30, 6 ) NULL,
ReqTimeUnscheduled DECIMAL ( 30, 6 ) NULL,
ReqType TEXT NULL,
RequestsReceived BIGINT NULL,
Rlbk BIGINT NULL,
RollbackLogPages BIGINT NULL,
TempFilePages BIGINT NULL,
TransactionStartTime TEXT NOT NULL DEFAULT '1900-01-01',
UncommitOp BIGINT NULL,
Userid VARCHAR ( 128 ) NULL,
previous_ApproximateCPUTime DECIMAL ( 30, 6 ) NOT NULL DEFAULT 0.0,
interval_ApproximateCPUTime AS ( COALESCE ( "ApproximateCPUTime", 0 ) - previous_ApproximateCPUTime ),
previous_Commit BIGINT NOT NULL DEFAULT 0,
interval_Commit AS ( COALESCE ( "Commit", 0 ) - previous_Commit ),
previous_Rlbk BIGINT NOT NULL DEFAULT 0,
interval_Rlbk AS ( COALESCE ( Rlbk, 0 ) - previous_Rlbk ) )
Ici est la cible de la table dans SQL Anywhere 11:
CREATE TABLE sa_t2 (
sampling_id BIGINT NOT NULL,
sample_set_number BIGINT NOT NULL,
connection_number BIGINT NOT NULL,
blocker_owner_table_name VARCHAR ( 257 ) NULL,
blocker_lock_type VARCHAR ( 32 ) NULL,
blocker_owner_name VARCHAR ( 128 ) NULL,
blocker_table_name VARCHAR ( 128 ) NULL,
blocker_reason TEXT NULL,
blocker_row_identifier VARCHAR ( 32 ) NULL,
current_engine_version TEXT NOT NULL,
page_size INTEGER NOT NULL,
ApproximateCPUTime DECIMAL ( 30, 6 ) NULL,
BlockedOn BIGINT NULL,
BytesReceived BIGINT NULL,
BytesSent BIGINT NULL,
CacheHits BIGINT NULL,
CacheRead BIGINT NULL,
"Commit" BIGINT NULL,
DiskRead BIGINT NULL,
DiskWrite BIGINT NULL,
FullCompare BIGINT NULL,
IndAdd BIGINT NULL,
IndLookup BIGINT NULL,
Isolation_level BIGINT NULL,
LastReqTime TEXT NOT NULL DEFAULT '1900-01-01',
LastStatement TEXT NULL,
LockCount BIGINT NULL,
LockName BIGINT NULL,
LockTableOID BIGINT NULL,
LoginTime TEXT NOT NULL DEFAULT '1900-01-01',
LogWrite BIGINT NULL,
Name VARCHAR ( 128 ) NULL,
NodeAddress TEXT NULL,
Prepares BIGINT NULL,
PrepStmt BIGINT NULL,
QueryLowMemoryStrategy BIGINT NULL,
QueryOptimized BIGINT NULL,
QueryReused BIGINT NULL,
ReqCountActive BIGINT NULL,
ReqCountBlockContention BIGINT NULL,
ReqCountBlockIO BIGINT NULL,
ReqCountBlockLock BIGINT NULL,
ReqCountUnscheduled BIGINT NULL,
ReqStatus TEXT NULL,
ReqTimeActive DECIMAL ( 30, 6 ) NULL,
ReqTimeBlockContention DECIMAL ( 30, 6 ) NULL,
ReqTimeBlockIO DECIMAL ( 30, 6 ) NULL,
ReqTimeBlockLock DECIMAL ( 30, 6 ) NULL,
ReqTimeUnscheduled DECIMAL ( 30, 6 ) NULL,
ReqType TEXT NULL,
RequestsReceived BIGINT NULL,
Rlbk BIGINT NULL,
RollbackLogPages BIGINT NULL,
TempFilePages BIGINT NULL,
TransactionStartTime TEXT NOT NULL DEFAULT '1900-01-01',
UncommitOp BIGINT NULL,
Userid VARCHAR ( 128 ) NULL,
previous_ApproximateCPUTime DECIMAL ( 30, 6 ) NOT NULL DEFAULT 0.0,
interval_ApproximateCPUTime DECIMAL ( 30, 6 ) NOT NULL COMPUTE ( COALESCE ( "ApproximateCPUTime", 0 ) - previous_ApproximateCPUTime ),
previous_Commit BIGINT NOT NULL DEFAULT 0,
interval_Commit BIGINT NOT NULL COMPUTE ( COALESCE ( "Commit", 0 ) - previous_Commit ),
previous_Rlbk BIGINT NOT NULL DEFAULT 0,
interval_Rlbk BIGINT NOT NULL COMPUTE ( COALESCE ( Rlbk, 0 ) - previous_Rlbk ),
PRIMARY KEY ( sample_set_number, connection_number ) );
- J'ai ajouté les instructions CREATE TABLE pour les tables source et cible. Poursuite de l'expérimentation indique que la variété des types de données peut être la cause de la fuite de mémoire; par exemple, plusieurs colonnes de TEXTE, 30 chiffres Décimaux, calculé sous forme de colonnes et ainsi de suite. Mes PROFONDES EXCUSES pour quitter le DDL à partir de l'original de l'énoncé du problème... un peu injuste.
- Les premières indications sont que le fait d'avoir plusieurs colonnes de TEXTE dans la table source les causes de la mémoire de SQL Server fuite. Le changement de tous, mais une seule colonne de TEXTE VARCHAR semble faire l'affaire. Un test complet est jusqu'à 250 000 lignes avec zéro RAM augmentation; je vais l'attendre à la fin, puis mettre en place un petit reproductible.
Vous devez vous connecter pour publier un commentaire.
N'avez-vous pas besoin de vider la table temp
#t
après chaque itération? c'est à dire ajouter unTRUNCATE TABLE #t
à la fin de votre procédure? Je pense que la table temporaire#t
existe jusqu'à ce que votre session se termine, pas jusqu'à ce que la procédure stockée se termine.SELECT INTO
juste ajoute à l'existant#t
, mais ne la remplace pas.Une autre chose serait d'utiliser une table permanente pas quelque chose stockées dans la base de données tempdb
#tables
.Le problème est à l'aide d'un serveur lié via le SQL Anywhere 11.0.1 fournisseur de SAOLEDB.11 pour insérer des données dans une colonne cible déclarée de plus de VARCHAR ( 8000 ). Ici est simplifié reproductible:
Vous pourriez essayer de l'exécution de l'insérer dans les lots au lieu de le jeu de données entier à la fois.
Au lieu d'utiliser des Tables temporaires, pouvez-vous essayer d'utiliser des tableaux de Variables?
par exemple.
J'ai eu un problème similaire, mon code contient de l'utilisation d'un simple #temp tableau dans une boucle, qui en est l'origine et je l'ai remplacé avec une table permanente.
Semble être au travail.
Merci
Naveen