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:
- Underused sp_WhoIsActive Capabilities For Troubleshooting SQL Server: @get_avg_time
- Underused sp_WhoIsActive capabilities: @delta_interval
- Troubleshooting Parameter Sniffing In SQL Server With sp_WhoIsActive
- Reasons Your Query Is Slow Right Now: Blocking, Blocking, Blocking
- Reasons Your Query Is Slow Right Now: A Bad Query Plan Appears
- Monitoring SQL Server Queries With sp_WhoIsActive
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.
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:
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!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on 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.