Re: Memory issues with 64-bit SQL Server 2005 on 64-bit Win 2003 C

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:148FF757-0DBD-4399-B5BE-1D769BA03D9C@xxxxxxxxxxxxxxxx
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/archive/2007/05/18/sql-and-the-working-set.aspx

5. A significant part of sql server process memory has been paged
http://groups.google.co.uk/group/microsoft.public.sqlserver.server/browse_frm/thread/4b03cc132e0550e3/a1171fc3bcbd4e43?lnk=raot&hl=en#a1171fc3bcbd4e43

6. Does SQL Server always respond to memory pressure?
http://blogs.msdn.com/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-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/WindowsServer/en/library/87f23f24-474b-4dea-bfb5-cfecb3dc5f1d1033.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


.

Relevant Pages

  • Re: Performance problems -- need guidance on scaling
    ... Thanks for the update on the FT Catalog corruption, ... will use, up to a max of 512Mb, if this memory is available. ... a server with 512 MB of RAM and a resource_usage value of 5 ... the data, you could, detach your SQL 2000 mdf & ldf files ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Memory Issues
    ... Exchange and SQL Server. ... you identified memory problems and large amounts of paging. ...
    (microsoft.public.sqlserver.setup)
  • Re: Memory issues with 64-bit SQL Server 2005 on 64-bit Win 2003 Clust
    ... The Paging File usage is due to how the large memory allocation APIs work. ... Swap 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. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Allocating more memory to sql server
    ... Are you sure you have Windows 2000 Advanced Server and not just Server? ... SQL Server. ... memory used by SQL Server that will give a better idea of the actual usage. ... You also need to restart SQL Server after enabling AWE for it to take ...
    (microsoft.public.sqlserver.setup)
  • The Company Who cheated me Fake client/server application
    ... Manager begin to Complain From yearly inventory Report the Process takes ... I write My Recommendation to manager Ask Him Purchasing Brand new server ... After Install Process Finish I Attach the Databases and Run Sql Agent ... Inside Fake client/server application: ...
    (microsoft.public.sqlserver.server)