Monday, October 27, 2008

Probable causes of high memory usage by SQL Server

Under normal conditions, 2 GB RAM memory of database server dedicated to hold SQL Server databases for a multi-user application is more than enough. But, some times the memory usage becomes so high that, application users will not be able to use the application, letting system to knees. Then, the database administrator will have to restart the SQL Server service to clear the memory usage requiring some down time. So, it's always better to prevent SQL Server from using so much high memory.

 

So, what may be the causes for high memory usage by SQL Server? I have listed out some of the most probable causes.

 

  1. Of course, too many users are using the application at the same time, this is likely and a general case.
  2. Some other external process or virus or malware may be causing the problem.
  3. A lot of uncommitted transactions in SQL may also cause this problem.
  4. Many open connections to SQL Database by application increase the use of memory in database server. For a web application, it's likely that users do not close the application by properly logging out. Due to this, the connection to database remains open till the garbage collector finds it and terminates the connection. If a user initiates a time consuming process in SQL from application and closes the web application, the process keeps running in back end and keeps using the memory.
  5. Excessive use of full outer joins in SQL procedures or query uses more memory while executing.
  6. Excessive use of temporary tables also uses more memory.
  7. Use of repetitive and many dead locks in SQL Server cause the increase in memory usage.

 

Usually, SQL server has not tendency to free memory space after it has finished using. This means, if the usage reaches 100 MB sometime due to some process, even after the process is completed, SQL Server will the free unneeded memory space.

 

The SQL Server memory usage can be controlled by SQL Server Enterprise Manager. First open Enterprise Manager; expand the Microsoft SQL Servers, SQL Server group and the server; right click the SQL Server instance and select properties. In the memory tab of properties window, select "Use a fixed memory size (MB)" and then slide the slider to limit the SQL Server memory usage. This is the memory usage for SQL Server instance only not for extended procedures, dlls and other external process. So, SQL Server may be using a little more that allocated over here sometimes.

 

 

 

0 comments: