Solved

CPU oddity, SQL v2005

Posted on 2014-03-12
14
343 Views
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?
SELECT COUNT(*) [AvailCPU]
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
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
  VISIBLE ONLINE (DAC)   1

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.
processor-properties-pg.png
0
Comment
Question by:dbaSQL
  • 7
  • 3
  • 2
  • +2
14 Comments
 
LVL 16

Expert Comment

by:DcpKing
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!)

hth

Mike
0
 
LVL 17

Author Comment

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


SELECT
      cpu_count [Logical CPU Count],
      hyperthread_ratio [Hyperthread Ratio],
      cpu_count/hyperthread_ratio [Physical CPU Count],
      physical_memory_in_bytes/1048576 [Physical Memory (MB)]
FROM
      sys.dm_os_sys_info OPTION (RECOMPILE);
0
 
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.
0
 
LVL 17

Author Comment

by:dbaSQL
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
  VISIBLE ONLINE (DAC)   1
0
 
LVL 82

Accepted Solution

by:
Dave Baldwin earned 500 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.
0
 
LVL 17

Author Comment

by:dbaSQL
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.
0
 
LVL 17

Author Comment

by:dbaSQL
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.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 82

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.
0
 
LVL 75

Expert Comment

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

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

Author Comment

by:dbaSQL
ID: 39930752
Standard
0
 
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

0
 
LVL 17

Author Comment

by:dbaSQL
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.
0
 
LVL 17

Author Closing Comment

by:dbaSQL
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.
0
 
LVL 82

Expert Comment

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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now