There are many ways how you can troubleshoot a slow-running query. One technique is to capture the wait stats for the query. Waits? What are those? In most simplistic terms, either your query is running or waiting for resources. When the query is waiting for resources, SQL Server will assign one or more wait type(s) to the query depending on what it is waiting for. Maybe it’s waiting for CPU, or memory, or IO, or waiting for a lock to be released – there are so many reasons why the execution can be stalled when the query has to wait for something. Thankfully, SQL Server captures that information but there is no easy way to extract that information ‘only’ for your given query. Extended Events can help here. You can set up an extended events session and capture wait stats ‘only’ for your query. You can apply a variety of filters to capture wait stats ‘only’ for your query. Cut down on the noise and only extract what you need. Extended Events (XE) is conceptually similar to the profiler tool but advanced and lightweight. While setting up the session, use the wait_info event class. Need a demo? Just put your request in the comments section and I will try to share a link to a video or a blog post. Sounds interesting? Watch the video below.

You can also visit DataPlatformGeeks (DPG) Video Library to watch more videos and to download video resources (presentation & demo code).

All DPG members (membership is free) have free access to many learning resources including in-person events, webinars, past event resources, videos, magazine & Hands-On-Labs.

If you wish to practice a SQL concept step-by-step, try SQLMaestros Hands-On-Labs. (Get access to hundreds of lab documents on SQL Server, Azure, Analytics, BI, Machine Learning, etc)

If you are looking for advanced SQL Server & MS Data Platform Training (in-person & online), explore SQLMaestros.

Hope the video was worth your time.