April 21, 2003 at 5:56 am
We have a 3 tier ASP based application running IIS 5 in the Frontend & SQL 2000 in the backend. COM+ is running in middle tier. Clients are using this application over the internet.
The OS is Win2000 SP3 with SQL 2000 SP3 with all latest updates.
The problem we are facing for few days is that SQL server process (SQLSerVr.exe) is taking too much memory. Once SQL memory consumption start rising, it doesn't come back to normal untill we restart the server.
The server has got 1 gig of memory. We have checked the SQL in detail but nothing suspicious has been found. We've even deployed the DB on another server but still the same issue on the new server as well.
We have scanned the servers for viruses well & nothing found. There are few errors in EventViewer, but nothing related to SQL server. We have even checked the SQL Profiler & monitored it. but nothing suspicious has been found.
Now we need to know:
1) Any idea what is cauing SQL to behave in this way?
2) Is there a way we can check what processes, threads & handles are runnning inside SQLSrvVe.exe process?
3) How to detect identify this SQL problem?
Your comments & suggestions are are highly appreciated.
April 21, 2003 at 7:18 am
If you have the memory configuration for SQL Server set to dynamic, it will continue to expand to use as much memory as possible. Remember, it's going to try and cache both execution plans from stored procedures and queries as well as pages of data in order to improve performance. As a result, SQL Server may very well be wanting more memory than you think it ought.
One way to check is to run performance monitor and look at the SQL Server:Memory Manager counters for Target Server Memory and Total Server Memory. The first (target) is what SQL Server wants. The second (total) is what SQL Server currently has.
You can set an upper limit as a governor. SQL Server will give back memory, but it does so slowly. This is typically why SQL Server (like any other RDBMS) runs on a box by itself. If, however, you need to share resources, setting the upper limit is typically the way most people go.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
April 21, 2003 at 8:53 am
With a 1GB box, I'd suspect SQL will get up to 800-850MB of RAM. That's normal as Brian explained.
Steve Jones
April 21, 2003 at 1:47 pm
quote:
With a 1GB box, I'd suspect SQL will get up to 800-850MB of RAM. That's normal as Brian explained.Steve Jones
http://www.sqlservercentral.com/columnists/sjones
Now we've added 2 gig memory. At the moment the memory usage by SQL is over 900 MB.
Is there a possibility that some thing from Application or middle tier components causing this problem on SQL server? If yes that how to identify those culprits?
April 21, 2003 at 1:55 pm
No, this is by design. if you need to keep memory for your apps, limit sql to some amount.
Steve Jones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply