Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
Can you show us the query you are using to get this number? – Edward Dortland Aug 29 '12 at 5:59
No, taskmanager shows the cpu to be very low. – lrb Aug 29 '12 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. – lrb Aug 29 '12 at 14:07
Do you get a similar percentage from running the query here?… – James Lupolt Aug 30 '12 at 5:11
Might want to see if Jonathan Kehayias's comment here applies to your situation (similar to Max Vernon's comment above):… – James Lupolt Aug 30 '12 at 5:17

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>
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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