Post reply

MEMORY AND TEMP TABLES

  • September 26, 2012 at 3:23 pm

    #282307

    I am using global temp tables in my store proc due to the requirement...After I run series of proc's my system memory is used up 97%...I have i5 processor, with 8gb ram and over 300gb of storage on my dev system. After the proc's have been completed, it looks like the memory usages is still over 90% and does not go down until I restart my system or kill sql server services. what can be wrong? I reformatted my system with new instance of sql08R2, still the same problem. does temp tables use up memory and stay there?

    I am using cursors in my procs, but I am deallocating them.......

  • September 26, 2012 at 3:35 pm

    #1542630

    If you haven't explicitly limited SQL Server's memory allocation, it will take all it can getand keep it until it is forced to release it. Databases are supposed to work this way, keeping data in memory to reduce the disk IO needed to satisfy requests. You can set the server memory max as one of the Server properties through SSMS. If this is a machine that does other work than SQL Server, you might want to do that. If this machine only supports SQL Server, you might want to set it low enough to leave enough for whatever OS you're running, and forget it after that.

    And then again, I might be wrong ...
    David Webb

  • September 26, 2012 at 3:50 pm

    #1542640

    Hi David,

    Thx for the reply..I have sql server memory max to 2.0gb...the box use AWE to allocate memory is also unchecked.

  • September 26, 2012 at 4:00 pm

    #1542645

    So the problem is not that it's using up 90% of the system memory and not releasing it, the problem is that it's using up more than 2.0GB, as you have specified? Where are you confirming that SQL Server is, indeed, using up over 7GB of memory?

  • September 26, 2012 at 5:18 pm

    #1542652

    This only happens when I run sql server. Again its a fresh install and I have nothing else installed and or any other process running. I just did few selects and updates to test it out again. the physcial memory usage is still at the max level. its looks like its not deallocating.

    I believe this might be my issue:

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

    If anyone has any suggestions, please help.

    Thanks

  • September 26, 2012 at 5:29 pm

    #1542653

    Ok, to reiterate David's comments, the memory never really "deallocates" from usage. This is by design. When you first fire up SQL Server, the memory allocation will slowly grow to what you have established as the "maximum" value in the server properties. It will not deallocate "unused" memory unless you reset the instance. Again, this is by design. There is nothing "wrong" with this.

    That said, if you're seeing that SQL Server is using more memory than you have designated, that's something else entirely. If that is what you are truly referring to, have you restarted the SQL Server service since changing the maximum memory allocation in the server properties?

  • September 26, 2012 at 5:36 pm

    #1542655

    Max server memory settings take effect immediately, no restart necessary.

    Some stuff on memory - http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ chapter 3

    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 26, 2012 at 5:38 pm

    #1542657

    sql007 (9/26/2012)

    I believe this might be my issue:

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx%5B/quote%5D

    Jonathan's article is explaining that SQL doesn't have a memory leak.

    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 26, 2012 at 5:39 pm

    #1542658

    His lack of an issue is the issue. 🙂

  • September 26, 2012 at 5:47 pm

    #1542660

    the issue here is..........i have set it at max 2gb for sql server, so why is it still holding more memory? I unserstand thats by design that it holds memory, but why do they have this setting(max size) if it is no use... I have been dev for 6+ years, and never have come across with this issue........

  • September 26, 2012 at 5:51 pm

    #1542661

    Answer this question, please - how much memory do you believe SQL Server is using and where are you seeing this information?

  • September 27, 2012 at 2:53 am

    #1542754

    What does the following return? (exact output)

    SELECT value_in_use FROM sys.configurations AS c WHERE name = 'max server memory (MB)'

    What does the Total Server Memory perfmon counter show?

    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 27, 2012 at 9:11 am

    #1542965

    Hi Gail,

    It was set at 2048mb, but anyways did a fresh install of the win 7 OS/sql2008r2 with the updates and patches last night. now it listens....

  • September 27, 2012 at 10:19 am

    #1542991

    I don't think you're quite getting it.

Viewing 14 posts - 1 through 13 (of 13 total)

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