SQL Configure: 2gb

Showing posts with label 2gb. Show all posts

Friday, 17 February 2012

Configuring SQL Server Ent. running on 8Gb - suggestions please ?

Im running SQL Server Enterprise Edition on Windows 2000 Advanced.
The server has 4 CPUS and 8gb of physical RAM running a 2Gb swap. The
server is solely SQL Server apart from a Tivoli client (for backups
and remote control).
The SQL Server is running a large datawarehouse, along with other
smaller databases and although the /PAE paramter is set in the
boot.ini file, I found the AWE setting is currently disabled (although
max server memory is strangely set to 7670 MB.)
Currently acual memory usage is 1,682 MB, with about only a few
SQLServer page fault per second. Performance is dog slow, and whilst
paritally down to design and some missing indexes, but is also down to
disk access (mainly (as a result of Business Objects usage) on the 9Gb
tempdb I expect - how do I check?).
Having had a look at the server, I am about to enable AWE and was
thinking of setting max server memory to 6Gb, or 7Gb if I add /3GB
option to the boot.ini file.
Does anyone else agree with this setting? Should I add /3Gb flag to
maximise memory availability to MsSQL ?
What other memory tuning recommendations would someone suggest I
investigate ?
Many thanks for any help in advance,
J.
Database spaceused info follows;
--
Size 183 GB
Unallocated space -48 GB
Reserved 232 GB
Data 116 GB
Index size 22 GB
Unused 92 GBI would enable the following :
/3GB, /PAE and AWE setting.
Please refer to Performance Tuning Guide from MicroSoft Press for more
details.
Are the CPU's HT enabled which show up as 8 CPUs in the OS or are they 2CPU
which show as 4 CPUs in the OS ?
Gopi
"JE" <sudmill@.yahoo.co.uk> wrote in message
news:f240f848.0504180946.3ceb643c@.posting.google.com...
> Im running SQL Server Enterprise Edition on Windows 2000 Advanced.
> The server has 4 CPUS and 8gb of physical RAM running a 2Gb swap. The
> server is solely SQL Server apart from a Tivoli client (for backups
> and remote control).
> The SQL Server is running a large datawarehouse, along with other
> smaller databases and although the /PAE paramter is set in the
> boot.ini file, I found the AWE setting is currently disabled (although
> max server memory is strangely set to 7670 MB.)
> Currently acual memory usage is 1,682 MB, with about only a few
> SQLServer page fault per second. Performance is dog slow, and whilst
> paritally down to design and some missing indexes, but is also down to
> disk access (mainly (as a result of Business Objects usage) on the 9Gb
> tempdb I expect - how do I check?).
> Having had a look at the server, I am about to enable AWE and was
> thinking of setting max server memory to 6Gb, or 7Gb if I add /3GB
> option to the boot.ini file.
> Does anyone else agree with this setting? Should I add /3Gb flag to
> maximise memory availability to MsSQL ?
> What other memory tuning recommendations would someone suggest I
> investigate ?
> Many thanks for any help in advance,
> J.
> Database spaceused info follows;
> --
> Size 183 GB
> Unallocated space -48 GB
> Reserved 232 GB
> Data 116 GB
> Index size 22 GB
> Unused 92 GB|||/3GB and /PAE in boot.ini
AWE enabled. Fixed memory size 7GB to SQL Server.
If the processors are HT enabled, then MAXDOP = 4
Geoff N. Hiten
Microsoft SQL Server MVP.
"JE" <sudmill@.yahoo.co.uk> wrote in message
news:f240f848.0504180946.3ceb643c@.posting.google.com...
> Im running SQL Server Enterprise Edition on Windows 2000 Advanced.
> The server has 4 CPUS and 8gb of physical RAM running a 2Gb swap. The
> server is solely SQL Server apart from a Tivoli client (for backups
> and remote control).
> The SQL Server is running a large datawarehouse, along with other
> smaller databases and although the /PAE paramter is set in the
> boot.ini file, I found the AWE setting is currently disabled (although
> max server memory is strangely set to 7670 MB.)
> Currently acual memory usage is 1,682 MB, with about only a few
> SQLServer page fault per second. Performance is dog slow, and whilst
> paritally down to design and some missing indexes, but is also down to
> disk access (mainly (as a result of Business Objects usage) on the 9Gb
> tempdb I expect - how do I check?).
> Having had a look at the server, I am about to enable AWE and was
> thinking of setting max server memory to 6Gb, or 7Gb if I add /3GB
> option to the boot.ini file.
> Does anyone else agree with this setting? Should I add /3Gb flag to
> maximise memory availability to MsSQL ?
> What other memory tuning recommendations would someone suggest I
> investigate ?
> Many thanks for any help in advance,
> J.
> Database spaceused info follows;
> --
> Size 183 GB
> Unallocated space -48 GB
> Reserved 232 GB
> Data 116 GB
> Index size 22 GB
> Unused 92 GB|||Geoff,
Why would you recommend MAXDOP = 4 if the CPUs are HT enabled ?
Enterprise Edition is designed to handle 8 CPUs, right ?
Gopi
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:es%23N2AERFHA.2680@.TK2MSFTNGP09.phx.gbl...
> /3GB and /PAE in boot.ini
> AWE enabled. Fixed memory size 7GB to SQL Server.
> If the processors are HT enabled, then MAXDOP = 4
> Geoff N. Hiten
> Microsoft SQL Server MVP.
> "JE" <sudmill@.yahoo.co.uk> wrote in message
> news:f240f848.0504180946.3ceb643c@.posting.google.com...
>> Im running SQL Server Enterprise Edition on Windows 2000 Advanced.
>> The server has 4 CPUS and 8gb of physical RAM running a 2Gb swap. The
>> server is solely SQL Server apart from a Tivoli client (for backups
>> and remote control).
>> The SQL Server is running a large datawarehouse, along with other
>> smaller databases and although the /PAE paramter is set in the
>> boot.ini file, I found the AWE setting is currently disabled (although
>> max server memory is strangely set to 7670 MB.)
>> Currently acual memory usage is 1,682 MB, with about only a few
>> SQLServer page fault per second. Performance is dog slow, and whilst
>> paritally down to design and some missing indexes, but is also down to
>> disk access (mainly (as a result of Business Objects usage) on the 9Gb
>> tempdb I expect - how do I check?).
>> Having had a look at the server, I am about to enable AWE and was
>> thinking of setting max server memory to 6Gb, or 7Gb if I add /3GB
>> option to the boot.ini file.
>> Does anyone else agree with this setting? Should I add /3Gb flag to
>> maximise memory availability to MsSQL ?
>> What other memory tuning recommendations would someone suggest I
>> investigate ?
>> Many thanks for any help in advance,
>> J.
>> Database spaceused info follows;
>> --
>> Size 183 GB
>> Unallocated space -48 GB
>> Reserved 232 GB
>> Data 116 GB
>> Index size 22 GB
>> Unused 92 GB
>|||You will still use all processors, just not on a single query. Remember,
virtual processors aren't the same as physical processors. I have found
that you can over-parallelize a query in a HT environment. Setting MAXDOP =4 will prevent this from happening. Since SP2, SQL counts HT processors
correctly, regardless of what the OS shows.
GNH
"gopi" <rgopinath@.hotmail.com> wrote in message
news:enY8sPERFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Geoff,
> Why would you recommend MAXDOP = 4 if the CPUs are HT enabled ?
> Enterprise Edition is designed to handle 8 CPUs, right ?
> Gopi
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:es%23N2AERFHA.2680@.TK2MSFTNGP09.phx.gbl...
>> /3GB and /PAE in boot.ini
>> AWE enabled. Fixed memory size 7GB to SQL Server.
>> If the processors are HT enabled, then MAXDOP = 4
>> Geoff N. Hiten
>> Microsoft SQL Server MVP.
>> "JE" <sudmill@.yahoo.co.uk> wrote in message
>> news:f240f848.0504180946.3ceb643c@.posting.google.com...
>> Im running SQL Server Enterprise Edition on Windows 2000 Advanced.
>> The server has 4 CPUS and 8gb of physical RAM running a 2Gb swap. The
>> server is solely SQL Server apart from a Tivoli client (for backups
>> and remote control).
>> The SQL Server is running a large datawarehouse, along with other
>> smaller databases and although the /PAE paramter is set in the
>> boot.ini file, I found the AWE setting is currently disabled (although
>> max server memory is strangely set to 7670 MB.)
>> Currently acual memory usage is 1,682 MB, with about only a few
>> SQLServer page fault per second. Performance is dog slow, and whilst
>> paritally down to design and some missing indexes, but is also down to
>> disk access (mainly (as a result of Business Objects usage) on the 9Gb
>> tempdb I expect - how do I check?).
>> Having had a look at the server, I am about to enable AWE and was
>> thinking of setting max server memory to 6Gb, or 7Gb if I add /3GB
>> option to the boot.ini file.
>> Does anyone else agree with this setting? Should I add /3Gb flag to
>> maximise memory availability to MsSQL ?
>> What other memory tuning recommendations would someone suggest I
>> investigate ?
>> Many thanks for any help in advance,
>> J.
>> Database spaceused info follows;
>> --
>> Size 183 GB
>> Unallocated space -48 GB
>> Reserved 232 GB
>> Data 116 GB
>> Index size 22 GB
>> Unused 92 GB
>>
>|||Geoff / Gopi,
Thanks for your help - its very welcome (Im an Oracle dba and havent
done any perf tuning on mssql since 6.5 - why did deprectiate memusage,
it was so much better).
I can confirm we have 2 physical HT CPUs in the box, showing up as 4.
Should I change server setting MAXDOP to 2 ?
JE.|||I have found such a setting helpful on OLTP systems. If you are running a
DSS system with lots of long-running queries, I don't have any specific HT
advice.
GNH
<sudmill@.yahoo.co.uk> wrote in message
news:1113906166.664862.178330@.g14g2000cwa.googlegroups.com...
> Geoff / Gopi,
> Thanks for your help - its very welcome (Im an Oracle dba and havent
> done any perf tuning on mssql since 6.5 - why did deprectiate memusage,
> it was so much better).
> I can confirm we have 2 physical HT CPUs in the box, showing up as 4.
> Should I change server setting MAXDOP to 2 ?
> JE.
>|||Thanks for the help. I have added /3GB (/pae was already in there),
changed the max memory to 6300Mb and enabled AWE and then rebooted w2k
server and all was good up until I restartede SQL server for the second
time.
I now seem to have lost all SQL Server performance counters. There is
no disable in the registry(not that I expect there to be).
Any ideas anyone, please ?
JE|||Open a command prompt and go to the folder "C:\Program Files\Microsoft SQL
Server\MSSQL\Binn" or whatever the corresponding BINN folder is on your
system.
Type:
lodctr sqlctr.ini
You may have to reboot the system to get it to see the newly registered
counters.
Geoff N. Hiten
Microsoft SQL Server MVP.
<sudmill@.yahoo.co.uk> wrote in message
news:1114016578.470416.129050@.l41g2000cwc.googlegroups.com...
> Thanks for the help. I have added /3GB (/pae was already in there),
> changed the max memory to 6300Mb and enabled AWE and then rebooted w2k
> server and all was good up until I restartede SQL server for the second
> time.
> I now seem to have lost all SQL Server performance counters. There is
> no disable in the registry(not that I expect there to be).
> Any ideas anyone, please ?
> JE
>|||Thanks Geoff.
I made "lodctr sqlctr.ini" change and then had to reboot the server and
bingo - they appeared again.
Am I right in thinking that using AWE with SQL Server, memory is never
paged out for, even though the server option "Reserve physical memory
for SQL Server" is not ticked ?
JE.|||AWE enabled has the same effect of locking SQL in memory. AWE memory is
non-addressable and is used only as data cache. AWE memory is treated
similar to a paging file by the application/OS so it will not get paged out.
You only need a 2-3GB maximum paging file.
Note that Task Manager won't show correct memory usage for SQL server
anymore since AWE is on. You have to use the performance counters to see
actual memory usage.
Geoff N. Hiten
Microsoft SQL Server MVP.
<sudmill@.yahoo.co.uk> wrote in message
news:1114079763.849616.238380@.l41g2000cwc.googlegroups.com...
> Thanks Geoff.
> I made "lodctr sqlctr.ini" change and then had to reboot the server and
> bingo - they appeared again.
> Am I right in thinking that using AWE with SQL Server, memory is never
> paged out for, even though the server option "Reserve physical memory
> for SQL Server" is not ticked ?
> JE.
>|||Geoff,
Now I have the stats back I am monitoring the usage and the W2k Process
counters report a 127Mb paging file against the SQL Server process, but
as you said this probably isnt accurate.
The SQL Server:Memory Manager shows a more accurate picture however;
Maximum Workspace 1989Mb
Target Server Memory 6310Mb
Total Server Memory 6310Mb (which concurrs with what I set)
SQL Cache 1.5Mb
I can also see AWE lookups etc in the SQL Server:Buffer Manager.
Unsurprisingly, the buffer cache hit ratio is now in the healthy upper
99% range. Is this a useful indicator of performance in SQL Server (in
Oracle lock / waits are deemed more important tuning metric than buffer
hit ratios )?
Cheers,
JE.|||Geoff,
Now I have the stats back I am monitoring the usage and the W2k Process
counters report a 127Mb paging file against the SQL Server process, but
as you said this probably isnt accurate.
The SQL Server:Memory Manager shows a more accurate picture however;
Maximum Workspace 1989Mb
Target Server Memory 6310Mb
Total Server Memory 6310Mb (which concurrs with what I set)
SQL Cache 1.5Mb
I can also see AWE lookups etc in the SQL Server:Buffer Manager.
Unsurprisingly, the buffer cache hit ratio is now in the healthy upper
99% range. Is this a useful indicator of performance in SQL Server (in
Oracle lock / waits are deemed more important tuning metric than buffer
hit ratios )?
Cheers,
JE.|||Wait state analysis is an important tuning methodology in SQL as well.
Buffer cache hit ratio is semi-useless for large memory systems, especially
since read-ahead can drastically inflate the percentages. I prefer Page
Life Expectency as an indicator of overall cache health.
Geoff N. Hiten
Microsoft SQL Server MVP
<sudmill@.yahoo.co.uk> wrote in message
news:1114102891.499752.230360@.l41g2000cwc.googlegroups.com...
> Geoff,
> Now I have the stats back I am monitoring the usage and the W2k Process
> counters report a 127Mb paging file against the SQL Server process, but
> as you said this probably isnt accurate.
> The SQL Server:Memory Manager shows a more accurate picture however;
> Maximum Workspace 1989Mb
> Target Server Memory 6310Mb
> Total Server Memory 6310Mb (which concurrs with what I set)
> SQL Cache 1.5Mb
> I can also see AWE lookups etc in the SQL Server:Buffer Manager.
> Unsurprisingly, the buffer cache hit ratio is now in the healthy upper
> 99% range. Is this a useful indicator of performance in SQL Server (in
> Oracle lock / waits are deemed more important tuning metric than buffer
> hit ratios )?
> Cheers,
> JE.
>

Configuring SQL Server Ent. running on 8Gb - suggestions please ?

Sunday, 12 February 2012

Configuring and using PAE on SQS Server 2000 Enterprise Edition

We are running SQL Server 2000 Enterprise Edition on a Windows Server 2003
machine with 16 GB of memory. I am trying to use more than the default 2GB of
memory for SQL Server.
I have added the /PAE switch to the boot.ini file on the server, configured
SQL Server by issuing a
sp_configure 'show advanced options', 1
RECONFIGURE
and a
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 14336
RECONFIGURE
GO
However, it looks like SQL Server is till using less than 2GB of memory when
running. I have run the sp_configure check on it's own and noticed that the
config_value for AWE enabled is 1, but that the run_value is still 0.
Could that be the problem? What do I have to do to fix this?
I know I did restart the server after adding the settings (the setting for
'show advanced options' and 'max server memory' both show the same value for
config and run_value. It is just the AWE enable setting that has a
discrepancy.
Should I set the 'max server memory' to less than 14 GB if the server has
16GB available? How much does the OS need?
Thanks
FredHow to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
Managing AWE Memor
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
AMB
"fredscuba" wrote:
> We are running SQL Server 2000 Enterprise Edition on a Windows Server 2003
> machine with 16 GB of memory. I am trying to use more than the default 2GB of
> memory for SQL Server.
> I have added the /PAE switch to the boot.ini file on the server, configured
> SQL Server by issuing a
> sp_configure 'show advanced options', 1
> RECONFIGURE
> and a
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 14336
> RECONFIGURE
> GO
> However, it looks like SQL Server is till using less than 2GB of memory when
> running. I have run the sp_configure check on it's own and noticed that the
> config_value for AWE enabled is 1, but that the run_value is still 0.
> Could that be the problem? What do I have to do to fix this?
> I know I did restart the server after adding the settings (the setting for
> 'show advanced options' and 'max server memory' both show the same value for
> config and run_value. It is just the AWE enable setting that has a
> discrepancy.
> Should I set the 'max server memory' to less than 14 GB if the server has
> 16GB available? How much does the OS need?
> Thanks
> Fred|||Alejandro,
I did go through the instructions that are shown on the pages you bookmarked
(see my original post).
My question is: why is SQL Server still using less than 2GB of memory even
after I have setup my boot.ini and enabled AWE and set the max server
memory?
Fred
"Alejandro Mesa" wrote:
> How to configure memory for more than 2 GB in SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
> Managing AWE Memory
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
>
> AMB
>
> "fredscuba" wrote:
> > We are running SQL Server 2000 Enterprise Edition on a Windows Server 2003
> > machine with 16 GB of memory. I am trying to use more than the default 2GB of
> > memory for SQL Server.
> >
> > I have added the /PAE switch to the boot.ini file on the server, configured
> > SQL Server by issuing a
> >
> > sp_configure 'show advanced options', 1
> > RECONFIGURE
> >
> > and a
> >
> > sp_configure 'awe enabled', 1
> > RECONFIGURE
> > GO
> > sp_configure 'max server memory', 14336
> > RECONFIGURE
> > GO
> >
> > However, it looks like SQL Server is till using less than 2GB of memory when
> > running. I have run the sp_configure check on it's own and noticed that the
> > config_value for AWE enabled is 1, but that the run_value is still 0.
> >
> > Could that be the problem? What do I have to do to fix this?
> >
> > I know I did restart the server after adding the settings (the setting for
> > 'show advanced options' and 'max server memory' both show the same value for
> > config and run_value. It is just the AWE enable setting that has a
> > discrepancy.
> >
> > Should I set the 'max server memory' to less than 14 GB if the server has
> > 16GB available? How much does the OS need?
> >
> > Thanks
> >
> > Fred|||What Edition of Windows 2003 Server?
AMB
"fredscuba" wrote:
> Alejandro,
> I did go through the instructions that are shown on the pages you bookmarked
> (see my original post).
> My question is: why is SQL Server still using less than 2GB of memory even
> after I have setup my boot.ini and enabled AWE and set the max server
> memory?
> Fred
>
> "Alejandro Mesa" wrote:
> > How to configure memory for more than 2 GB in SQL Server
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
> >
> > Managing AWE Memory
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
> >
> >
> > AMB
> >
> >
> > "fredscuba" wrote:
> >
> > > We are running SQL Server 2000 Enterprise Edition on a Windows Server 2003
> > > machine with 16 GB of memory. I am trying to use more than the default 2GB of
> > > memory for SQL Server.
> > >
> > > I have added the /PAE switch to the boot.ini file on the server, configured
> > > SQL Server by issuing a
> > >
> > > sp_configure 'show advanced options', 1
> > > RECONFIGURE
> > >
> > > and a
> > >
> > > sp_configure 'awe enabled', 1
> > > RECONFIGURE
> > > GO
> > > sp_configure 'max server memory', 14336
> > > RECONFIGURE
> > > GO
> > >
> > > However, it looks like SQL Server is till using less than 2GB of memory when
> > > running. I have run the sp_configure check on it's own and noticed that the
> > > config_value for AWE enabled is 1, but that the run_value is still 0.
> > >
> > > Could that be the problem? What do I have to do to fix this?
> > >
> > > I know I did restart the server after adding the settings (the setting for
> > > 'show advanced options' and 'max server memory' both show the same value for
> > > config and run_value. It is just the AWE enable setting that has a
> > > discrepancy.
> > >
> > > Should I set the 'max server memory' to less than 14 GB if the server has
> > > 16GB available? How much does the OS need?
> > >
> > > Thanks
> > >
> > > Fred|||Are you sure it is Enterprise Edition and not Std? What Edition is the OS?
What does @.@.VERSION or xp_MSVer show? After you get it going I would start
out with 15 or 15.5GB instead of 16 just to make sure the OS has plenty and
there is a little room to spare for other apps.
--
Andrew J. Kelly SQL MVP
"fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
news:6007E2E5-304A-4C53-9A29-E061F3E439BB@.microsoft.com...
> Alejandro,
> I did go through the instructions that are shown on the pages you
> bookmarked
> (see my original post).
> My question is: why is SQL Server still using less than 2GB of memory even
> after I have setup my boot.ini and enabled AWE and set the max server
> memory?
> Fred
>
> "Alejandro Mesa" wrote:
>> How to configure memory for more than 2 GB in SQL Server
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>> Managing AWE Memory
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
>>
>> AMB
>>
>> "fredscuba" wrote:
>> > We are running SQL Server 2000 Enterprise Edition on a Windows Server
>> > 2003
>> > machine with 16 GB of memory. I am trying to use more than the default
>> > 2GB of
>> > memory for SQL Server.
>> >
>> > I have added the /PAE switch to the boot.ini file on the server,
>> > configured
>> > SQL Server by issuing a
>> >
>> > sp_configure 'show advanced options', 1
>> > RECONFIGURE
>> >
>> > and a
>> >
>> > sp_configure 'awe enabled', 1
>> > RECONFIGURE
>> > GO
>> > sp_configure 'max server memory', 14336
>> > RECONFIGURE
>> > GO
>> >
>> > However, it looks like SQL Server is till using less than 2GB of memory
>> > when
>> > running. I have run the sp_configure check on it's own and noticed that
>> > the
>> > config_value for AWE enabled is 1, but that the run_value is still 0.
>> >
>> > Could that be the problem? What do I have to do to fix this?
>> >
>> > I know I did restart the server after adding the settings (the setting
>> > for
>> > 'show advanced options' and 'max server memory' both show the same
>> > value for
>> > config and run_value. It is just the AWE enable setting that has a
>> > discrepancy.
>> >
>> > Should I set the 'max server memory' to less than 14 GB if the server
>> > has
>> > 16GB available? How much does the OS need?
>> >
>> > Thanks
>> >
>> > Fred|||Andrew & Alejandro,
I am running Server 2003 Enterprise Edition and SQL Server 2000 Enterprise
Edition.
I think the problem is that the config_value for 'AWE enabled' is 1, but
that the run_value is still 0.
Why would that be? What do I have to do to fix this?
I am fairly sure that I restarted the server after changing the AWE setting,
but I can do it again after hours (it is a production server).
Is there any reason why the 'AWE enabled' setting would stay at a run_value
of 0, even after I try to set it with:
sp_configure 'awe enabled', 1
RECONFIGURE
Fred
"Andrew J. Kelly" wrote:
> Are you sure it is Enterprise Edition and not Std? What Edition is the OS?
> What does @.@.VERSION or xp_MSVer show? After you get it going I would start
> out with 15 or 15.5GB instead of 16 just to make sure the OS has plenty and
> there is a little room to spare for other apps.
> --
> Andrew J. Kelly SQL MVP
>
> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> news:6007E2E5-304A-4C53-9A29-E061F3E439BB@.microsoft.com...
> > Alejandro,
> >
> > I did go through the instructions that are shown on the pages you
> > bookmarked
> > (see my original post).
> >
> > My question is: why is SQL Server still using less than 2GB of memory even
> > after I have setup my boot.ini and enabled AWE and set the max server
> > memory?
> >
> > Fred
> >
> >
> > "Alejandro Mesa" wrote:
> >
> >> How to configure memory for more than 2 GB in SQL Server
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
> >>
> >> Managing AWE Memory
> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
> >>
> >>
> >> AMB
> >>
> >>
> >> "fredscuba" wrote:
> >>
> >> > We are running SQL Server 2000 Enterprise Edition on a Windows Server
> >> > 2003
> >> > machine with 16 GB of memory. I am trying to use more than the default
> >> > 2GB of
> >> > memory for SQL Server.
> >> >
> >> > I have added the /PAE switch to the boot.ini file on the server,
> >> > configured
> >> > SQL Server by issuing a
> >> >
> >> > sp_configure 'show advanced options', 1
> >> > RECONFIGURE
> >> >
> >> > and a
> >> >
> >> > sp_configure 'awe enabled', 1
> >> > RECONFIGURE
> >> > GO
> >> > sp_configure 'max server memory', 14336
> >> > RECONFIGURE
> >> > GO
> >> >
> >> > However, it looks like SQL Server is till using less than 2GB of memory
> >> > when
> >> > running. I have run the sp_configure check on it's own and noticed that
> >> > the
> >> > config_value for AWE enabled is 1, but that the run_value is still 0.
> >> >
> >> > Could that be the problem? What do I have to do to fix this?
> >> >
> >> > I know I did restart the server after adding the settings (the setting
> >> > for
> >> > 'show advanced options' and 'max server memory' both show the same
> >> > value for
> >> > config and run_value. It is just the AWE enable setting that has a
> >> > discrepancy.
> >> >
> >> > Should I set the 'max server memory' to less than 14 GB if the server
> >> > has
> >> > 16GB available? How much does the OS need?
> >> >
> >> > Thanks
> >> >
> >> > Fred
>
>|||The most obvious reason would be that you didn't actually restart SQL
Server. I would try that and see if it fixes it.
--
Andrew J. Kelly SQL MVP
"fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
news:27D53CE0-A064-4B3A-83CA-25293115FA99@.microsoft.com...
> Andrew & Alejandro,
> I am running Server 2003 Enterprise Edition and SQL Server 2000 Enterprise
> Edition.
> I think the problem is that the config_value for 'AWE enabled' is 1, but
> that the run_value is still 0.
> Why would that be? What do I have to do to fix this?
> I am fairly sure that I restarted the server after changing the AWE
> setting,
> but I can do it again after hours (it is a production server).
> Is there any reason why the 'AWE enabled' setting would stay at a
> run_value
> of 0, even after I try to set it with:
> sp_configure 'awe enabled', 1
> RECONFIGURE
>
> Fred
>
> "Andrew J. Kelly" wrote:
>> Are you sure it is Enterprise Edition and not Std? What Edition is the
>> OS?
>> What does @.@.VERSION or xp_MSVer show? After you get it going I would
>> start
>> out with 15 or 15.5GB instead of 16 just to make sure the OS has plenty
>> and
>> there is a little room to spare for other apps.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
>> news:6007E2E5-304A-4C53-9A29-E061F3E439BB@.microsoft.com...
>> > Alejandro,
>> >
>> > I did go through the instructions that are shown on the pages you
>> > bookmarked
>> > (see my original post).
>> >
>> > My question is: why is SQL Server still using less than 2GB of memory
>> > even
>> > after I have setup my boot.ini and enabled AWE and set the max server
>> > memory?
>> >
>> > Fred
>> >
>> >
>> > "Alejandro Mesa" wrote:
>> >
>> >> How to configure memory for more than 2 GB in SQL Server
>> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>> >>
>> >> Managing AWE Memory
>> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
>> >>
>> >>
>> >> AMB
>> >>
>> >>
>> >> "fredscuba" wrote:
>> >>
>> >> > We are running SQL Server 2000 Enterprise Edition on a Windows
>> >> > Server
>> >> > 2003
>> >> > machine with 16 GB of memory. I am trying to use more than the
>> >> > default
>> >> > 2GB of
>> >> > memory for SQL Server.
>> >> >
>> >> > I have added the /PAE switch to the boot.ini file on the server,
>> >> > configured
>> >> > SQL Server by issuing a
>> >> >
>> >> > sp_configure 'show advanced options', 1
>> >> > RECONFIGURE
>> >> >
>> >> > and a
>> >> >
>> >> > sp_configure 'awe enabled', 1
>> >> > RECONFIGURE
>> >> > GO
>> >> > sp_configure 'max server memory', 14336
>> >> > RECONFIGURE
>> >> > GO
>> >> >
>> >> > However, it looks like SQL Server is till using less than 2GB of
>> >> > memory
>> >> > when
>> >> > running. I have run the sp_configure check on it's own and noticed
>> >> > that
>> >> > the
>> >> > config_value for AWE enabled is 1, but that the run_value is still
>> >> > 0.
>> >> >
>> >> > Could that be the problem? What do I have to do to fix this?
>> >> >
>> >> > I know I did restart the server after adding the settings (the
>> >> > setting
>> >> > for
>> >> > 'show advanced options' and 'max server memory' both show the same
>> >> > value for
>> >> > config and run_value. It is just the AWE enable setting that has a
>> >> > discrepancy.
>> >> >
>> >> > Should I set the 'max server memory' to less than 14 GB if the
>> >> > server
>> >> > has
>> >> > 16GB available? How much does the OS need?
>> >> >
>> >> > Thanks
>> >> >
>> >> > Fred
>>|||Hi,
Apologies if this has already been checked, but have you looked in the SQL
Server log for an entry of 'Address Windowing Extensions enabled' to clarify
if AWE is enabled or not?
--
Regards
Robert
"fredscuba" wrote:
> Andrew & Alejandro,
> I am running Server 2003 Enterprise Edition and SQL Server 2000 Enterprise
> Edition.
> I think the problem is that the config_value for 'AWE enabled' is 1, but
> that the run_value is still 0.
> Why would that be? What do I have to do to fix this?
> I am fairly sure that I restarted the server after changing the AWE setting,
> but I can do it again after hours (it is a production server).
> Is there any reason why the 'AWE enabled' setting would stay at a run_value
> of 0, even after I try to set it with:
> sp_configure 'awe enabled', 1
> RECONFIGURE
>
> Fred
>
> "Andrew J. Kelly" wrote:
> > Are you sure it is Enterprise Edition and not Std? What Edition is the OS?
> > What does @.@.VERSION or xp_MSVer show? After you get it going I would start
> > out with 15 or 15.5GB instead of 16 just to make sure the OS has plenty and
> > there is a little room to spare for other apps.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> > news:6007E2E5-304A-4C53-9A29-E061F3E439BB@.microsoft.com...
> > > Alejandro,
> > >
> > > I did go through the instructions that are shown on the pages you
> > > bookmarked
> > > (see my original post).
> > >
> > > My question is: why is SQL Server still using less than 2GB of memory even
> > > after I have setup my boot.ini and enabled AWE and set the max server
> > > memory?
> > >
> > > Fred
> > >
> > >
> > > "Alejandro Mesa" wrote:
> > >
> > >> How to configure memory for more than 2 GB in SQL Server
> > >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
> > >>
> > >> Managing AWE Memory
> > >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
> > >>
> > >>
> > >> AMB
> > >>
> > >>
> > >> "fredscuba" wrote:
> > >>
> > >> > We are running SQL Server 2000 Enterprise Edition on a Windows Server
> > >> > 2003
> > >> > machine with 16 GB of memory. I am trying to use more than the default
> > >> > 2GB of
> > >> > memory for SQL Server.
> > >> >
> > >> > I have added the /PAE switch to the boot.ini file on the server,
> > >> > configured
> > >> > SQL Server by issuing a
> > >> >
> > >> > sp_configure 'show advanced options', 1
> > >> > RECONFIGURE
> > >> >
> > >> > and a
> > >> >
> > >> > sp_configure 'awe enabled', 1
> > >> > RECONFIGURE
> > >> > GO
> > >> > sp_configure 'max server memory', 14336
> > >> > RECONFIGURE
> > >> > GO
> > >> >
> > >> > However, it looks like SQL Server is till using less than 2GB of memory
> > >> > when
> > >> > running. I have run the sp_configure check on it's own and noticed that
> > >> > the
> > >> > config_value for AWE enabled is 1, but that the run_value is still 0.
> > >> >
> > >> > Could that be the problem? What do I have to do to fix this?
> > >> >
> > >> > I know I did restart the server after adding the settings (the setting
> > >> > for
> > >> > 'show advanced options' and 'max server memory' both show the same
> > >> > value for
> > >> > config and run_value. It is just the AWE enable setting that has a
> > >> > discrepancy.
> > >> >
> > >> > Should I set the 'max server memory' to less than 14 GB if the server
> > >> > has
> > >> > 16GB available? How much does the OS need?
> > >> >
> > >> > Thanks
> > >> >
> > >> > Fred
> >
> >
> >|||And check to make sure the account sql server is running under has Lock
Pages In Memory rights.
--
Andrew J. Kelly SQL MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u2cLo5lhFHA.576@.TK2MSFTNGP15.phx.gbl...
> The most obvious reason would be that you didn't actually restart SQL
> Server. I would try that and see if it fixes it.
> --
> Andrew J. Kelly SQL MVP
>
> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> news:27D53CE0-A064-4B3A-83CA-25293115FA99@.microsoft.com...
>> Andrew & Alejandro,
>> I am running Server 2003 Enterprise Edition and SQL Server 2000
>> Enterprise
>> Edition.
>> I think the problem is that the config_value for 'AWE enabled' is 1, but
>> that the run_value is still 0.
>> Why would that be? What do I have to do to fix this?
>> I am fairly sure that I restarted the server after changing the AWE
>> setting,
>> but I can do it again after hours (it is a production server).
>> Is there any reason why the 'AWE enabled' setting would stay at a
>> run_value
>> of 0, even after I try to set it with:
>> sp_configure 'awe enabled', 1
>> RECONFIGURE
>>
>> Fred
>>
>> "Andrew J. Kelly" wrote:
>> Are you sure it is Enterprise Edition and not Std? What Edition is the
>> OS?
>> What does @.@.VERSION or xp_MSVer show? After you get it going I would
>> start
>> out with 15 or 15.5GB instead of 16 just to make sure the OS has plenty
>> and
>> there is a little room to spare for other apps.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
>> news:6007E2E5-304A-4C53-9A29-E061F3E439BB@.microsoft.com...
>> > Alejandro,
>> >
>> > I did go through the instructions that are shown on the pages you
>> > bookmarked
>> > (see my original post).
>> >
>> > My question is: why is SQL Server still using less than 2GB of memory
>> > even
>> > after I have setup my boot.ini and enabled AWE and set the max server
>> > memory?
>> >
>> > Fred
>> >
>> >
>> > "Alejandro Mesa" wrote:
>> >
>> >> How to configure memory for more than 2 GB in SQL Server
>> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>> >>
>> >> Managing AWE Memory
>> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
>> >>
>> >>
>> >> AMB
>> >>
>> >>
>> >> "fredscuba" wrote:
>> >>
>> >> > We are running SQL Server 2000 Enterprise Edition on a Windows
>> >> > Server
>> >> > 2003
>> >> > machine with 16 GB of memory. I am trying to use more than the
>> >> > default
>> >> > 2GB of
>> >> > memory for SQL Server.
>> >> >
>> >> > I have added the /PAE switch to the boot.ini file on the server,
>> >> > configured
>> >> > SQL Server by issuing a
>> >> >
>> >> > sp_configure 'show advanced options', 1
>> >> > RECONFIGURE
>> >> >
>> >> > and a
>> >> >
>> >> > sp_configure 'awe enabled', 1
>> >> > RECONFIGURE
>> >> > GO
>> >> > sp_configure 'max server memory', 14336
>> >> > RECONFIGURE
>> >> > GO
>> >> >
>> >> > However, it looks like SQL Server is till using less than 2GB of
>> >> > memory
>> >> > when
>> >> > running. I have run the sp_configure check on it's own and noticed
>> >> > that
>> >> > the
>> >> > config_value for AWE enabled is 1, but that the run_value is still
>> >> > 0.
>> >> >
>> >> > Could that be the problem? What do I have to do to fix this?
>> >> >
>> >> > I know I did restart the server after adding the settings (the
>> >> > setting
>> >> > for
>> >> > 'show advanced options' and 'max server memory' both show the same
>> >> > value for
>> >> > config and run_value. It is just the AWE enable setting that has a
>> >> > discrepancy.
>> >> >
>> >> > Should I set the 'max server memory' to less than 14 GB if the
>> >> > server
>> >> > has
>> >> > 16GB available? How much does the OS need?
>> >> >
>> >> > Thanks
>> >> >
>> >> > Fred
>>
>|||Hi
Before you set AWE you need to set "Lock Pages In Memory" first.
How to set the "Lock Pages In Memory", Please see URL
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_servp_2vqh.asp
By default SQL Seerver 2000 in Windows 2003 is disable.
Goog luck
Bogi Wu
"fredscuba" wrote:
> We are running SQL Server 2000 Enterprise Edition on a Windows Server 2003
> machine with 16 GB of memory. I am trying to use more than the default 2GB of
> memory for SQL Server.
> I have added the /PAE switch to the boot.ini file on the server, configured
> SQL Server by issuing a
> sp_configure 'show advanced options', 1
> RECONFIGURE
> and a
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 14336
> RECONFIGURE
> GO
> However, it looks like SQL Server is till using less than 2GB of memory when
> running. I have run the sp_configure check on it's own and noticed that the
> config_value for AWE enabled is 1, but that the run_value is still 0.
> Could that be the problem? What do I have to do to fix this?
> I know I did restart the server after adding the settings (the setting for
> 'show advanced options' and 'max server memory' both show the same value for
> config and run_value. It is just the AWE enable setting that has a
> discrepancy.
> Should I set the 'max server memory' to less than 14 GB if the server has
> 16GB available? How much does the OS need?
> Thanks
> Fred|||OK, I did reboot the server and afterwards I could verify that the run_value
for 'AWE enabled' is now 1.
The 'min server memory' is set to 1024 (1 GB) and the 'max server memory'
is set to 10240 (10GB). The server has 16GB of memory, but I will be sharing
that with the OS and with a SAS application, so I thought that 10GB for SQL
Server is a good number to start with.
*HOWEVER* when I check the memory utilization on the server through the Task
Manager, it now shows that the process sqlservr.exe is only using 141,164 K
of memory - only about 138 MB!!
Why would this be? Could this be correct? The server is being used pretty
heavily right now - it is not a transaction heavy load, more a batch type
load with millions of records being updated in a batch.
My impression was that if AWE is enabled and PAE is used, that the memory
allocation is not dynamic anymore, but static, according to what you specify
in the max server memory setting.
Why would it then show such a low memory utilization by SQL Server? (It used
to hover around 1.7 GB before I enabled PAE and AWE).
Fred
"Andrew J. Kelly" wrote:
> And check to make sure the account sql server is running under has Lock
> Pages In Memory rights.
> --
> Andrew J. Kelly SQL MVP
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u2cLo5lhFHA.576@.TK2MSFTNGP15.phx.gbl...
> > The most obvious reason would be that you didn't actually restart SQL
> > Server. I would try that and see if it fixes it.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> > news:27D53CE0-A064-4B3A-83CA-25293115FA99@.microsoft.com...
> >> Andrew & Alejandro,
> >>
> >> I am running Server 2003 Enterprise Edition and SQL Server 2000
> >> Enterprise
> >> Edition.
> >>
> >> I think the problem is that the config_value for 'AWE enabled' is 1, but
> >> that the run_value is still 0.
> >>
> >> Why would that be? What do I have to do to fix this?
> >>
> >> I am fairly sure that I restarted the server after changing the AWE
> >> setting,
> >> but I can do it again after hours (it is a production server).
> >>
> >> Is there any reason why the 'AWE enabled' setting would stay at a
> >> run_value
> >> of 0, even after I try to set it with:
> >>
> >> sp_configure 'awe enabled', 1
> >> RECONFIGURE
> >>
> >>
> >> Fred
> >>
> >>
> >> "Andrew J. Kelly" wrote:
> >>
> >> Are you sure it is Enterprise Edition and not Std? What Edition is the
> >> OS?
> >> What does @.@.VERSION or xp_MSVer show? After you get it going I would
> >> start
> >> out with 15 or 15.5GB instead of 16 just to make sure the OS has plenty
> >> and
> >> there is a little room to spare for other apps.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> >> news:6007E2E5-304A-4C53-9A29-E061F3E439BB@.microsoft.com...
> >> > Alejandro,
> >> >
> >> > I did go through the instructions that are shown on the pages you
> >> > bookmarked
> >> > (see my original post).
> >> >
> >> > My question is: why is SQL Server still using less than 2GB of memory
> >> > even
> >> > after I have setup my boot.ini and enabled AWE and set the max server
> >> > memory?
> >> >
> >> > Fred
> >> >
> >> >
> >> > "Alejandro Mesa" wrote:
> >> >
> >> >> How to configure memory for more than 2 GB in SQL Server
> >> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
> >> >>
> >> >> Managing AWE Memory
> >> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
> >> >>
> >> >>
> >> >> AMB
> >> >>
> >> >>
> >> >> "fredscuba" wrote:
> >> >>
> >> >> > We are running SQL Server 2000 Enterprise Edition on a Windows
> >> >> > Server
> >> >> > 2003
> >> >> > machine with 16 GB of memory. I am trying to use more than the
> >> >> > default
> >> >> > 2GB of
> >> >> > memory for SQL Server.
> >> >> >
> >> >> > I have added the /PAE switch to the boot.ini file on the server,
> >> >> > configured
> >> >> > SQL Server by issuing a
> >> >> >
> >> >> > sp_configure 'show advanced options', 1
> >> >> > RECONFIGURE
> >> >> >
> >> >> > and a
> >> >> >
> >> >> > sp_configure 'awe enabled', 1
> >> >> > RECONFIGURE
> >> >> > GO
> >> >> > sp_configure 'max server memory', 14336
> >> >> > RECONFIGURE
> >> >> > GO
> >> >> >
> >> >> > However, it looks like SQL Server is till using less than 2GB of
> >> >> > memory
> >> >> > when
> >> >> > running. I have run the sp_configure check on it's own and noticed
> >> >> > that
> >> >> > the
> >> >> > config_value for AWE enabled is 1, but that the run_value is still
> >> >> > 0.
> >> >> >
> >> >> > Could that be the problem? What do I have to do to fix this?
> >> >> >
> >> >> > I know I did restart the server after adding the settings (the
> >> >> > setting
> >> >> > for
> >> >> > 'show advanced options' and 'max server memory' both show the same
> >> >> > value for
> >> >> > config and run_value. It is just the AWE enable setting that has a
> >> >> > discrepancy.
> >> >> >
> >> >> > Should I set the 'max server memory' to less than 14 GB if the
> >> >> > server
> >> >> > has
> >> >> > 16GB available? How much does the OS need?
> >> >> >
> >> >> > Thanks
> >> >> >
> >> >> > Fred
> >>
> >>
> >>
> >
> >
>
>|||Hi,
I followed the link you provided, but there is a note saying that "This
functionality is available only if you are running the MicrosoftÂ® WindowsÂ®
2000 operating system".
Can I or should I do it if I am running on Windows Server 2003?
Fred
"BogiWu" wrote:
> Hi
> Before you set AWE you need to set "Lock Pages In Memory" first.
> How to set the "Lock Pages In Memory", Please see URL
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_servp_2vqh.asp
> By default SQL Seerver 2000 in Windows 2003 is disable.
> Goog luck
> Bogi Wu
> "fredscuba" wrote:
> > We are running SQL Server 2000 Enterprise Edition on a Windows Server 2003
> > machine with 16 GB of memory. I am trying to use more than the default 2GB of
> > memory for SQL Server.
> >
> > I have added the /PAE switch to the boot.ini file on the server, configured
> > SQL Server by issuing a
> >
> > sp_configure 'show advanced options', 1
> > RECONFIGURE
> >
> > and a
> >
> > sp_configure 'awe enabled', 1
> > RECONFIGURE
> > GO
> > sp_configure 'max server memory', 14336
> > RECONFIGURE
> > GO
> >
> > However, it looks like SQL Server is till using less than 2GB of memory when
> > running. I have run the sp_configure check on it's own and noticed that the
> > config_value for AWE enabled is 1, but that the run_value is still 0.
> >
> > Could that be the problem? What do I have to do to fix this?
> >
> > I know I did restart the server after adding the settings (the setting for
> > 'show advanced options' and 'max server memory' both show the same value for
> > config and run_value. It is just the AWE enable setting that has a
> > discrepancy.
> >
> > Should I set the 'max server memory' to less than 14 GB if the server has
> > 16GB available? How much does the OS need?
> >
> > Thanks
> >
> > Fred|||Never mind - the account running SQL Server already has the ability to lock
pages in memory.
My problem now is that SQL Server does not seem to be using all of the
memory I set aside for it under the max server memory setting. (see my other
post from today)
Fred
"fredscuba" wrote:
> Hi,
> I followed the link you provided, but there is a note saying that "This
> functionality is available only if you are running the MicrosoftÂ® WindowsÂ®
> 2000 operating system".
> Can I or should I do it if I am running on Windows Server 2003?
>
> Fred
> "BogiWu" wrote:
> > Hi
> > Before you set AWE you need to set "Lock Pages In Memory" first.
> > How to set the "Lock Pages In Memory", Please see URL
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_servp_2vqh.asp
> > By default SQL Seerver 2000 in Windows 2003 is disable.
> >
> > Goog luck
> >
> > Bogi Wu
> > "fredscuba" wrote:
> >
> > > We are running SQL Server 2000 Enterprise Edition on a Windows Server 2003
> > > machine with 16 GB of memory. I am trying to use more than the default 2GB of
> > > memory for SQL Server.
> > >
> > > I have added the /PAE switch to the boot.ini file on the server, configured
> > > SQL Server by issuing a
> > >
> > > sp_configure 'show advanced options', 1
> > > RECONFIGURE
> > >
> > > and a
> > >
> > > sp_configure 'awe enabled', 1
> > > RECONFIGURE
> > > GO
> > > sp_configure 'max server memory', 14336
> > > RECONFIGURE
> > > GO
> > >
> > > However, it looks like SQL Server is till using less than 2GB of memory when
> > > running. I have run the sp_configure check on it's own and noticed that the
> > > config_value for AWE enabled is 1, but that the run_value is still 0.
> > >
> > > Could that be the problem? What do I have to do to fix this?
> > >
> > > I know I did restart the server after adding the settings (the setting for
> > > 'show advanced options' and 'max server memory' both show the same value for
> > > config and run_value. It is just the AWE enable setting that has a
> > > discrepancy.
> > >
> > > Should I set the 'max server memory' to less than 14 GB if the server has
> > > 16GB available? How much does the OS need?
> > >
> > > Thanks
> > >
> > > Fred|||Task Manager isn't PAE aware. Use Perf Mon.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
news:3AB99BA7-408F-4B05-B44B-DA2D78FE0053@.microsoft.com...
> OK, I did reboot the server and afterwards I could verify that the run_value
> for 'AWE enabled' is now 1.
> The 'min server memory' is set to 1024 (1 GB) and the 'max server memory'
> is set to 10240 (10GB). The server has 16GB of memory, but I will be sharing
> that with the OS and with a SAS application, so I thought that 10GB for SQL
> Server is a good number to start with.
> *HOWEVER* when I check the memory utilization on the server through the Task
> Manager, it now shows that the process sqlservr.exe is only using 141,164 K
> of memory - only about 138 MB!!
> Why would this be? Could this be correct? The server is being used pretty
> heavily right now - it is not a transaction heavy load, more a batch type
> load with millions of records being updated in a batch.
> My impression was that if AWE is enabled and PAE is used, that the memory
> allocation is not dynamic anymore, but static, according to what you specify
> in the max server memory setting.
> Why would it then show such a low memory utilization by SQL Server? (It used
> to hover around 1.7 GB before I enabled PAE and AWE).
>
> Fred
> "Andrew J. Kelly" wrote:
>> And check to make sure the account sql server is running under has Lock
>> Pages In Memory rights.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:u2cLo5lhFHA.576@.TK2MSFTNGP15.phx.gbl...
>> > The most obvious reason would be that you didn't actually restart SQL
>> > Server. I would try that and see if it fixes it.
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> >
>> >
>> > "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
>> > news:27D53CE0-A064-4B3A-83CA-25293115FA99@.microsoft.com...
>> >> Andrew & Alejandro,
>> >>
>> >> I am running Server 2003 Enterprise Edition and SQL Server 2000
>> >> Enterprise
>> >> Edition.
>> >>
>> >> I think the problem is that the config_value for 'AWE enabled' is 1, but
>> >> that the run_value is still 0.
>> >>
>> >> Why would that be? What do I have to do to fix this?
>> >>
>> >> I am fairly sure that I restarted the server after changing the AWE
>> >> setting,
>> >> but I can do it again after hours (it is a production server).
>> >>
>> >> Is there any reason why the 'AWE enabled' setting would stay at a
>> >> run_value
>> >> of 0, even after I try to set it with:
>> >>
>> >> sp_configure 'awe enabled', 1
>> >> RECONFIGURE
>> >>
>> >>
>> >> Fred
>> >>
>> >>
>> >> "Andrew J. Kelly" wrote:
>> >>
>> >> Are you sure it is Enterprise Edition and not Std? What Edition is the
>> >> OS?
>> >> What does @.@.VERSION or xp_MSVer show? After you get it going I would
>> >> start
>> >> out with 15 or 15.5GB instead of 16 just to make sure the OS has plenty
>> >> and
>> >> there is a little room to spare for other apps.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
>> >> news:6007E2E5-304A-4C53-9A29-E061F3E439BB@.microsoft.com...
>> >> > Alejandro,
>> >> >
>> >> > I did go through the instructions that are shown on the pages you
>> >> > bookmarked
>> >> > (see my original post).
>> >> >
>> >> > My question is: why is SQL Server still using less than 2GB of memory
>> >> > even
>> >> > after I have setup my boot.ini and enabled AWE and set the max server
>> >> > memory?
>> >> >
>> >> > Fred
>> >> >
>> >> >
>> >> > "Alejandro Mesa" wrote:
>> >> >
>> >> >> How to configure memory for more than 2 GB in SQL Server
>> >> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>> >> >>
>> >> >> Managing AWE Memory
>> >> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
>> >> >>
>> >> >>
>> >> >> AMB
>> >> >>
>> >> >>
>> >> >> "fredscuba" wrote:
>> >> >>
>> >> >> > We are running SQL Server 2000 Enterprise Edition on a Windows
>> >> >> > Server
>> >> >> > 2003
>> >> >> > machine with 16 GB of memory. I am trying to use more than the
>> >> >> > default
>> >> >> > 2GB of
>> >> >> > memory for SQL Server.
>> >> >> >
>> >> >> > I have added the /PAE switch to the boot.ini file on the server,
>> >> >> > configured
>> >> >> > SQL Server by issuing a
>> >> >> >
>> >> >> > sp_configure 'show advanced options', 1
>> >> >> > RECONFIGURE
>> >> >> >
>> >> >> > and a
>> >> >> >
>> >> >> > sp_configure 'awe enabled', 1
>> >> >> > RECONFIGURE
>> >> >> > GO
>> >> >> > sp_configure 'max server memory', 14336
>> >> >> > RECONFIGURE
>> >> >> > GO
>> >> >> >
>> >> >> > However, it looks like SQL Server is till using less than 2GB of
>> >> >> > memory
>> >> >> > when
>> >> >> > running. I have run the sp_configure check on it's own and noticed
>> >> >> > that
>> >> >> > the
>> >> >> > config_value for AWE enabled is 1, but that the run_value is still
>> >> >> > 0.
>> >> >> >
>> >> >> > Could that be the problem? What do I have to do to fix this?
>> >> >> >
>> >> >> > I know I did restart the server after adding the settings (the
>> >> >> > setting
>> >> >> > for
>> >> >> > 'show advanced options' and 'max server memory' both show the same
>> >> >> > value for
>> >> >> > config and run_value. It is just the AWE enable setting that has a
>> >> >> > discrepancy.
>> >> >> >
>> >> >> > Should I set the 'max server memory' to less than 14 GB if the
>> >> >> > server
>> >> >> > has
>> >> >> > 16GB available? How much does the OS need?
>> >> >> >
>> >> >> > Thanks
>> >> >> >
>> >> >> > Fred
>> >>
>> >>
>> >>
>> >
>> >
>>|||Thanks!
Perf Mon shows a static number for the memory assigned to SQL Server and it
matches what I set the max server memory setting to.
Fred
"Tibor Karaszi" wrote:
> Task Manager isn't PAE aware. Use Perf Mon.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> news:3AB99BA7-408F-4B05-B44B-DA2D78FE0053@.microsoft.com...
> > OK, I did reboot the server and afterwards I could verify that the run_value
> > for 'AWE enabled' is now 1.
> >
> > The 'min server memory' is set to 1024 (1 GB) and the 'max server memory'
> > is set to 10240 (10GB). The server has 16GB of memory, but I will be sharing
> > that with the OS and with a SAS application, so I thought that 10GB for SQL
> > Server is a good number to start with.
> >
> > *HOWEVER* when I check the memory utilization on the server through the Task
> > Manager, it now shows that the process sqlservr.exe is only using 141,164 K
> > of memory - only about 138 MB!!
> >
> > Why would this be? Could this be correct? The server is being used pretty
> > heavily right now - it is not a transaction heavy load, more a batch type
> > load with millions of records being updated in a batch.
> >
> > My impression was that if AWE is enabled and PAE is used, that the memory
> > allocation is not dynamic anymore, but static, according to what you specify
> > in the max server memory setting.
> >
> > Why would it then show such a low memory utilization by SQL Server? (It used
> > to hover around 1.7 GB before I enabled PAE and AWE).
> >
> >
> > Fred
> >
> > "Andrew J. Kelly" wrote:
> >
> >> And check to make sure the account sql server is running under has Lock
> >> Pages In Memory rights.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> news:u2cLo5lhFHA.576@.TK2MSFTNGP15.phx.gbl...
> >> > The most obvious reason would be that you didn't actually restart SQL
> >> > Server. I would try that and see if it fixes it.
> >> >
> >> > --
> >> > Andrew J. Kelly SQL MVP
> >> >
> >> >
> >> > "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> >> > news:27D53CE0-A064-4B3A-83CA-25293115FA99@.microsoft.com...
> >> >> Andrew & Alejandro,
> >> >>
> >> >> I am running Server 2003 Enterprise Edition and SQL Server 2000
> >> >> Enterprise
> >> >> Edition.
> >> >>
> >> >> I think the problem is that the config_value for 'AWE enabled' is 1, but
> >> >> that the run_value is still 0.
> >> >>
> >> >> Why would that be? What do I have to do to fix this?
> >> >>
> >> >> I am fairly sure that I restarted the server after changing the AWE
> >> >> setting,
> >> >> but I can do it again after hours (it is a production server).
> >> >>
> >> >> Is there any reason why the 'AWE enabled' setting would stay at a
> >> >> run_value
> >> >> of 0, even after I try to set it with:
> >> >>
> >> >> sp_configure 'awe enabled', 1
> >> >> RECONFIGURE
> >> >>
> >> >>
> >> >> Fred
> >> >>
> >> >>
> >> >> "Andrew J. Kelly" wrote:
> >> >>
> >> >> Are you sure it is Enterprise Edition and not Std? What Edition is the
> >> >> OS?
> >> >> What does @.@.VERSION or xp_MSVer show? After you get it going I would
> >> >> start
> >> >> out with 15 or 15.5GB instead of 16 just to make sure the OS has plenty
> >> >> and
> >> >> there is a little room to spare for other apps.
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >>
> >> >>
> >> >> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> >> >> news:6007E2E5-304A-4C53-9A29-E061F3E439BB@.microsoft.com...
> >> >> > Alejandro,
> >> >> >
> >> >> > I did go through the instructions that are shown on the pages you
> >> >> > bookmarked
> >> >> > (see my original post).
> >> >> >
> >> >> > My question is: why is SQL Server still using less than 2GB of memory
> >> >> > even
> >> >> > after I have setup my boot.ini and enabled AWE and set the max server
> >> >> > memory?
> >> >> >
> >> >> > Fred
> >> >> >
> >> >> >
> >> >> > "Alejandro Mesa" wrote:
> >> >> >
> >> >> >> How to configure memory for more than 2 GB in SQL Server
> >> >> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
> >> >> >>
> >> >> >> Managing AWE Memory
> >> >> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp
> >> >> >>
> >> >> >>
> >> >> >> AMB
> >> >> >>
> >> >> >>
> >> >> >> "fredscuba" wrote:
> >> >> >>
> >> >> >> > We are running SQL Server 2000 Enterprise Edition on a Windows
> >> >> >> > Server
> >> >> >> > 2003
> >> >> >> > machine with 16 GB of memory. I am trying to use more than the
> >> >> >> > default
> >> >> >> > 2GB of
> >> >> >> > memory for SQL Server.
> >> >> >> >
> >> >> >> > I have added the /PAE switch to the boot.ini file on the server,
> >> >> >> > configured
> >> >> >> > SQL Server by issuing a
> >> >> >> >
> >> >> >> > sp_configure 'show advanced options', 1
> >> >> >> > RECONFIGURE
> >> >> >> >
> >> >> >> > and a
> >> >> >> >
> >> >> >> > sp_configure 'awe enabled', 1
> >> >> >> > RECONFIGURE
> >> >> >> > GO
> >> >> >> > sp_configure 'max server memory', 14336
> >> >> >> > RECONFIGURE
> >> >> >> > GO
> >> >> >> >
> >> >> >> > However, it looks like SQL Server is till using less than 2GB of
> >> >> >> > memory
> >> >> >> > when
> >> >> >> > running. I have run the sp_configure check on it's own and noticed
> >> >> >> > that
> >> >> >> > the
> >> >> >> > config_value for AWE enabled is 1, but that the run_value is still
> >> >> >> > 0.
> >> >> >> >
> >> >> >> > Could that be the problem? What do I have to do to fix this?
> >> >> >> >
> >> >> >> > I know I did restart the server after adding the settings (the
> >> >> >> > setting
> >> >> >> > for
> >> >> >> > 'show advanced options' and 'max server memory' both show the same
> >> >> >> > value for
> >> >> >> > config and run_value. It is just the AWE enable setting that has a
> >> >> >> > discrepancy.
> >> >> >> >
> >> >> >> > Should I set the 'max server memory' to less than 14 GB if the
> >> >> >> > server
> >> >> >> > has
> >> >> >> > 16GB available? How much does the OS need?
> >> >> >> >
> >> >> >> > Thanks
> >> >> >> >
> >> >> >> > Fred
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>

Friday, 17 February 2012

Configuring SQL Server Ent. running on 8Gb - suggestions please ?

Configuring SQL Server Ent. running on 8Gb - suggestions please ?

Configuring SQL Server Ent. running on 8Gb - suggestions please ?

Sunday, 12 February 2012

Configuring and using PAE on SQS Server 2000 Enterprise Edition

Configuring and using PAE on SQS Server 2000 Enterprise Edition

SQL Configure

Blog Archive

About Me