May 13, 2014 at 11:11 am
Hello everyone my supervisor recently showed me that in the task manager the server is consuming 98% of RAM most of the time. sqlserver service is using around 3 GB they are also other processes but don't seem to consume much memory. how can I prevent the memory usage from going up this high?
May 13, 2014 at 11:30 am
Set the max memory for that instance of SQL Server to a reasonable amount. On a system with 4GB of memory where the server is dedicated to SQL Server I would recommend no more than 3GB.
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
May 13, 2014 at 3:52 pm
Yeah, you have to set max memory. SQL Server will attempt to use all the memory available to it, so the only way for sure to limit that is to limit SQL Server. Within SQL Server, there are ways to maximize memory use, but it really depends on what your databases are doing which of those is most applicable.
BTW, in 2014, a 4gb system is teeny tiny. You might want to bump the server size up a little. My laptop has 16gb.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2014 at 5:15 pm
Is there actually a performance issue here, or is this drive by management? Are you monitoring your PLE for example?
I think 98 is high, but I expect 90+ on my servers - SQL Servers are different from most servers especially file servers, they should be using most of the memory. This is the potted summary I generally give to the network guys when I tell them I will kill them if they ever bounce a SQL Server server / VM "Because it's running out of memory"
Briefly, when you start up SQL Server, as you run queries it takes the data from the discs to fulfil those queries. This is slow. It also generates a plan of how it is going to access the data efficiently - which takes time and CPU resources.
In order to respond efficiently in future, SQL Server stores
1) the data
2) the query plan
in RAM so
1) it doesn't have to go to disc every time to get, say, your customer table which you're using all the time (generally it's said RAM is 500,000 times faster than disc - whatever, it's just a hell of a lot faster)
2) it doesn't have to recalculate how to pull the data together to service your query, saving time and CPU resource
the reason SQL Server is using all that memory is (usually) to make it more efficient. This is actually a good thing. There's a bit lot more to it than that, but that's the basic principle behind what you're seeing. Having said that, I would, if I were you set SQL Server's max Memory to between 2 and 2.5 GB. I'd also put more memory in as Grant said. Max out will cost buttons, probably, and SQL Server LOVES RAM.
I can direct you to more in depth explanations if you like, (as can many other people on here, who are a bit better than me) just let me know if that would help
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy