High page file usage is affecting response times.
Impact : Medium
Slow SQL responses will degrade the user experience, resulting in poor efficiency of your organization’s operations. Pressure on available resources (CPU and/or memory) is forcing the system to swap pages from memory to disk, which slows all processes down.
Expected behavior :
There is no standard metric for page file usage. Based on our experience, page file usage should be lower than 1 gigabyte for optimal server functionality.The longer a page can stay in the buffer pool and be read from memory the better the system performance.
In SQL Server, data is stored in 8 KB pages. When data is required for input/output the page is first read from the disk and stored into memory. The area allocated is called the Buffer Pool. Once a page is loaded into the pool, all subsequent access is very fast and efficient, whereas disk I/O is relatively slow. As much memory as possible should be allocated to the buffer pool, without starving the operating system.
Allocate the optimal proportion of physical RAM to BP, but leave sufficient RAM for OS operations. Set a value in the Maximum server memory option of the ‘Server memory options’ page, that leaves enough memory for the OS itself. We recommend around 4 GB less than the total amount of RAM installed
Continually high usage associated with low PLE may indicate the need for a CPU upgrade or the addition of multiple processors. See our explanation of CPU usage here
Alternatively, a high CPU usage rate may indicate a poorly tuned application or SQL code. Optimizing the application can lower CPU utilization.
Review the amount of total RAM allocated to SQL (while still keeping sufficient RAM for operating system needs). Compare the ratio of SQL memory to database size – based on our experience, a ratio of total RAM greater than 20% of the largest database is optimal.
If possible, allocate more memory to SQL, or alternately add physical RAM to the server and increase SQL memory allocation.
Check what else is running that might be slowing disk access speed. The buffer pool swaps pages out to disk when memory space is needed. This process is highly dependent on the efficiency of data read/write actions to the hard drive, and on level of demand. Allocation of resources between the operating system and SQL must take into account what functions the server performs (email, anti-virus, backup etc).
To speed up disk I/O and lower latency, locate the page file on the fastest available drive (or upgrade to more efficient hardware). As well, reduce competition for this resource from other operations. Make sure that SQL has priority. Where possible, try reducing system workload or adding more memory.
AimBetter resource monitor gives comprehensive statistics on total disk space, free space and page file activity.
Paging File Usage indicates the size of the paging file on disk used by the operating system. Paging is a process that occurs on systems with insufficient CPU power or memory resources. To provide enough memory for the running processes, it temporarily stores some of the memory pages into the paging file on disk. Next time a process needs this page, it will cannot be read from RAM, but must be read from the page file on disk.
With regard to SQL Server processes, when there is no memory pressure on the system, pages are written into memory and are only flushed after the process is completed, freeing up the page for the next call.
A hard page fault is when the page is not in memory, but has to be loaded from a paging file on disk. This affects performance, as writing and reading a page from disk is much slower than writing and reading from memory. See more about Page Life Expectancy here.