A SQL Server Memory Grant Helper Query For The Sentry One Repository

Posted on by Erik Darling

SEMA4

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 Sentry One 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!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts

5 thoughts on “A SQL Server Memory Grant Helper Query For The Sentry One Repository”

  1. Thanks Eric!
    I try to mine different things out of Sentry with various degrees of success. This is a new one for me. Will be useful. Thanks for pushing it to the front

  2. Hi Erik,

    This is great and never knew about this. I ran and found one SP which purges data one of the db every hour for partitioned tables. Column SecondsBetweenQueryStartingAndMemoryGranted on avg shows 220 mins and HowFastTheQueryRanAfterItGotMemory on avg 20 seconds. Also the memory granted is just 1024KB. Why would so much time be spent in allocating 1 MB memory ?

    1. The answer can sometimes be more complicated, but generally it’s just that other queries running when that query wants to run have used up all the available memory for queries.

      You should look at your wait stats for RESOURCE_SEMAPHORE next.

      Thanks!

      1. Thanks, I do not see that wait, but will check further for any issues. Also is the data cumulative? And can the query also show the database name for the available text data as my sentry has almost 30 servers listed?

Comments are closed.