search

amachanic / sp_whoisactive

sp_whoisactive
GNU General Public License v3.0
1.14k stars 281 forks source link

Added Memory DMV to handle memory requests, and grants #47

Closed mfuller333 closed 3 years ago

mfuller333 commented 3 years ago

I added requested and granted memory from sys.dm_exec_query_memory_grants DMV replacing what was there from sys.sysprocesses. This fundamentally changes how we are counting memory and the DMV we are relying on for our memory counters. Instead of counting pages and calculating our extents to come up with our kb usage; I am just exposing the pre-calculated requested, and granted memory counters measured in kb. This lets us to see exactly who is requesting how much memory, further allowing us to focus on the problem queries causing the Resource Semaphore condition to occur.

mfuller333 commented 3 years ago

Had to check this out and back in, still getting used to using Git.

mfuller333 commented 3 years ago

I will resubmit this once I add all the additional output columns (requested, required, max_used, dop, cost) to the XML output. I also changed it to work with the used, and granted memory counters instead of requested and granted. This way its not single purpose and at a glance allows you to see the bloating clearly and correct it. Since the disk gets three counters its only fair to let memory have two.