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 GB
I 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.c om...
> 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.c om...
> 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.c om...
>
|||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...
>
|||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.googlegr oups.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.googlegr oups.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.