资源池“内部"中的系统内存不足 [英] There is insufficient system memory in resource pool 'internal'

查看：138
本文介绍了资源池“内部"中的系统内存不足的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

SQL Server 2008链接服务器和临时INSERT导致内存快速泄漏，最终导致服务器无响应，并出现以下错误:

SQL Server 2008 Linked Server and ad-hoc INSERTs cause a rapid memory leak which eventually causes the server to become non-responsive and ends with the following error:

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

在重新启动SQL Server之前，服务器保持无响应.

The server remains non-responsive until SQL Server is restarted.

正在使用的软件:

  • Windows Vista Ultimate 64位内部版本6001 SP1

  • Windows Vista Ultimate 64 bit build 6001 SP1

Microsoft SQL Server 2008(SP1)-10.0.2734.0(X64)2009年9月11日版权所有(c)1988-2008 Windows NT上具有高级服务(64位)的Microsoft Corporation Express Edition 6.0(内部版本6001:Service Pack 1)

Microsoft SQL Server 2008 (SP1) - 10.0.2734.0 (X64) Sep 11 2009 14:30:58 Copyright (c) 1988-2008 Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)

SAOLEDB.11驱动程序

SAOLEDB.11 driver from SQL Anywhere 11.0.1.2276

将最大服务器内存(MB)设置为2048没有帮助.

Setting max server memory (MB) to 2048 did not help.

向服务器的启动参数"中添加各种-g值(例如-g256;)无济于事.

Adding various -g values (e.g., -g256;) to the server Startup Parameters did not help.

使用DBCC FREESYSTEMCACHE('ALL')，DBCC FREESESSIONCACHE和DBCC FREEPROCCACHE没有帮助.

Using DBCC FREESYSTEMCACHE ( 'ALL' ), DBCC FREESESSIONCACHE and DBCC FREEPROCCACHE did not help.

向SQL Server 2008 Service Pack 1安装累积更新包4并没有帮助，即使它包含涉及链接服务器使用的内存泄漏症状的修复程序.

Installing the Cumnulative update package 4 to SQL Server 2008 Service Pack 1 did not help, even though it contained a fix to a memory leak symptom involving Linked Server usage.

从INSERT分隔SELECT ... ROW_NUMBER()OVER ...查询无济于事.实验表明，复杂的SELECT不会导致内存泄漏，而INSERT会导致内存泄漏.

Separating the SELECT ... ROW_NUMBER() OVER ... query from the INSERT did not help. Experimentation showed that the complex SELECT did not cause the memory leak, the INSERT did.

更改代码以使用临时的"INSERT INTO OPENROWSET"语法而不是链接服务器无济于事；下面的代码显示了链接服务器的使用情况.

Changing the code to use the ad-hoc "INSERT INTO OPENROWSET" syntax instead of a linked server did not help; the code below shows the linked server usage.

sysinternals.com Process Explore实用程序显示内存使用与sqlserver.exe相关联，而不与SQL Anywhere OLEDB驱动程序SAOLEDB.11使用的DLL相关.

The sysinternals.com Process Explore utility shows that the memory usage was associated with sqlserver.exe, not the DLLs used by the SQL Anywhere OLEDB driver SAOLEDB.11.

请注意，链接服务器(代理表)的SQL Anywhere版本工作正常，可以在单个事务中将190万行从SQL Server 2008表拉"到SQL Anywhere 11数据库.此处显示的逻辑是尝试使用链接服务器功能来推送"行；相同的方向，不同的语法.

Note that the SQL Anywhere version of linked server (proxy tables) works OK, to "pull" 1.9 million rows from a SQL Server 2008 table to a SQL Anywhere 11 database in a single transaction. The logic shown here is an attempt to use the linked server feature to "push" the rows; same direction, different syntax.

代码如下；三或四次执行EXECUTE copy_mss_t2后，4G的RAM已耗尽:

The code follows; 4G of RAM is exhausted after three or four executions of the EXECUTE 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

这是SQL Server源表，其中包含190万行中约1G的数据:

Here is the SQL Server source table, containing about 1G of data in 1.9 million rows:

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 ) )

这是SQL Anywhere 11中的目标表:

Here is the target table in 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 ) );

推荐答案

您是否不需要在每次迭代后清空临时表#t?即在程序末尾添加TRUNCATE TABLE #t?我认为临时表#t一直存在，直到您的会话结束为止，直到存储过程结束为止. SELECT INTO只是追加到现有的#t上，而不是替换它.

Don't you need to empty the temp table #t after each iteration? i.e. add a TRUNCATE TABLE #t at the end of your procedure? I think that temp table #t exists until your session ends, not until the stored procedure ends. SELECT INTO just appends to the existing #t, but does not replace it.

另一件事是使用永久表，而不是存储在tempdb #tables中的东西.

Another thing would be to use a permanent table not something stored in tempdb #tables.

这篇关于资源池“内部"中的系统内存不足的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆