Our system type is 32-bit with only 2 GB RAM. I've tried to set the max server memory to 1000 MB but to no avail. Still the memory used as displayed in the task manager is always 1.78 GB or more, and not getting lower. Appreciate your help. Thanks!

答案

Normally when we have dedicaed SQL Servers, no other services or applications are run on them. Sometimes this just can't be the case at smaller businesses where hardware and software are at a premium.

Some things I would like to point out:

1. The server has 2 GB of ram for SQL Server, RDP sessions, Mirroring, and other programs or services running on it. This is very low in my opinion. Either dedicate the machine solely to SQL Server or add memory. If neither are an option, put the max server
memory much lower, in the realm of 512 MB and see if that helps at all. There is a good chance that SQL Server is being paged and not actually the culprit.

2. For the page swap check your IO for excessive consumption with perfmon and also add in the paging file % usage, see if it changes drastically over time given a server reboot to the point of high usage.

3. Double check your wait stats to see what SQL Server is waiting on. Granted you had said there were no performance issues, but it may help to show where an issue may be. For example, if you have high latch waits, that's indicitive of memory issues, coupled
with a high page file usage, etc may give you the information needed to get more memory for the server (I'm not saying memory, it could be anything just using that an as example).

全部回复

The max server memory value is only for the buffer pool, there is memory that SQL Server uses outside of that. It is kind of a misnomer as it's not the maximum amount of memory that SQL Server can use.

Even though it is 2 GB, it is good habit to use perfmon to check memory values on a 32 bit system.

Are you having a slowdown in performance because of this? Page file swap going crazy? Are there any other application that run on the server or any other services that are serving to end users?

Items that will be located outside of the buffer pool are things like linked servers, com objects, clr objects, etc. Are any of these being heavily utilized?

Yes, we are always monitoring the system performance and it's always almost reaching the top of the RAM and CPU memory.

No, we are not having slowdown in performance but sometimes we cannot RDC to our server due to lack of memory (I suppose). I'm not sure about the page swap though, my apologies but could you advise what I need to check regarding this? Yes, we have some softwares
running inside and outside the server which connects to the SQL.

Normally when we have dedicaed SQL Servers, no other services or applications are run on them. Sometimes this just can't be the case at smaller businesses where hardware and software are at a premium.

Some things I would like to point out:

1. The server has 2 GB of ram for SQL Server, RDP sessions, Mirroring, and other programs or services running on it. This is very low in my opinion. Either dedicate the machine solely to SQL Server or add memory. If neither are an option, put the max server
memory much lower, in the realm of 512 MB and see if that helps at all. There is a good chance that SQL Server is being paged and not actually the culprit.

2. For the page swap check your IO for excessive consumption with perfmon and also add in the paging file % usage, see if it changes drastically over time given a server reboot to the point of high usage.

3. Double check your wait stats to see what SQL Server is waiting on. Granted you had said there were no performance issues, but it may help to show where an issue may be. For example, if you have high latch waits, that's indicitive of memory issues, coupled
with a high page file usage, etc may give you the information needed to get more memory for the server (I'm not saying memory, it could be anything just using that an as example).