Confusion over SQL 2005 Standard CPU Support

I'm a little confused over the maximum CPU count supported by SQL 2005 Standard Edition (this particular edition supports four CPUs).

Does the figure refer to four physical CPUs regardless of whether they are dual-cored or hyperthreaded, or does the figure refer to the number of logical CPUs available to the OS?

Let me cut to the chase - if I purchase a server containing four dual-core CPUs and install SQL Server 2005 Standard, will SQL Server see the eight CPUs and utilise a maximum of four of these, or will it be able to use all eight (because there are actually only four physical CPUs)?

Thanks, Chris.

It's per processor regardless of the number of cores. See the link below.

Doug

http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc

On the bottom of page two: Microsoft has been driving thought leadership in this area by charging the same amount per processor, regardless of how many cores are in the processor.

|||

Hi Doug, thanks for the info.

You've actually answered another question that I was going to ask in my original post (but forgot to include).

One thing that's missing is any information regarding SQL Server 2005 Standard's limit of 4 CPUs and whether this limit refers to logical or physical CPUs. I'm now happy with the licensing aspect, they couldn't have made it any clearer, however I don't want to purchase four dual-core CPUs if only four of the eight cores will actually be used by SQL Server.

I have been forwarded the following link that clearly describes SQL Express' behaviour when presented with a single dual-core and/or hyperthreaded CPU, however there seems to be a lack of information as to whether the same rules apply to the Standard and Workgroup editions of SQL Server.

http://support.microsoft.com/kb/914278/en-us

Thanks again, Chris.

|||

From emperical evidence, it looks like Standard is restricting on logical processors.

We have a production config with SQL2005-Standard-SP1 on Dell 6850 (quad-socket/dual-core/hyper-threaded [tulsa]) ..... Taskmgr only shows 4 logical cpus busy at any one time.

Also have SQL2005-Standard-SP1 on Dell 2950 (dual-socket/dual-core [woodcrest]) .... Taskmgr shows all 4 cores busy.

Nice confusing tactics ... license on sockets .... tether performance on logicals.

Confusion over SQL 2005 Standard CPU Support

I'm a little confused over the maximum CPU count supported by SQL 2005 Standard Edition (this particular edition supports four CPUs).

Does the figure refer to four physical CPUs regardless of whether they are dual-cored or hyperthreaded, or does the figure refer to the number of logical CPUs available to the OS?

Let me cut to the chase - if I purchase a server containing four dual-core CPUs and install SQL Server 2005 Standard, will SQL Server see the eight CPUs and utilise a maximum of four of these, or will it be able to use all eight (because there are actually only four physical CPUs)?

Thanks, Chris.

It's per processor regardless of the number of cores. See the link below.

Doug

http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc

On the bottom of page two: Microsoft has been driving thought leadership in this area by charging the same amount per processor, regardless of how many cores are in the processor.

|||

Hi Doug, thanks for the info.

You've actually answered another question that I was going to ask in my original post (but forgot to include).

One thing that's missing is any information regarding SQL Server 2005 Standard's limit of 4 CPUs and whether this limit refers to logical or physical CPUs. I'm now happy with the licensing aspect, they couldn't have made it any clearer, however I don't want to purchase four dual-core CPUs if only four of the eight cores will actually be used by SQL Server.

I have been forwarded the following link that clearly describes SQL Express' behaviour when presented with a single dual-core and/or hyperthreaded CPU, however there seems to be a lack of information as to whether the same rules apply to the Standard and Workgroup editions of SQL Server.

http://support.microsoft.com/kb/914278/en-us

Thanks again, Chris.

|||

From emperical evidence, it looks like Standard is restricting on logical processors.

We have a production config with SQL2005-Standard-SP1 on Dell 6850 (quad-socket/dual-core/hyper-threaded [tulsa]) ..... Taskmgr only shows 4 logical cpus busy at any one time.

Also have SQL2005-Standard-SP1 on Dell 2950 (dual-socket/dual-core [woodcrest]) .... Taskmgr shows all 4 cores busy.

Nice confusing tactics ... license on sockets .... tether performance on logicals.

Confusion over SQL 2005 Standard CPU Support

I'm a little confused over the maximum CPU count supported by SQL 2005 Standard Edition (this particular edition supports four CPUs).

Does the figure refer to four physical CPUs regardless of whether they are dual-cored or hyperthreaded, or does the figure refer to the number of logical CPUs available to the OS?

Let me cut to the chase - if I purchase a server containing four dual-core CPUs and install SQL Server 2005 Standard, will SQL Server see the eight CPUs and utilise a maximum of four of these, or will it be able to use all eight (because there are actually only four physical CPUs)?

Thanks, Chris.

Hi,

all is physical CPUs, as well as licencing and limitations.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens, thanks for the reply.

Just to clarify in my own mind - are you saying that an instance of SQL Server 2005 Standard will be able to fully utilise four dual-core CPUs (i.e. all eight logical CPUs)?

Thanks again, Chris.

|||Well, interesting thing, I would assume you could use them as SQL Server starts a separate scheduler for the single core:

http://support.microsoft.com/kb/914278/en-us

Don′t thing that this differs much from the full editions.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

SQL Server 2005 only counts physical CPU's for licensing purposes, whether they are hyper-threaded, dual-core, or quad-core. SQL Server 2005 will use all of the logical CPU's that are present, based on the physical CPU count limitation.

You should be careful about getting Standard Edition instead of Enterprise Edition. Lots of important features, such as online index rebuild, online restore and fast recovery are only in Enterprise Edition.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Confusion over SQL 2005 Standard CPU Support

I'm a little confused over the maximum CPU count supported by SQL 2005 Standard Edition (this particular edition supports four CPUs).

Does the figure refer to four physical CPUs regardless of whether they are dual-cored or hyperthreaded, or does the figure refer to the number of logical CPUs available to the OS?

Let me cut to the chase - if I purchase a server containing four dual-core CPUs and install SQL Server 2005 Standard, will SQL Server see the eight CPUs and utilise a maximum of four of these, or will it be able to use all eight (because there are actually only four physical CPUs)?

Thanks, Chris.

Hi,

all is physical CPUs, as well as licencing and limitations.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens, thanks for the reply.

Just to clarify in my own mind - are you saying that an instance of SQL Server 2005 Standard will be able to fully utilise four dual-core CPUs (i.e. all eight logical CPUs)?

Thanks again, Chris.

|||Well, interesting thing, I would assume you could use them as SQL Server starts a separate scheduler for the single core:

http://support.microsoft.com/kb/914278/en-us

Don′t thing that this differs much from the full editions.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

SQL Server 2005 only counts physical CPU's for licensing purposes, whether they are hyper-threaded, dual-core, or quad-core. SQL Server 2005 will use all of the logical CPU's that are present, based on the physical CPU count limitation.

You should be careful about getting Standard Edition instead of Enterprise Edition. Lots of important features, such as online index rebuild, online restore and fast recovery are only in Enterprise Edition.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

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.

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 ?

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