# Chapter 7: The Waiting Game (Diagnostic Guide) Every time the elephant stops moving, he leaves a trail of breadcrumbs explaining *why*. To understand your workload, you must learn to read these signs. ## The Diagnostic Flowchart When your query is slow, look at the "Wait State": ### 1. Is the Elephant Still "Working"? (CPU/No Wait) If there is **no wait event** (or it says "CPU"), the elephant is in "The Sweat" mode. He is currently hammering away at a task. - **Top Suspects:** [[CPU]], [[Memory]] (heavy sorting), or [[Extension]] (complex logic). - **Pro Tip:** Look for [[Sort]] or [[HashJoin]] operations in your Explain Plan. ### 2. Is the Elephant Waiting for a Book? (IO Wait) If you see **IO** events, the elephant is in "The Sigh" mode, waiting for the basement elevator. - **Top Suspects:** [[IO|Disk IO]] (The filing cabinet is too slow). - **Pro Tip:** You might need more [[Memory|Index Cheat Sheets]] to avoid the basement altogether. ### 3. Is the Elephant Bumping into Others? (Lock/LWLock Wait) If you see **Locks**, the elephant is trapped in a crowd. - **Top Suspects:** [[Lock|Heavy Locks]] (someone is renovating the warehouse) or [[LWLock|LWLocks]] (shoving past others to see a shared buffer). - **Pro Tip:** Check [[Activity]] to see who is holding the padlock. ### 4. Is the Elephant Waiting for a Message? (IPC Wait) If you see **IPC**, the elephant is waiting for his teammates to yell back. - **Top Suspects:** [[IPC]] (Parallel workers talking to each other). - **Pro Tip:** This is usually a sign of [[Gather|Parallelism]] in action. ### 5. Is the Elephant Just Bored? (Client/Activity Wait) If you see **Client**, the elephant is just sitting there waiting for *you* to tell him what to do next. - **Top Suspect:** [[Client]] (The network or your application code is slow). --- ## The Wait Event Library - [[Activity]] - Sleeping or idling. - [[BufferPin]] - Waiting for a page to be unlocked. - [[Client]] - Waiting for the user. - [[Extension]] - Custom code is running. - [[IO]] - Waiting for the physical disk. - [[IPC]] - Parallel worker chatter. - [[Lock]] - Heavy table/row blockades. - [[LWLock]] - Light, high-speed memory friction. - [[Timeout]] - Stubbornly gave up.