We have a 2 node 64-bit Windows 2003 Active/Active Cluster with 2 instances
on each node. Each node has 2 virtual servers/named instances; 1 SQL 2000
(32-bit) and 1 SQL 2005 (64-bit). Thus the setup is as follows:

SQLNode1:
- SrvrA\sql01
- SrvrB\sql51

SQLNode2:
- SrvrC\sql02
- SrvrD\sql52

Each node has 4 dual cores, 8GB RAM. The swap file is on a separate disk and
is set to 8 GB. Each instance is configured/allocated 1845 MB "max server
memory" with no minimum. The reason why only 1845 MB is allocated to each
instance is because should all instances move/failover to the same node this
would avoid that the SQL instances would compete for the memory. As you can
see the total memory allocation for all instances is 7380 MB which leaves 812
MB for the operating system (in a failover state).

Basically what is happening is that the sqlsrvr.exe (2005) is using
approximately 150MB memory and paging the rest.

The SQL 2000's are working fine, no performance issues there. Both SQL
2005's instances are using approx 150MB RAM and paging the rest.
Unfortunately no errors related to this were logged in the SQL Server error
log or the Windows Event Log.

We tried various options, including setting the "min server memory" and also
confirmed that the service account had the policy "lock pages in memory" set.

I found the following articles which explain similar issues with similar
environments:

1. Discussion which appears to be related to 64-bit systems
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100315

2. How to reduce paging of buffer pool memory in the 64-bit version of SQL
Server 2005
http://support.microsoft.com/kb/918483

3. How to determine the appropriate page file size for 64-bit versions of
Windows Server 2003
http://support.microsoft.com/kb/889654/

4. SQL and the Working Set
http://blogs.technet.com/askperf/arc...rking-set.aspx

5. A significant part of sql server process memory has been paged
http://groups.google.co.uk/group/mic...171fc3bcbd4e43

6. Does SQL Server always respond to memory pressure?
http://blogs.msdn.com/slavao/archive...-pressure.aspx

7. The sizes of the working sets of all the processes in a console session
may be trimmed when you use Terminal Services to log on to or log off from a
computer that is running Windows Server 2003
http://support.microsoft.com/kb/905865/en-us

8. Windows Server 2003, Enterprise Edition: System requirements
http://technet2.microsoft.com/Window....mspx?mfr=true

Most of the environments mentioned in the above links had (much more) RAM
allocated on one SQL Server instance than the amount allocated in our setup.
In a number of documents I came across the recommendation that besides the
amount required for SQL Server, 2 GB are reserved for the operating system,
antivirus is not installed on the database servers, and an amount of RAM be
allocated for other services and applications.

My gut feeling is that we should do a number of things. The first is to
schedule a server reboot at least every month (to clear any possible memory
leaks...?). The second is that more RAM is added to the nodes. We could
also modify the size of the pagefile to 1.5 times the allocated RAM. What is
true is that the pagefile utilisation is very high and we might encounter
bottlenecks (due to disk IO) should the activity increase. I would
appreciate any comments from your end.

Thanks and regards,

Reuben

Something is obviously wrong with what you described, and I don't think a
scheduled regular reboot will address the root cause.

First fo all, how did you get 150MB (in the following)?

And, I'd focus on what is causing SQL Server page. Most likely, you are
running something else on the server that is eating up a lot of memory. I ran
into a situation where a component of a monitoring program used up a few GBs
of memory, and because SQL Server was not busy, SQL Server was paged out.

Linchi

"Reuben Sultana" wrote:

Something is obviously wrong with what you described, and I don't think a
scheduled regular reboot will address the root cause.

First fo all, how did you get 150MB (in the following)?

And, I'd focus on what is causing SQL Server page. Most likely, you are
running something else on the server that is eating up a lot of memory. I ran
into a situation where a component of a monitoring program used up a few GBs
of memory, and because SQL Server was not busy, SQL Server was paged out.

Linchi

"Reuben Sultana" wrote:

Something is obviously wrong with what you described, and I don't think a
scheduled regular reboot will address the root cause.

First fo all, how did you get 150MB (in the following)?

And, I'd focus on what is causing SQL Server page. Most likely, you are
running something else on the server that is eating up a lot of memory. I ran
into a situation where a component of a monitoring program used up a few GBs
of memory, and because SQL Server was not busy, SQL Server was paged out.

Linchi

"Reuben Sultana" wrote:

Something is obviously wrong with what you described, and I don't think a
scheduled regular reboot will address the root cause.

First fo all, how did you get 150MB (in the following)?

And, I'd focus on what is causing SQL Server page. Most likely, you are
running something else on the server that is eating up a lot of memory. I ran
into a situation where a component of a monitoring program used up a few GBs
of memory, and because SQL Server was not busy, SQL Server was paged out.

Linchi

"Reuben Sultana" wrote:

Something is obviously wrong with what you described, and I don't think a
scheduled regular reboot will address the root cause.

First fo all, how did you get 150MB (in the following)?

And, I'd focus on what is causing SQL Server page. Most likely, you are
running something else on the server that is eating up a lot of memory. I ran
into a situation where a component of a monitoring program used up a few GBs
of memory, and because SQL Server was not busy, SQL Server was paged out.

Linchi

"Reuben Sultana" wrote:

Something is obviously wrong with what you described, and I don't think a
scheduled regular reboot will address the root cause.

First fo all, how did you get 150MB (in the following)?

And, I'd focus on what is causing SQL Server page. Most likely, you are
running something else on the server that is eating up a lot of memory. I ran
into a situation where a component of a monitoring program used up a few GBs
of memory, and because SQL Server was not busy, SQL Server was paged out.

Linchi

"Reuben Sultana" wrote:

Something is obviously wrong with what you described, and I don't think a
scheduled regular reboot will address the root cause.

First fo all, how did you get 150MB (in the following)?

And, I'd focus on what is causing SQL Server page. Most likely, you are
running something else on the server that is eating up a lot of memory. I ran
into a situation where a component of a monitoring program used up a few GBs
of memory, and because SQL Server was not busy, SQL Server was paged out.

Linchi

"Reuben Sultana" wrote:

Something is obviously wrong with what you described, and I don't think a
scheduled regular reboot will address the root cause.

First fo all, how did you get 150MB (in the following)?

And, I'd focus on what is causing SQL Server page. Most likely, you are
running something else on the server that is eating up a lot of memory. I ran
into a situation where a component of a monitoring program used up a few GBs
of memory, and because SQL Server was not busy, SQL Server was paged out.

Linchi

"Reuben Sultana" wrote:

Something is obviously wrong with what you described, and I don't think a
scheduled regular reboot will address the root cause.

First fo all, how did you get 150MB (in the following)?

And, I'd focus on what is causing SQL Server page. Most likely, you are
running something else on the server that is eating up a lot of memory. I ran
into a situation where a component of a monitoring program used up a few GBs
of memory, and because SQL Server was not busy, SQL Server was paged out.

Linchi

"Reuben Sultana" wrote: