High CPU on SQL 2008 64BIT

  • Guys,

    The CPU on the SQL 2008 sp2 64bit box is always at 70% utilization which is causing queries to time out especially the ones which have sorting operations

    like 'DISTINCT' and 'ORDER BY' clause. On further investigation from performance monitor we found that one of the SQL processes was consuming most of the CPU.

    We looked for the the processid in the sysprocess and found that cmd check point was the related processid consuming the resources.

    spidkpidstatus hostname cmdDB Name

    111948background CHECKPOINT master

    When the CPU is normal with < 10% utilization interspersed with spikes, the sorting queries comeback in 0 secs.

    Below are the env variables

    System: Windows 2008 R2 Standard 64bit

    SQL Server: Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)

    DB Size: 3gb

    Number of DBS

    on the instance: 2

    DB Modes:Stand by/read only since these are destination log shipping databases with logs restored every night.

    Any suggestions and inputs would help.

    Thanks

  • What do you see if you set up perfmon to collect CPU information and SQLServer:Buffer Manager Checkpoint pages/sec?

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Thanks for the response David

    I look at perfmon and gathered the process which consumes most CPU, subsequently from that process I grabbed the corresponding thread and plugged it in the the query below

    select cmd, cpu, spid, lastwaittype

    from master..sysprocesses

    --WHERE CMD = 'RESOURCE MONITOR'

    WHERE KPID = '1764'

    cmdcpuspidlastwaittype

    RESOURCE MONITOR3215319061PREEMPTIVE_XE_CALLBACKEXECUTE

    Please let me know if you need any further info

    Thanks

  • Yes, I see what you did. What I need to know though is Checkpoint pages/sec high when CPU is high and is there always a direct correlation. The wait type information is not helpful to me at this point.

    So, if you could run perfmon and look at those two counters together and get back to me that would be great.

    Note: I see that you had this similar issue back in November. Are you still looking for the same answer?

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • David,

    Back in November when we had the issue with "Resource Monitor" upon investigation it was indicated that it is a know issue and SP2 would take care of it (Pls see the links below). Since then we applied SP2 but the problem is still persistent.

    http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/bd9e4476-39e2-46f8-a426-bef9601f9ad9

    http://support.microsoft.com/kb/968722/en-us

  • Ok - thanks for the update. Please let me know what you see with those two counters.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • David there is a direct correlation i.e checkpoint pages is high when cpu is high.

  • So, there is very little you can do to alleviate that aside from making sure that your IO subsystem can handle that activity; which at present it is not.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Once you know the CPU is high, I'd start drilling down on what's causing it to be that high. What processes are using it up? I'd go to the DMO objects and start gathering metrics on CPU, and I'd monitor them to see when you're hitting waits, what those waits are, and what those queries are doing. It might be a system configuration issue, but it's most likely a fundamental tuning issue, incorrect or missing indexes, badly written queries, etc.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • Enable Optimize for Adhoc Workloads option for the SQL Server Instance. This will reduce the procedure cache bloating and the CPU compilation overhead.

    sp_configure 'show advanced options', 1

    go

    reconfigure

    go

    sp_configure 'optimize for ad hoc workloads', 1

    go

    reconfigure

    go

    Aslo check for the following Perfmon Counters and let us know their values

    SQL Server: SQL Statistics -- Batch Requests/Sec

    SQL Server: SQL Statistics -- SQL Compilations/Sec

    SQL Server: SQL Statistics -- SQL ReCompilations/Sec

    Thank You,

    Best Regards,

    SQLBuddy

Viewing 10 posts - 1 through 10 (of 10 total)

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