2

Today I was just taking a look on my performance counters and found this...

Average Batch Request per second was 0,6
Average Page Reads per second was 12,5
Average Page Writes per second was ~15000
Average Checkpoint Pages per second was ~15000
Average Page Splits per second was 325

Buffer Cache hit ratio at 99%

Page Life Expectancy at 37256

After searching a bit I found that more than 90 Page Reads and Writes per second could indicate memory pressure but how Page Writes could be an indicator of memory pressure if for example I'm loading data in tables constantly?

Thanks!

Improve this question
6
  • it is definetely busy but Page Life Expectancy at 37K that can't be memory pressure for sure. what have you been reading exactly?
    – Marcello Miorelli
    Commented Sep 21, 2016 at 15:46
  • A pair of developers were testing some data loads on SSIS attacking of the DWH Databases
    – J1mmy
    Commented Sep 21, 2016 at 15:52
  • Ignore the BCHR completely and about PLE do you have NUMA nodes, if yes look for PLE of each NUMA node. If you take my opinion I guess these stats are normal
    – Shanky
    Commented Sep 21, 2016 at 15:55
  • I can't see more than 1 Instance_name on Buffer Node Page Life Expectancy so I assume I have only 1 Numa (000). Thanks!
    – J1mmy
    Commented Sep 21, 2016 at 16:06
  • Average Batch Request per second 6, and page splits 325? Not looking realistic.
    – Ashwini Mohan
    Commented Sep 21, 2016 at 18:05

2 Answers 2

Reset to default
1

Page write is just an indicator of logical writes as well as physical writes. Logical write is an operation that happens when the page is being written in the buffer(dirty buffer) and physical write is an operation that happens when the same dirty buffer is converted to clean buffer and is being written on the physical disk.

Definitely, this is not a single metric to judge memory pressure. You need to combine different metrics for coming to a conclusion.

But in your case, it doesn't seem to be a problem as your page life expectancy is quite high which indicates a page will remain in the buffer for around 37k secs. So you don't need to worry about memory pressure I think.

Improve this answer
0

It's good, high PLE value shows there is no memory pressure.

SQL Server process all it's transaction through memory (RAM). When we make any request to SQL Server using query (INSERT/UPDATE/DELETE/SELECT), it first pull data pages (size of a data page is 8KB and group of 8 data pages make and extent) from disk to memory (buffer) to read/write it. Available page in buffer also serve other requests and save disk IO.

So, as you said 90 pages read and write / sec.

It's only 90 * 8 = 720 KB (Pages in use by active session) and rest may be used as buffer by SQL Server.

When

  1. PLE < (buffer size/4)*300
  2. you start to face paging
  3. BCHR < 95 frequently

It means your server is facing memory pressure.

Thanks

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.