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 (https://blog.sqlauthority.com)
17 Comments. Leave new
Nice code sir..it’s very useful…!!!
Thanks Pinal..
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.
It’s useful information Pinal
How do you get the number of cores which is needed for sql 2012?
Right click on server instance–>properties–>processors
You will see how many processors and which are enabled.
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.
Useful, thanks.
is it applies for Virtual machines?
Thanks.
Finding actual number of physical cpu installed
By Rahul Singh, 2013/01/14
The Sql Server always see Logical cpu it does not distinguish between Hyperthreaded logical cpu and physical processor using the below query we can find out actual number of physical cpu installed on the server
Like always. The first place to find your answer “sqlauthority.com”
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.
Standard edition is limited to 64GB by design
Hello Pinal,
I tried the query that you shared written by Glenn Berry i greatly respect him and his work towards SQL community but the query did not run in my SQL 2012 environment, I have to tweak the query a little to make it work and the results were as expected. Below is the query with my tweak with all due respect and credit to Glenn B.
Regards
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS Hyperthread_Ratio,
cpu_count/hyperthread_ratio AS Physical_CPU_Count,
physical_memory_kb/1024 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
Please replace the — with — because it is a comment.
simple question: So what if you only have one logical and 1 physical processor? Then just use 1 file?