July 10, 2009 at 4:41 am
I have a newly configured UAT Server (SQL Server 2005 Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)). There are no huge databases on the Server - about 15 databases with the biggest one around 4GB. There is 32GB of RAM on the server and the max server memory limit had not been set.
Now the problem is that the sqlservr.exe process memory usage kept on growing untill it reached around 10 Gb. Now it has been staying around the 10 Gb mark for a while now.
What might have caused such a high memory usage and how do I troubleshoot it ???
July 10, 2009 at 4:43 am
Have you set "min" or "max" memory settings?
July 10, 2009 at 4:46 am
The max server or min server memory limit had not been set.
The max memory limit is by default 2147483647 MB.
July 10, 2009 at 8:29 am
SQL Server just takes memory as it's needed for queries, caching, etc. On 64-bit, I would see it trying to cache everything it could, and perhaps running up to 10GB. The worktables, sorting, aggregates, all cause more copies of data to be spooled up and held. My guess is that SQL Server doesn't release that memory on 64bit any quicker than 32bit.
I don't think there is a problem here. Likely you could set a limit on memory and the server would still run fine, but my guess is that it thinks this is a good level of memory for the activity on the server.
July 10, 2009 at 9:38 am
Can I go ahead and limit the memory usage to say about 5Gb ?? Is there anything that will be affected due to this ??
And most importantly just to confirm I would not need a restart for this to take effect...
Thanx in advance !
July 10, 2009 at 10:14 am
I believe that you will need to restart.
If you limit memory, SQL Server will have less to work with. That can affect performance, but there's no way to know how much it will affect performance. You will have to experiment a bit and determine what your memory requirements should be v. the performance of the server.
July 10, 2009 at 3:54 pm
You may check the size of the buffer pool and how memory is distributed. The following queries will give you this information.
-- how memory is distributed
selecttype, sum(multi_pages_kb) as mem_allocated_in_kb
fromsys.dm_os_memory_clerks
wheremulti_pages_kb != 0
groupby type
-- amount of memory consumed by BPool
-- note that currenlty only BPool uses AWE
selectsum(multi_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb + awe_allocated_kb) as [Used by BPool with AWE, Kb]
fromsys.dm_os_memory_clerks
wheretype = 'MEMORYCLERK_SQLBUFFERPOOL'
If you need more detail, you can get the detailed memory per object.
Thanks.
BJ
July 10, 2009 at 4:06 pm
Why do you want to limit the memory? SQL Server is designed to maximize performance by having as much data as possible in memory.
July 10, 2009 at 4:29 pm
What else is running on the server? Is it dedicated to SQL Server?
July 10, 2009 at 4:33 pm
I guess I'm trying to figure out what the problem is, you have 32 GB of RAM and SQL Server is only using 10 GB. Does not equate to a problem to me. Now, with 32 GB of RAM it took all of it, then yes, you'd have a problem. At that point setting the max memory for SQL Server to 28 GB is what I would do, as that would still leave 4 GB of RAM for the OS.
July 10, 2009 at 4:36 pm
Any logic for leaving 4GB for the OS? Does it need it in 64bit?
Or would you be OK running to 30GB for SQL?
July 10, 2009 at 4:45 pm
I would have to agree with Lynn. I see no problems here. If you would feel better limiting the memory for SQL I would set the max memory to be 28GB. Unless you're running applications other than SQL on this server you should be fine with this.
I'm not sure if it is an Enterprise Edition "thing" but I don't recall having to restart services when configuring the min and max memory. I think it was an online operation, and since this is Developer edition the same rules should apply. Curious why you're running Developer Edition in production though.
Finally, don't forget to grant the SQL Service the "Lock Pages In Memory" right. Yes, you still need to do this on a 64-bit box. However, you don't need to worry about setting AWE.
Definitely *DON'T* limit memory to 5GB max. There is a reason SQL is using 10GB, and it's probably a good reason.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
July 10, 2009 at 4:48 pm
Timothy Ford (7/10/2009)
I would have to agree with Lynn. I see no problems here. If you would feel better limiting the memory for SQL I would set the max memory to be 28GB. Unless you're running applications other than SQL on this server you should be fine with this.I'm not sure if it is an Enterprise Edition "thing" but I don't recall having to restart services when configuring the min and max memory. I think it was an online operation, and since this is Developer edition the same rules should apply. Curious why you're running Developer Edition in production though.
Finally, don't forget to grant the SQL Service the "Lock Pages In Memory" right. Yes, you still need to do this on a 64-bit box. However, you don't need to worry about setting AWE.
Definitely *DON'T* limit memory to 5GB max. There is a reason SQL is using 10GB, and it's probably a good reason.
It isn't production, from the original post:
I have a newly configured UAT Server (SQL Server 2005 Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)).
July 10, 2009 at 4:51 pm
Steve Jones - Editor (7/10/2009)
Any logic for leaving 4GB for the OS? Does it need it in 64bit?Or would you be OK running to 30GB for SQL?
I'll have to research this again, but I seem to remember reading the with 32 GB RAM, you should leave about 4 GB for the OS. Could be wrong, but I like to be a little bit cautious with things like this. You could start higher and reduce it if needed.
July 10, 2009 at 9:56 pm
Steve Jones - Editor (7/10/2009)
Any logic for leaving 4GB for the OS? Does it need it in 64bit?Or would you be OK running to 30GB for SQL?
Actually, more so on x64 because SQL Server will happily take all of the memory and starve the OS. On x86 the OS will have 2GB available that SQL Server cannot get to at all. There would be memory pressure on x86 (maybe) depending on what the OS needs.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply