How to Monitor for High CPU utilization in SQL Server

  • Geoff A

    SSChampion

    Points: 11417

    Comments posted to this topic are about the item How to Monitor for High CPU utilization in SQL Server

  • vyankarla

    SSC Enthusiast

    Points: 149

    Very good article. How about running the VBS file using windows Task scheduler. ?

  • satalaj

    SSC Veteran

    Points: 268

    Hi,

    What actions do you perform if it reaches more than 90%. In my scenario, I have multiple SQL jobs which runs in while loop

    e.g.

    While(1=1)

    begin

    -- do something

    -- delete top 500 records from x table of SERVER A and insert into x table of SERVER B

    END

    CPU usage of SERVER A reaches more than 90%. I know its because of infinite while loop but

    I have to transfer the record from server A to Server B as soon as records gets entered into SERVER A.

    Is there any way to deal with it.

    Thanks, Satalaj.

  • arr.nagaraj

    SSCertifiable

    Points: 6518

    Good article. Handy way to monitor CPU.

    But one improvement I would like to see is a way to find which process is causing the CPU hit.

    It may not be always SQL Server process. Or atleast it should say SQL usage % and other process usage %. Another improvement can be some information to figure out what internally caused the CPU pressure within SQL Server, if SQL was consuming the most of CPU. For addressing these, I prefer DMVs.

  • hrc

    Old Hand

    Points: 323

    What about @@CPU_BUSY and @@IO_BUSY

    if you checked those two values e.g. every 10 seconds and the @CPU_BUSY had increased 8 seconds I would assume that it was running on 80%, that without the VBA and the security issues it imposes.

  • Boxer

    SSC-Addicted

    Points: 495

    Satalaj, you have to make sure that while running procedures in infinite loop, you make sure that you identify which procedures running parallely are getting the CPU usage up (by parallel, I mean the tasks running in parallel). Also, check the CXPACKETS running as the wait type.

    You can also use SQL Server Profiler and also perfmon correlated together to toggle through the database and traces, to see when CPU usage creeps up.

    Rgds!!

  • arr.nagaraj

    SSCertifiable

    Points: 6518

    What about @@CPU_BUSY and @@IO_BUSY

    if you checked those two values e.g. every 10 seconds and the @CPU_BUSY had increased 8 seconds I would assume that it was running on 80%, that without the VBA and the security issues it imposes.

    Sys.dm_os_ring_buffer gives CPU usage % directly. Refer http://strictlysql.blogspot.com/2010/06/finding-cpu-utilization-in-sql-server.html

  • Geoff A

    SSChampion

    Points: 11417

    arr.nagaraj (11/17/2010)


    What about @@CPU_BUSY and @@IO_BUSY

    if you checked those two values e.g. every 10 seconds and the @CPU_BUSY had increased 8 seconds I would assume that it was running on 80%, that without the VBA and the security issues it imposes.

    Sys.dm_os_ring_buffer gives CPU usage % directly. Refer http://strictlysql.blogspot.com/2010/06/finding-cpu-utilization-in-sql-server.html

    yes, that DMV will provide CPU info, however, those views are not available in SQL 2000. And unfortunately, like most of us, I still have quite a few SQL Server 2000 systems to look after.

  • Geoff A

    SSChampion

    Points: 11417

    hrc (11/17/2010)


    What about @@CPU_BUSY and @@IO_BUSY

    if you checked those two values e.g. every 10 seconds and the @CPU_BUSY had increased 8 seconds I would assume that it was running on 80%, that without the VBA and the security issues it imposes.

    might be possible, but i never found a good way to do that with either @@CPU_BUSY or sp_monitor.

    by using the Win32_PerfRawData_PerfOS_Processor object, the data is coming from the same place that perfmon uses. I find that to be highly accurate.

    Thanks

  • satalaj

    SSC Veteran

    Points: 268

    yes, I know that query who leads high CPU utilisation.

    I had written that query in while loop. It simply deletes top 500 records with UPDlock,READPAST

    and insert them into linked SERVER B.

    Any suggestion?

  • Larry Kruse

    SSCrazy

    Points: 2617

    Hey Geoff! Many thanks for the interesting article. I always learn a lot by reading these.

    I'd also echo the comments by Raj in that I'd like to know more about what is going on to cause this condition. I have some processes running during the middle of our night which are causing slow-downs of other processing, but I cannot find what is happening during those times. While I'm certain that SQLSERVR.exe that is consuming the CPU, I cannot tell what inside of SQLSERVR might be causing the problem.

    Is there a way to discover which process/processes inside of SQLSERVR might be causing this condition?

    Many thanks for the informative article!

    Larry

  • hrc

    Old Hand

    Points: 323

    Reply to satalaj: How about using a trigger on insert (and there copy the newly inserted records onto server b and delete the old ones). You have the pseudotables "inserted", "deleted" and "updated" (afaIr) to skim.

    How do you go about deleting the 500 records? Like this: "delete from table where ref in (select top 500 ref from table)"? This is probably the one draining power. You should check the relevant indexes.

  • matt.austin

    SSC Enthusiast

    Points: 162

    Does this work for Virtual Servers? If not any suggestions?

  • satalaj

    SSC Veteran

    Points: 268

    Hi hrc

    The insert operation was performed by Asp.net technology on SERVER A. you suggested that use triggers which means insert on SERVER A should depend on availability of the SERVER B in transaction. We can't afford this.

    Below is the way I have implemented the row processing in infinite loop.

    Begin tran tran1

    declare @x Table(

    [ID] [bigint] NOT NULL,

    [Mobile] [varchar] (50) NOT NULL,

    [Message] [varchar](50) NOT NULL,

    [createdate] [datetime] )

    SET XACT_ABORT ON

    DELETE top(500) xyz with (UPDLOCK,READPAST)

    OUTPUT deleted .* INTO @x

    Insert into [34].[apiTEST].[dbo].[xyz]

    select * from @x

    Commit

    -- here 34 is linked server B

    Thanks, Satalaj.

  • Geoff A

    SSChampion

    Points: 11417

    matt.austin (11/17/2010)


    Does this work for Virtual Servers? If not any suggestions?

    yes, it will work on VM's as well as Physical servers.

Viewing 15 posts - 1 through 15 (of 60 total)

You must be logged in to reply to this topic. Login to reply