Fellow DBA's, I am not necessarily having a serious ongoing issue but am trying to understand what I saw.
I was reviewing who was using memory across all dbs. SQL 2016 had max mem set to 32 gb.
Of that, tempdb was taking about 60-70%. There were no queries running. version store is not running.
I have tempdb broken across 8 files. Each was setup exactly as planned. Each has grown very close to each other. Each was about 5600 mb each.
My below scrips indicate that about 18 gb of that sql memory space is unallocated to tempdb by looking at allocation units not tied to buffer descriptors.
SELECT Unallocated_MB = COUNT(1)/128
FROM sys.dm_os_buffer_descriptors bd
LEFT JOIN sys.allocation_units au ON bd.allocation_unit_id =
au.allocation_unit_id WHERE bd.database_id = 2
AND au.allocation_unit_id IS NULL
1 - How could tempdb still have buffers tied it for hours? I would have thought it would have released most of the 18gb? Or am I reading/interpreting this wrong?
thx
MG