SqlServer.exe is on high memory even physical mem is at 1..5GB

Hi

You must understand that SQL Server 2000(you never specified which version you are using) uses an advanced memory caching engine. It will attempt to retain data that is queried often in memory. Therefore it will appear as if SQL Server is "stealing" all the RAM on the server but if another application/process/task needs memory, sql server will release the memory required. Task manager my display that the sqlserver.exe is using 1.3Gb of RAM but it might not be using all of it.

One of my production database servers has 2Gb of memory as the amount displayed next to sqlserver.exe averages round about 1.5 - 1.7Gb. The applications also run on a seperate web server and memory isn't a problem.

There is a good chance that you can reboot your server and sql server will take all 1.3gbs back in 30 min easily.I've seen SQL Server do that.

I'm not saying that it isn't a memory utilisation problem but it might be something else or a combination of things eg:

network problems?
have you looked at the web server cpu/memory utilisation when the apps become slow?
how many users are connecting to the application(both servers)?
are your tables indexed correctly?
maybe there are long running queries that need to be tuned?

you will need to use sql profiler to exam whats going on with sql server, performance monitor for the server as a whole (web server too) to start to see what the cause could be.

you also need to see if any hardware changed recently, did an application change go in, has this only happended recently or for a while?

There is an option to limit the amount of memory that sql server can use but if this is a memory problem setting limits will probably cause more hassles in the long run.

Hope this helps to get you on the right track

John

Hi John,

There are no network problems known yet. Except that the db server is running too high on memory. The Web Server CPU/memory utilisation when the app becomes slow is on quite high toll.

There are quite a lot of users connected to both application and both db Server online; ard 5000 users on the average each day. I suspect there are some queries that may need to be tuned. Can advise further how to check the indexing is perfect?

There wasn't change in hardware recently..

Thanks,
libroos

Hi

You can use SQL Profiler to run a trace on the sql server to determine which queries are being used a lot. Save the trace to a file. Then run the Index Tuning Wizard an use the trace file as the source.

Make sure you filter out all the unecessary stuff in the trace as the trace will add more load to the server.

Have you considered the possibility that your server spec might be to low for 5000 users? What type of cpu and speed is in your dual server box? My database server mentioned above has 4 cpu's and there are 5000 front end users and at times the apps might slow down but usually its a network problem or something as oppossed to the server.

Ideally you want SQL Server to be using a lot of memory as the queries/data are being stored in memory so if the query is executed frequently then the data is read from memory instead of reading from the database again.

Have a look at the following thread where someone is having a similiar problem:

thread183-506512

btw..which SQL Service Pack are you running and which OS service pack are you running?

Hope this helps

John