CPU oddity, SQL v2005

Posted on 2014-03-12
Last Modified: 2014-03-17
I have a problem with a v2005 instance.  I just took on this project, the server is still fairly new to me.  It is virtualized with an assigned 8 cores.  I am told that 'it never uses more than one cpu' by the guy who runs the IT department.  He's shown me screen shots from some monitoring app where one core is 100% and 7 other process are at 0%.  He says again and again that there are 8 cores and SQL is using only one.  I have also seen a similar graphic from perfmon.

This has become very pressing, so today I've begun to diagnose.  First, the results below are 8, 4 and 4, respectively:

-- how many cpu's does SQL see?
select cpu_count from sys.dm_os_sys_info

-- how many of them are online/available/used to SQL?
FROM sys.dm_os_schedulers
AND is_online = 1

-- how many of them are offline/not avaiable to SQL?
select COUNT(*) [NotAvailCPU]
from sys.dm_os_schedulers
where is_online = 0

That tells me that SQL sees all 8 cores, but it is only using 4.  If I've got 8 cores, why would 4 be offline?  This gives a little more detail on the schedulers:

select status,count(*) from sys.dm_os_schedulers group by status

  Status                         Count
  HIDDEN ONLINE           48
  VISIBLE ONLINE              4
  VISIBLE OFFLINE             4

Again, if the box has 8 cores, why would 4 be offline/unavailable?  I am a huge Ozar advocate, so I ran blitz to see what he could tell me.  (  He tells me this:

CPU Schedulers Offline
Some CPU cores are not accessible to SQL Server due to affinity masking or licensing problems.

Ouch!  Haven't seen that before.  Why would SQL not be using all of it's CPU's?  I read further ( and see that this is caused by affinity masking or virtualization.  

Yes, this is a virtualized environment, but first I take a look at server\Properties\Processor, and I see the attached screen shot.  Now it could just be me, but why are those boxes checked per cpu?  If the auto boxes are checked, aren't these boxes empty/unchecked?  I've looked at 2 other servers --  every where else, the boxes per cpu are NOT checked.

Also, affinity I/o mask and affinity mask both have 0 for config and run values.  Maybe I just haven't paid attention to this before, but this seems off to me.

Oh.  Also, you see that max worker threads at 255 -- this dmv tells me there are 288:
select max_workers_count from sys.dm_os_sys_info

Which is it --  255 or 288?  

I am betting/guessing this was an upgrade from v2000, which had the default of 255.  And, it should probably be set back.  Would you agree?

CPU usage is the big one.  Please.  Any advice is truly welcome.
Question by:dbaSQL
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
  • 7
  • 3
  • 2
  • +2
LVL 16

Expert Comment

ID: 39925323
I _think_ you can turn hyperthreading on and off in SQL Server 2005 - that might account for half your seen cores being unavailable (I'm not at my machine to look, so I can't tell you how to check right now - sorry!)


LVL 17

Author Comment

ID: 39925360
I have this piece from Glen Berry, but I'm not sure exactly how to translate the hyperthread_ratio.

      cpu_count [Logical CPU Count],
      hyperthread_ratio [Hyperthread Ratio],
      cpu_count/hyperthread_ratio [Physical CPU Count],
      physical_memory_in_bytes/1048576 [Physical Memory (MB)]
      sys.dm_os_sys_info OPTION (RECOMPILE);
LVL 38

Expert Comment

by:Jim P.
ID: 39925363
I am betting/guessing this was an upgrade from v2000

Here's an experiment that could probably prove that. Do you have enough room to do a parallel install of a brand new SQL2005 named instance?

Then see if the new instance works correctly. If it does that pretty much answers your questions.
Anecdotal personal experiences: I have never upgraded a SQL Server in place. And after SQL 2000 I never moved/restored an instance of SQL Server because you could no longer edit the system tables in master. That was caused by a disaster recovery test held three months after we upgraded to SQL 2005. The recovery took two days, was not a success for all databases, and I had to write up one of the longest responses to the DR auditor in my life. I had to find a workaround that was quick and efficient.

My method depends on the fact that I manually script all my maintenance jobs (backups, deletes, tuning) instead of using the GUI. I also setup all my instances to run the sp_help_revlogin  to dump to disk every eight hours. That allows me to attach/restore the database, recreate the SQL server logins, and then recreate the maint jobs. About the only issue is if you have an application that uses the SQLCLR functions. But if you are re-installing the app anyway...

Another trick if you have access to the DNS setup. Build the server with whatever name. Then when you install the DB to the server create a CName record like AppName1 that points to ServerName1. Then if you ever want to move a database to a new server call the server ServerName2 and then edit the DNS for AppName1 to point to the new server. The end user will probably never know.
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

LVL 17

Author Comment

ID: 39925378
Unfortunately, no.  There isn't any room or time to do another installation.  This is a production server.  Right now, I really just need to answer -- why does it look like SQL is not using all CPUs?  Per the DMV's -- If SQL has 8 cores, why are only 4 online?

select status,count(*) from sys.dm_os_schedulers group by status

  Status                         Count
  HIDDEN ONLINE           48
  VISIBLE ONLINE              4
  VISIBLE OFFLINE             4
LVL 83

Accepted Solution

Dave Baldwin earned 500 total points
ID: 39925472
This page lists the cores supported by the different version of SQL Server.  It says SQL SErver 2005 Standard Edition is limited to 4.  There is a drop down for other versions like 2008 and 2012.
LVL 17

Author Comment

ID: 39925522
I can't believe that I haven't seen this before today.  Wow!

I don't know if that explains my question about the affinity I/O and affinity mask, but that explains the specs that the engine is giving me in the DMVs.
LVL 17

Author Comment

ID: 39929203
Any thoughts about the affinity I/o mask, and affinity mask?

I spoke to the customer.  They say it was not an upgrade, it was built in v2005 from scratch.   Yet nobody can remember why max worker threads was 255.
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39929656
Any thoughts about the affinity I/o mask, and affinity mask?
No, I don't know about those things.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39930727
What edition of SQL Server?  Don't go from memory, run this:

If it is the Express Edition that would certainly explain why it is only using one CPU.  :)
LVL 17

Author Comment

ID: 39930752
LVL 38

Expert Comment

by:Jim P.
ID: 39930793
Here's the result from the sp_configure on a standard edition. I changed the xp_cmdshell. The rest should be the defaults.

Ad Hoc Distributed Queries                   0          1          0          0
affinity I/O mask                  -2147483648 2147483647          0          0
affinity mask                      -2147483648 2147483647          0          0
Agent XPs                                    0          1          0          0
allow updates                                0          1          0          0
awe enabled                                  0          1          0          0
blocked process threshold                    0      86400          0          0
c2 audit mode                                0          1          0          0
clr enabled                                  0          1          0          0
cost threshold for parallelism               0      32767          5          5
cross db ownership chaining                  0          1          0          0
cursor threshold                            -1 2147483647         -1         -1
Database Mail XPs                            0          1          0          0
default full-text language                   0 2147483647       1033       1033
default language                             0       9999          0          0
default trace enabled                        0          1          1          1
disallow results from triggers               0          1          0          0
fill factor (%)                              0        100          0          0
ft crawl bandwidth (max)                     0      32767        100        100
ft crawl bandwidth (min)                     0      32767          0          0
ft notify bandwidth (max)                    0      32767        100        100
ft notify bandwidth (min)                    0      32767          0          0
index create memory (KB)                   704 2147483647          0          0
in-doubt xact resolution                     0          2          0          0
lightweight pooling                          0          1          0          0
locks                                     5000 2147483647          0          0
max degree of parallelism                    0         64          0          0
max full-text crawl range                    0        256          4          4
max server memory (MB)                      16 2147483647 2147483647 2147483647
max text repl size (B)                       0 2147483647 2147483647 2147483647
max worker threads                         128      32767          0          0
media retention                              0        365          0          0
min memory per query (KB)                  512 2147483647       1024       1024
min server memory (MB)                       0 2147483647          0          8
nested triggers                              0          1          1          1
network packet size (B)                    512      32767       4096       4096
Ole Automation Procedures                    0          1          1          1
open objects                                 0 2147483647          0          0
PH timeout (s)                               1       3600         60         60
precompute rank                              0          1          0          0
priority boost                               0          1          1          1
query governor cost limit                    0 2147483647          0          0
query wait (s)                              -1 2147483647         -1         -1
recovery interval (min)                      0      32767          0          0
remote access                                0          1          1          1
remote admin connections                     0          1          0          0
remote login timeout (s)                     0 2147483647         20         20
remote proc trans                            0          1          0          0
remote query timeout (s)                     0 2147483647        600        600
Replication XPs                              0          1          0          0
scan for startup procs                       0          1          0          0
server trigger recursion                     0          1          1          1
set working set size                         0          1          0          0
show advanced options                        0          1          1          1
SMO and DMO XPs                              0          1          1          1
SQL Mail XPs                                 0          1          0          0
transform noise words                        0          1          0          0
two digit year cutoff                     1753       9999       2049       2049
user connections                             0      32767          0          0
user instance timeout                        5      65535         60         60
user instances enabled                       0          1          1          1
user options                                 0      32767          0          0
Web Assistant Procedures                     0          1          0          0
xp_cmdshell                                  0          1          1          1

Open in new window

LVL 17

Author Comment

ID: 39930806
I know what the sp_configure return is.  My question is just about what I'm seeing in the gui.  If we are using the defaults, do we expect those boxes to be checked per cpu?  Maybe this is norm.  I just haven't seen this before, and I need to confirm.
LVL 17

Author Closing Comment

ID: 39934151
Any insight on the affinity selections in the gui would be great.  But, no need to keep this open.   Thank you for the assist on the cpu supported by different versions, Dave.  Much appreciated.
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39934858
You're welcome, glad to help.

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

756 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