sql server memory check.

Heisenberg 261 Reputation points
2021-09-16T19:55:43.947+00:00

hi Folks,
im trying to see if any of my sql server is under memory pressure. Im getting quite a conflicting values from different parameters.
here is PLE status from idera for last 5 days. As per the image below . PLE is well below the baseline. Memory allocated to sql server is 230G.

132816-image.png

Below query shows me "available memory is high" which i think is good.
132817-image.png

Buffer cache ratio is 90+
132861-image.png

memory grants pending is 0
132724-image.png

Following 2 values are 0 as well
![132871-image.png][5]

3: /api/attachments/132861-image.png?platform=QnA [5]: /api/attachments/132871-image.png?platform=QnA

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,129 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Heisenberg 261 Reputation points
    2021-09-16T19:59:52.797+00:00

    so im bit confused... if my servers are under memory pressure. Most of the time i see SQL Server transaction replication latency issue (which i think is more related to IO). I see lot of pagiolatch_sx and ex waits for the active sessions.

    0 comments No comments

  2. Heisenberg 261 Reputation points
    2021-09-16T20:09:59.37+00:00

    here are my top waits from dm_os_wait_stats.

    132872-image.png


  3. Seeya Xi-MSFT 16,471 Reputation points
    2021-09-17T07:46:59.787+00:00

    Hi @SQLServerBro,

    According to your description, your PAGEIOLATCH_SH and PAGEIOLATCH_EX wait types are many, which can reflect performance.

    PAGEIOLATCH_EX
    Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.
    How To Troubleshoot :

    1. PAGEIOLATCH_EX and PAGEIOLATCH_SH are often reported as high – if so, disk io subsystem will be your first target
    2. The disk spin rate should not be the focus – but rather the workload. Therefore , for testing – run the workload and monitor disks
    3. It’s not just slow IO subsystems relative to workload but also overworked IO subsystems that can be the cause.
    4. General database tuning can help – but a single transaction is defined by : amount\speed of disks , memory amount availability , processor speed.
    5. Index management
    6. Differentiate between disk issues and memory issues – after all ,if memory is experiencing pressure – than dealing with incoming data will be slower
    7. Logical drive profiles – separating random file access with sequential files

    PAGEIOLATCH_SH
    This wait type accumulates while SQL Server is waiting for a page to be retrieved from disk and loaded into memory. The page collected will be used for a shared purpose (read operation). If this value is high it is likely disk or memory available are not keeping up with the workload
    Possible solutions:

    1. Add additional memory
    2. Investigate disk latency
    3. Increase the maximum memory allocation
    4. Indexing, look for high disk I/O queries
    5. Enable data page compression if CPU usage is not suffering

    Note: It’s normal to see some PAGEIOLATCH_* waits, but if you’re frequently seeing them with wait times consistently above 10 milliseconds and you’re experiencing some type of latency, it suggests that the I/O subsystem is under pressure. Conversely, if you see many brief waits, you may be affected by increased I/O activity.

    For more information, please refer to: PAGEIOLATCH_SH and PAGEIOLATCH_EX.

    Best regards,
    Seeya


    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  4. Heisenberg 261 Reputation points
    2021-09-17T15:50:12.73+00:00

    Thank you both for your response. Ive read lot of articles that says "page life expectancy" should be high. , in my system ive seen in idera tool it has been consistently below baseline (line in the red). At times ive seen its goes below 300, which imo is too low for a server that has 235G allocated with db size of around 1TB.

    The issue we are having is with the 2 subscribers, and lot of times i see sessions will have a wait of "pageiolatch_sh" and "pageiolatch_ex". mostly the _sh. There are some heavy reports run on this db servers. doesnt it also mean if you see high number of pageiolatch_sh waits it is trying to ready page from disk ,rather than it is being in the buffer. so if we had more memory allocated it doesnt have to go to disk subsystem. Indicating memory pressure.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.