To begin with, can we limit the amount of memory used by SQL server. Use
the fixed memory setting instead of dynamic. That way we can see if we
still have application problems. Is it possible that 1 or all 4
applications are experiencing memory leaks ?
I have some articles that may be helpful from the SQL perspective...
Application Performance Troubleshooting
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
| Subject: "memusage" in the sysprocesses table
| Date: Thu, 14 Mar 2002 22:37:04 GMT
| Content-Type: text/plain; charset="windows-1252"
| User-Agent: Mozilla/4.0 (compatible; MSIE 5.0; Windows 2000) Opera 6.0
| Newsgroups: microsoft.public.sqlserver.tools
| NNTP-Posting-Host: bb-203-125-131-178.singnet.com.sg 126.96.36.199
| Lines: 1
| Path: cpmsftngxa07!cpmsftngxa09!tkmsftngp01!tkmsftngp07
| Xref: cpmsftngxa07 microsoft.public.sqlserver.tools:10108
| X-Tomcat-NG: microsoft.public.sqlserver.tools
| the sysprocesses table in the master db describes the "memusage" as
| memusage int
| "Number of pages in the procedure cache that are
| currently allocated to this process. A negative number
| indicates that the process is freeing memory allocated
| by another process."
| if some (Java)applications has a very high figure for this column,
| will it affect the performance of the whole server(for other
| ASP applications)?
| the scenario is :
| we have an application server running on another machine. that machine
| accesses this database server (MS Sql 7.0). there are about 4
| running on the application server.
| when i issue a "select * from master..sysprocesses", i could find, that
| 4 applications memusage is the highest. Over a period of time, it keeps on
| increasing and those 4 applications fail to respond. Once the application
| server is restarted, things are normal. But, the memusage keep on
| and EXCEEDS THE PHYSICAL RAM on the SQL server. The CPU usage
| shots to 100% at regular intervals also.
| waiting for any suggestions....