That is correct, "Memory".
A SQL server has memory capacity, let's say 29 GB. Out of that 29 GB, the memory being used for other stuff daily, so my concern using the #TEMPTBL is, as a #TEMPTBL will be drop at the end but what I am trying to check is, to minimize adding more memories usage into that 29 GB from #TEMPTBL of my query of SP. The smaller the better (keep the SQL performance up)
So, if I can compare and see how much the total memory being used from the session I am running because of my #TMPTBL on my query or Stored Procedure(s), I can decide whether to use it or not to use #TEMPTBL.
Below is the query I am working on but I am not too sure if the [SPACE Allocated FOR Internal Objects (in GB)] and [SPACE Deallocated FOR Internal Objects (in GB)] are the actual memory being used out of the total SQL server memory I am using.
SELECT es.session_id AS [SESSION ID]
,status AS [Status]
,DB_NAME(su.database_id) AS [DATABASE Name]
,cpu_time AS [CPU TIME (in milisec)]
,total_scheduled_time AS [Total Scheduled TIME (in milisec)]
,total_elapsed_time AS [Elapsed TIME (in milisec)]
,((internal_objects_alloc_page_count * 8)/(1241024)) AS [SPACE Allocated FOR Internal Objects (in GB)]
,(internal_objects_dealloc_page_count * 8)/(1241024) AS [SPACE Deallocated FOR Internal Objects (in GB)]
FROM sys.dm_db_session_space_usage su
INNER join sys.dm_exec_sessions es
ON su.session_id = es.session_id
WHERE login_name like '%Mel%'
Here is the result set: