so after reading all those, this means:
1) if the waitype is DTC, it is waiting for transactions distributed in all machine to complete before it actually/total complete, so the only wait to solve it is to wait ?
2) I don't understand this, PAGEIOLATCH_EX, please explain what it means
3) so nothing can b done for this wait type: SOS_SCHEDULER_YIELD ? CPU hight usage means need to buy new CPU ?
4) WRITELOG, so this one keep waiting can mean disk problem as it can't find the log disk to write to or STILL writing which is quite impossible ?
so it is hard to see which SYSTEM DB or user database's log file or which log files having problem ...
so it seems for phsical IO related problem, only the following is needed:
as by whoisactive or whatever, I ALSO want to pinpoint any potential hardware/disk problem.
5) no wait type for indicate any RAM problem ?
I read this: http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx
also those for SQL server 2005/2008 but not 2012/2014, wait type no diff ?
from here: http://www.sqlskills.com/blogs/paul/worrying-wait-type/
"I’d be unsurprised to see ASYNC_NETWORK_IO, CXPACKET, PAGEIOLATCH_SH, or WRITELOG as the top wait, and I’d be intrigued to see SOS_SCHEDULER_YIELD as the top wait as that’s very uncommon. None of these would be the most worrying IMHO. PAGELATCH_EX would mean there’s a big bottleneck somewhere, which may be easy to alleviate (if in tempdb) or not, but queries would still be progressing."
So is it also the same wait type you all might worry too ? usually what should be worry about ? I worry about disk IO problem.
now some logical table access lock waittype:
do they means some one type the table lock without releasing it ? very important to catch something developer done wrong.... :):)):)
if seeing this LCK_M_IX, by a spid, the query of that spid is waiting for a lock release by another spid show by the blocking_session_id, and we can read the SQL text of that blocking_Session_id and start to tune it or/and ask developer why that blocking_Session_id query ran so long?
so the author of that link seems very worry about LCK_M_IX, how about you all ?