asked
Memory Leak in sql server?
Hello Friends and Geeks, I just want to know 1. What is memory leak in sql server? 2. How to find out memory leak is affected my sql server? 3. what is the solution for memory leak? Thanks in advance.
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
Hi. There isn't a memory leak as far as I know. Why do ask?
If you are asking about SQL Server's apparent increase in consumption of memory to take all you've got, then that's normal, and expected behaviour. You might want to configure your server to limit the maximum amount of memory that SQL Server can use. For example, on a server with 24GB RAM, you might want to restrict SQL Server to use 18GB, leaving the remaining 6GB for other processes (Windows, third party backup software, etc). To do this, you would either do it using SSMS or using the `sp_configure` stored procedure. There's an [overview of optimising memory][1] which may help. Using `sp_configure`: exec sp_configure 'max server memory', 18432 -- being the memory in MB you wish to allocate reconfigure [1]:
http://technet.microsoft.com/en-us/library/ms177455(v=sql.105).aspx
Tom, I believe you wrongly understand my question. What ever you said is AWE(Address Windowing Extensions). It's database option.
If you have an actual memory leak, which means memory resources are getting allocated, but not released or reused in any way, effectively hiding them from both SQL Server and the Windows OS, then you need to contact Microsoft. But, it's probably just SQL Server's normal behavior. If you have not set a maximum memory setting on your server, SQL Server will attempt to use all the memory on the system.
SQL Server doesn't automatically "take" MAXMEM. It starts out by taking what it needs and then, usually in 4KB chunks, it grabs more RAM as required - when data is read in from disk to satisfy queries. The defaults for MAXMEM tell SQL Server to take all it can. This can look like a memory leak if observed from server boot over time but it is just standard operations. SQL Server will consume what it can as RAM I/O is extremely fast compared to disk I/O. That's why a lot of answers to resource issues you will see include "buy more RAM." @ThomasRushton gave you the solution in limiting SQL Server so it doesn't starve the O/S. So in answer to your questions: 1) There is no memory leak 2) Unless you cap SQL Server's MAXMEM it may affect the O/S and other applications 3) The fix is to cap MAXMEM at a setting that preserves ample RAM for other applications and the O/S
17 People are following this question.
