SQL Server problem: Excessive paging and memory bottlenecks

Paging and Memory bottlenecks are quite common when a performance problem strikes on your SQL Server. As we discussed here previously and relevant blog posts below talks about them:

Paging and Available Memory for Operating sysem, what you need to know? 

SQL Server Memory - what's your method on Configuration and Troubleshooting Issues

Using PERFMON to get memory counters, what you need to check?

By default, SQL Server changes its memory requirements dynamically, on the basis of available system resources. If SQL Server needs more memory, it queries the operating system to determine whether free physical memory is available and uses the available memory. If SQL Server does not need the memory currently allocated to it, it releases the memory to the operating system. These are few common memory issues that occurs and need to troubleshoot them, page file is more important as it links with RAM to prevent excessive paging, the aim should not be to try to prevent paging activity completely.  In this case page faults will add more fuel to the problem, soft and hard page faults occurs on the server. A page fault occurs when a process requests a page in memory and the system cannot find the page at the requested location.  If the requested page is actually elsewhere in memory, then the fault is a soft page fault.  If the page has to be retrieved from the disk, then a hard fault occurs.  Most systems can handle soft page faults with no issues. 

However, if there are lots of hard page faults you may experience delays.  The additional disk I/O resulting from constantly paging to disk can interfere with applications that are trying to access data stored on the same disk as the page file.  Although high page faults on a system is a fairly straightforward issue, it requires some extensive data gathering and analysis in SYSMON, the important counters are Memory: pages/sec, Page Reads /sec & Available Bytes and let us see each of them :

If the Pages / sec multiplied by 4,000 (the 4k page size) is greater than 70% of the total number of Logical Disk Bytes / sec to the disk(s) where the page file is located on a consistent basis then you should investigate.  Then for Page Reads/sec which should be for for sustained values, so if the value is consistently greater than 50% of the total number of Logical Disk operations to the disk where the page file resides, then there is an inordinate amount of paging taking place to resolve hard faults. Then it comes to Available Bytes and when you see if it falls below 5 % of RAM that is installed then you should consider the type of service & application running at that time.

Such issues with paging talks more to the disk where the changes are written to the disk, that there will be page write operations occurring.  As the value of Available Bytes decreases, the number of hard Page Faults will normally increase.  The total number of Pages /sec that can be sustained by the system is a function of the disk bandwidth.  This does however mean that there is no simple number to determine whether or not the disks are saturated.  Instead you have to identify how much of the overall disk traffic is being caused by paging activity.

So whenever you see any memory issues then keep an eye on above SYSMON counters and monitoring should get you better information.