Why is my Memory: pages/sec counter exceeding 20 for my SQL Server?

Question

I have a server running SQL Server 2000, SP3. I recently used Performance Monitor to track the Memory: pages/sec counter to see if I have a paging problem. What surprised me what that the pages/sec were running over 200, which is way above the maximum value of 20 suggested on your website. What is going on?

Answer

On a dedicated SQL Server box that is properly tuned and has sufficient hardware to carry its load, the pages/sec should be less than 20 on average, although you will often see spikes higher than this. These occasional spikes present no performance problems.

For those who may not be familiar with the pages/sec counter, what this counter measures is the number of physical pages read to (or written to) disk in order to resolve hard page faults. A hard page fault can occur when data an application needs is not in RAM, but on the hard disk instead, and must be moved from disk to RAM so that it can be used. In addition, if there is no more room in RAM for an application's data, it must often be written to disk. As you might imagine, both of these processes are time consuming (relatively speaking), and because of this, performance is always better if there are fewer hard page faults. For optimum performance, all the data an application needs should always be in RAM. This, of course, is not always possible, and that is why hard page faults occur. A certain number of them are expected and normal.

So what are some examples of what can cause high page faults? Here is a list of some of the most common ones, although it is not comprehensive:

Normal Causes of High Paging Rates

• When a computer is first booted.
• When an application is first started, or exited.
• When data is loaded into an application, or saved from an application to disk.
• When a file is being written to a disk, or copied off of a disk.
• When backups are being made or restored.

Fixable Causes of High Paging Rates

• Defective I/O hardware.
• Defective or buggy I/O drivers.
• When the operating systemdoesn't have enough RAM for all the needs of the currently running applications on the system.

So, if your server is running at a high rate of pages/sec, most likely, one of the above circumstances is causing it. As you can see, some of these events can't be avoided, but others potentially can.

The first step to identifying the potential cause of the high paging rates on your server is to use Task Manager. Start Task Manager and go to the "Processes" tab. There, you will see a large number of columns for each of the processes running on your server. Check to see if there is one called "Page Faults." If not, then go to the "View" drop-down menu and select "Select Columns" and check the box next to "Page Faults."

The page fault figures you see in Task Manager are the number of page faults that have occurred for each of the various processes that are currently running. This is a cumulative figure, so it is the total number of page faults each process has incurred since it was last started.

If your server has been running a long time, some of these processes will have hundreds of thousands, if not millions of page faults. These figures may or may not be an indication of what process is causing an excessive paging problem, but they will provide a clue. Once you have "Page Faults" displayed on the Process Tab, now click on the word "Page Faults," and this will cause all of the processes to be ordered, from the most to the least.

Now, your goal is to look at each process, and the number of page faults each one has, to help you determine which process is causing the most page faults. Hopefully, one or more of these processes will have a disproportionate number of page faults, which may indicate that they are the cause of your high page fault problem. You may find that a process with a lot of page faults is normal, even though it is high. I can't tell you what to expect when you look at this data, as each server is different. But your goal is to try to identify any potential problems.

You may be surprised to see that the sqlservr.exe process (the SQL Server engine) has a high number of page faults. This may be normal. If your server has been up a long time, it will accumulate a large number of page faults over time. Assuming that SQL Server's memory is correctly configured, it will rarely exceed over 20 pages/sec during normal operations. But, if you prevent SQL Server from getting enough memory (by manually assigning memory instead of letting SQL Server dynamically allocate it, for example), it is possible to force SQL Server to page excessively, greatly hurting SQL Server's performance. So you will want to check to see if SQL Server's memory setting is appropriate.

If SQL Server is not the problem (and it probably won't be), then the next thing to closely look at are other programs or services running on the same server. Ideally, SQL Server should be a dedicated server. But if you add other programs to the same server as SQL Server, and you don't have enough RAM for both SQL Server and the other programs, then the other programs may be causing the paging problem. If this is the case, either get more RAM or remove the offending software.

If the above doesn't resolve your paging issue, then I would take a look at your I/O hardware and drivers, and see if they might be causing the problem. Although this is not common, I have seen this problem before.