We are having 6GB of memory on SQL server. How do we ensure that SQL server
uses at least 5 GB of memory?
S/w detail
SQL 2000 with sp3
Windows 2003 (32 bit) no spThis is a multi-part message in MIME format.
--060402090907040201010607
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
boot.ini needs the /pae switch to access memory over 4GB (and /3gb
wouldn't hurt either in this case). You need to reboot for that switch
to take affect.
Also, you need to enable AWE memory for your SQL instance with
sp_configure. That is:
exec sp_configure 'awe enabled', 1
reconfigure
go
Then restart the SQL instance (with the Services Control Manager or NET
STOP/NET START). When you use AWE memory SQL Server won't swap out
pages to disk if you're running low on memory (like it does with dynamic
memory management when the memory is less than 4GB). So Microsoft
recommends to set an upper limit on SQL memory usage with the "max
server memory" sp_configure option. For example, to set a max limit of
5GB for the SQL instance (so you don't kill the O/S and other apps on
the box) you would execute this SQL statement:
exec sp_configure "max server memory" 5120
reconfigure
go
You may as well do the 2 sp_configure statements in the same batch and
then restart the SQL instance to save you restarting SQL twice (minimise
downtime). SQL Books Online has a fair bit of stuff on using AWE memory:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
It's a bit unusual to request that SQL uses "at least" 5GB. SQL will
consume the memory as it needs it (starting at < 100MB), caching data
that's accessed and query plans that get compiled. Why not just let it
do it's thing?
This is a very common question on the sqlserver newsgroups. Maybe I
should blog it (like about a hundred other people already have I'm sure).
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Amar wrote:
>We are having 6GB of memory on SQL server. How do we ensure that SQL server
>uses at least 5 GB of memory?
>S/w detail
>SQL 2000 with sp3
>Windows 2003 (32 bit) no sp
>
>
>
--060402090907040201010607
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>boot.ini needs the /pae switch to access memory over 4GB (and /3gb
wouldn't hurt either in this case). You need to reboot for that switch
to take affect.<br>
<br>
Also, you need to enable AWE memory for your SQL instance with
sp_configure. That is:<br>
</tt>
<blockquote><tt>exec sp_configure 'awe enabled', 1</tt><br>
<tt>reconfigure</tt><br>
<tt>go</tt><br>
</blockquote>
<tt>Then restart the SQL instance (with the Services Control Manager or
NET STOP/NET START). When you use AWE memory SQL Server won't swap out
pages to disk if you're running low on memory (like it does with
dynamic memory management when the memory is less than 4GB). So
Microsoft recommends to set an upper limit on SQL memory usage with the
"max server memory" sp_configure option. For example, to set a max
limit of 5GB for the SQL instance (so you don't kill the O/S and other
apps on the box) you would execute this SQL statement:<br>
</tt>
<blockquote><tt>exec sp_configure "max server memory" 5120</tt><br>
<tt>reconfigure</tt><br>
<tt>go</tt><br>
</blockquote>
<tt>You may as well do the 2 sp_configure statements in the same batch
and then restart the SQL instance to save you restarting SQL twice
(minimise downtime). SQL Books Online has a fair bit of stuff on using
AWE memory:<br>
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp</a><br>">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp</a><br>
<br>
It's a bit unusual to request that SQL uses "at least" 5GB. SQL will
consume the memory as it needs it (starting at < 100MB), caching
data that's accessed and query plans that get compiled. Why not just
let it do it's thing?<br>
<br>
This is a very common question on the sqlserver newsgroups. Maybe I
should blog it (like about a hundred other people already have I'm
sure).<br>
</tt>
<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"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Amar wrote:
<blockquote cite="mid6D049C2F-5D53-482C-B076-C0BA30770687@.microsoft.com"
type="cite">
<pre wrap="">We are having 6GB of memory on SQL server. How do we ensure that SQL server
uses at least 5 GB of memory?
S/w detail
SQL 2000 with sp3
Windows 2003 (32 bit) no sp
</pre>
</blockquote>
</body>
</html>
--060402090907040201010607--|||Hi
You need Enterprise Edition of SQL Server 2000 and Enterprise Edition of
Windows Server 2003
And read KB 274750
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750 on how to
configure it.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Amar" wrote:
> We are having 6GB of memory on SQL server. How do we ensure that SQL server
> uses at least 5 GB of memory?
> S/w detail
> SQL 2000 with sp3
> Windows 2003 (32 bit) no sp
>
>|||Thanks Mike for pointing towards right direction
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> You need Enterprise Edition of SQL Server 2000 and Enterprise Edition of
> Windows Server 2003
> And read KB 274750
> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750 on how to
> configure it.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Amar" wrote:
> > We are having 6GB of memory on SQL server. How do we ensure that SQL server
> > uses at least 5 GB of memory?
> >
> > S/w detail
> > SQL 2000 with sp3
> > Windows 2003 (32 bit) no sp
> >
> >
> >|||Thanks Mike. Great info. Yes a blog would be ver good.
Amar
"Mike Hodgson" wrote:
> boot.ini needs the /pae switch to access memory over 4GB (and /3gb
> wouldn't hurt either in this case). You need to reboot for that switch
> to take affect.
> Also, you need to enable AWE memory for your SQL instance with
> sp_configure. That is:
> exec sp_configure 'awe enabled', 1
> reconfigure
> go
> Then restart the SQL instance (with the Services Control Manager or NET
> STOP/NET START). When you use AWE memory SQL Server won't swap out
> pages to disk if you're running low on memory (like it does with dynamic
> memory management when the memory is less than 4GB). So Microsoft
> recommends to set an upper limit on SQL memory usage with the "max
> server memory" sp_configure option. For example, to set a max limit of
> 5GB for the SQL instance (so you don't kill the O/S and other apps on
> the box) you would execute this SQL statement:
> exec sp_configure "max server memory" 5120
> reconfigure
> go
> You may as well do the 2 sp_configure statements in the same batch and
> then restart the SQL instance to save you restarting SQL twice (minimise
> downtime). SQL Books Online has a fair bit of stuff on using AWE memory:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
> It's a bit unusual to request that SQL uses "at least" 5GB. SQL will
> consume the memory as it needs it (starting at < 100MB), caching data
> that's accessed and query plans that get compiled. Why not just let it
> do it's thing?
> This is a very common question on the sqlserver newsgroups. Maybe I
> should blog it (like about a hundred other people already have I'm sure).
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Amar wrote:
> >We are having 6GB of memory on SQL server. How do we ensure that SQL server
> >uses at least 5 GB of memory?
> >
> >S/w detail
> >SQL 2000 with sp3
> >Windows 2003 (32 bit) no sp
> >
> >
> >
> >
> >
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment