Re: Use all available processors ?

There are a couple of issues here.

Processor Affinity of the DBMS.
Parallelism of a single query execution in the DBMS.

Processor Affinity for IO in the OS Kernel.

Kevin hinted at a problem with older platforms hard-coding IO to CPU 0 and
Network IO to CPU N (the highest processor available). Since Windows 2000
and later, this is no longer the case. However, it can still be a problem.
Although these functions are no longer dedicated specific processors, they
are dedicated to one. You can still observe this behavior by looking at the
perfmon counters Processor:Interrupt Requests per Second(*) and
Processor:DPC per Second(*). You will see that, typically CPU 0, executes
the majority of these kernel mode threads. There is a boot parameter that
overrides this behavior and equally distributes the requests across all
available processors (or any other affinity that you configure).
http://download.microsoft.com/download/5/D/6/5D6EAF2B-7DDF-476B-93DC-7CF0072878E6/NDIS_RSS.doc

In SQL Server 2000 SP4, there were trace flags introduced to support NUMA
awareness in the DBMS and directly affects memory optimization and processor
affinity.
http://support.microsoft.com/kb/921928

In SQL Server 2005, the core DB engine provided for full NUMA integration
and configuration.
http://www.microsoft.com/sql/technologies/performance/default.mspx

http://download.microsoft.com/download/e/3/5/e35ecac9-203d-4f98-b2dc-7df0be1b0a03/DSS_Improvements.doc

For the DBMS, processor affinity dictates which processors available could
be used for query execution, but it says nothing about which processors to
use for any particular query.

For the degree of parallelism, this is on a per-query execution, and states
how many concurrent execution contexts a SINGLE query could consume
simultaneously. Keep in mind that the DBMS must satisfy queries from
multiple clients as well as maximize the throughput of any single execution.

Sincerely,


Anthony Thomas


--

"Kevin3NF" <kevin@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:e7BBlt8OHHA.1240@xxxxxxxxxxxxxxxxxxxxxxx
I may have my wires crossed, MAXDOP and processor affinity are not the
same
thing.

I believe MAXDOP is telling queries to only use x processors, but it does
not say which and differnt queries can use different ones.

The MCDBA study materials actually point out that on certain systems (more
than 4 cpus, more functions than SQL Server), you might want to NOT use
processor 0 (I/O requests go here), or the last CPUS (NIC requests go
through these...one per nic). This may or may not be relevant to Win2K3
or
SQL 2005

--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm

Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com


"Manoj Kumar" <ManojKumar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:05E814D8-454E-4F61-A36D-E60DFD29BCAE@xxxxxxxxxxxxxxxx
Thanks for the reply.

I notice that the processor utilization is almost uniform[across all 4
processors, via Perfmon and Task manager] even though the "max degree of
parallelism", "config_value and run_value are set to "1".

All of the available processors are selected in the "Processor" tab. It
appears that this overrides the "max degree of parallelism" values and
uses
all of the available processors.


--
Manoj Kumar


"Linchi Shea" wrote:

We always use all the processors. No issues there whatsoever.

Linchi

"Manoj Kumar" wrote:

Hi There,

Any known issues in using all available processors in the DB server?
[SQL
2000 Enterprize+sp4]

Right now it is configured to use just 1 processor.

TIA

--
Manoj Kumar




.

Relevant Pages

  • Re: chooses not to generate code at all
    ... DBMS is so that the DBMS engine can execute it when triggered by some update activity on the stored data. ... If there is no trigger, ... favorite database and see what the tools call things. ... If the procedure execution is not triggered by DBMS ...
    (comp.object)
  • Re: chooses not to generate code at all
    ... DBMS is so that the DBMS engine can execute it when triggered by some update activity on the stored data. ... If one stores a procedure but does not provide a trigger then that procedure is not an integral part of the DBMS; it's just stored there, which is exactly what DBs do. ... The trigger for execution is insert/update/delete of data by the DBMS and the invocation is controlled by the DBMS. ... high level control subsystem that expanded into several of classes with ...
    (comp.object)
  • Re: chooses not to generate code at all
    ... DBMS is so that the DBMS engine can execute it when triggered by some update activity on the stored data. ... So I stand by my original assertion that if there is no trigger for the stored procedure, it isn't an integral part of the DBMS execution. ... Consider a memory-mapped OODB like ObjectStore. ...
    (comp.object)
  • Re: Data driven people arguments
    ... > whose semantics is not relevant to the DBMS. ... > Contrast that with stored procedures whose execution is triggered by the ... if I keep my storing and retrieving logic seperate from my other ...
    (comp.object)
Loading