0

We are using SQL server 2008 in Windows Server 2008 R2 and Ram is 32 GB. When we check in the Windows Task manager 25GB is always occupied even though no one is accessing the server.

Around 10 clients connected to the server. When we run a report from application, (2 or more clients runs it simultaneously) the memory occupied fully ie 32 Gb and saving records from other application got failed.

I checked in RAMmap tool AWE occupies 25 GB.

How to handle this situation? ie., how to limit the usage of memory for SQL server or adding records when the report query takes long time to run? Thanks

  • What are you wanting to limit the memory to? Could you also post he current value of the setting "Maximum server memory (in MB)" from Server Properties > Memory? (Right click on the server in SSMS and click properties, then memory) – George.Palacios Oct 8 '19 at 8:55
  • 1
    saving records from other application got failed - what does it mean? Do you have any errors? Add info about them to your question. Also add result of SELECT @@version. – Denis Rubashkin Oct 8 '19 at 8:56
2

As suggested set the “Maximum Server Memory”.

https://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/

Additionally, please note that your error is more likely related to locking or timeouts rather than “running out memory”. An extensive exclusive lock on the target table combined with missing indexes for the update statement can result in what you are experiencing.

1) Tune your report query and ensure that its not having redundant excessive reads. Verify that hash and sort operators in your execution plan are not spilling to disk.

2) Make sure that your update statement has logical supporting indexes.

Tuning both process might enhance concurrency of your application.

Good luck :)

| improve this answer | |
-1

It seems you have not set the "Maximum Server Memory" at your instance level. In case you don't set them up and there are tables which are voluminous and connections which retrieves them or puts joins on heavy tables on periodic basis will cause an issue. Other issue here is - consumed memory won't be released until there is a request from Windows.

you should set them up based on various guidelines and also keeping in mind the load which is present on your database server. Also keep in mind that these values are in MB(Mega Bytes) and not in Giga bytes, do calculations in advance before setting that up. By default this value will be set as "2147483647".

Also note that this change doesn't require reboot of SQL service and hence can be changed on the fly. Please consider reading below links for more detailed understanding:

https://www.brentozar.com/blitz/max-memory/

https://www.mssqltips.com/sqlservertip/4182/setting-a-fixed-amount-of-memory-for-sql-server/

I hope above helps.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.