why I cannot see memory related waits while server is under pressure?

Question

Post reply

why I cannot see memory related waits while server is under pressure?

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80412 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 2

See the following article and the link to the other article list in it.

https://blog.sqlauthority.com/2020/06/18/sql-server-queries-waiting-for-memory-grant-performance-tuning/

That will help you provide the proof you need to increase your RAM.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16279 More actions · Answer 3

on some of my busy and misbehaved servers I have the following query running every 10 seconds with output to a table.

with this table its easy to identify queries that may require rewriting as well queries that may request incorrect memory grant due to bad statistics

insert into MemoryGrants
select getdate() as report_time
     , qmg.session_id
     , qmg.request_id
     , qmg.scheduler_id
     , qmg.dop
     , qmg.request_time
     , qmg.grant_time
     , qmg.requested_memory_kb
     , qmg.granted_memory_kb
     , qmg.required_memory_kb
     , qmg.used_memory_kb
     , qmg.max_used_memory_kb
     , qmg.query_cost
     , qmg.timeout_sec
     , qmg.resource_semaphore_id
     , qmg.queue_id
     , qmg.wait_order
     , qmg.is_next_candidate
     , qmg.wait_time_ms
     , qmg.plan_handle
     , qmg.sql_handle
     , qmg.group_id
     , qmg.pool_id
     , qmg.is_small
     , qmg.ideal_memory_kb
     , t.text
from sys.dm_exec_query_memory_grants qmg
cross apply sys.dm_exec_sql_text(qmg.sql_handle) as t
where qmg.requested_memory_kb > 100000 -- queries requesting more than 100MB of memory
;