tempdb在缓冲池中占用100GB内存

2014-06-11 01:47:10 962 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL Server内存管理：Buffer Pool、Plan Cache与动态仲裁机制详解 SQL Server内存管理并非简单配置max server memory即可，其本质是操作系统与数据库引擎协同下的动态资源调度系统。核心在于理解Buffer Pool的LRU缓存机制、Plan Cache的计划重用逻辑，以及Dynamic Memory Arbitrator如何基于Page Life Expectancy、内存压力信号实时调整目标内存。该机制决定了SQL Server在OLTP高并发与OLAP大扫描场景下的性能分水岭——内存不足将直接引发物理I/O飙升、查询溢出tempdb及编译风暴。实际阅读详情

tempdb在缓冲池中占用100GB内存，这个问题主要是有大量的临时表创建和删除，临时表上又建有索引引起的，我测试过，建立临时表，然后删除临时表，但缓存依旧停留在缓冲池，难道一定要使用DBCC DROPCLEANBUFFERS 来删除缓存，但这个同时也删除了其他的缓存。
如何删除tempdb的缓存，或者让tempdb在每次使用都尽可能的不缓存数据？
为什么tempdb的缓存总不释放？

...全文

SQL Server INSERT性能深度解析：从语法到底层资源消耗 INSERT是关系型数据库中最基础的DML操作，但在SQL Server中，它远不止语法层面的数据写入——其执行过程深度耦合事务日志、锁机制、统计信息更新、索引维护与tempdb资源调度。理解INSERT的执行原理，有助于识别隐式转换、触发器开销、页分裂、U锁争用等高频性能陷阱；掌握执行计划中的Compute Scalar、Table Spool、Index Insert等关键算子，可精准定位‘简单插入’背后的高延迟根源。本文聚焦SQLServer Insert实际运行时的行为特征与工程调优路径，覆盖高并发阅读详情

最爱午夜

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

Q315054403 2014-06-13

打赏
举报

TempDB是系统管理的对象，表现的根源都在系统设计运作的状况要治本，要从系统设计、开发去调整

最爱午夜 2014-06-13

打赏
举报

tempdb恢复正常了，监控一天，发现tempdb在缓冲池中没有再增大过了，由于之前tempdb只有一个数据文件，导致数据文件膨胀到了150GB，现在设置了1个主文件，5个次要文件，每个10GB，然后每个文件的大小再也没有超过10GB。缓冲池的内存估计是由于tempdb过度读写导致，由于tempdb不重启服务不会重新生成，并且文件过度膨胀之后，不能压缩。每次读写都要耗费大量资源。结贴啦，感谢各位的建议。

最爱午夜 2014-06-12

打赏
举报

IIS的回收机制，这个我也不懂。问题就出在这个上面吧，其它的实在是找不出来，一些监控数据都正常。就算tempdb占用缓存多，以前也是这样，但是性能没什么变化。

發糞塗牆 2014-06-12

打赏
举报

看看是用户对象占了空间还是内部对象占了空间，如果都不是，就是行版本存储 --user objects：

DECLARE userobj_cursor CURSOR FOR  
select  
     sys.schemas.name + '.' + sys.objects.name  
from sys.objects, sys.schemas 
where object_id > 100 and  
      type_desc = 'USER_TABLE'and  
      sys.objects.schema_id = sys.schemas.schema_id 
go 
 
open userobj_cursor 
go 
 
declare @name varchar(256) 
fetch userobj_cursor into @name 
while (@@FETCH_STATUS = 0)  
begin 
    exec sp_spaceused @objname = @name 
        fetch userobj_cursor into @name   
end 
close userobj_cursor

--internal objects:

SELECT  
    t1.session_id, 
    (t1.internal_objects_alloc_page_count + task_alloc) as allocated, 
    (t1.internal_objects_dealloc_page_count + task_dealloc) as   
    deallocated  
from sys.dm_db_session_space_usage as t1,  
    (select session_id,  
        sum(internal_objects_alloc_page_count) 
            as task_alloc, 
    sum (internal_objects_dealloc_page_count) as  
        task_dealloc  
      from sys.dm_db_task_space_usage group by session_id) as t2 
where t1.session_id = t2.session_id and t1.session_id >50 
order by allocated DESC

發糞塗牆 2014-06-12

打赏
举报

改配置不应该那么早就做。问题应该是为什么不释放

最爱午夜 2014-06-12

打赏
举报

引用 38 楼 DBA_Huangzj 的回复:

[quote=引用 35 楼 jack11430 的回复:] [quote=引用 30 楼 shinger126 的回复:] [quote=引用 24 楼 jack11430 的回复:] 找了问题，发现在数据库中老是报错，18056. The client was unable to reuse a session with SPID 106, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message. 中文：客户端无法重新使用 SPID 为 106 的会话，该会话已被重置用于连接池。失败 ID 为 29。此错误可能是由于先前的操作失败引起的。请查看错误日志，了解紧位于此错误消息之前的失败操作。怎么办啊？

http://blog.csdn.net/yangzhawen/article/details/8209167看这里[/quote] 这个回答不靠谱，我是通过限制查询的并行度来优化的，而不是他上面说的那些方法。还有，出现他上面说的情况，微软的帮助文档提到两点： 1、CPU占用率高， 2、上下文切换频繁。我们的服务器主要是上下文切换频繁，我立即限制了并行查询，然后服务器又回复正常，但是大型查询却会变慢。[/quote]除了并行度，还可以借用阈值来控制，比如阈值超过25秒之后才进行并行操作[/quote] 阀值问题，由默认的5s改为10s。并行查询由0改为6，启动6个CPU执行并行查询。

發糞塗牆 2014-06-12

打赏
举报

还有个问题，有没有启用快照隔离？

女DBA一枚 2014-06-12

打赏
举报

大了不一定影响性能啊，有些业务和逻辑导致很大还shrink不下去的可以考虑把tempdb多分几个文件组。

nzperfect 2014-06-12

打赏
举报

引用 38 楼 DBA_Huangzj 的回复:

[quote=引用 35 楼 jack11430 的回复:] [quote=引用 30 楼 shinger126 的回复:] [quote=引用 24 楼 jack11430 的回复:] 找了问题，发现在数据库中老是报错，18056. The client was unable to reuse a session with SPID 106, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message. 中文：客户端无法重新使用 SPID 为 106 的会话，该会话已被重置用于连接池。失败 ID 为 29。此错误可能是由于先前的操作失败引起的。请查看错误日志，了解紧位于此错误消息之前的失败操作。怎么办啊？

發糞塗牆 2014-06-12

打赏
举报

引用 35 楼 jack11430 的回复:

[quote=引用 30 楼 shinger126 的回复:] [quote=引用 24 楼 jack11430 的回复:] 找了问题，发现在数据库中老是报错，18056. The client was unable to reuse a session with SPID 106, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message. 中文：客户端无法重新使用 SPID 为 106 的会话，该会话已被重置用于连接池。失败 ID 为 29。此错误可能是由于先前的操作失败引起的。请查看错误日志，了解紧位于此错误消息之前的失败操作。怎么办啊？

专注or全面 2014-06-12

打赏
举报

引用 32 楼 jack11430 的回复:

数据库设计有问题，大量的多表连接，最多可以达到25个表连接，业务逻辑复杂，大量临时表被创建，删除，导致tempdb的缓存数据达到103GB，这个问题不是主要导致服务器慢的原因。原因是IIS中垃圾回收机制，导致服务器内存被占，但还监控不出来。所才显示内存占用低。修改了回收机制，现在服务器又回复正常了。另一个是并行查询太多，并行度没有被控制，导致CPU上下文切换太多。

IIS中的垃圾回收机制是怎么修改的呢？谢谢

nzperfect 2014-06-12

打赏
举报

应用变慢，在DB端可以直接抓trace来确认是否是语句慢，如果不是，则考虑其它方面。

最爱午夜 2014-06-12

打赏
举报

引用 30 楼 shinger126 的回复:

[quote=引用 24 楼 jack11430 的回复:] 找了问题，发现在数据库中老是报错，18056. The client was unable to reuse a session with SPID 106, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message. 中文：客户端无法重新使用 SPID 为 106 的会话，该会话已被重置用于连接池。失败 ID 为 29。此错误可能是由于先前的操作失败引起的。请查看错误日志，了解紧位于此错误消息之前的失败操作。怎么办啊？

nzperfect 2014-06-12

打赏
举报

引用 32 楼 jack11430 的回复:

数据库设计有问题，大量的多表连接，最多可以达到25个表连接，业务逻辑复杂，大量临时表被创建，删除，导致tempdb的缓存数据达到103GB，这个问题不是主要导致服务器慢的原因。原因是IIS中垃圾回收机制，导致服务器内存被占，但还监控不出来。所才显示内存占用低。修改了回收机制，现在服务器又回复正常了。另一个是并行查询太多，并行度没有被控制，导致CPU上下文切换太多。

这样看来问题并不在DB上，而是在iis这块了。 oltp，一般并行度可以设置为1.

nzperfect 2014-06-12

打赏
举报

如方便，可以将以下结果发给我，34813284@qq.com

SELECT  @@VERSION

SELECT  * ,
        cntr_value * 1.0 / 1024 / 1024 AS Memory_GB
FROM    sys.dm_os_performance_counters
WHERE   ( [OBJECT_NAME] LIKE '%Memory Manager%' )
        AND counter_name IN ( 'Target Server Memory (KB)',
                              'Total Server Memory (KB)' )

SELECT  SUM(single_pages_kb) * 1.0 / 1024 AS total_single_pages_MB ,
        SUM(multi_pages_kb) * 1.0 / 1024 AS total_multi_pages_MB ,
        SUM(virtual_memory_reserved_kb) * 1.0 / 1024 AS total_virtual_memory_reserved_MB ,
        SUM(virtual_memory_committed_kb) * 1.0 / 1024 AS total_virtual_memory_committed_MB ,
        SUM(awe_allocated_kb) * 1.0 / 1024 AS total_awe_allocated_MB ,
        SUM(shared_memory_reserved_kb) * 1.0 / 1024 AS total_shared_memory_reserved_MB ,
        SUM(shared_memory_committed_kb) * 1.0 / 1024 AS total_shared_memory_committed_MB
FROM    sys.dm_os_memory_clerks

SELECT  a.name DBName ,
        SUM(CAST(b.size * 8.0 / 1024 / 1024 AS NUMERIC(20, 3))) [FileSize(GB)]
FROM    sys.databases a
        INNER JOIN sys.master_files b ON a.database_id = b.database_id
GROUP BY a.name
ORDER BY [FileSize(GB)] DESC

SELECT  CASE database_id
          WHEN 32767 THEN 'ResourceDb'
          ELSE DB_NAME(database_id) END AS Database_name ,
        COUNT(*) AS cached_pages_count ,
        COUNT(*) * 8.0 / 1024 / 1024 AS cached_pages_count_inSize_GB
FROM    sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id),
        database_id
ORDER BY cached_pages_count DESC;
 

SELECT TOP 50
[Wait type] = wait_type,
[Total_Wait time (s)] = wait_time_ms / 1000,
[Max_Wait_time (s)] = max_wait_time_ms /1000,
[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0
        / SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
and wait_type NOT IN
('KSOURCE_WAKEUP', 'SLEEP_BPOOL_FLUSH', 'BROKER_TASK_STOP',
'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',    
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',
'LAZYWRITER_SLEEP', 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 
'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'LAZYWRITER_SLEEP', 
'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE', 
'SLEEP_TASK', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS', 
'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH')
ORDER BY wait_time_ms DESC;

最爱午夜 2014-06-12

打赏
举报

数据库设计有问题，大量的多表连接，最多可以达到25个表连接，业务逻辑复杂，大量临时表被创建，删除，导致tempdb的缓存数据达到103GB，这个问题不是主要导致服务器慢的原因。原因是IIS中垃圾回收机制，导致服务器内存被占，但还监控不出来。所才显示内存占用低。修改了回收机制，现在服务器又回复正常了。另一个是并行查询太多，并行度没有被控制，导致CPU上下文切换太多。

nzperfect 2014-06-12

打赏
举报

重新审视下这个问题，在这种场景下可能会产生这样的现象：你的用户数据库不大，应该不超过60GB，或者活跃数据不超过60GB. 你的系统大量使用临时表或着有很多排序、分组、聚合之类的要使用的tempdb来缓存数据如果内存没有压力，已经删除的tempdb临时表数据虽然被删除，但在buffer pool中没有被释放，因为内存没有瓶颈，这个是SQL设计使然。进一步排查，需要楼主提供更多一些的信息。

shinger126 2014-06-12

打赏
举报

引用 24 楼 jack11430 的回复:

找了问题，发现在数据库中老是报错，18056. The client was unable to reuse a session with SPID 106, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message. 中文：客户端无法重新使用 SPID 为 106 的会话，该会话已被重置用于连接池。失败 ID 为 29。此错误可能是由于先前的操作失败引起的。请查看错误日志，了解紧位于此错误消息之前的失败操作。怎么办啊？

http://blog.csdn.net/yangzhawen/article/details/8209167看这里

xiaoxiangqing 2014-06-12

打赏
举报

估计是临时表没有释放导致的

专注or全面 2014-06-12

打赏
举报

或者私信一下这个http://blog.csdn.net/yangzhawen/article/details/8425375 他专门把这个问题当做面试题，肯定最清楚不过了

加载更多回复（40）

tempdb在缓冲池中占用100GB内存

相关推荐