Tag Archives: xp_msver

Get SQL Server Physical Cores, Physical and Virtual CPUs, and Processor type information using Transact-SQL (T-SQL) script

Today, I received email from one of my blog follower asking if there is any DMV or SQL script, which he can use to find the following information about the processors that are available to and consumed by SQL Server:

  • Total number of physical CPUs
  • Total number of physical cores per CPUs
  • Total number of physical cores
  • Total number of virtual CPUs
  • Processor type (x86 or x64)

As a result of this question, I wrote the following script using sys.dm_os_sys_info and xp_msver, which returns the required information:

DECLARE @xp_msver TABLE (
	[idx] [int] NULL
	,[c_name] [varchar](100) NULL
	,[int_val] [float] NULL
	,[c_val] [varchar](128) NULL
	)

INSERT INTO @xp_msver
EXEC ('[master]..[xp_msver]');;

WITH [ProcessorInfo]
AS (
	SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus]
		,CASE 
			WHEN hyperthread_ratio = cpu_count
				THEN cpu_count
			ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
			END AS [number_of_cores_per_cpu]
		,CASE 
			WHEN hyperthread_ratio = cpu_count
				THEN cpu_count
			ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
			END AS [total_number_of_cores]
		,[cpu_count] AS [number_of_virtual_cpus]
		,(
			SELECT [c_val]
			FROM @xp_msver
			WHERE [c_name] = 'Platform'
			) AS [cpu_category]
	FROM [sys].[dm_os_sys_info]
	)
SELECT [number_of_physical_cpus]
	,[number_of_cores_per_cpu]
	,[total_number_of_cores]
	,[number_of_virtual_cpus]
	,LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]
FROM [ProcessorInfo]

The code of this script is tested on SQL Server 2005 and above versions.

Hope you will this post useful 😉 .

Advertisement

Find the (available) amount of memory available to SQL Server (instance / machine)

From time to time, I see the following question posted on various SQL forums asking how we can determine the (available / assigned) amount of memory for an SQL Server instance / machine. As we understand that there is no common script or procedure, which we can use in all versions of SQL Server to determine the amount of memory available and assigned to SQL Server instance and machine. Therefore, I decided to write this blog post, where I’ll share a script for each version of SQL Server that will help us determine the total amount of physical memory available on the operating system and the total memory available in SQL Server 2000, SQL Server 2005, SQL Server 2008 and SQL Server 2012.

SQL Server 2000 Script:

-- To get the total physical memory installed on SQL Server
CREATE TABLE #OS_Available_Memory (
	ID [int]
	,NAME [sysname]
	,Physical_Memory_In_MB [int]
	,Physical_Memory_In_Bytes [nvarchar](512)
	)

INSERT #OS_Available_Memory
EXEC [master]..[xp_msver]

SELECT [Name]
	,[Physical_Memory_In_MB]
	,[Physical_Memory_In_Bytes]
FROM #OS_Available_Memory
WHERE NAME = 'PhysicalMemory'
GO

DROP TABLE #OS_Available_Memory

--To get the minimum and maximum size of memory configured for SQL Server
SELECT *
FROM [master]..[sysconfigures]
WHERE [comment] IN ('Minimum size of server memory (MB)', 'Maximum size of server memory (MB)')

SQL Server 2005 Script:

-- To get the total physical memory installed on SQL Server
SELECT physical_memory_in_bytes / 1024 / 1024 AS [Physical_Memory_In_MB]
	,virtual_memory_in_bytes / 1024 / 1024 AS [Virtual_Memory_In_MB]
FROM [master].[sys].[dm_os_sys_info]

--To get the minimum and maximum size of memory configured for SQL Server
SELECT [name] AS [Name]
	,[configuration_id] AS [Number]
	,[minimum] AS [Minimum]
	,[maximum] AS [Maximum]
	,[is_dynamic] AS [Dynamic]
	,[is_advanced] AS [Advanced]
	,[value] AS [ConfigValue]
	,[value_in_use] AS [RunValue]
	,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN ('Min server memory (MB)', 'Max server memory (MB)')

SQL Server 2008/200R2 and SQL Server 2012 Script:

-- To get the total physical memory installed on SQL Server
SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
	,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
	,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
	,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
	,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
	,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
	,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]

--To get the minimum and maximum size of memory configured for SQL Server.
SELECT [name] AS [Name]
	,[configuration_id] AS [Number]
	,[minimum] AS [Minimum]
	,[maximum] AS [Maximum]
	,[is_dynamic] AS [Dynamic]
	,[is_advanced] AS [Advanced]
	,[value] AS [ConfigValue]
	,[value_in_use] AS [RunValue]
	,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN ('Min server memory (MB)', 'Max server memory (MB)')

For SQL Server 2000, I used internal system table and extended stored procedure to find out information about available memory to SQL Server instance and machine whereas for SQL Server 2005 and above, I used system view and dynamic management view to retrieve the same information.