Why SQL Server occupy the entire Physical Memory in a Web Application

[Guest_imported]

We developed an intranet application. When accessing the pages continuously for about 30 hrs by 25 users, the SQL Server memory occupation reaches to around 300MB and the system perfomance becomes very slow ad finally system crashes. Most of the pages contain text area objects so the volume of data transfer is very high. Why SQL Server not releases the memory once occupied?
The following are the software configuration.

Windows NT
Apache 1.3.20 Web Server
tomcat 3.2.1 JSP Engine
JDK1.3.1
SQL Server 7.0
JDBC-ODBC Bridge Driver
Poolman1.4b5 for Conection Pooling.
IE5 Browser

The application contain around 1500 JSP files, 500 Bean files, 300 tables, 65 stored procedures, 20 views and 10 triggers.

The Server config is as follows.

RAM 393
Vertual Memory 1GB
CPU pentium3
HDD - 8GB


Regards
Rony
rony@sslindia.com/ronysspl@yahoo.com

 

[aalmeida]

SQL Srv as configured out of the box will attempt to get all the available memory to it self until requested by the OS, and this is the best way to handle the memory. You can configure it to use all or some of the memory using the EM seting realistic values for the memory SQL is allowd to use.
But I would recomend you to:
1) go over your app and see where you can limit the retrival of records because I have an sence that what is causing it to crash is that your app is bringing to memory more record at a time than it realy needsthink the problem.
2) Add more Memory and CPU to your Box, the minimum I would think for a dev box would be duo CPU and 2Gb Ram or performance will be very poor specially on big apps.
3) Virtual Memory does not help to much since it creates more IO requestes that are very slow therefore SQL will send data to RAM instad of Disk since it was already retrieved from the disk would not make sence to send it back to the disk.
I hope I could help
AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[Guest_imported]

Hi aalmeida,

If I am setting the Db property max memory to 240MB or Fixed size memory to 240MB, can I avoid the system crash?..Then how will be the system perfomance?

Regards
Rony