CPU oddity, SQL v2005

Posted on 2014-03-12
Medium Priority
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.  (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.
Question by:dbaSQL
  • 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.
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

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 84

Accepted Solution

Dave Baldwin earned 2000 total points
ID: 39925472
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.
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 84

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 84

Expert Comment

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

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

578 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