There is no such a thing in sql server as temporary memory. Temp tables and table variables created in tempdb and may use shared buffer pool of sql server as any other objects do
But
if you still insist on knowing everything about server memory use the following script:
set nocount on
declare @PI sysname
declare @PI_MemoryManager sysname
declare @TotalServerMemoryKB numeric
declare @TargetServerMemoryKB numeric
declare @MemoryConnectionsKB numeric
declare @MemoryLocksKB numeric
declare @MemoryOptimizerKB numeric
declare @MemoryMinKB numeric
declare @MemoryMaxKB numeric
declare @MemorySortHashKB numeric
declare @PhysicalRAMKB numeric
declare @qs_xp_msver table(
[idx] [int] NULL,
[name] [sysname] NOT NULL,
[internal_value] [int] NULL,
[char_value] [sysname] NULL
)
insert into @qs_xp_msver (idx, name, internal_value, char_value)
exec master.dbo.xp_msver 'PhysicalMemory'
select @PI = case when convert(sysname,ServerProperty('ServerName')) like '%\%' then 'MSSQL$' + @@servicename else 'SQLServer' end
set @PI_MemoryManager = @PI + ':Memory Manager'
select @TotalServerMemoryKB = sum(case when lower(p.counter_name) = 'total server memory (kb)' then convert(numeric,cntr_value) else 0 end)
, @TargetServerMemoryKB = sum(case when lower(p.counter_name) = 'target server memory (kb)' then convert(numeric,cntr_value) else 0 end)
, @MemoryConnectionsKB = sum(case when lower(p.counter_name) = 'connection memory (kb)' then convert(numeric,cntr_value) else 0 end)
, @MemoryLocksKB = sum(case when lower(p.counter_name) = 'lock memory (kb)' then convert(numeric,cntr_value) else 0 end)
, @MemoryOptimizerKB = sum(case when lower(p.counter_name) = 'optimizer memory (kb)' then convert(numeric,cntr_value) else 0 end)
, @MemorySortHashKB = sum(case when lower(p.counter_name) = 'granted workspace memory (kb)' then convert(numeric,cntr_value) else 0 end)
from sys.dm_os_performance_counters p
where object_name = @PI_MemoryManager
and lower(counter_name) in
(
'total server memory (kb)'
, 'target server memory (kb)'
, 'connection memory (kb)'
, 'lock memory (kb)'
, 'optimizer memory (kb)'
, 'granted workspace memory (kb)'
)
and instance_name <> '_Total'
select @MemoryMinKB = sum(case when name like 'min%' then convert(numeric,value_in_use) * 1024 else 0 end)
, @MemoryMaxKB = sum(case when name like 'max%' then convert(numeric,value_in_use) * 1024 else 0 end)
from
sys.configurations with (readpast)
where
name in ('min server memory (MB)', 'max server memory (MB)')
declare @PhysicalRAM numeric
select @PhysicalRAM = ceiling(convert(numeric,
substring(char_value, charindex('(', char_value) + 1, charindex(')', char_value) - 1 - charindex('(', char_value) ) )
/ (1.0))
from @qs_xp_msver
if (@PhysicalRAM is null)
set @PhysicalRAMKB = 0
else if (@PhysicalRAM < 0)
set @PhysicalRAMKB = (convert(numeric,4096.0)*1024*1024 + @PhysicalRAM) /1024
else
set @PhysicalRAMKB = @PhysicalRAM/1024
if (@PhysicalRAMKB < @MemoryMaxKB and @PhysicalRAMKB > 0 )
set @MemoryMaxKB = @PhysicalRAMKB
set nocount off
select
@TotalServerMemoryKB as CurrentMemoryKB
, @MemoryMinKB as MemoryMinKB
, @MemoryMaxKB as MemoryMaxKB
, @MemoryConnectionsKB as MemoryConnectionsKB
, @MemoryLocksKB as MemoryLocksKB
, @MemoryOptimizerKB as MemoryOptimizerKB
, @MemorySortHashKB as MemorySortHashKB
, @PhysicalRAMKB as PhysicalRAMKB
, @TotalServerMemoryKB as TotalServerMemoryKB
, @TargetServerMemoryKB as TargetServerMemoryKB
