Hi,

I have the next setup:

Server 1 (primary)

Server 2 (secondary) sync mode.

Server 3 (secondary) async mode.

Server 1 and 2, are in the same zone/datacenter. Server 3 is outside the country.

We do not know why, but 'server 1' last Saturday had 100% cpu for several hours; we do not know what caused it; can anyone think of a scenario where this could have happened?!

(the other 2 servers didn't have high cpu)

High read activity on Server1?

Thanks for your reply... Yes, but the application said that the load was the same as every Saturday, and this didnt happen in the past... I can't prove it; but if I dont find another reason, that must be it.

Well, if Server2 and Server3 were secondaries at the time, it is not surprising that they did not have a lot of CPU load.

There are umpteen possible reasons why you Server1 had 100% CPU on Saturday. But if you had no monitoring or tracing active, it will be difficult to say what it was after the fact.

Hi erland, thanks for your reply; but you say

if Server2 and Server3 were secondaries at the time, it is not surprising that they did not have a lot of CPU load

I do not understand that Lets say I run millions of expensive updates on server1 over a short period, those same transactions are executed on server2, so if there cpu load on 1, there should be the same on 2.

You can use Extended Events to capture the cpu_time but

this first thing to check if CPU is at 100% is to look for parallel queries:

-- Tasks running in parallel (filtering out MARS requests below):
select * from sys.dm_os_tasks as t
 where t.session_id in (
   select t1.session_id
    from sys.dm_os_tasks as t1
   group by t1.session_id
  having count(*) > 1
  and min(t1.request_id) = max(t1.request_id));

-- Requests running in parallel:
 select *
   from sys.dm_exec_requests as r
   join (
           select t1.session_id, min(t1.request_id)
          from sys.dm_os_tasks as t1
         group by t1.session_id
        having count(*) > 1
           and min(t1.request_id) = max(t1.request_id)
      ) as t(session_id, request_id)
     on r.session_id = t.session_id
    and r.request_id = t.request_id;