dm_os_sys_memory and dm_os_sys_info

• Chitown

  Hall of Fame

  Points: 3239

  More actions

  October 21, 2015 at 11:21 am

  #301665

  I have 2 SQL Statements.

  Declare @Memory int

  SELECT @Memory = (SELECT round (total_physical_memory_kb / 1024 /1024.0 ,0) AS "Total_Physical_Memory_In_GB"

  FROM [master].[sys].[dm_os_sys_memory]) which works fine in SQL server 2008 and up but doesn't work with SQL Server 2005.

  This is another SQL Statement

  SELECT round (physical_memory_in_bytes / 1024 /1024.0 ,0) AS "Total_Physical_Memory_In_GB"

  FROM [master].[sys].[dm_os_sys_info] which works in 2005.

  What I am trying to do here is that I need to run a SQL statement which captures the memory for all servers including 2005 from the central management servers. I believe I have to use IF statement but I need help with the syntax. Any help please?:-D:-D

• Chitown

  Hall of Fame

  Points: 3239

  More actions

  October 21, 2015 at 12:37 pm

  #1834749

  It may work but I don't know. drop table #temp

  Create table #temp(

  ServerID int,

  Memory int,

  Edition varchar (200),

  Version varchar (100),

  CoreCount int)

  Declare @serverID int,

  @Memory int,

  @Edition varchar (200),

  @Version varchar (200),

  @CoreCount int

  select @serverID = server_id from sys.servers

  IF @@VERSION != 'SQL Server 2005%'

  Select @Memory = (SELECT round (total_physical_memory_kb / 1024 /1024.0 ,0) AS "Total_Physical_Memory_In_GB"

  FROM [master].[sys].[dm_os_sys_memory])

  ELSE

  SELECT @Memory = (SELECT round (physical_memory_in_bytes / 1024 /1024.0 ,0) AS "Total_Physical_Memory_In_GB"

  FROM [master].[sys].[dm_os_sys_info])

  set @Edition = convert (varchar (200), (Select SERVERPROPERTY ('Edition')))

  set @Version = convert (varchar (200), (Select SERVERPROPERTY ('ProductVersion')))

  select @CoreCount = (select cpu_count from sys.dm_os_sys_info)

  delete from dba.dbo.inventory_server

  where @serverID = ServerID

  insert into inventory_server (ServerID, Memory, Edition, Version, CoreCount)

  select

  @serverID,

  @Memory,

  @Edition,

  @Version,

  @CoreCount

  SELECT * FROM #temp

  Let me know if I can use a different query?

• JeremyE

  SSCoach

  Points: 16198

  More actions

  October 21, 2015 at 4:16 pm

  #1834792

  create table #SVer(ID int, Name sysname, Internal_Value int, Value nvarchar(512))

  insert #SVer exec master.dbo.xp_msver

  select Internal_Value AS [PhysicalMemory_MB] from #SVer where Name = N'PhysicalMemory'

  drop table #SVer

• Chitown

  Hall of Fame

  Points: 3239

  More actions

  October 22, 2015 at 8:38 am

  #1834922

  JeremyE (10/21/2015)

  ---

  create table #SVer(ID int, Name sysname, Internal_Value int, Value nvarchar(512))

  insert #SVer exec master.dbo.xp_msver

  select Internal_Value AS [PhysicalMemory_MB] from #SVer where Name = N'PhysicalMemory'

  drop table #SVer

  Sorry to bug you Jeremy but how would I put this query (which works for every version) into the table I created. I am new to SQL Server and can't seem to figure that out. Can I get the whole syntax?:-D:-D:-D:-D:-D:-D

• Lowell

  SSC Guru

  Points: 323518

  More actions

  October 22, 2015 at 1:09 pm

  #1835009

  i think you have to switch to dynamic sql for an all in one script right?

  something like this?

  Declare @serverID int,

  @Memory int,

  @Edition varchar (200),

  @Version varchar (200),

  @CoreCount int

  select @serverID = server_id from sys.servers

  IF EXISTS(SELECT * from master.sys.all_columns where object_name(object_id) = 'dm_os_sys_memory' AND name='total_physical_memory_kb')

  EXEC sp_executesql N'Select @m = (SELECT round (total_physical_memory_kb / 1024 /1024.0 ,0) AS "Total_Physical_Memory_In_GB"

  FROM [master].[sys].[dm_os_sys_memory])',N'@M float output', @Memory output

  ELSE

  IF EXISTS(SELECT * from master.sys.all_columns where object_name(object_id) = 'dm_os_sys_memory' AND name='physical_memory_in_bytes')

  EXEC sp_executesql N'Select @m = (SELECT round (physical_memory_in_bytes / 1024 /1024.0 ,0) AS "Total_Physical_Memory_In_GB"

  FROM [master].[sys].[dm_os_sys_memory])',N'@M float output', @Memory output

  SELECT @Memory As Memory

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Chitown

  Hall of Fame

  Points: 3239

  More actions

  October 22, 2015 at 1:45 pm

  #1835015

  Lowell (10/22/2015)

  ---

  i think you have to switch to dynamic sql for an all in one script right?

  something like this?

  Declare @serverID int,

  @Memory int,

  @Edition varchar (200),

  @Version varchar (200),

  @CoreCount int

  select @serverID = server_id from sys.servers

  IF EXISTS(SELECT * from master.sys.all_columns where object_name(object_id) = 'dm_os_sys_memory' AND name='total_physical_memory_kb')

  EXEC sp_executesql N'Select @m = (SELECT round (total_physical_memory_kb / 1024 /1024.0 ,0) AS "Total_Physical_Memory_In_GB"

  FROM [master].[sys].[dm_os_sys_memory])',N'@M float output', @Memory output

  ELSE

  IF EXISTS(SELECT * from master.sys.all_columns where object_name(object_id) = 'dm_os_sys_memory' AND name='physical_memory_in_bytes')

  EXEC sp_executesql N'Select @m = (SELECT round (physical_memory_in_bytes / 1024 /1024.0 ,0) AS "Total_Physical_Memory_In_GB"

  FROM [master].[sys].[dm_os_sys_memory])',N'@M float output', @Memory output

  SELECT @Memory As Memory

  Thanks for the help but it didn't work for me. I ran it and I got NULL value. I am sure SQL statements like this is probably a piece of cake but for me, it is like climbing mount Everest. I am fairly new to development (Doing this for 3 and half week) Anyways, This is what I ran and when I ran a select * from #Temp, it gave me 0 rows.

  This is what I have

  --DECLARE @Table table (memory int)

  --INSERT INTO @TABLE EXEC ('SELECT round (total_physical_memory_kb / 1024 /1024.0 ,0) AS "Total_Physical_Memory_In_GB" FROM [master].[sys].[dm_os_sys_memory]')

  drop table #temp

  Create table #temp(

  ServerID int,

  Memory int,

  Edition varchar (200),

  Version varchar (100),

  CoreCount int)

  --Declare @serverID int,

  --@Memory int,

  --@Edition varchar (200),

  --@Version varchar (200),

  --@CoreCount int

  --select @serverID = server_id from sys.servers

  --Select @Memory = (Select memory from @Table)

  --set @Edition = convert (varchar (200), (Select SERVERPROPERTY ('Edition')))

  --declare @sql nvarchar(max)

  --if (select cast(left(cast(SERVERPROPERTY('ProductVersion') as varchar), 4) as decimal(5, 3))) <=10

  --set @sql = N'SELECT ROUND (physical_memory_in_bytes / 1024 / 1024.0,0) AS "memory" from [master].[sys].[dm_os_sys_info]'

  -- ELSE

  --set @sql = N'SELECT round (total_physical_memory_kb / 1024 /1024.0 ,0) AS "Total_Physical_Memory_In_GB"

  --FROM [master].[sys].[dm_os_sys_memory]'

  --select @CoreCount = (select cpu_count from sys.dm_os_sys_info)

  --delete from dba.dbo.inventory_server

  --where @serverID = ServerID

  -- insert into #TEMP (ServerID, Memory, Edition, Version, CoreCount)

  -- select

  --@serverID,

  --@Memory,

  --@Edition,

  --@Version,

  --@CoreCount

  --SELECT * FROM #TEMP

  Declare @serverID int,

  @Memory int,

  @Edition varchar (200),

  @Version varchar (200),

  @CoreCount int

  select @serverID = server_id from sys.servers

  IF EXISTS(SELECT * from master.sys.all_columns where object_name(object_id) = 'dm_os_sys_memory' AND name='total_physical_memory_kb')

  EXEC sp_executesql N'Select @m = (SELECT round (total_physical_memory_kb / 1024 /1024.0 ,0) AS "Total_Physical_Memory_In_GB"

  FROM [master].[sys].[dm_os_sys_memory])',N'@M float output', @Memory output

  --ELSE

  --IF EXISTS(SELECT * from master.sys.all_columns where object_name(object_id) = 'dm_os_sys_memory' AND name='physical_memory_in_bytes')

  -- EXEC sp_executesql N'Select @m = (SELECT round (physical_memory_in_bytes / 1024 /1024.0 ,0) AS "Total_Physical_Memory_In_GB"

  --FROM [master].[sys].[dm_os_sys_memory])',N'@M float output', @Memory output

  set @Edition = convert (varchar (200), (Select SERVERPROPERTY ('Edition')))

  set @Version = convert (varchar (200), (Select SERVERPROPERTY ('ProductVersion')))

  select @CoreCount = (select cpu_count from sys.dm_os_sys_info)

  delete from dba.dbo.inventory_server

  where @serverID = ServerID

  insert into inventory_server (ServerID, Memory, Edition, Version, CoreCount)

  select

  @serverID,

  @Memory,

  @Edition,

  @Version,

  @CoreCount

  --SELECT @Memory As Memory

• JeremyE

  SSCoach

  Points: 16198

  More actions

  October 22, 2015 at 2:13 pm

  #1835018

  In your modified query, you are not using the #temp table to insert any data. It's being inserted into the dba.dbo.inventory_server table. You would need to select from dba.dbo.inventory_server.

• Chitown

  Hall of Fame

  Points: 3239

  More actions

  October 22, 2015 at 2:33 pm

  #1835026

  JeremyE (10/22/2015)

  ---

  In your modified query, you are not using the #temp table to insert any data. It's being inserted into the dba.dbo.inventory_server table. You would need to select from dba.dbo.inventory_server.

  Silly me.

  New update. I ran this

  --DECLARE @Table table (memory int)

  --INSERT INTO @TABLE EXEC ('SELECT round (total_physical_memory_kb / 1024 /1024.0 ,0) AS "Total_Physical_Memory_In_GB" FROM [master].[sys].[dm_os_sys_memory]')

  drop table #temp

  Create table #temp(

  ServerID int,

  Memory int,

  Edition varchar (200),

  Version varchar (100),

  CoreCount int)

  --Declare @serverID int,

  --@Memory int,

  --@Edition varchar (200),

  --@Version varchar (200),

  --@CoreCount int

  --select @serverID = server_id from sys.servers

  --Select @Memory = (Select memory from @Table)

  --set @Edition = convert (varchar (200), (Select SERVERPROPERTY ('Edition')))

  --declare @sql nvarchar(max)

  --if (select cast(left(cast(SERVERPROPERTY('ProductVersion') as varchar), 4) as decimal(5, 3))) <=10

  --set @sql = N'SELECT ROUND (physical_memory_in_bytes / 1024 / 1024.0,0) AS "memory" from [master].[sys].[dm_os_sys_info]'

  -- ELSE

  --set @sql = N'SELECT round (total_physical_memory_kb / 1024 /1024.0 ,0) AS "Total_Physical_Memory_In_GB"

  --FROM [master].[sys].[dm_os_sys_memory]'

  --select @CoreCount = (select cpu_count from sys.dm_os_sys_info)

  --delete from dba.dbo.inventory_server

  --where @serverID = ServerID

  -- insert into #TEMP (ServerID, Memory, Edition, Version, CoreCount)

  -- select

  --@serverID,

  --@Memory,

  --@Edition,

  --@Version,

  --@CoreCount

  --SELECT * FROM #TEMP

  Declare @serverID int,

  @Memory int,

  @Edition varchar (200),

  @Version varchar (200),

  @CoreCount int

  select @serverID = server_id from sys.servers

  IF EXISTS(SELECT * from master.sys.all_columns where object_name(object_id) = 'dm_os_sys_memory' AND name='total_physical_memory_kb')

  EXEC sp_executesql N'Select @m = (SELECT round (total_physical_memory_kb / 1024 /1024.0 ,0) AS "Total_Physical_Memory_In_GB"

  FROM [master].[sys].[dm_os_sys_memory])',N'@M float output', @Memory output

  ELSE

  IF EXISTS(SELECT * from master.sys.all_columns where object_name(object_id) = 'dm_os_sys_memory' AND name='physical_memory_in_bytes')

  EXEC sp_executesql N'Select @m = (SELECT round (physical_memory_in_bytes / 1024 / 1024.0,0) AS "memory"

  from [master].[sys].[dm_os_sys_info]',N'@M float output', @Memory output

  set @Edition = convert (varchar (200), (Select SERVERPROPERTY ('Edition')))

  set @Version = convert (varchar (200), (Select SERVERPROPERTY ('ProductVersion')))

  select @CoreCount = (select cpu_count from sys.dm_os_sys_info)

  --delete from dba.dbo.inventory_server

  --where @serverID = ServerID

  insert into #temp (ServerID, Memory, Edition, Version, CoreCount)

  select

  @serverID,

  @Memory,

  @Edition,

  @Version,

  @CoreCount

  Got the server ID, Memory, Edition, Version and CoreCount (worked in 2008 and up) :-):-)

  But in 2005, I get server ID, Edition, Version and CoreCount but NULL value for memory. :-D:-D