SQL Configure: 8gb

Showing posts with label 8gb. 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 ?

Tuesday, 14 February 2012

Configuring Memory for SQL 2005 Standard on Windows 2003 Enterpris

Hello,
I need some assistance configuring SQL 2005 running on an 8GB server running
Windows 2003 Enterprise. I've read some of the SQL 2005 documentation, but
I'm still confused as to what I need to do. Given this configuration, do I
need to do anything for SQL 2005 to be able to utilize the 8GB of RAM, and if
I do, what exactly needs to be done? Sorry, I'm a newbie when it comes to SQL
2005.
Thanks."Marks70" <Marks70@.discussions.microsoft.com> wrote in message
news:28A505B8-A98A-4394-8802-F04AC63E72B5@.microsoft.com...
> Hello,
> I need some assistance configuring SQL 2005 running on an 8GB server
> running
> Windows 2003 Enterprise. I've read some of the SQL 2005 documentation, but
> I'm still confused as to what I need to do. Given this configuration, do I
> need to do anything for SQL 2005 to be able to utilize the 8GB of RAM, and
> if
> I do, what exactly needs to be done? Sorry, I'm a newbie when it comes to
> SQL
> 2005.
>
When running 64bit Windows and 64bit SQL Server, nothing special needs to be
done.
On 32bit Windows, or when running 32bit SQL Server on 64bit Windows, see the
following BOL topic:
Using AWE
http://msdn2.microsoft.com/en-us/library/ms175581.aspx
However, AWE has a performance penalty, so you might be better off just
using the /3GB switch and limiting SQL Server to non-AWE memory. Basically
each server workload will, at some point, experience a diminishing
performance return for incremental memory addition. Under load, measure the
SQLServer:Buffer Manager: Page reads/sec. If adding memory stops materially
affecting this number at or below 3GB, you probably don't want to use AWE.
David|||Thanks for your response. Please let me know if I am understanding this
correctly. If I use the /3GB switch, my OS will have access to 1GB of memory
and SQL 2005 (and other apps) will have access to 3GB. However, the remaining
4GB of the server's total of 8GB will not be used? Am I understanding this
correctly?
Also, if I use the /pae parameter (and therefore should not use the /3GB
parameter), I would have access to the entire 8GB but may not have as good of
performance as with using the /3GB switch? Did I understand you correctly?
Thanks!
"David Browne" wrote:
> "Marks70" <Marks70@.discussions.microsoft.com> wrote in message
> news:28A505B8-A98A-4394-8802-F04AC63E72B5@.microsoft.com...
> > Hello,
> >
> > I need some assistance configuring SQL 2005 running on an 8GB server
> > running
> > Windows 2003 Enterprise. I've read some of the SQL 2005 documentation, but
> > I'm still confused as to what I need to do. Given this configuration, do I
> > need to do anything for SQL 2005 to be able to utilize the 8GB of RAM, and
> > if
> > I do, what exactly needs to be done? Sorry, I'm a newbie when it comes to
> > SQL
> > 2005.
> >
> When running 64bit Windows and 64bit SQL Server, nothing special needs to be
> done.
> On 32bit Windows, or when running 32bit SQL Server on 64bit Windows, see the
> following BOL topic:
> Using AWE
> http://msdn2.microsoft.com/en-us/library/ms175581.aspx
>
> However, AWE has a performance penalty, so you might be better off just
> using the /3GB switch and limiting SQL Server to non-AWE memory. Basically
> each server workload will, at some point, experience a diminishing
> performance return for incremental memory addition. Under load, measure the
> SQLServer:Buffer Manager: Page reads/sec. If adding memory stops materially
> affecting this number at or below 3GB, you probably don't want to use AWE.
> David
>
>|||The penalty for AWE is not that heavy that you should ignore it altogether.
The /PAE is so windows can access memory above 4GB and allows apps using AWE
to also access the memory above 4GB as well. The If SQL Server is the only
app on the server then I would set the /3GB and /PAE in the boot.ini. Then
set the MAX Memory setting in SQL Server to 7GB and turn on AWE. You may
have to adjust the max setting down some if you need more memory for the os
or other apps.
--
Andrew J. Kelly SQL MVP
"Marks70" <Marks70@.discussions.microsoft.com> wrote in message
news:8F92E162-EADB-4ECD-A616-A6A5E9ADF7A6@.microsoft.com...
> Thanks for your response. Please let me know if I am understanding this
> correctly. If I use the /3GB switch, my OS will have access to 1GB of
> memory
> and SQL 2005 (and other apps) will have access to 3GB. However, the
> remaining
> 4GB of the server's total of 8GB will not be used? Am I understanding this
> correctly?
> Also, if I use the /pae parameter (and therefore should not use the /3GB
> parameter), I would have access to the entire 8GB but may not have as good
> of
> performance as with using the /3GB switch? Did I understand you correctly?
> Thanks!
> "David Browne" wrote:
>> "Marks70" <Marks70@.discussions.microsoft.com> wrote in message
>> news:28A505B8-A98A-4394-8802-F04AC63E72B5@.microsoft.com...
>> > Hello,
>> >
>> > I need some assistance configuring SQL 2005 running on an 8GB server
>> > running
>> > Windows 2003 Enterprise. I've read some of the SQL 2005 documentation,
>> > but
>> > I'm still confused as to what I need to do. Given this configuration,
>> > do I
>> > need to do anything for SQL 2005 to be able to utilize the 8GB of RAM,
>> > and
>> > if
>> > I do, what exactly needs to be done? Sorry, I'm a newbie when it comes
>> > to
>> > SQL
>> > 2005.
>> >
>> When running 64bit Windows and 64bit SQL Server, nothing special needs to
>> be
>> done.
>> On 32bit Windows, or when running 32bit SQL Server on 64bit Windows, see
>> the
>> following BOL topic:
>> Using AWE
>> http://msdn2.microsoft.com/en-us/library/ms175581.aspx
>>
>> However, AWE has a performance penalty, so you might be better off just
>> using the /3GB switch and limiting SQL Server to non-AWE memory.
>> Basically
>> each server workload will, at some point, experience a diminishing
>> performance return for incremental memory addition. Under load, measure
>> the
>> SQLServer:Buffer Manager: Page reads/sec. If adding memory stops
>> materially
>> affecting this number at or below 3GB, you probably don't want to use
>> AWE.
>> David
>>|||Thanks Andrew. You actually answered a follow-up question I had reqarding
whether or not I should be using both the /3GB and /PAE in the boot.ini file.
Do you know what the affect would be if I only added the /PAE switch without
the /3GB switch?
"Andrew J. Kelly" wrote:
> The penalty for AWE is not that heavy that you should ignore it altogether.
> The /PAE is so windows can access memory above 4GB and allows apps using AWE
> to also access the memory above 4GB as well. The If SQL Server is the only
> app on the server then I would set the /3GB and /PAE in the boot.ini. Then
> set the MAX Memory setting in SQL Server to 7GB and turn on AWE. You may
> have to adjust the max setting down some if you need more memory for the os
> or other apps.
> --
> Andrew J. Kelly SQL MVP
>
> "Marks70" <Marks70@.discussions.microsoft.com> wrote in message
> news:8F92E162-EADB-4ECD-A616-A6A5E9ADF7A6@.microsoft.com...
> > Thanks for your response. Please let me know if I am understanding this
> > correctly. If I use the /3GB switch, my OS will have access to 1GB of
> > memory
> > and SQL 2005 (and other apps) will have access to 3GB. However, the
> > remaining
> > 4GB of the server's total of 8GB will not be used? Am I understanding this
> > correctly?
> >
> > Also, if I use the /pae parameter (and therefore should not use the /3GB
> > parameter), I would have access to the entire 8GB but may not have as good
> > of
> > performance as with using the /3GB switch? Did I understand you correctly?
> >
> > Thanks!
> >
> > "David Browne" wrote:
> >
> >>
> >> "Marks70" <Marks70@.discussions.microsoft.com> wrote in message
> >> news:28A505B8-A98A-4394-8802-F04AC63E72B5@.microsoft.com...
> >> > Hello,
> >> >
> >> > I need some assistance configuring SQL 2005 running on an 8GB server
> >> > running
> >> > Windows 2003 Enterprise. I've read some of the SQL 2005 documentation,
> >> > but
> >> > I'm still confused as to what I need to do. Given this configuration,
> >> > do I
> >> > need to do anything for SQL 2005 to be able to utilize the 8GB of RAM,
> >> > and
> >> > if
> >> > I do, what exactly needs to be done? Sorry, I'm a newbie when it comes
> >> > to
> >> > SQL
> >> > 2005.
> >> >
> >>
> >> When running 64bit Windows and 64bit SQL Server, nothing special needs to
> >> be
> >> done.
> >>
> >> On 32bit Windows, or when running 32bit SQL Server on 64bit Windows, see
> >> the
> >> following BOL topic:
> >> Using AWE
> >> http://msdn2.microsoft.com/en-us/library/ms175581.aspx
> >>
> >>
> >> However, AWE has a performance penalty, so you might be better off just
> >> using the /3GB switch and limiting SQL Server to non-AWE memory.
> >> Basically
> >> each server workload will, at some point, experience a diminishing
> >> performance return for incremental memory addition. Under load, measure
> >> the
> >> SQLServer:Buffer Manager: Page reads/sec. If adding memory stops
> >> materially
> >> affecting this number at or below 3GB, you probably don't want to use
> >> AWE.
> >>
> >> David
> >>
> >>
> >>
>
>|||"Marks70" <Marks70@.discussions.microsoft.com> wrote in message
news:3D3541BC-D7AA-43FD-881E-19FBDE54BFF9@.microsoft.com...
> Thanks Andrew. You actually answered a follow-up question I had reqarding
> whether or not I should be using both the /3GB and /PAE in the boot.ini
> file.
> Do you know what the affect would be if I only added the /PAE switch
> without
> the /3GB switch?
>
SQL Server would use a 2GB user mode address space and access the rest of
the memory through AWE.
David|||As opposed to using a 3GB user mode address space if the /3GB switch was
used? I guess I'm trying to get a better idea of why I would use the /3GB
switch in conjunction with using the /PAE switch as opposed to just using the
/PAE switch by itself.
"David Browne" wrote:
> "Marks70" <Marks70@.discussions.microsoft.com> wrote in message
> news:3D3541BC-D7AA-43FD-881E-19FBDE54BFF9@.microsoft.com...
> > Thanks Andrew. You actually answered a follow-up question I had reqarding
> > whether or not I should be using both the /3GB and /PAE in the boot.ini
> > file.
> > Do you know what the affect would be if I only added the /PAE switch
> > without
> > the /3GB switch?
> >
> SQL Server would use a 2GB user mode address space and access the rest of
> the memory through AWE.
> David
>
>|||"Marks70" <Marks70@.discussions.microsoft.com> wrote in message
news:4E5ACEB7-F6F1-47BA-A867-33664F4362A2@.microsoft.com...
> As opposed to using a 3GB user mode address space if the /3GB switch was
> used?
>I guess I'm trying to get a better idea of why I would use the /3GB
> switch in conjunction with using the /PAE switch as opposed to just using
> the
> /PAE switch by itself.
>
Using /3GB gives SQL Server a larger user mode address space (which is
good), but constricts to total kernel memory to 1GB. This limits the total
physical memory usable on the server to (I think) 16GB.
David
> "David Browne" wrote:
>> "Marks70" <Marks70@.discussions.microsoft.com> wrote in message
>> news:3D3541BC-D7AA-43FD-881E-19FBDE54BFF9@.microsoft.com...
>> > Thanks Andrew. You actually answered a follow-up question I had
>> > reqarding
>> > whether or not I should be using both the /3GB and /PAE in the boot.ini
>> > file.
>> > Do you know what the affect would be if I only added the /PAE switch
>> > without
>> > the /3GB switch?
>> >
>> SQL Server would use a 2GB user mode address space and access the rest of
>> the memory through AWE.
>> David
>>|||For one that is 1GB more that SQL Server can address directly (without using
AWE) and 1 GB less that needs to be managed by AWE. But it can also come in
handy if you need more directly addressable memory than just 2GB. The data
buffer is the only part of SQL Server than can use the AWE memory.
Everything else needs to use the 2 or 3GB (depending on the /3GB) so it can
get tight with only 2GB in some cases.
--
Andrew J. Kelly SQL MVP
"Marks70" <Marks70@.discussions.microsoft.com> wrote in message
news:4E5ACEB7-F6F1-47BA-A867-33664F4362A2@.microsoft.com...
> As opposed to using a 3GB user mode address space if the /3GB switch was
> used? I guess I'm trying to get a better idea of why I would use the /3GB
> switch in conjunction with using the /PAE switch as opposed to just using
> the
> /PAE switch by itself.
> "David Browne" wrote:
>> "Marks70" <Marks70@.discussions.microsoft.com> wrote in message
>> news:3D3541BC-D7AA-43FD-881E-19FBDE54BFF9@.microsoft.com...
>> > Thanks Andrew. You actually answered a follow-up question I had
>> > reqarding
>> > whether or not I should be using both the /3GB and /PAE in the boot.ini
>> > file.
>> > Do you know what the affect would be if I only added the /PAE switch
>> > without
>> > the /3GB switch?
>> >
>> SQL Server would use a 2GB user mode address space and access the rest of
>> the memory through AWE.
>> David
>>

Configuring Memory for SQL 2005 Standard on Windows 2003 Enterpris

Configuring memory for SQL 2000

I'm planning to install SQL Server 2000 Enterprise Edition on Windows Server
2003 Standard Edition machine with 8Gb of memory.
How do I force SQL server to use at least 6Gb out of 8Gb?
Thank you in advance!
Leon
Do you mean "at least 6GB" or "up to 6GB"? Either way you'll need the
/pae switch in boot.ini. This allows the OS to see more than 4GB of
physical memory. Then turn on AWE memory and set the min & max memory
settings in SQL Server as appropriate with the sp_configure proc.
For between 6GB and 7GB:
exec sp_configure 'awe enabled', 1
exec sp_configure 'min server memory', 6144
exec sp_configure 'max server memory', 7168
-- then stop & restart the server (needed for the awe enabled option)
For anything up to 6GB:
exec sp_configure 'awe enabled', 1
exec sp_configure 'max server memory', 6144
-- then stop & restart the server (needed for the awe enabled option)
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
Leon Shargorodsky wrote:

>I'm planning to install SQL Server 2000 Enterprise Edition on Windows Server
>2003 Standard Edition machine with 8Gb of memory.
>How do I force SQL server to use at least 6Gb out of 8Gb?
>Thank you in advance!
>Leon
>

Configuring memory for SQL 2000

I'm planning to install SQL Server 2000 Enterprise Edition on Windows Server
2003 Standard Edition machine with 8Gb of memory.
How do I force SQL server to use at least 6Gb out of 8Gb?
Thank you in advance!
LeonThis is a multi-part message in MIME format.
--010707060304040203090309
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Do you mean "at least 6GB" or "up to 6GB"? Either way you'll need the
/pae switch in boot.ini. This allows the OS to see more than 4GB of
physical memory. Then turn on AWE memory and set the min & max memory
settings in SQL Server as appropriate with the sp_configure proc.
For between 6GB and 7GB:
exec sp_configure 'awe enabled', 1
exec sp_configure 'min server memory', 6144
exec sp_configure 'max server memory', 7168
-- then stop & restart the server (needed for the awe enabled option)
For anything up to 6GB:
exec sp_configure 'awe enabled', 1
exec sp_configure 'max server memory', 6144
-- then stop & restart the server (needed for the awe enabled option)
Hope this helps.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Leon Shargorodsky wrote:
>I'm planning to install SQL Server 2000 Enterprise Edition on Windows Server
>2003 Standard Edition machine with 8Gb of memory.
>How do I force SQL server to use at least 6Gb out of 8Gb?
>Thank you in advance!
>Leon
>
--010707060304040203090309
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>Do you mean "at least 6GB" or "up to 6GB"?Â Either way you'll need
the /pae switch in boot.ini.Â This allows the OS to see more than 4GB
of physical memory.Â Then turn on AWE memory and set the min & max
memory settings in SQL Server as appropriate with the sp_configure proc. 
 
For between 6GB and 7GB: 
Â Â Â exec sp_configure 'awe enabled', 1 
Â Â Â exec sp_configure 'min server memory', 6144 
</tt><tt>Â Â Â exec sp_configure 'max server memory', 7168 
Â Â Â -- then stop & restart the server (needed for the awe enabled
option) 
 
For anything up to 6GB: 
Â Â Â exec sp_configure 'awe enabled', 1 
Â Â Â exec sp_configure 'max server memory', 6144 
</tt><tt>Â Â Â -- then stop & restart the server (needed for the awe
enabled option) 
 
Hope this helps.</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Leon Shargorodsky wrote:
<blockquote cite="midAD5292DB-518D-4927-A01D-ABCF84E22115@.microsoft.com"
type="cite">
<pre wrap="">I'm planning to install SQL Server 2000 Enterprise Edition on Windows Server
2003 Standard Edition machine with 8Gb of memory.
How do I force SQL server to use at least 6Gb out of 8Gb?
Thank you in advance!
Leon
</pre>
</blockquote>
</body>
</html>
--010707060304040203090309--

Configuring memory for SQL 2000

I'm planning to install SQL Server 2000 Enterprise Edition on Windows Server
2003 Standard Edition machine with 8Gb of memory.
How do I force SQL server to use at least 6Gb out of 8Gb?
Thank you in advance!
LeonDo you mean "at least 6GB" or "up to 6GB"? Either way you'll need the
/pae switch in boot.ini. This allows the OS to see more than 4GB of
physical memory. Then turn on AWE memory and set the min & max memory
settings in SQL Server as appropriate with the sp_configure proc.
For between 6GB and 7GB:
exec sp_configure 'awe enabled', 1
exec sp_configure 'min server memory', 6144
exec sp_configure 'max server memory', 7168
-- then stop & restart the server (needed for the awe enabled option)
For anything up to 6GB:
exec sp_configure 'awe enabled', 1
exec sp_configure 'max server memory', 6144
-- then stop & restart the server (needed for the awe enabled option)
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
Leon Shargorodsky wrote:

>I'm planning to install SQL Server 2000 Enterprise Edition on Windows Serve
r
>2003 Standard Edition machine with 8Gb of memory.
>How do I force SQL server to use at least 6Gb out of 8Gb?
>Thank you in advance!
>Leon
>

configuring locks in awe system

I am running Microsoft SQL Server 2000 - 8.00.760 Enterprise Edition
on Windows 2003 Enterprise Edition (NT 5.2 Build 3790:) I have 4CPU
and 8GB of RAM. I have AWE enabled, /pae /3gb switch is on in
boot.ini, In my errorlog I have noticed few times error messages as
"The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system
administrator to check the SQL Server lock and memory configuration..
" I haven't modified anything in sp_configure 'locks', so i am
assuming SQL server is dynamically managing the lock memory. Running
perfmon for few days I observed that Lock Requests/sec are usually few
100,000 most of the times and sometimes goes to 2+ million. However I
haven't seen Lock Memory (KB) going more than 40MB. So i wonder why am
I seeing the Error: 1204 in my sql log ? And any advise on how to
prevent it.
Thanks
Aswineeaswinee (aswinee@.yahoo.com) writes:
> I am running Microsoft SQL Server 2000 - 8.00.760 Enterprise Edition
> on Windows 2003 Enterprise Edition (NT 5.2 Build 3790:) I have 4CPU
> and 8GB of RAM. I have AWE enabled, /pae /3gb switch is on in
> boot.ini, In my errorlog I have noticed few times error messages as
> "The SQL Server cannot obtain a LOCK resource at this time. Rerun your
> statement when there are fewer active users or ask the system
> administrator to check the SQL Server lock and memory configuration..
> " I haven't modified anything in sp_configure 'locks', so i am
> assuming SQL server is dynamically managing the lock memory. Running
> perfmon for few days I observed that Lock Requests/sec are usually few
> 100,000 most of the times and sometimes goes to 2+ million. However I
> haven't seen Lock Memory (KB) going more than 40MB. So i wonder why am
> I seeing the Error: 1204 in my sql log ? And any advise on how to
> prevent it.

I guess there are two questions here:
1) is that locking frequency natural, or is there is a need to investigate
this?
2) are you really hitting hard limits of number of available locks.

I don't know what the maximum number of locks, but I expect it to be more
than two million. Then again, two million lock requests in one second,
does not tell us how many locks thar actually are in use. A lock can be
held for a short moment, or several hours (which would be due to poor
design).

But two million lock requests, or even 100000 sounds a lot to me. But if
that is a busy system it could be normal.

Since this is not really my field of expertise, so I asked in our private
MVP forum, and I got these suggestion:

few interesting points to check are:
- make sure your statistics are up to date
- you have sufficient indexes to run your query efficiently

which is pretty standard. But this is more interesting:

I'd also ask if trace flag 1211 has been set, see
http://support.microsoft.com/?kbid=323630

With 1211 set, you turn off lock escalation, and this can lead to a lot of
locks being requested.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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 ?

Tuesday, 14 February 2012

Configuring Memory for SQL 2005 Standard on Windows 2003 Enterpris

Configuring Memory for SQL 2005 Standard on Windows 2003 Enterpris

Configuring memory for SQL 2000

Configuring memory for SQL 2000

Configuring memory for SQL 2000

configuring locks in awe system

SQL Configure

Blog Archive

About Me