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

  • Hi,

    It may be trivial question but I am really confused at this moment.

    At one of my servers I noticed that cached plans are deleted pretty much every 4 hours. Data files on that server consume a little bit more than 700 GB and SQL's memory is limited to 25 GB. I found one process that runs every few hours for which memory grant is about 3 GB. I know that there is huge room for improvement regarding query optimization and indexing...

    But... I do not see any waits related to the memory. How it is possible that all plans are cleared out every 4 hours (which suggest heavy memory pressure) and at the same time I cannot see confirmation of that fact in wait statistics?

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

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

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


    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)
    Intro to Tally Tables and Functions

  • 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


  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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