Post reply

sql server memory leak.

  • September 22, 2011 at 6:11 am

    #257659

    I have 16 processors and 24 Gb Ram in my server . but now the server is using 21 gb of ram for last 3 days.

    suggest ASAP.... how to fix it

    Thanks in advance

  • September 22, 2011 at 6:16 am

    #1385087

    That sounds actually pretty good (assuming the box is dedicated to the server).

    It's normal to leave arm for the OS and 3 GB sounds decent.

    SQL will literally take al lthe ram it can get unless you limit him. Then it won't release it unless the os really fights for it.

    That's why it's a great practice to set a max memory use for the server so that the os won't starve.

    Keep in mind that some memory processes of Sql server won't be affected by that limit. So aim on the low side especially if you play a lor with clr and xml.

  • September 22, 2011 at 6:20 am

    #1385089

    That's not a memory leak. It's perfectly normal, documented behaviour. SQL uses as much memory as it can get to cache data and plans for optimal performance

    If you want to reduce the memory SQL's using, set the max server memory setting lower.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 22, 2011 at 6:43 am

    #1385108

    Due to that ,, I got the avg. page lock/ avg. object lock is more than 95 % ... last few days.

    Will it work :

    sp_configure 'max server memory',15360; -- (15 GB)

    Thanks

  • September 22, 2011 at 6:46 am

    #1385110

    dm_tapas (9/22/2011)

    Due to that ,, I got the avg. page lock/ avg. object lock is more than 95 % ... last few days.

    Absolutely sure SQL's memory usage is the cause?

    Will it work :

    sp_configure 'max server memory',15360; -- (15 GB)

    Sure it'll work, but it means you're leaving 9GB of memory going to waste. Why would you not want SQL to use that memory? (unless there are other things on the server)

    With 24GB of memory in a server and the server dedicated to SQL, I'd set max server memory to 20GB and move it higher if there was still free memory. Unused memory is a waste of resources.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 22, 2011 at 11:50 pm

    #1385532

    See, My question is why SQL Server is using 21 GB of memory? Server looks fine .. but , memory consumption is too high.

    Tell me : How can I moderate the memory usage?

    Please suggest something..!!!

    Thanks

    Tapas

  • September 23, 2011 at 12:38 am

    #1385540

    dm_tapas (9/22/2011)

    See, My question is why SQL Server is using 21 GB of memory? Server looks fine .. but , memory consumption is too high.

    Tell me : How can I moderate the memory usage?

    Please suggest something..!!!

    Thanks

    Tapas

    Gail and Remi already point out.

    By default design SQL will use as much as memory they need and it will not release it until the OS asks. You can limit it by setting max memory.

    Why do you want it reduce it?

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • September 23, 2011 at 1:06 am

    #1385546

    In this server The total databases sizes are around 50 GB. The problem is that ,SQL Server: Object Lock Average Wait and

    SQL Server: Page Lock Average Wait is too high (above 98 %) Client is asking to reduce this. Every where server looks fine. Only last few days Memory consumption is high (Using 21 GB out of 24 GB)

    Due to the memory , Lock Avg. is too high , last few days I observed this.

    So, I don't want to restrict the Memory. I want to restrict Lock Avg. wait. ..

    Thanks

    TAPAS

  • September 23, 2011 at 2:18 am

    #1385566

    dm_tapas (9/23/2011)

    So, I don't want to restrict the Memory. I want to restrict Lock Avg. wait. ..

    Lock average wait time has absolutely nothing whatsoever to do with memory usage.

    If you want to reduce lock average wait time then look at the locks, look at the queries taking those locks and see if you can optimise those queries and tune indexes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 23, 2011 at 2:59 am

    #1385583

    Okay, Thanks

  • September 25, 2011 at 5:40 am

    #1386195

    Just a question, is SQL SErver the only service running on this machine? If so, give it all the memory it can take (leaving a couple of gb for the OS). It'll use it and you'll be happier for it.

    "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

Viewing 11 posts - 1 through 10 (of 10 total)

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