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.
DavidThis is a multi-part message in MIME format.
--020409090600020109050001
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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/default.asp?url=/library/en-us/adminsql/ad_config_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
>
>
--020409090600020109050001
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>According to SQL BOL, the "min server memory" and "max server
memory" sp_configure settings <b>do not</b> require a restart in order
to take affect (see <a
href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3wmr.asp</a>). ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3wmr.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3wmr.asp</a>).
These are the 2 settings you'll need to change to set a fixed memory
size for SQL server.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
David C wrote:
<blockquote cite="mid%23MTGnEeHFHA.3612@.TK2MSFTNGP09.phx.gbl"
type="cite">
<pre wrap="">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
</pre>
</blockquote>
</body>
</html>
--020409090600020109050001--|||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/default.asp?url=/library/en-us/adminsql/ad_config_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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment