sql server memory check.

Question

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.

Below query shows me "available memory is high" which i think is good.

Buffer cache ratio is 90+

memory grants pending is 0

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

Answer 1

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.

Answer 2

here are my top waits from dm_os_wait_stats.

Answer 3

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.

Answer 4

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.