Потребление памяти SQL Server 2008 R2 без использования [закрыто]
У меня есть приложение, использующее SQL Server 2008 R2. У меня проблема с потреблением памяти на стороне клиента. Всякий раз, когда клиент запускает приложение, SQL-сервер начинает потреблять память, даже если клиент просто находится в режиме ожидания и не использует ни одну из функций приложения. Это в конечном итоге замедляет его работу на компьютере после длительного ожидания.
Вопрос в том, почему SQL-сервер потребляет память, даже если он не работает, и как я могу решить эту проблему.
Пожалуйста, помогите.
Like Ryan Ries said in comments, if the SQL Server is running on a different host than the client and the client is having memory issues then the application needs fixed. If this is the case then we don't have anything to go on to help with that. Tuning an app requires a lot of information about the app and environment.
Regarding general SQL Server memory consumption, that's by design. SQL Server will load as much into memory as it can so that it's working with data in memory and deferring expensive disk I/O operations until absolutely necessary. If your databases are larger than your physical memory or you have other things running on the same host then you can quickly experience memory contention. They default setting for SQL Server is to use as much memory as it can.
You can set a maximum memory value for SQL Server so that it consumes no more than that amount of memory. Usually if SQL Server is the only thing on the server I will leave 2-4GB of memory available for the OS and let SQL Server have the rest. So for a box with 64 GB of memory I'll set SQL Servers max memory to 60 GB.
Here's how:
sp_configure 'max server memory (MB)',60000
GO
reconfigure
GO
(This sets max memory to 60GB. Of course set the value to fit your server's specs.)
This setting is dynamic so there's no need to restart the SQL Server instance for this to take effect. It will take affect when the reconigure statement is executed.