tempdb在缓冲池中占用100GB内存，这个问题主要是有大量的临时表创建和删除，临时表上又建有索引引起的，我测试过，建立临时表，然后删除临时表，但缓存依旧停留在缓冲池，难道一定要使用DBCC DROPCLEANBUFFERS 来删除缓存，但这个同时也删除了其他的缓存。
如何删除tempdb的缓存，或者让tempdb在每次使用都尽可能的不缓存数据？
为什么tempdb的缓存总不释放？
------解决方案--------------------
你怎么监控的？有些对象是会缓存，但是大部分的临时表对象不会缓存
------解决方案--------------------
-- 楼主，用这个查查
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;
------解决方案--------------------
如方便，可以将以下结果发给我，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',