SQLServerCentral.com / SQL Server 2008 - General / SQL Server 2008 / MEMORY AND TEMP TABLES / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 20:25:03 GMT20RE: MEMORY AND TEMP TABLEShttp://www.sqlservercentral.com/Forums/Topic1364949-391-1.aspxI don't think you're quite getting it.Thu, 27 Sep 2012 10:19:10 GMTTheGreenShepherdRE: MEMORY AND TEMP TABLEShttp://www.sqlservercentral.com/Forums/Topic1364949-391-1.aspxHi 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....Thu, 27 Sep 2012 09:11:53 GMT sql007RE: MEMORY AND TEMP TABLEShttp://www.sqlservercentral.com/Forums/Topic1364949-391-1.aspxWhat does the following return? (exact output)[code="sql"]SELECT value_in_use FROM sys.configurations AS c WHERE name = 'max server memory (MB)'[/code]What does the Total Server Memory perfmon counter show?Thu, 27 Sep 2012 02:53:41 GMTGilaMonsterRE: MEMORY AND TEMP TABLEShttp://www.sqlservercentral.com/Forums/Topic1364949-391-1.aspxAnswer this question, please - how much memory do you believe SQL Server is using and where are you seeing this information?Wed, 26 Sep 2012 17:51:55 GMTTheGreenShepherdRE: MEMORY AND TEMP TABLEShttp://www.sqlservercentral.com/Forums/Topic1364949-391-1.aspxthe 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........Wed, 26 Sep 2012 17:47:02 GMT sql007RE: MEMORY AND TEMP TABLEShttp://www.sqlservercentral.com/Forums/Topic1364949-391-1.aspxHis lack of an issue is the issue. :)Wed, 26 Sep 2012 17:39:54 GMTTheGreenShepherdRE: MEMORY AND TEMP TABLEShttp://www.sqlservercentral.com/Forums/Topic1364949-391-1.aspx[quote][b] sql007 (9/26/2012)[/b][hr]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[/quote]Jonathan's article is explaining that SQL doesn't have a memory leak.Wed, 26 Sep 2012 17:38:57 GMTGilaMonsterRE: MEMORY AND TEMP TABLEShttp://www.sqlservercentral.com/Forums/Topic1364949-391-1.aspxMax server memory settings take effect immediately, no restart necessary.Some stuff on memory - [url]http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/[/url] chapter 3Wed, 26 Sep 2012 17:36:21 GMTGilaMonsterRE: MEMORY AND TEMP TABLEShttp://www.sqlservercentral.com/Forums/Topic1364949-391-1.aspxOk, 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?Wed, 26 Sep 2012 17:29:23 GMTTheGreenShepherdRE: MEMORY AND TEMP TABLEShttp://www.sqlservercentral.com/Forums/Topic1364949-391-1.aspxThis 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.aspxIf anyone has any suggestions, please help.ThanksWed, 26 Sep 2012 17:18:00 GMT sql007RE: MEMORY AND TEMP TABLEShttp://www.sqlservercentral.com/Forums/Topic1364949-391-1.aspxSo 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?Wed, 26 Sep 2012 16:00:14 GMTTheGreenShepherdRE: MEMORY AND TEMP TABLEShttp://www.sqlservercentral.com/Forums/Topic1364949-391-1.aspxHi David, Thx for the reply..I have sql server memory max to 2.0gb...the box use AWE to allocate memory is also unchecked.Wed, 26 Sep 2012 15:50:17 GMT sql007RE: MEMORY AND TEMP TABLEShttp://www.sqlservercentral.com/Forums/Topic1364949-391-1.aspxIf 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.Wed, 26 Sep 2012 15:35:42 GMTDavid Webb-CDSMEMORY AND TEMP TABLEShttp://www.sqlservercentral.com/Forums/Topic1364949-391-1.aspxI 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.......Wed, 26 Sep 2012 15:23:32 GMT sql007