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

最爱午夜 2014-06-11 01:47:10
tempdb在缓冲池中占用100GB内存,这个问题主要是有大量的临时表创建和删除,临时表上又建有索引引起的,我测试过,建立临时表,然后删除临时表,但缓存依旧停留在缓冲池,难道一定要使用DBCC DROPCLEANBUFFERS 来删除缓存,但这个同时也删除了其他的缓存。
如何删除tempdb的缓存,或者让tempdb在每次使用都尽可能的不缓存数据?
为什么tempdb的缓存总不释放?
...全文
781 61 打赏 收藏 转发到动态 举报
写回复
用AI写文章
61 条回复
切换为时间正序
请发表友善的回复…
发表回复
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。此错误可能是由于先前的操作失败引起的。请查看错误日志,了解紧位于此错误消息之前的失败操作。 怎么办啊?
http://blog.csdn.net/yangzhawen/article/details/8209167看这里[/quote] 这个回答不靠谱, 我是通过限制查询的并行度来优化的,而不是他上面说的那些方法。 还有,出现他上面说的情况,微软的帮助文档提到两点: 1、CPU占用率高, 2、上下文切换频繁。 我们的服务器主要是上下文切换频繁,我立即限制了并行查询,然后服务器又回复正常,但是大型查询却会变慢。[/quote]除了并行度,还可以借用阈值来控制,比如阈值超过25秒之后才进行并行操作[/quote] 那个基本没什么效果。
發糞塗牆 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。此错误可能是由于先前的操作失败引起的。请查看错误日志,了解紧位于此错误消息之前的失败操作。 怎么办啊?
http://blog.csdn.net/yangzhawen/article/details/8209167看这里[/quote] 这个回答不靠谱, 我是通过限制查询的并行度来优化的,而不是他上面说的那些方法。 还有,出现他上面说的情况,微软的帮助文档提到两点: 1、CPU占用率高, 2、上下文切换频繁。 我们的服务器主要是上下文切换频繁,我立即限制了并行查询,然后服务器又回复正常,但是大型查询却会变慢。[/quote]除了并行度,还可以借用阈值来控制,比如阈值超过25秒之后才进行并行操作
专注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。此错误可能是由于先前的操作失败引起的。请查看错误日志,了解紧位于此错误消息之前的失败操作。 怎么办啊?
http://blog.csdn.net/yangzhawen/article/details/8209167看这里[/quote] 这个回答不靠谱, 我是通过限制查询的并行度来优化的,而不是他上面说的那些方法。 还有,出现他上面说的情况,微软的帮助文档提到两点: 1、CPU占用率高, 2、上下文切换频繁。 我们的服务器主要是上下文切换频繁,我立即限制了并行查询,然后服务器又回复正常,但是大型查询却会变慢。
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)

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧