Hello sql expert,
I am trying to find how much memory being used in my temp table. Is there a sql statement
that tell my session, total memory and how much total memory from the server I am
using?
For example, I created a simple TempTable and I want to know how much memory (in MB and GB) being used if I execute
below query from the session I am using on this query.
SELECT A,B,C
INTO #TEMPTABLE
FROM MyTable
Thank you all
Look at the query plan. You can use an estimated query plan if you don't want to actually run the query, or the actual query plan if you do.
Thanks Scott. I will try and see if there is something what I am looking for...thanks again for all your help as always.
I know there are various things in play here, including caching and working-storage etc., but just to double check your use of "memory" 
A #TEMP table will be stored in the TEMPDB database as distinct from a @TEMP Table Variable which will be stored in memory (albeit that it will spill over into TEMPDB if it gets large).
Thanks Kristen,
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:
Thanks Kristen
SQL handles storage of temp tables and table variables the same way. There's a common misunderstanding that table variables are initially always stored "only in memory", but that's just not true. The most important difference between temp tables and table variables is in how statistics are maintained, or not.
Thank you Scott
