baabhu (10/28/2012)

---

Hello Everyone,

Our production server experiences lot of blocking and locking from application.

I have attached the top waits from server. Can anyone help to understand or guide me what should be our first goal.

We cannot stop the traces for few days since we are in the midst of troubleshooting a application issue.

Other than trace wait I see a lot of waits on the OLEDB. We have Peer to peer replication setup done across networks.

wait_type waiting_tasks_count wait_time_ms
TRACEWRITE 10376534 14220260175
OLEDB 300522122 14205053714
WRITELOG 547624686 7070550328

Does this sounds too much on a OLTP systems. Or is this a normal figure.

Please let me know if I need to submit any other information.

Thank you.
Babu

capnhector (10/29/2012)

---

one thing with sys.dm_os_wait_stats (which given the format is where i think you are looking) is that they are cumulative since last server restart (or stats reset). so with out knowing how much uptime your server has had these numbers could be really low, really high, or somewhere in the middle. also if you dont have any thing to compare to these numbers may be what is normal for your server and since you are looking at them while there is a problem have no idea if its even where you should be looking.

give that you ask if its to much on a oltp system i dont think you have a server base line (which is different for every server based on what its work load is.) with out alot more information it will be hard to pinpoint what is going on.

Lee Crain (10/31/2012)

---

This page from Paul Randal's blog may assist you:
http://www.sqlskills.com/blogs/paul/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx

scogeb (10/31/2012)

---

Cool article, thanks for posting it!

One of the main servers I deal with has CXPACKET waits of about 60%. I've told the developers which SPs have a bunch, but they have yet to change the maxdop on them. It will be interesting to see how much that helps performance.