RAM,Max,Min,CPU counts by T-SQL

• mtz676

  SSCrazy Eights

  Points: 9261

  More actions

  June 23, 2015 at 12:40 am

  #319386

  1.How to capture the RAM size and Max memory and Min memory setting in a SQL server (2008) and above without needing any additional permissions

  2.How to Capture total CPU count and logical processors count using t-sql

  Thanks

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 23, 2015 at 1:34 am

  #1807256

  You need the VIEW SERVER STATE permissions to run this query:

  SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],

  cpu_count/hyperthread_ratio AS [Physical CPU Count],

  --physical_memory_in_bytes/1048576 AS [Physical Memory (MB)] --> USE this for 2008

  physical_memory_kb/1024 AS [Physical Memory (MB)], --> USE this for 2012 and above

  [min server memory (MB)],[max server memory (MB)]

  FROM sys.dm_os_sys_info AS si

  CROSS APPLY (

  SELECT *

  FROM (

  SELECT name, value

  FROM sys.configurations

  WHERE name IN ('min server memory (MB)','max server memory (MB)')

  ) AS sc

  PIVOT( MIN(value) FOR name IN ([min server memory (MB)],[max server memory (MB)])) AS p

  ) AS ca

  -- Gianluca Sartori