Post reply

MS-SQL 2016 Eating Memory

  • October 18, 2017 at 1:32 am

    #329258

    Hi All,

    I'm a newbie here and in general to MS-SQL  but I've been thrown in at the deep end and having some issues with our SQL Server.

    We're running 2016 on a semi-dedicated VM machine. It's got 24gig RAM and 4 Cores from an 8Core processor.
    The server is also running SAGE (I know...but it is what it is...)
    I'm now at the point of rebooting the server every morning as SQL is eating up all available memory.
    We've set the memory buffer to 12/10/8 gig to try to contain it, but it just keeps taking it.
    This wouldn;t be so bad if it actually released it, but it doesn't.
    6hrs later and it's taken close to 22gig - leaving next to nothing on the server for anything else (e.g. SAGE) and everything starts to run very slow.

    I read somewhere over the weekend that there can be a situation where SQL uses the memory to store and write information, but if it's a bad "call" or request, it keeps the memory allocation stored rather than then releasing it. But to be honest - this is somewhat over my head.

    We've been running PowerBI and Mashups on it for 6 mths - and it's been fine.

    Can anyone help, advise any routes to take, or any tests to try to try to fault find what is going on? It's driving me up the wall.
    If you need any more information, please do just ask and thanks for taking the time to read this.

  • October 18, 2017 at 1:38 am

    #1964150

    SQL Server will use all the memory it is able to, that is normal behaviour. What have you set as the Max Server Memory?

    Thanks

  • October 18, 2017 at 1:41 am

    #1964151

    NorthernSoul - Wednesday, October 18, 2017 1:38 AM

    SQL Server will use all the memory it is able to, that is normal behaviour. What have you set as the Max Server Memory?

    Thanks

    Hi Northern Soul.

    Max Memory presently set at 8gig.
    I just checked it now, and it's at 14.6gig (sorry, let me clarify. The Max Memory is still at 8gig - but SQL is presently taking 14.6gig)

  • October 18, 2017 at 1:47 am

    #1964153

    dextrous - Wednesday, October 18, 2017 1:41 AM

    NorthernSoul - Wednesday, October 18, 2017 1:38 AM

    SQL Server will use all the memory it is able to, that is normal behaviour. What have you set as the Max Server Memory?

    Thanks

    Hi Northern Soul.

    Max Memory presently set at 8gig.
    I just checked it now, and it's at 14.6gig

    SQL Server will not use more than the max server memory which is currently 8gb. Are you saying that you think it's currently using 14.6gb? Do you have more than 1 instance on the server?

    Thanks

  • October 18, 2017 at 2:08 am

    #1964155

    dextrous - Wednesday, October 18, 2017 1:41 AM

    NorthernSoul - Wednesday, October 18, 2017 1:38 AM

    SQL Server will use all the memory it is able to, that is normal behaviour. What have you set as the Max Server Memory?

    Thanks

    Hi Northern Soul.

    Max Memory presently set at 8gig.
    I just checked it now, and it's at 14.6gig (sorry, let me clarify. The Max Memory is still at 8gig - but SQL is presently taking 14.6gig)

    Have you restarted the instance since you lowered the maximum amount?

    Also, what do you classify as SQL Server is using. For example, if you mean SQL Server, SSRS, SSIS, SSAS combined are using 14.6, this is expected. Each service uses it's own memory allocation. Limited the Data Engine doesn't limit SSRS for example.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • October 18, 2017 at 3:19 am

    #1964160

    NorthernSoul - Wednesday, October 18, 2017 1:47 AM

    dextrous - Wednesday, October 18, 2017 1:41 AM

    NorthernSoul - Wednesday, October 18, 2017 1:38 AM

    SQL Server will use all the memory it is able to, that is normal behaviour. What have you set as the Max Server Memory?

    Thanks

    Hi Northern Soul.

    Max Memory presently set at 8gig.
    I just checked it now, and it's at 14.6gig

    SQL Server will not use more than the max server memory which is currently 8gb. Are you saying that you think it's currently using 14.6gb? Do you have more than 1 instance on the server?

    Thanks

    Nope, only the one as far as I know. At least, there is only one instance showing as running

  • October 18, 2017 at 3:27 am

    #1964161

    dextrous - Wednesday, October 18, 2017 3:19 AM

    NorthernSoul - Wednesday, October 18, 2017 1:47 AM

    dextrous - Wednesday, October 18, 2017 1:41 AM

    NorthernSoul - Wednesday, October 18, 2017 1:38 AM

    SQL Server will use all the memory it is able to, that is normal behaviour. What have you set as the Max Server Memory?

    Thanks

    Hi Northern Soul.

    Max Memory presently set at 8gig.
    I just checked it now, and it's at 14.6gig

    SQL Server will not use more than the max server memory which is currently 8gb. Are you saying that you think it's currently using 14.6gb? Do you have more than 1 instance on the server?

    Thanks

    Nope, only the one as far as I know. At least, there is only one instance showing as running

    As Thom has asked, what else do you have running on the server and have you recently lowered the max server memory value as it can take a while for SQL to release the memory?

    Thanks

  • October 18, 2017 at 3:29 am

    #1964163

    Here's a SCR of the top memory munchers.
    Yes the server was rebooted this morning after the setting was changed yesterday.
    Rebooted @ 7.00am GMT (3.5hrs)
    Once back online, SQL was showing 355meg and then once someone logged in to our core system, it then went straight to 8.5gig - now it seems to increase about 100meg every 10 mins.

  • October 18, 2017 at 3:43 am

    #1964165

    dextrous - Wednesday, October 18, 2017 3:29 AM

    Here's a SCR of the top memory munchers.
    Yes the server was rebooted this morning after the setting was changed yesterday.
    Rebooted @ 7.00am GMT (3.5hrs)
    Once back online, SQL was showing 355meg and then once someone logged in to our core system, it then went straight to 8.5gig - now it seems to increase about 100meg every 10 mins.

    If the Server rebooted 3.5 hours ago and you say you've set the Max memory, i'd suggest you haven't.

    What does the following return?
    SELECT [name], [value], value_in_use, [description]
    FROM sys.configurations
    WHERE name like '%server memory%';

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • October 18, 2017 at 3:47 am

    #1964166

    dextrous - Wednesday, October 18, 2017 3:29 AM

    Here's a SCR of the top memory munchers.
    Yes the server was rebooted this morning after the setting was changed yesterday.
    Rebooted @ 7.00am GMT (3.5hrs)
    Once back online, SQL was showing 355meg and then once someone logged in to our core system, it then went straight to 8.5gig - now it seems to increase about 100meg every 10 mins.

    Are you sure you have set the max memory to 8 gb?

    Thanks

  • October 18, 2017 at 4:16 am

    #1964171

    Thom - I've asked the Devs to run the script.
    I'll get the outcome for you hopefully in next hour as they are in meeting right now.

    Northern - I've asked 5 times to the data manager (also in the above meeting) and he assures me it is set at 8gig.

    Not sure how long they will be - but will update as soon as I know.
    When a server is rebooted - does SQL lose the record of the capped Memory usage?

  • October 18, 2017 at 4:20 am

    #1964172

    dextrous - Wednesday, October 18, 2017 4:16 AM

    Thom - I've asked the Devs to run the script.
    I'll get the outcome for you hopefully in next hour as they are in meeting right now.

    Northern - I've asked 5 times to the data manager (also in the above meeting) and he assures me it is set at 8gig.

    Not sure how long they will be - but will update as soon as I know.
    When a server is rebooted - does SQL lose the record of the capped Memory usage?

    No the max server memory setting will not change after a reboot.

    Thanks

  • October 18, 2017 at 4:25 am

    #1964173

    dextrous - Wednesday, October 18, 2017 4:16 AM

    When a server is rebooted - does SQL lose the record of the capped Memory usage?

    No, once you set the Maximum amount, it stays there. It's an Instance setting, so unless someone changes it, it'll remain as the value set.

    Can I assume you do not have the ability to access the SQL yourself? You could easily do so by just pasting it into SSMS or SQLCMD. You can even run it on a login that has no permissions other than public. Trouble shooting a problem when we can't speak to someone who is doing the things we're asking is not going to be quick or easy. No offence, but I find things always get lost in translation with middle men.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • October 18, 2017 at 4:38 am

    #1964174

    Thom - I couldn't agree more. However - I'm here in this situation where I'm the one being asked by the Data Manager "Why is it doing this".
    They've come from a much much large business where they would have DB's and Admins in SQL who would worry about all of this. We do not have that luxury. I'm fairly proficient on most things - but always been mySQL not MS SQL - but I'll visually see the query being run, collect the data and report back.
    It's frustrating situation, I can assure you, but I am where I am alas :/

    Sorry - and to add - I do have full access to all platforms and systems - so yes, I can access SSMS and run the querry - just very concious I'm not an expert in this area and can not risk screwing up a live environment. 🙁

  • October 18, 2017 at 4:51 am

    #1964176

    Definately locked in at 8gb
    Here's the SCR from the query

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply