4

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

  • If you modify the first query to filter on database_id=2 and group by allocation_unit_id how does it compare with the second query grouped by the same thing? – Martin Smith Apr 22 '16 at 13:02
  • No, in the first query there is an allocationunit of 517MB and one of 433MB but both are not found in the result of query 2 – Frederik Vanderhaegen Apr 22 '16 at 13:11
  • In the second query it looks like the query needs to take account of the type. Not just use ON p.partition_id = a.container_id as sometimes it should join on hobt_id instead. See msdn.microsoft.com/en-GB/library/ms189792.aspx – Martin Smith Apr 22 '16 at 13:15
  • I adjusted my second query but still are the allocationunits of 517MB and 433MB not present in the results – Frederik Vanderhaegen Apr 22 '16 at 13:32
  • I'd poke around in the metadata views and see if you can find those allocation unit id anywhere as presumably they are getting eliminated in the join. You could also get a random pageid for one of them from the first query and look at the page with dbcc page to see if that sheds any light. – Martin Smith Apr 22 '16 at 14:18
2

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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.