- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
SQL Server 2008 链接服务器和临时插入导致快速内存泄漏,最终导致服务器无响应并以以下错误结束:
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
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
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 ) )
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
存在直到您的 session 结束,而不是直到存储过程结束。 SELECT INTO
只是附加到现有的 #t
,但不会取代它。
另一件事是使用永久表而不是存储在 tempdb 中的内容 #tables
.
关于sql-server-2008 - 资源池 'internal'系统内存不足,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/1537812/
SQL Server 2008 链接服务器和临时插入导致快速内存泄漏,最终导致服务器无响应并以以下错误结束: Msg 701, Level 17, State 123, Server BRECK-PC
我收到此错误: 资源池“默认”中的系统内存不足,无法运行此查询。 我正在运行 100,000 个简单的插入语句,如下所示。我在第 85,000 次插入时遇到了错误。 这是我正在学习的类(class)的
我已将 commons-pooling-1.6.jar 添加到我的类路径并尝试实例化一个 StackObjectPool 但每次都失败: // Deprecated. ObjectPool oPool
我有一个正在运行的服务,每分钟可获得 50-100 个查询。这些都不是高成本查询。该服务已经运行了大约 3-4 个月,没有出现任何错误。 几天前突然开始出现“资源池‘默认’中的系统内存不足,无法运行此
我只是想右击然后选择“从数据库生成模型”。我得到了错误: Unable to generate the model because of the following exception: 'An er
我是一名优秀的程序员,十分优秀!