SQL SERVER – Finding Count of Logical CPU using T-SQL Script – Identify Virtual Processors

I recently received email from one of my very close friend from California. His question was very interesting. He wanted to know how many virtual processors are there available for SQL Server. He already had script for SQL Server 2008 but was mainly looking for SQL Server 2000. He made me go to my past. I found following script from my old emails (I have no reference listed along with it, so not sure the original source).

-- Identify Virtual Processors in for SQL Server 2005, 2008, 2008R2, 2012
SELECT cpu_count
FROM sys.dm_os_sys_info
GO
-- Identify Virtual Processors in for SQL Server 2000
CREATE TABLE #TempTable
([Index] VARCHAR(2000),
[Name] VARCHAR(2000),
[Internal_Value] VARCHAR(2000),
[Character_Value] VARCHAR(2000)) ;
INSERT INTO #TempTable
EXEC xp_msver;
SELECT Internal_Value AS VirtualCPUCount
FROM #TempTable
WHERE Name = 'ProcessorCount';
DROP TABLE #TempTable
GO

Yesterday I shared on facebook page about I am writing this blog post, SQL Server Expert Simran Jindal shared following script which is applicable to SQL Server 2005 and later versions. I just got update from her that this query is of my dear friend and SQL Server MVP Glenn Berry. Thanks Glenn.

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_in_MB,
sqlserver_start_time, affinity_type_desc -- (affinity_type_desc is only in 2008 R2)
FROM sys.dm_os_sys_info

If know any other reliable method to get the count of logical CPU, please share that in comment and I will update this blog post with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

14 thoughts on “SQL SERVER – Finding Count of Logical CPU using T-SQL Script – Identify Virtual Processors

  1. neither of the above work if you have a quad core machine. I have yet to find anything in a SQL Query that works for quad core. If you find anything, please post it. Thank you.

  2. On SQL Server 2012, physical_memory_in_bytes has been renamed to physical_memory_kb in sys.dm_os_sys_info.

    This is a very helpful script. Thanks.

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #015 « SQL Server Journey with SQL Authority

  4. Pinal, try running this script on a VM where sql edition is std and ram is higher than 64 gb, you will be surprised. my server has 128gb but [Physical_memory_in_bytes] shows 64 gb, go figure.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s