SQL Server Community Tools: How To Use sp_WhoIsActive To Get Memory Grant Information

Posted on by Erik Darling

Woah woah woah

Up until now, I’ve written about scripts that I wrote from scratch. But they’re far from the only tools I use in my every day life.

A big one is, and always has been, sp_WhoIsActive. It’s great. You hit F5, and it tells you everything running on your server.

What more could you ask for?

In the past, I’ve written a bit about it:

And get this! It’s still under active development. It’s slow, but it happens. I’m going to spend the first couple posts this week talking about cool new features in the most recent release that I’ve been using lately.

Get It

A recent addition to sp_WhoIsActive in version 12 is collecting a bunch of memory grant information for running queries.

To get the extra details via a nifty clickable XML column, use this:

EXEC sp_WhoIsActive
    @get_memory_info = 1;

You’ll get some new top-level columns in the results that look like this:

SQL Server Memory Grants
kaboom

But you’ll also get an XML clickable column called memory_info that has much more detail in it.

When a query is running normally, the first XML fragment will look like this:

<memory_grant>
  <request_time>2022-08-25T19:05:26.663</request_time>
  <grant_time>2022-08-25T19:05:26.663</grant_time>
  <requested_memory_kb>5927872</requested_memory_kb>
  <granted_memory_kb>5927872</granted_memory_kb>
  <used_memory_kb>1833160</used_memory_kb>
  <max_used_memory_kb>1833160</max_used_memory_kb>
  <ideal_memory_kb>9573888</ideal_memory_kb>
  <required_memory_kb>4864</required_memory_kb>
  <dop>8</dop>
  <query_cost>3380.9998</query_cost>
</memory_grant>

When a query is running (or not running, depending on how you look at it) abnormally, and waiting on RESOURCE_SEMAPHORE, it will look like this:

<memory_grant>
  <request_time>2022-08-25T19:05:27.030</request_time>
  <wait_time_ms>1656</wait_time_ms>
  <requested_memory_kb>5927872</requested_memory_kb>
  <ideal_memory_kb>9573888</ideal_memory_kb>
  <required_memory_kb>4864</required_memory_kb>
  <queue_id>8</queue_id>
  <wait_order>0</wait_order>
  <is_next_candidate>1</is_next_candidate>
  <dop>8</dop>
  <query_cost>3380.9998</query_cost>
</memory_grant>

This can be really valuable information to get, similar to what sp_PressureDetector will give you.

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Related Posts