2

I have recently started monitoring a production database with transactional replication. The CPU % of Total Wait time = %93.00.

This is horrible. I confirmed all 8 processors are being utilized. This database has been in production for six years, so I am wondering is the 93 percent reporting 93% of all processors or is there something I am missing?

enter image description here

9
  • Can you show us the query you are using to get this number? Aug 29, 2012 at 5:59
  • No, taskmanager shows the cpu to be very low.
    – Ross Bush
    Aug 29, 2012 at 13:57
  • Hi Edward, thats the thing. When I execute.....SELECT CPU_Pressure=SUM(signal_wait_time_ms) * 100 / SUM(wait_time_ms), CPU_Wait=SUM(signal_wait_time_ms), CPU_ResourceWait=SUM(wait_time_ms - signal_wait_time_ms), CPU_TotalWait=SUM(wait_time_ms) FROM SYS.DM_OS_WAIT_STATS....I get 19 for CPU pressure. I am creating a baseline in the data collector and it is reporting that the CPU is 98% of all waits.
    – Ross Bush
    Aug 29, 2012 at 14:07
  • 1
    Do you get a similar percentage from running the query here? sqlskills.com/BLOGS/PAUL/post/… Aug 30, 2012 at 5:11
  • 1
    Might want to see if Jonathan Kehayias's comment here applies to your situation (similar to Max Vernon's comment above): social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/… Aug 30, 2012 at 5:17

1 Answer 1

1

SOS_SCHEDULER_YEILD is not the cause, it's a side effect. It just means that something else is burning the cycles. In your graph, the second is LOGGING; this makes sense considering you are running replication. Based on my prior experience, you are probably experiencing log reader. This is typical; you can reduce your PollingInterval and increase CommitBatchSize

Use ProcessExplorer, sort by highest CPU usage process, must be sqlservr. Right click on sqlsrvr, go to properties, click on threads tab, sort by highest CPU column to see the highest cpu consuming thread. Note the thread id(s) down. Execute the below statement to see what is query is using the most CPU

select r.session_id, st.text, qp.query_plan from sys.dm_os_threads as ot join sys.dm_os_tasks as t on t.worker_address=ot.worker_address join sys.dm_exec_requests as r on t.session_id=r.session_id cross apply sys.dm_exec_sql_text(r.sql_handle) as st cross apply sys.dm_exec_query_plan(r.plan_handle) as qp where os_thread_id=<from process explorer high CPU thread>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.