During peak database activity times, memory pressure is causing severe performance issues with many queries waiting for memory grants from the resource semaphore. I'd like to target queries for optimization that are associated with large memory grants and long duration times. However, when I filter the event calendar or Top SQL by duration and memory grants, the duration time includes the wait time.
If a query is requesting a large amount of memory but is spending most of its time waiting for the resource semaphore but runs under a minute, I would rather prioritize a query that requests a large amount of memory but runs for an hour. If the former query waited for 59 minutes but finishes in 1.5 minutes, then we want to look at the plan eventually but I want to see that is spent most of its time waiting.
Is this possible in SQL Sentry?