Paging and Available Memory for Operating sysem, what you need to know?
Performance tuning exercise involves using SYSMON for the server based tasks, also I have seen users referring that MEMORY is at peak level by referring to Task Manager on the server. Not always depend upon the task manager, for optimum information better to rely upon SYSMON counters. In this scenario capturing the Available Byes & Memory:Pages/Sec will indicate that how many bytes of memory are currently available for use by processes and the number of pages that either were retrieved from disk due to hard page faults or written to disk to free space in the working set due to page faults.
When looking at Pages/sec, start investigating if you’re getting over 100 on a system with a slow disk. Please note that the stated values of over 20 pages, which appear in many other sources of documentation, are out of date. On a system with a fast disk subsystem, even 500 pages per second may not create an issue. The number of Pages Output/sec is only an issue when disk load becomes an issue. This value is completely meaningless unless it is evaluated in concert with the disk throughput capacity.
Always break up this count into pages output and pages input if the counter is above 100 per second. If you suspect paging is the issue, the best threshold value to use for diagnosing the problem is disk read time on the logical disk that holds the page file. To rule out paging as an issue, look for disk idle times of greater than 85 percent and transfer times of less than 15 ms.
Obviously the low values of available bytes counter indicates that there is an overall shortage of memory on the computer or that an application is not releasing memory. Similary the higher rate of Pages/sec counter indicates that excessive paging is happening at the same time. For further assessment on the paging you should monitor Memory: Page Faults/Sec counter too. So on the Enterprise wide environment even if the computer has plenty of available memory, the windows process. Windows Virtual Memory Manager (VMM) takes pages from SQL Server and other processes as it trims the working-set sizes of those processes. Majority of the cases I have seen that during the VMM activity will deliver page faults. For further assurance again use SYSMON to monitor the Process: Page Faults/sec counter to assess whether SQL Server or another any process on the server is causing this paging issues.
Coming to the basics of Paging, it is generally defined as the process of moving pages out of physical memory to virtual memory (this will have slow performance when disks are not optimized or striped enough). In this case by default when SQL Server releases memory back to the Operating System during on-demand or generic process, it is reducing the amount of memory is has committed and you would see drop in the above counter values.
On the other note When Pages/sec is greater than 0 or Page Reads/sec is greater than 5, Windows is going to disk to resolve memory references (via a hard page fault), which comes at the price of disk I/O and CPU resources.