CPU oddity, SQL v2005

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.  (http://www.brentozar.com/blitz/).  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 (http://www.brentozar.com/blitz/schedulers-disabled/) 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.
LVL 17
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
This page http://technet.microsoft.com/en-us/library/ms143760%28v=sql.90%29.aspx 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.
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!)


dbaSQLAuthor Commented:
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);
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Jim P.Commented:
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.
dbaSQLAuthor Commented:
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
dbaSQLAuthor Commented:
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.
dbaSQLAuthor Commented:
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.
Dave BaldwinFixer of ProblemsCommented:
Any thoughts about the affinity I/o mask, and affinity mask?
No, I don't know about those things.
Anthony PerkinsCommented:
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.  :)
dbaSQLAuthor Commented:
Jim P.Commented:
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

dbaSQLAuthor Commented:
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.
dbaSQLAuthor Commented:
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.
Dave BaldwinFixer of ProblemsCommented:
You're welcome, glad to help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.