question

Mohamed4053 avatar image
Mohamed4053 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.
memorymemory-utilization
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
David Wimbush avatar image
David Wimbush answered
Hi. There isn't a memory leak as far as I know. Why do ask?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom, I believe you wrongly understand my question. What ever you said is AWE(Address Windowing Extensions). It's database option.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.