question

MarkGordon-2676 avatar image
0 Votes"
MarkGordon-2676 asked MarkGordon-2676 commented

TempDB and Memory

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

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered MarkGordon-2676 commented

It is completely normal and desirable for the buffer to contain pages for a long time.

Once SQL Server allocates RAM, it never releases it, unless the OS signals low memory. This is completely normal and expected and desirable behavior and does not in any way indicate an issue.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered MarkGordon-2676 commented

Hi MarkGordon-2676,
How are things going on?
In addition, please refer to this workaround in this article which might help:

My recommendation is to monitor your server, find out how much space TempDB uses, and make your TempDB significantly larger than that. The longer you have watched your server, the better. My recommendation is to take the peak size outside of a one-time process and make the total TempDB data files at least 150% of that size. If you have 4 TempDB data files and the peak size used is 4 GB, 150% of that is 6 GB, so make each file at least 1.5 GB. Then set autogrowth to something reasonable because one-time processes will happen.

Best Regards,
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.