Memory issues with 64-bit SQL Server 2005 on 64-bit Win 2003 Clust

#81

Reuben,
I assume that you are having SQL Server 2005 (64-bit) STANDARD EDITION.
If thats correct then you will not see the pre allocated-AWE allocation.
Until unless the SQL Server requires it.
This is one of the microsofts undocumented issue going around, I have
reguested microsoft to document this last week.
and soon you will see a KB on it.

Hence you will have to depend on the min. and max memeory allocation.
Please set your min memory allocation to to same as max memory allocation
and you should be good.
Keep position on the update after you have SET your min. memory threshold to
1800MB

Cheers
Raman Gosala

"Reuben Sultana" wrote:

Quote:

Hi Raman,

I checked the installed .NET drivers and (luckily) confirmed they are
64-bit. Reinstalling the whole thing would have been a problem since the
setup is a cluster and is live...

1) I did set the "lock pages in memory" prior to installing any of the SQL
Server instances.
2) The Network adapters are "Broadcom NetXtreme Gigabit Ethernet" - here's a
stupid question: where do I find the driver version...? :-(
3) With regards to the BIOS firmware this is updated by the hardware
supplier when an update is available.

I also checked the individual patch levels for the .NET drivers, SQL Server
2005 and the OS (via Windows Update). The .NET drivers are version
2.0.50727.832. A quick check at
http://blogs.msdn.com/dougste/archiv...e-clr-2-0.aspx
shows that ew are a few patches behind, however we can catch up with SP1.
The SQL Server is fully patched, however Windows Update reported that the OS
requires 25 High Proirity KB's, etc. one of which is the .NET SP1. Will read
some about each and apply if I find nothing worrying.

I am also doing some research into the information returned by the DMV's.

If the performance remains the same we shall have no other alternative
besides opening a call with Microsoft.

Thanks,

Reuben.

"Raman Gosala" wrote:

Reuben,

I had this same issue a long time ago and found that the .NET drivers
installed on the box was 32 bit
So I was recommended to reinstall the OS2003 and then install the .NET 64
bit and then install you SQL Server you should be good, in this case it
worked fine

During the above mentioned solution, I had taken care of
1) Lock pages in memory -- I guess you might have taken care of it as well.
2) Update your Network card drivers especially if you are using the Broadcom
5708S drivers
3) Update your BIOS firmware.
http://support.microsoft.com/kb/918483
and the issue was resolved was happy with microsoft solution.

However I have been assigned a new project which has about 40 new servers
all are from IBM Blades with quad core and 32 GB on some of the machines and
64 GB on some of the machines
as soon as I installed the x64 with SP2 and the patest cumulative 9 pack I
still see the same error.
I have a case opened with Microsoft now to see what they have to say as in
this case I have already applied all the above mentioend fixes but still see
the same issue the exact error is read between lines
------------------------
A significant part of sql server process memory has been paged out. This may
result in a performance degradation. Duration: 0 seconds. Working set (KB):
38500, committed (KB): 105592, memory utilization: 36%.
--------------------------
I shall update if I here more from microsoft.

"Reuben Sultana" wrote:

First of all, a big thank you to all for the tips. I ran Performance Monitor
and recorded the following figures at one point in time:

MSSQL$INSTNAME:Memory Manager | Total Server Memory = 2097152 (2048 MB)
MSSQL$INSTNAME:Memory Manager | Target Server Memory = 2097152 (2048 MB)
Process | Working Set | sqlservr = 2018269594 (1925 MB)
Process | Working Set | sqlservr#1 = 153129247 (146 MB)
Memory | Available MBytes = 1564
Memory | Pages/sec = 15.84 (AVG); 1567 (MAX)
MSSQL$INSTNAME:Buffer Manager | Buffer cache hit ratio = 99.864
MSSQL$INSTNAME:Buffer Manager | Total pages = 262144

There seems to be an anomaly with the memory totals. The above were taken
on SQLNode1 which had only two active instances. Each instance is configured
to use between 0 and 1845MB out of the 8GB available. If we sum the "Working
Set (sqlservr)", "Working Set (sqlservr#1)" and "Available MBytes"
[1925+146+1564] we get 3635 MB. This leaves us with 4557 MB unaccounted for.
Could it be a memory leak?
Even if we had to take the maximum server memory used by each SQL Server
instance [1845*2] the "Available MBytes" does not make sense [8192 - (1845*2)
- 1564 = 2938]. This does not even leave enough memory for a failover. What
could be eating away at the available memory?

Reuben.

"Linchi Shea" wrote:

Task Manager cannot
count over 2GB so your numbers are not accurate.

This has become beyond amazing. I mean for how many centuries now this Task
Manager nusance has been around, and yet they somehow could never find time
to fix it. I just checked one of my Win2K3 x64 boxes that runs SQL2005 x64,
and even on a pure x64 box, this Task Manager nusance is still present. So
while Task Manager shows sqlservr to use about 214MB, and SQL Memroy Manager
counter Total Server Mmeory shows 18GB.

Linchi

"Geoff N. Hiten" wrote:

Do not use the Task Manager to determine menory usage. Task Manager cannot
count over 2GB so your numbers are not accurate. The Paging File usage is
due to how the large memory allocation APIs work. They appear to Task
Manager as paging file use.

Swap (paging) files for SQL do not need to be more than about 2-4 GB
regardless of physical memory on the box. If you use Performance Monitor to
track actual paging activity, you will see very little on a properly tuned
SQL Server. Note that backup actuvity will show up as paging due to the
APIs used.

--
Geoff N. Hiten
Principal SQL Infrastructure Consultant
Microsoft SQL Server MVP

"Reuben Sultana" <ReubenSultana (AT) discussions (DOT) microsoft.com> wrote in message
news:148FF757-0DBD-4399-B5BE-1D769BA03D9C (AT) microsoft (DOT) com...
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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Memory issues with 64-bit SQL Server 2005 on 64-bit Win 2003 Clust

microsoft.public.sqlserver.clustering microsoft.public.sqlserver.clustering

Re: Memory issues with 64-bit SQL Server 2005 on 64-bit Win 2003 C - 09-25-2008 , 09:51 AM