Udostępnij za pośrednictwem

Determine the (available) amount of memory of a SQL Server (instance / machine)

  • Artykuł

Comming across a question of a colleague of mine, the following script can be used to determine the (available / assigned) amount of memory for an instance / machine. Notice that the DMVs are only available on SQL Server 2005, for using the script on versions 2000 and 2005 you will need to use the 2000 script. In addition be aware that one script uses internal sys tables which might change in the future of SQL Server (without notice) and you should better stick to the DMV if you want to keep your implementations futureproof.

SQL Server 2000:

Get the physical memory:

create table #SVer(ID int,  Name  sysname, Internal_Value int, Value nvarchar(512))
insert #SVer exec master.dbo.xp_msver
               
SELECT *
FROM #SVer
WHere Name = 'PhysicalMemory'
GO

drop table #SVer                                              

Get the configured memory:

Select * FROM
sysconfigures AS cfg
Where comment IN
('Minimum size of server memory (MB)','Maximum size of server memory (MB)')

 

SQL Server 2005:

**Get the physical memory:** Select                 physical\_memory\_in\_bytes,                 virtual\_memory\_in\_bytes from sys.dm\_os\_sys\_info getting the configured SQL Server options:

Get the configured memory:

SELECT
cfg.name AS [Name],
cfg.configuration_id AS [Number],
cfg.minimum AS [Minimum],
cfg.maximum AS [Maximum],
cfg.is_dynamic AS [Dynamic],
cfg.is_advanced AS [Advanced],
cfg.value AS [ConfigValue],
cfg.value_in_use AS [RunValue],
cfg.description AS [Description]
FROM
sys.configurations AS cfg
WHERE name IN ('min server memory (MB)','max server memory (MB)')

-Jens

Comments