Sql-server – TempDB uses a lot of space in buffer pool

database-internalssql serversql server 2014tempdb

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 = 1

Allocation 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

Best Answer

That looks like a data page (page type=1) from a table variable or temp table (negative object id of -1948083318) in the leaf of a clustered index (indexid=1).

From the allocation information and the fact that this does not show up in your second query presumably the underlying object has been dropped. You may be able to get additional information about it, such as original object name, by looking for that allocation unit id in sys.fn_dblog if you're lucky.

If it belongs to a dropped object then one would certainly hope these pages are first candidates for putting on the free list.

However the article TempDB memory leak? and associated connect item indicate that there might be a problem in this area...

Related Question