reducing physical memory usage

  • 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?

  • 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

  • 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

  • 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