Beyond MAXDOP and cost threshold, my CXPACKET parallelism advanced solution looks at other causes, like skewed data and threads stuck on IO, memory or CPU.
SQL SERVER – CXPACKET – Parallelism – Usual Solution – Wait Type – Day 6 of 28
CXPACKET is one of the top wait stats on most multi-CPU systems. Day 6 explains why parallel queries create it and the usual fix with MAXDOP.
SQL SERVER – Capturing Wait Types and Wait Stats Information at Interval – Wait Type – Day 5 of 28
Wait stats reset when services restart. Day 5 shows my method for capturing wait types and wait stats in a table at intervals to compare them later.
SQL SERVER – DMV – sys.dm_os_waiting_tasks and sys.dm_exec_requests – Wait Type – Day 4 of 28
sys.dm_os_wait_stats shows the instance-level picture. This query joins sys.dm_os_waiting_tasks and sys.dm_exec_requests to see what waits right now.
SQL SERVER – DMV – sys.dm_os_wait_stats Explanation – Wait Type – Day 3 of 28
sys.dm_os_wait_stats is the key DMV for understanding wait stats. Day 3 covers the basics of this DMV and how its wait times can point to bottlenecks.
SQL SERVER – Signal Wait Time Introduction with Simple Example – Wait Type – Day 2 of 28
When do wait stats occur? Day 2 explains the running, runnable and suspended states of a query and introduces signal wait time with a simple example.
SQL SERVER – Introduction to Wait Stats and Wait Types – Wait Type – Day 1 of 28
Day 1 of my 28-day series on wait stats and wait types: how reader feedback and six months of tuning work led me to share these real world findings.