question

pits avatar image
pits asked

sql consuming more memory

Hello Experts, I am using sql server2008r2 entp version Received alert : Percentage of Committed Memory in Use is too high from task manager noticed my sql is consuming more memory (e.g 50 gb ) of the box then what are the steps to encounter the issue? from where to start? which commands are there? can you please help?
sql memory consumption
3 comments
10 |1200

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

Dude, this is perfectly normal for SQL Server. The only thing you need to do is put a cap on it with the sp_configure max server memory - set it to 45056. The bulk of the memory that SQL Server uses is to cache data to improve the performance of your frequently-run queries. Do you really want to cripple your server?
2 Likes 2 ·
How much memory does the server have? What else is running on the server? Is it just a SQL Server, or is it providing other services too?
0 Likes 0 ·
thanks Thomas, this is one of the node of cluster and has memory 50gb,and sqlserver.exe consuming 43gb. No other application exist on this server. Is there any command in management studio we can run to identify whats going on and which process consuming this much high memory
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
sp_configure is your friend - http://msdn.microsoft.com/en-us/library/ms188787.aspx The options that can be configured - http://msdn.microsoft.com/en-us/library/ms189631.aspx Specifically, you'll want to look at Max server memory - http://msdn.microsoft.com/en-us/library/ms178067.aspx You'll want to set it to something like (Amount of server memory in MB - 6144) - that will leave 6 GB of RAM free for the OS and anything else on the box. Note that SQL Server will grab as much memory as it can over time, and is pretty good about managing it. However, if you've not set the maximum server memory, it might get to the point where it doesn't leave enough for Windows to do its thing most effectively.
10 |1200

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

ramesh 1 avatar image
ramesh 1 answered
you are using sQL Server 32 Bit or 64 bit? if you are using 64 bit then it is a regular habbit of SQL Server 64 bit server, **set the max memory for better performance of the server** have a look into the Available MBytes of the Server they should be 10% of the installed RAM, if they are at 100MB, then it might be memory leak
10 |1200

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.