SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MEMORY AND TEMP TABLES


MEMORY AND TEMP TABLES

Author
Message
sql007
 sql007
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 234
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.......
David Webb-CDS
David Webb-CDS
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3980 Visits: 8586
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
sql007
 sql007
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 234
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.
TheGreenShepherd
TheGreenShepherd
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1730 Visits: 608
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?
sql007
 sql007
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 234
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
TheGreenShepherd
TheGreenShepherd
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1730 Visits: 608
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219301 Visits: 46279
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219301 Visits: 46279
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


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


TheGreenShepherd
TheGreenShepherd
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1730 Visits: 608
His lack of an issue is the issue. Smile
sql007
 sql007
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 234
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........
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search