Sql-server – How to get the free temporary memory available in a server

sql serversql-server-2005

How to get the free temporary memory available in a server

For example:- I am running one SSIS package to upload data to tables. First it will move all the data to temporary table and then it will upload to actual Tables. So before executing this package is there any way to get the available remaining temporary memory size? including SQL Server ( Because there are some parallel jobs are running by SQL Server 2005 and using the temporary space). So if I can get the remaining memory size I can decide whether I can run the package or not. Please help me.

Best Answer

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
Related Question