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

Question

缓存内存tempdb

Answer 1

我通过下面的代码查询到的：
select COUNT(*)*8/1024 as 'CachedSize(MB)',
case database_id when 32767 then 'resourceDb'
else DB_NAME(database_id)
end as 'Database'
from sys.dm_os_buffer_descriptors 
group by DB_NAME(database_id),database_id
order by 'CachedSize(MB)' desc
我们的业务库在缓冲池里面才占40GB的内存，我又去比较了一下另一个服务器上面的tempdb，发现才占3GB的内存，于是锁定了问题。

Answer 2

如果你现在的情况还存在，执行一下这个，贴结果给我看看：
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 > 50ORDER BY allocated DESC

Answer 3

dbcc inputbuffer(220)
dbcc inputbuffer(210)
dbcc inputbuffer(207)
dbcc inputbuffer(104)
dbcc inputbuffer(83)看看这些会话都在干嘛

Answer 4

楼主家的服务器的内存是多少的?tempdb在缓冲池中占用100GB内存, 没多算一个0吧.

Answer 5

没有，服务器有168GB的内存单独给SQLSERVER用，
服务器总内存224GB

Answer 6

另一个服务器有96GB内存，78GB给SQLSERVER。
但是它的tempdb在缓冲池只占3GB的缓存。比较了一下，发现问题在于tempdb的数据缓存占缓冲池的大小是主要问题。

Answer 7

请问下版主。。这语句查询出来的是什么东东，学习中。看脚本先看FROM，然后去联机丛书看看这个/些表是干嘛的，然后看对应列的解释

Answer 8

我也觉的啊。。 +1存储过程 里，我从没写过 drop table #临时表的。
会话结束了,这个资源也就适放了啊.. 
加drop table 可能会快一点点回收资源而已啊.

Answer 9

 -- 楼主，用这个查查
use tempdb
go  SELECT t.dbName, t.TableName, t.cachedPageCt, t.Mb
    FROM(  

SELECT CASE database_id 
        WHEN 32767 THEN 'ResourceDb' 
        ELSE db_name(a.database_id) 
        END AS dbName,OBJECT_NAME(c.object_id) TableName,COUNT(*) cachedPageCt,COUNT(*)*8/1024.0 Mb          
from sys.dm_os_buffer_descriptors a 
left join sys.allocation_units b ON a.allocation_unit_id=b.allocation_unit_id  
left join sys.partitions c ON   b.container_id=c.hobt_id            
WHERE 1=1           
   group by a.database_id,c.object_id  having COUNT(*)*8/1024.0>1
  )t 
WHERE (t.dbName ='tempdb' )order by dbName,cachedPageCt DESC;

Answer 10

除非不是会话级临时表，而是全局临时表，或者根本就是放在tempdb里的实体表。

Answer 11

晕，现在才看到你的私信，这提醒功能真烂，你看看那几个会话是不是blocking

Answer 12

请问下版主。。这语句查询出来的是什么东东，学习中。看脚本先看FROM，然后去联机丛书看看这个/些表是干嘛的，然后看对应列的解释OK，谢谢版主

Answer 13

 -- 楼主，用这个查查
use tempdb
go  SELECT t.dbName, t.TableName, t.cachedPageCt, t.Mb
    FROM(  

SELECT CASE database_id 
        WHEN 32767 THEN 'ResourceDb' 
        ELSE db_name(a.database_id) 
        END AS dbName,OBJECT_NAME(c.object_id) TableName,COUNT(*) cachedPageCt,COUNT(*)*8/1024.0 Mb          
from sys.dm_os_buffer_descriptors a 
left join sys.allocation_units b ON a.allocation_unit_id=b.allocation_unit_id  
left join sys.partitions c ON   b.container_id=c.hobt_id            
WHERE 1=1           
   group by a.database_id,c.object_id  having COUNT(*)*8/1024.0>1
  )t 
WHERE (t.dbName ='tempdb' )order by dbName,cachedPageCt DESC;

Answer 14

tempbd不就是用来保存所有的临时表和临时存储过程的么， 在断开联接时会自动删除临时表和存储过程的

Answer 15

找了问题，发现在数据库中老是报错，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。此错误可能是由于先前的操作失败引起的。请查看错误日志，了解紧位于此错误消息之前的失败操作。怎么办啊？

Answer 16

临时表Session结束掉不就可以了？

Answer 17

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

Answer 18

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

Answer 19

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

Answer 20

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

Answer 21

如方便，可以将以下结果发给我，[email protected]  @@VERSIONSELECT  * ,
        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_clerksSELECT  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)] DESCSELECT  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;

Answer 22

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

Answer 23

http://blog.csdn.net/yangzhawen/article/details/8209167看这里这个回答不靠谱，
我是通过限制查询的并行度来优化的，而不是他上面说的那些方法。
还有，出现他上面说的情况，微软的帮助文档提到两点：
1、CPU占用率高，
2、上下文切换频繁。
我们的服务器主要是上下文切换频繁，我立即限制了并行查询，然后服务器又回复正常，但是大型查询却会变慢。

Answer 24

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

Answer 25

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

Answer 26

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

Answer 27

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

Answer 28

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

Answer 29

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

Answer 30

看看是用户对象占了空间还是内部对象占了空间，如果都不是，就是行版本存储
--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 

Answer 31

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

Answer 32

今天早上重启了服务器，现在为止，tempdb已经在缓冲池占了21GB的缓存，
按你的代码查询，第一个没有对象被缓存，第二个比较多，
真的是行版本占了内存吗？

Answer 33

如果真是行版本存储，那占缓存就比较说的过去，虽然没用的行版本每分钟都会被清空，但是如果一直被使用，那就不会被清空。http://msdn.microsoft.com/zh-cn/library/ms175492.aspx可以看看官方资料，当然，如果你还有其他猜测，可以先看看那些猜测是否也影响了tempdb。

Answer 34

没有任何作用，文档中的DMV都看过了，查询不出什么数据来