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]
WHERE status = 'VISIBLE ONLINE'
AND is_online = 1
-- how many of them are offline/not avaiable to SQL?
select COUNT(*) [NotAvailCPU]
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
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\Processo
r, 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.