Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
FM
Starting Member
2 Posts |
 Posted - 2003-04-21 : 08:07:39
|
| 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. |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-04-21 : 09:03:52
|
| How much is too much ?SQL Server is designed to use memory. Memory is quick.You can limit the amount of memory in the server options (right click in Enterprise Manager).Damian |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-04-21 : 09:24:10
|
| Have a look at INF: SQL Server Memory Usage[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363[/url]There are a few handle/memory leaks but most of these tend to concern applications running localy on the server. You can set up a trace log to monitor the sqlserver process private bytes and handle count over a 24 hour period. If these values constantly grow and by quite a large amount then it may indicate a bug you are coming across but these are pretty rare. The general trend you should see is a fairly constant value with short term peaks. I'd be inclined to set max server memory to around 800 MB if you have 1 GB on the server to leave a bit of breathing room. Remember that the value for max server memory is just the limit for the buffer pool and that it is completely normal for SQL to use more than this value for other memory requirements (e.g. extended procs/net libraries/exe's/backup and restore operations). HTHJasper Smith0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-21 : 09:38:06
|
| Are you running anything else except SQL Server on that box?Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-21 : 13:56:13
|
| 1) SQL Server is behaving this way because that's how it was designed2) Yes you can what is running in SQL Server by using SQL Profiler, but that isn't going to prove/disprove anything since SQL Server is designed to be a memory hog.3) There isn't a problem on your machine unless other applications are running on it. If there are, then move them off this box or move SQL Server to a dedicated box. SQL Server is designed to use memory, so there really isn't a problem here except if you are running other applications on the server. If you can't move the applications or move SQL Server to a dedicated box, then set the maximum memory option as Merkin suggests. But just keep in mind that you are hindering SQL Server by doing that when it needs to use more than the maximum.Tara |
|
|
|
FM
Starting Member
2 Posts |
Posted - 2003-04-21 : 16:02:43
|
quote:
Are you running anything else except SQL Server on that box?Brett8-)
Earlier the server was running with IIS, SQL & com+ in one box for long. There were major updates in our application. Sfter that, we faced performance issues. Now we have seperated the SQl from IIS & com+ server, which are running on another server. Now we've added 2 gig memory on both servers. 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? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-21 : 16:14:27
|
| SQL Server using lots of memory isn't a problem. That's how the product was designed, so there isn't anything to look for to find out what's the culprit. 900MB isn't a huge amount of memory for SQL Server to be using. As a matter of fact, 900MB isn't a lot at all on a server with 2GB of RAM installed. In a lot of instances, SQL Server will practically use all of what's available. But if you think that SQL Server shouldn't be using this amount (although I'm not sure how you would determine how much it should use), then you should run SQL Profiler along with Performance Monitor/System Monitor. Monitoring the two programs isn't enough though. In depth analysis of the two outputs would need to be done, so since SQL Server using a lot of memory isn't a problem, I wouldn't go down this route. What route should you take though? None, unless some other application is affected by it. Also, do not reboot the server in order to release the memory since that's a waste of time because SQL Server is just going to try and consume it again. SQL Server does not release the memory after it's done with it, but it will release it to another application if SQL Server no longer needs it AND another application/resource is requesting it.TaraEdited by - tduggan on 04/21/2003 16:16:17 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-21 : 16:14:34
|
quote:
SQL Server is behaving this way because that's how it was designed
I bet your database is 900mg and it's got the whole thing in memory...which is a good thing. Disk I/O has got to be the worst culprite for performance (except for the rogue developers)How big is the database, I mean if you've got 2 gbHow big is temdb btwBrett8-)Edited by - x002548 on 04/21/2003 16:18:08 |
|
|
|
|
|
|
|
|
SQLSerVr.exe eating up too much memory