I'm running a SQL Server 2014 with 2 cores and a max memory of 6GB. When I'm checking what's in the buffer pool, I see that TempDB uses 1GB of memory. That's the query I used:
SELECT COUNT(*) AS cached_pages_count ,
(count(*) * 8.0)/1024 as MB,
CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE DB_NAME(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ,
database_id
ORDER BY cached_pages_count DESC;
But when I check what tables are in TempDB, there are only 40 tables and they use 728KB. This is the query:
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY t.Name
I also checked sys.dm_db_session_space_usage to see if there was a session that allocated all that space in TempDB
My buffer cache is 2.2GB and almost 50% is taken by TempDB
Can someone try to explain why TempDB is so large?
UPDATE I did a DBCC PAGE of some pages found in the dm_os_buffer_descriptors and it looks like they are not allocated (GAM/SGAM Not Allocated, PFS 0_PCT_FULL)
PAGE HEADER:
Page @0x0000000DB026C000
m_pageId = (1:159735) m_headerVersion = 1
m_type = 1 m_typeFlagBits = 0x0 m_level = 0
m_flagBits = 0x20 m_objId (AllocUnitId.idObj) = -1948083318
m_indexId (AllocUnitId.idInd) = 1 Metadata: AllocUnitId = 435280365092864 Metadata: PartitionId = 0 Metadata: IndexId = -1 Metadata: ObjectId = 0 m_prevPage = (1:160115) m_nextPage = (1:160144) pminlen = 13 m_slotCnt = 217 m_freeCnt = 3406
m_freeData = 4352 m_reservedCnt = 0 m_lsn = (13668:97392:89) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 0
DB Frag ID = 1Allocation Status
GAM (1:2) = NOT ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:153672) = 0x0 0_PCT_FULL DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED
ON p.partition_id = a.container_id
as sometimes it should join onhobt_id
instead. See msdn.microsoft.com/en-GB/library/ms189792.aspx