Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

MEMORY AND TEMP TABLES Expand / Collapse
Author
Message
Posted Wednesday, September 26, 2012 3:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 83, Visits: 209
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.......
Post #1364949
Posted Wednesday, September 26, 2012 3:35 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:34 PM
Points: 901, Visits: 7,163
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
Post #1364953
Posted Wednesday, September 26, 2012 3:50 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 83, Visits: 209
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.
Post #1364963
Posted Wednesday, September 26, 2012 4:00 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:12 PM
Points: 567, Visits: 469
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?
Post #1364968
Posted Wednesday, September 26, 2012 5:18 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 83, Visits: 209
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
Post #1364977
Posted Wednesday, September 26, 2012 5:29 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:12 PM
Points: 567, Visits: 469
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?
Post #1364978
Posted Wednesday, September 26, 2012 5:36 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
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 2008, MVP
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

Post #1364980
Posted Wednesday, September 26, 2012 5:38 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
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 2008, MVP
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

Post #1364982
Posted Wednesday, September 26, 2012 5:39 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:12 PM
Points: 567, Visits: 469
His lack of an issue is the issue. :)
Post #1364983
Posted Wednesday, September 26, 2012 5:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 83, Visits: 209
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........
Post #1364985
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse