Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 4th, 2003, 11:03 AM
Ned Ned is offline
Authorized User
 
Join Date: Jun 2003
Location: Toronto, ON, Canada.
Posts: 66
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL memory management

Hi everyone,
SQL server 2000 is eating up the memory as we are executing queries. Looks like SQL allocates memory to each result set. Does anybody know how to refresh memory after each query execustion?
Thanks,
-Ned
__________________
-Ned
Reply With Quote
  #2 (permalink)  
Old June 4th, 2003, 06:26 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If you don't set a ceiling on memory usage SQL Server will use as much memory as is available. When it has aquired memory it will not release it, unless you drop the ceiling. This is by design and is not a problem. Just set a ceiling that leaves enough space for whatever else is running on the machine.

regards
David Cameron
Reply With Quote
  #3 (permalink)  
Old June 5th, 2003, 12:35 PM
Ned Ned is offline
Authorized User
 
Join Date: Jun 2003
Location: Toronto, ON, Canada.
Posts: 66
Thanks: 0
Thanked 0 Times in 0 Posts
Default

David,
Can you explain me how can I set & drop ceiling?

Thanx,
-Ned

Quote:
quote:Originally posted by David Cameron
 If you don't set a ceiling on memory usage SQL Server will use as much memory as is available. When it has aquired memory it will not release it, unless you drop the ceiling. This is by design and is not a problem. Just set a ceiling that leaves enough space for whatever else is running on the machine.

regards
David Cameron
-Ned
Reply With Quote
  #4 (permalink)  
Old June 5th, 2003, 02:21 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by Ned
 David,
Can you explain me how can I set & drop ceiling?
If I may drop in here...

Enterprise Manager, right click the server, then Properties, Memory tab.

You can adjust the minimum and maximum memory used by SQL Server on this tab.

SQL Server generally runs best with the 'Dynamically configure SQL Server memory' option selected. You should use the Fixed memory option only under special circumstances (like you know what you are doing!) as you can starve other server processes if you are not careful.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #5 (permalink)  
Old June 5th, 2003, 03:34 PM
Ned Ned is offline
Authorized User
 
Join Date: Jun 2003
Location: Toronto, ON, Canada.
Posts: 66
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Jeff,
This does not work in my case, I have already tried that.
Take the following scenario:
If you open the Task Manager, see sqlservr.exe Mem usage under Processes. Now open the SQL query analyzer and issue some SELECT statement and watch how the Mem Usage is increased, and it will keep increasing as you issue more different select statements.
Even though we fix memory.
Do you have any other solution to release the memory that SQL acquire for issued queries?

-Ned

Quote:
quote:Originally posted by JeffMason
 
Quote:
quote:Originally posted by Ned
Quote:
 David,
Can you explain me how can I set & drop ceiling?
If I may drop in here...

Enterprise Manager, right click the server, then Properties, Memory tab.

You can adjust the minimum and maximum memory used by SQL Server on this tab.

SQL Server generally runs best with the 'Dynamically configure SQL Server memory' option selected. You should use the Fixed memory option only under special circumstances (like you know what you are doing!) as you can starve other server processes if you are not careful.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #6 (permalink)  
Old June 5th, 2003, 05:08 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Overall memory management in SQL Server is complex. I suggest you read the topic "Dynamically Managing Memory on Windows NT and Windows 2000" in BOL.

Generally and simply speaking, SQL Server will consume just about as much memory as is physically available, stopping just short of having the system start paging. This memory is allocated on demand. As other processes require memory, it will begin to release memory back to the system as the system needs it.

The settings in EM only influence the size of the database buffer pool but SQL Server needs memory for lots of other things, like the query execution cache and buffering of the transaction log to name a couple. This usage cannot be individually contolled, I believe.

Why do you think you need to tighten the screws so much?


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #7 (permalink)  
Old June 6th, 2003, 10:04 AM
Ned Ned is offline
Authorized User
 
Join Date: Jun 2003
Location: Toronto, ON, Canada.
Posts: 66
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Jeff,
The reason I wanted to tight the screws, because some of my applications fails to load.
But I understand about SQL that this is the nature of the beast...
Thanks anyway,
-Ned

Quote:
quote:Originally posted by JeffMason
 Overall memory management in SQL Server is complex. I suggest you read the topic "Dynamically Managing Memory on Windows NT and Windows 2000" in BOL.

Generally and simply speaking, SQL Server will consume just about as much memory as is physically available, stopping just short of having the system start paging. This memory is allocated on demand. As other processes require memory, it will begin to release memory back to the system as the system needs it.

The settings in EM only influence the size of the database buffer pool but SQL Server needs memory for lots of other things, like the query execution cache and buffering of the transaction log to name a couple. This usage cannot be individually contolled, I believe.

Why do you think you need to tighten the screws so much?


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #8 (permalink)  
Old February 4th, 2005, 12:55 PM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to simakas
Default

i'm having the same problem. any new ideas?
i'm using simple connection assurance method by seleting 1 from database (select 1); though i can inform user about a connection loss. after a week of SW trial server exceeded memory... ...and programs started to halt. i tested select 1 with a rate of 1ms and server crashed after a few hours :(
Reply With Quote
  #9 (permalink)  
Old April 15th, 2005, 06:55 AM
Registered User
 
Join Date: Apr 2005
Location: , , USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am also having the same problem. The SQLserver process is steadily eating more and more memory even when there is no activity going on with the server. (overnight after everyone goes home)

Just some background...
Originally the memory settings were set on dynamic and the amount of memory for the SQLServer was 0 to max. After setting the Dynamic Memory settings to limit the amount of memory to less than max, I restarted the SQLServer so they would take effect.

Some of the other people were saying that if other processes need memory then SQLServer will relinquish memory, but this doesnt seem to be the case.

Anyone know why SQLServer would just continue to allocate memory for itself?????

Reply With Quote
  #10 (permalink)  
Old July 28th, 2005, 01:26 PM
Registered User
 
Join Date: Jul 2005
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Throughout this chain I see no answer to the underlying problem. That is, sql server does not release memory properly. So, can anyone respond with a way to have sql server release the memory when no longer needed, other than restarting the DB?

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Server Management Studio Error! everest SQL Server 2005 0 August 14th, 2008 12:13 PM
Custom Memory Management lacos C++ Programming 0 February 21st, 2008 03:56 AM
mysql memory management kamakshiganesh MySQL 0 September 24th, 2007 04:47 AM
FileUpload Memory Management Question dparsons ASP.NET 2.0 Professional 0 February 21st, 2007 10:22 AM
Cant Open SQL Management Studio sahmed1983 BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5 1 January 8th, 2007 02:47 PM



All times are GMT -4. The time now is 04:08 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.