While working with a client, I came up with a query against the SentryOne repository.
The point of it is to find queries that waited more than a second to get a memory grant. I wrote it because this information is logged but not exposed in the GUI yet.
It will show you basic information about the collected query, plus:
- How long it ran in seconds
- How long it waited for memory in seconds
- How long it ran for after it got memory
SELECT HostName, CPU, Reads, Writes, Duration, StartTime, EndTime, TextData, TempdbUserKB, GrantedQueryMemoryKB, DegreeOfParallelism, GrantTime, RequestedMemoryKB, GrantedMemoryKB, RequiredMemoryKB, IdealMemoryKB, Duration / 1000. AS DurationSeconds, DATEDIFF(SECOND, StartTime, GrantTime) AS SecondsBetweenQueryStartingAndMemoryGranted, (Duration - DATEDIFF(MILLISECOND, StartTime, GrantTime)) / 1000. AS HowFastTheQueryRanAfterItGotMemory FROM PerformanceAnalysisTraceData WHERE DATEDIFF(SECOND, StartTime, GrantTime) > 1 ORDER BY SecondsBetweenQueryStartingAndMemoryGranted DESC
The results I saw were surprising! Queries that waited 10+ seconds for memory, but finished instantly when they finally got memory.
If you’re a SentryOne user, you may find this helpful. If you find queries waiting a long time for memory, you may want to look at if you’re hitting RESOURCE_SEMAPHORE waits too.
Thanks for reading!