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.
>