Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2008 R2 Agent only see's 40 processors

Posted on 2014-03-26
17
Medium Priority
?
42 Views
Last Modified: 2016-01-21
Why would a SQL 2008 R2 server agent only see 40 processors when the server has 80?  The SQL Server iteslf see's 80 but the agent only 40?  I thought that R2 didn't have this limitation?
0
Comment
Question by:WeberTech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 2
17 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 39956384
Where do you "see" that in "SQL 2008 R2 server agent"??
Can you post a screenshot?
0
 
LVL 25

Expert Comment

by:Tony Giangreco
ID: 39956385
Run Windows Updates & update the system Bios and drivers.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39956396
Also - Is "Hyper-Threading" enabled on that computer? I suggest disable it if only SQL is running on it.

"A hyper-threaded CPU introduces common CPU cache invalidation issues that physical multiprocessor implementations do not experience. The application workload can affect the performance gains and the common CPU cache behavior."

http://support.microsoft.com/kb/322385

And lot more food for thought about hyper-threading here:

"•Turn off hyperthreading if the logical CPU count that would result exceeds the number of CPUs supported by your OS or other software.  Example: Windows Server 2008 R2 supports up to 64 logical  processors, If you have a 40 core server (4, 10 core processors) hyperthreading would result in 80 logical cores. This is 16 cores greater than the OS will support. At start up those 16 logical cores will not be initialized and will be idle.  That is 8 physical cores of processing power not in use."

http://blogs.msdn.com/b/sqladventurer/archive/2012/07/11/hyperthreading-turbo-boost-sql-server-and-you.aspx

http://sqlblog.com/blogs/joe_chang/archive/2013/04/08/hyper-threading-performance.aspx
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:WeberTech
ID: 39956568
lcohan,
Here is the screen shots below
SQL3.jpg
SQL4.jpg
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39956778
Can you please try run commands below in your SQL SSMS? It may be the difference between virtual/logical processors I mentioned about:

-- Identify Virtual Processors
SELECT cpu_count
FROM sys.dm_os_sys_info
 GO


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
0
 
LVL 25

Expert Comment

by:Tony Giangreco
ID: 39956802
If this is running on a server, please post the brand and model
0
 
LVL 1

Author Comment

by:WeberTech
ID: 39956891
We aren't running hyper threading on this server.
0
 
LVL 1

Author Comment

by:WeberTech
ID: 39956907
Here is the results of the query.
3-26-2014-1-10-05-PM.jpg
0
 
LVL 1

Author Comment

by:WeberTech
ID: 39957042
Does MAXDOP have anything to do with this?  Its currently set at 4.  Should this be changed to 8?
0
 
LVL 1

Author Comment

by:WeberTech
ID: 39969991
Anyone know?  Please?
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39970038
What is the SQL Server Service pack and what is your SSMS Service pack from where you run these GUI "properties"? SSMS needs SQL Service pack to be applied as well...
0
 
LVL 1

Author Comment

by:WeberTech
ID: 39973271
Microsoft SQL Server Management Studio  -  10.50.4000.0
Microsoft SQL Server 2008 R2 - 10.50.4000.0
0
 
LVL 1

Author Comment

by:WeberTech
ID: 39973646
our bigger issue is why does the sql agent only acknowlege 40 processors when sql server acknowledges 80
0
 
LVL 1

Author Comment

by:WeberTech
ID: 39975840
Is there a CU that fixes this issue?
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39976559
I personally never heard to be honest and never seen such thing like in your screen shots (I checked ALL servers I have access to and they all show the exact same number in the two places from your screenshot) and I suggest you post the same question on MSDN support/forums.

http://msdn.microsoft.com/en-us/hh361695.aspx
0
 
LVL 1

Author Comment

by:WeberTech
ID: 39978099
Thanks lcohan.  I posted that question on that forum.  I hope we can get an answer soon.  May I ask if your running any CU packages on your SQL servers?  I saw there is about 12 of them for SP2.  I'm curious if any of those address this issue.
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 39978397
In my opinion none of the fixes listed under the latest CU11 (which includes all previous CU's) are related to the issue you are seeing.

CU#11
http://support.microsoft.com/kb/2926028
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question