Brent Ozar made his paid online tutorial free for the month of March. As awesome as he himself is same are his tutorials, pretty awesome. Learned so much in those ~4 hours which helped me better design my troubleshooting methodology for the query.
There were way too many things to learn but I loved decoder ring for the most common wait types. Below are the common wait types and also the way to find the queries using sp_blitzcache with sortorder to find the queries which might be causing these waits.
CXPACKET: queries going parallel to read a lot of data or do a lot of CPU work. Sort by CPU and by READS.
LCK%: locking, so look for long-running queries. Sort by DURATION, and look for the warning of “Long Running, Low CPU.” That’s probably a query being blocked.
PAGEIOLATCH: Reading data pages that aren’t cached in RAM. Sort by READS.
RESOURCE_SEMAPHORE: Queries can’t get enough workspace memory to start running. Sort by MEMORY GRANT, although that isn’t available in older versions of SQL.
SOS_SCHEDULER_YIELD: CPU pressure, so sort by CPU.
WRITELOG: Writing to the transaction log for delete/update/insert (DUI) work. Sort by WRITES.
HADR_SYNC_COMMIT: Check for Avg ms per wait and its solution is either write less data or move the secondary closer. Not related to query tuning; batching changes.
BACKUPIO: Might be taking backups way too often.
PREEMPTIVE_COM_GETDATA/PREMPTIVE_OS_waitforsingleobject/ Memory_allocation_ext: Dead bored CPU.
ASYNC_Network_IO: It is just waiting for the application to consume the data.
CLR_AUTO_EVENT: Can be ignored if it 1x or 2x of the server time.
being a dba 