Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Number of Processors(SQL Query) Expand / Collapse
Author
Message
Posted Sunday, July 27, 2008 8:32 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 7, 2014 10:40 PM
Points: 99, Visits: 598
Please help me if there is any method to retreive using query, the number of processors in a server on which sql server is installled
Post #541586
Posted Monday, July 28, 2008 3:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:51 AM
Points: 39,871, Visits: 36,216
Yes, there is. You can use the xp_msver extended procedure. I'm not sure of the security requirements

exec xp_msver 'ProcessorCount'




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #541699
Posted Monday, July 28, 2008 4:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 8:41 AM
Points: 39, Visits: 275
/* well, this will work, though it is a little elaborate. it does however provide some other cool info about the processer(s)*/
DECLARE
@nProcessorCount INT
,@vcProcessorModel VARCHAR(5)
,@vcProcessorType VARCHAR(30)
,@vcProcessorNameString VARCHAR(60)

CREATE TABLE #tblStats
(
[Index] INT,
[Name] VARCHAR(200),
Internal_Value VARCHAR(50),
Character_Value VARCHAR(200)
)

INSERT INTO #tblStats
EXEC master.dbo.xp_msver 'ProcessorCount'
INSERT INTO #tblStats
EXEC master.dbo.xp_msver 'ProcessorType'
INSERT INTO #tblStats
EXEC master.dbo.xp_msver 'ProcessorType'


SELECT @nProcessorCount = Internal_Value FROM #tblStats WHERE [Index] = 16
SELECT @vcProcessorModel = Internal_Value FROM #tblStats WHERE [Index] = 18
SELECT @vcProcessorType = Character_Value FROM #tblStats WHERE [Index] = 18

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'HARDWARE\DESCRIPTION\System\CentralProcessor\0',
N'ProcessorNameString',
@vcProcessorNameString OUTPUT,
N'no_output'

SELECT
@nProcessorCount
,@vcProcessorModel
,@vcProcessorType
,@vcProcessorNameString


DROP TABLE #tblStats

-- let me know if you have any issues
Post #541711
Posted Monday, July 28, 2008 7:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:57 AM
Points: 1,081, Visits: 1,493
Since MS licenses on actual CPU (and not cores) I needed to find out the actual cpu for our servers. Our sysadmin provided me an executable (attached) that is exactly what I needed. Change the .txt to .exe and you're good-to-go.

Tim White

  Post Attachments 
Intel-CPU-Count.txt (456 views, 48.00 KB)
Post #541867
Posted Monday, July 28, 2008 7:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:57 AM
Points: 1,081, Visits: 1,493
You can also download this code at http://softwarecommunity.intel.com/articles/eng/2728.htm



Tim White
Post #541876
Posted Monday, July 28, 2008 7:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 7, 2014 10:40 PM
Points: 99, Visits: 598
I thank all of you for all the responses, this has been very useful to me, I have finally decided to use the xp_msver, by Gail Shaw. Thank you very much once again.
Post #541914
Posted Monday, July 28, 2008 8:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:57 AM
Points: 1,081, Visits: 1,493
Just want to make sure you understand that this is a "core" count and not a true cpu count.

Tim White
Post #541922
Posted Monday, July 28, 2008 8:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 7, 2014 10:40 PM
Points: 99, Visits: 598
I understand that it gives the number of cores and not the actual number of physical processors, I believe this is the only option available as I did not see any suggestion which would give the number of physical processors, other than a manual check. I required this as a sql query since i need to run this from a central server on a larger number of servers added as linked servers to the central server. Your replies are enough for me to work on this requirement. Thanks a lot all of you!!
Post #541928
Posted Monday, July 28, 2008 8:50 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, September 8, 2014 11:08 AM
Points: 644, Visits: 2,134
Try this, it's something I wrote to automate a program that could be optimized on number of processors used:

create procedure num_processors
as
set nocount on
declare @numprocs int
create table #numprocs
(
id int,
colname varchar(128),
IV int,
CV varchar(128)
)
insert #numprocs
exec master..xp_msver
select @numprocs = IV from #numprocs
where colname like '%ProcessorCount%'
drop table #numprocs
return @numprocs
go

Hope it helps. It gets the actual internal processor representation from SQL Server so hopefully it is accurate.


Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Post #541952
Posted Saturday, September 3, 2011 3:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:27 PM
Points: 7,107, Visits: 12,657
Waseem Jaleel (7/28/2008)
I thank all of you for all the responses, this has been very useful to me, I have finally decided to use the xp_msver, by Gail Shaw. Thank you very much once again.

I know this is a really old thread but I found it through Google looking for something else so I am confident others will too. xp_msver does not show physical CPU sockets, it shows logical processors.

This will show you the number of physical sockets on your server:

SELECT  cpu_count / hyperthread_ratio AS physical_cpu_sockets
FROM sys.dm_os_sys_info ;


Credit: http://sqlblog.com/blogs/kalen_delaney/archive/2007/12/08/hyperthreaded-or-not.aspx


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1169657
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse