Allocating memory

If I want to change SQL Server to use a fixed memory size, do I have to
stop/start SQL Server...or even the whole server to do so? Thanks.
DavidAccording to SQL BOL, the "min server memory" and "max server memory"
sp_configure settings *do not* require a restart in order to take affect
(see
http://msdn.microsoft.com/library/d... />
g_3wmr.asp).
These are the 2 settings you'll need to change to set a fixed memory
size for SQL server.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
David C wrote:

>If I want to change SQL Server to use a fixed memory size, do I have to
>stop/start SQL Server...or even the whole server to do so? Thanks.
>David
>
>|||Can I ask the reason for this change? While there certainly times when it
is appropriate setting it to a fixed size is rarely the correct thing to do
for best overall effeciency.
Andrew J. Kelly SQL MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:%23MTGnEeHFHA.3612@.TK2MSFTNGP09.phx.gbl...
> If I want to change SQL Server to use a fixed memory size, do I have to
> stop/start SQL Server...or even the whole server to do so? Thanks.
> David
>|||While setting the min and max server memory can be used to limit the memory
allocation SQL Server uses, it would still be in Dynamic Allocation Mode.
These two settings are only targets not fixed amounts. If you want to use a
fixed memory size, use the Set Working Set Size parameter. This is the ONLY
STATIC setting.
However, I would question why anyone would want to limit the DBMS from doing
its job, reducing the number of I/O operations by keeping the most
frequently used queries and data pages in memory...to the maximum allowable.
Sincerely,
Anthony Thomas
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:eaXxz9eHFHA.904@.tk2msftngp13.phx.gbl...
According to SQL BOL, the "min server memory" and "max server memory"
sp_configure settings do not require a restart in order to take affect (see
http://msdn.microsoft.com/library/d... />
g_3wmr.asp).
These are the 2 settings you'll need to change to set a fixed memory size
for SQL server.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
David C wrote:
If I want to change SQL Server to use a fixed memory size, do I have to
stop/start SQL Server...or even the whole server to do so? Thanks.
David|||One of our clients is using SBS 2003 and the Exchange "store" at times
is sucking up almost all of the RAM and (apparently) it is bringing SQL
to it's knees as customers SQL response literally stops.
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||I don't know that much about exchange and it's memory usage but is there a
way to limit Exchange instead? If you did set the Fixed memory size it
might depend some on which service is started first. You can try it and see
what happens but don't take too much or Exchange will crawl as well.
Andrew J. Kelly SQL MVP
"David" <daman@.biteme.com> wrote in message
news:e9mFphmHFHA.2648@.TK2MSFTNGP14.phx.gbl...
> One of our clients is using SBS 2003 and the Exchange "store" at times
> is sucking up almost all of the RAM and (apparently) it is bringing SQL
> to it's knees as customers SQL response literally stops.
> David
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||The server has 1.5GB RAM. What if I set the SQL memory to 512 (min) and
1536 (max)? Wouldn't that force SQL to have at least 512? Customer
could care less if mail is a little slow.
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||No that may not do what you want. The Min setting states that when SQL
Server grabs that much it will not release any below that amount back to the
OS. If Exchange gets the memory first then Sql Server may never reach the
MIN value. The only way to grab it and hold it right off the bat is to use
the Fixed amount. And if Exchange is that much of a hog it may never
release any back to Sql Server so the MAX setting would be useless.
Andrew J. Kelly SQL MVP
"David" <daman@.biteme.com> wrote in message
news:%23tbRt$nHFHA.576@.TK2MSFTNGP15.phx.gbl...
> The server has 1.5GB RAM. What if I set the SQL memory to 512 (min) and
> 1536 (max)? Wouldn't that force SQL to have at least 512? Customer
> could care less if mail is a little slow.
> David
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||It may be time for a call to Microsoft PSS to sort out your Exchange
issue. Doesn't sound like SQL is the problem here and Exchange has had
its share of issues over the past few years.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Andrew J. Kelly wrote:

>No that may not do what you want. The Min setting states that when SQL
>Server grabs that much it will not release any below that amount back to th
e
>OS. If Exchange gets the memory first then Sql Server may never reach the
>MIN value. The only way to grab it and hold it right off the bat is to use
>the Fixed amount. And if Exchange is that much of a hog it may never
>release any back to Sql Server so the MAX setting would be useless.
>
>