Tell me more ×
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. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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
have you looked at Task Manager? does it show similar cpu useage? – Max Vernon Aug 29 '12 at 4:42
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. – ltn Aug 29 '12 at 13:57
1  
Do you get a similar percentage from running the query here? sqlskills.com/BLOGS/PAUL/post/… – James Aug 30 '12 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/… – James Aug 30 '12 at 5:17
show 5 more comments

1 Answer

up vote 1 down vote

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

 
discard

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.