Memory performance counters

When observing and analyzing Windows Memory problems, we often encounter the concept of page fault, which translates Chinese characters into "Page errors ", when a process needs to read/write its own virtual address space (VAS, virtual address space, virtual Memory Manager) to the physical memory, the vmm needs to load the page from the paging file on the hard disk to the physical memory. This loading process is called
Page Fault. Note: loading data from the hard disk to the memory is relatively slow.

Page fault is divided into hard page fault and soft page fault. Hard page fault indicates that the requested file is no longer in the physical memory and needs to be read from the paging file on the hard disk. Soft page fault indicates that the page still exists in the standby table with the physical memory. In perfmon.exePage faults/secThe counter is used to observe the page per second.
The number of fault times, which is the sum of hard + soft page fault.

How can we observe hard page fault? Page reads/sec and page input/sec. Both counters are used to observe the hard page read from the hard disk. Page reads/sec indicates that the page fault reads from the hard disk.TimesAnd page
Input/sec indicates the read pageNumber. Therefore, (page input/sec)/(page read/sec) = the number of pages read by each page fault.

What about pages/sec? = Page input/sec + page output/sec is the sum of read and write pages that access the hard disk due to page shortage.

 

Memory pointer of SQL

Large memory consumption in SQL Server requires a buffer pool, so monitoring the buffer pool can detect SQL Memory problems. Perfmon.exe provides counters for this purpose:

(1) MSSQL $ <Instance>: Memory Manager \TotalServer Memory (Kb): The current buffer pool size;

(2) MSSQL $ <Instance>: Memory Manager \TargetServer Memory (Kb): the ideal size of the buffer pool. On machines with no memory pressure, the total and target values should be relatively close. If the total value is much smaller than the target value, it indicates that the buffer pool size cannot be increased due to memory pressure and further investigation is required;

(3) MSSQL $ <Instance>: Buffer Manager \ page life expectancy: the expected time (in seconds) for the SQL Server to load the page to the buffer pool ). When the memory is tight, data pages need to be cleared frequently, so Microsoft recommends 300 seconds. However, when the memory is sufficient, it can easily reach thousands of seconds.

I looked at the three counters in my notebook and found that total (= 60.67 MB) is much smaller than target (= 1443.8 MB ), does this mean that my machine has a memory problem and I need to add memory? Actually it is not. Let's look at the third counter, which is about 24,929 seconds. This shows that the memory pressure is not high. My notebook has 6 GB of memory and is installed with SQL Server express with Visual Studio 2010. Besides the occasional use of SQL
Basically, there is no traffic for SQL. At this time, the total is much smaller than the target, which indicates great potential and exploration!

 

Reference resources

1. Performance Monitor counters
2. Perfmon: high number of pages/sec not necessarily low memory
3. Monitoring (database engine)
4. Server Memory options
5. Effects of Min and Max Server Memory