Understanding and Troubleshooting ‘Win2003 PF Usage’ on the server running SQL Server 2000

  • Article

 

I’ve been recently asked to explain the reason of high PF Usage on Windows 2003 server running SQL Server 2000 Enterprise Edition.

Problem Statement:
- From: OS Task Manager > Performance Tab: High value depicted in “PF Usage” graph.
- On stopping SQL Server Service, the graph drops down.

So does that mean that SQL Server is responsible for High Page File (PF) usage? lets see…..

Before we solve this puzzle, lets first determine the OS and SQL Server memory configuration:

OS Memory Configuration:
- Physical Memory (RAM): 4 GB’s
- Paging file size “c:\pagefile.sys” : 4092 MB’s

SQL Server Configuration:
- SQL Server 2000 Enterprise Edition: 32-bit
- AWE : Enabled
- MIN_SERVER_MEMORY:
1 GB’s - MAX_SERVER_MEMORY: 4 GB’s

Here’s what PF usage graph says…

image

And the perfmon counter…

clip_image002

- Memory Available Mbytes is very low around 48 Mb’s

clip_image004

- Total memory consumption by SQL is high around 3036 Mb’s

clip_image006

So here’s the answer - finally

What does PF Usage graph reflect?
“Total” PF Usage indicates the current amount of virtual memory that is being used by programs and system processes. Actually, this value is summation of Physical Memory or RAM + Page file in use. So if either of them gets utilized (i.e. Page File or Physical Memory), it will get reflected in the PF usage graph.
This is not reflect the actual page file usage, instead what it shows is the potential page file usage (i.e. if everything in RAM that could be sent to the page-file actually was, this would be the space required)

Then what is actual Page Files usage?
Actual page-file usage, as shown in Perfmon counters is 5% (i.e. 204 MB’s). This is very nominal.

So here, does this indicate high utilization of Physical Memory by SQL Server?
Yes!

Why is SQL consuming high memory, when there is no work load?
- Here, the MAX_SERVER_MEMORY = 4 GB’s (Total RAM) and AWE = enabled
- So all physical memory is grabbed by SQL when SQL services is started.

Here’s what SQL BOL Server Memory Optionshas to says: “Instances of SQL Server 2000 running in Address Windowing Extensions (AWE) memory mode do allocate all the FULL amount of memory specified in MAX_SERVER_MEMORY on server start-up.”

Hence it is always recommended to limit SQL Server Physical Memory usage by limiting MAX_SERVER_MEMORY.

Hope that answers!

Technical References:
<> Managing AWE Memory: https://msdn.microsoft.com/en-us/library/aa213764(SQL.80).aspx
<> Server Memory Options: https://msdn.microsoft.com/en-us/library/aa196734(SQL.80).aspx

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.