I am working with a SQL Server 7 (SP4) box with 8 processors. When I
bring up Enterprise Manager and go to the Processor tab under server
properties, I can see the configuration for the parallel execution of
queries. In the list box, it is set to use only 1 processor. I set
it to use 2 processors, and click Apply, and then OK. When I go back
to the dialog box, it is set to use only 1 processor again, as if I
had never set the option. When I run sp_configure, it would indicate
that I have, in fact, set the parallel execution to use 2 processors
(max degree of parallelism = 2). It's only from the processor tab
that I think I've only set it to use 1 processor.
Is there a way I can verify that 2 processors are getting used in the
query parallelism? Or is it possible that another configuration
setting is preventing this from happening?
Here is the complete sp_configure, if it helps:
affinity mask 0 2147483647 0 0
allow updates 0 1 1 1
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 52 52
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 32 32
max degree of parallelism 0 32 2 2
max server memory (MB) 4 2147483647 5500 5500
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 500 500
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 5500 5500
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 0 0
set working set size 0 1 1 1
show advanced options 0 1 1 1
spin counter 1 2147483647 20000 20000
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196609 196609
Unicode locale id 0 2147483647 1033 1033
user connections 0 32767 0 0
user options 0 4095 0 0Sp_configure looks right. Might be a bug in EM?
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"AAAWalrus" <aaawalrus@.yahoo.com> wrote in message
news:8b266bc2.0311110819.3541dc88@.posting.google.com...
> I am working with a SQL Server 7 (SP4) box with 8 processors. When I
> bring up Enterprise Manager and go to the Processor tab under server
> properties, I can see the configuration for the parallel execution of
> queries. In the list box, it is set to use only 1 processor. I set
> it to use 2 processors, and click Apply, and then OK. When I go back
> to the dialog box, it is set to use only 1 processor again, as if I
> had never set the option. When I run sp_configure, it would indicate
> that I have, in fact, set the parallel execution to use 2 processors
> (max degree of parallelism = 2). It's only from the processor tab
> that I think I've only set it to use 1 processor.
> Is there a way I can verify that 2 processors are getting used in the
> query parallelism? Or is it possible that another configuration
> setting is preventing this from happening?
> Here is the complete sp_configure, if it helps:
> affinity mask 0 2147483647 0 0
> allow updates 0 1 1 1
> cost threshold for parallelism 0 32767 5 5
> cursor threshold -1 2147483647 -1 -1
> default language 0 9999 0 0
> default sortorder id 0 255 52 52
> extended memory size (MB) 0 2147483647 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 1600000 0 0
> language in cache 3 100 3 3
> language neutral full-text 0 1 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max async IO 1 255 32 32
> max degree of parallelism 0 32 2 2
> max server memory (MB) 4 2147483647 5500 5500
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 10 1024 500 500
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 5500 5500
> nested triggers 0 1 1 1
> network packet size (B) 512 65535 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 1 1
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 0 0
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 5 5
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 0 0
> resource timeout (s) 5 2147483647 10 10
> scan for startup procs 0 1 0 0
> set working set size 0 1 1 1
> show advanced options 0 1 1 1
> spin counter 1 2147483647 20000 20000
> time slice (ms) 50 1000 100 100
> two digit year cutoff 1753 9999 2049 2049
> Unicode comparison style 0 2147483647 196609 196609
> Unicode locale id 0 2147483647 1033 1033
> user connections 0 32767 0 0
> user options 0 4095 0 0|||You can either inspect the query execution plan (should see parallelism for
queries/subqueries that cost more than 5)
or/and to take a look in Processor Usage in Performance Monitor if you can
afford to be the only one to make traffic on the server
at certain time
Uzytkownik "AAAWalrus" <aaawalrus@.yahoo.com> napisal w wiadomosci
news:8b266bc2.0311110819.3541dc88@.posting.google.com...
> I am working with a SQL Server 7 (SP4) box with 8 processors. When I
> bring up Enterprise Manager and go to the Processor tab under server
> properties, I can see the configuration for the parallel execution of
> queries. In the list box, it is set to use only 1 processor. I set
> it to use 2 processors, and click Apply, and then OK. When I go back
> to the dialog box, it is set to use only 1 processor again, as if I
> had never set the option. When I run sp_configure, it would indicate
> that I have, in fact, set the parallel execution to use 2 processors
> (max degree of parallelism = 2). It's only from the processor tab
> that I think I've only set it to use 1 processor.
> Is there a way I can verify that 2 processors are getting used in the
> query parallelism? Or is it possible that another configuration
> setting is preventing this from happening?
> Here is the complete sp_configure, if it helps:
> affinity mask 0 2147483647 0 0
> allow updates 0 1 1 1
> cost threshold for parallelism 0 32767 5 5
> cursor threshold -1 2147483647 -1 -1
> default language 0 9999 0 0
> default sortorder id 0 255 52 52
> extended memory size (MB) 0 2147483647 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 1600000 0 0
> language in cache 3 100 3 3
> language neutral full-text 0 1 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max async IO 1 255 32 32
> max degree of parallelism 0 32 2 2
> max server memory (MB) 4 2147483647 5500 5500
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 10 1024 500 500
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 5500 5500
> nested triggers 0 1 1 1
> network packet size (B) 512 65535 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 1 1
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 0 0
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 5 5
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 0 0
> resource timeout (s) 5 2147483647 10 10
> scan for startup procs 0 1 0 0
> set working set size 0 1 1 1
> show advanced options 0 1 1 1
> spin counter 1 2147483647 20000 20000
> time slice (ms) 50 1000 100 100
> two digit year cutoff 1753 9999 2049 2049
> Unicode comparison style 0 2147483647 196609 196609
> Unicode locale id 0 2147483647 1033 1033
> user connections 0 32767 0 0
> user options 0 4095 0 0|||the best way is to use profiler, capture the classes for degree of
parallelism.
BTW, just having a cost of 5 does not guarentee parallism, only certain
query plan operators can be run in parallel.
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Tomasz" <tp@.nospam.com> wrote in message
news:efKp2EHqDHA.2444@.TK2MSFTNGP09.phx.gbl...
> You can either inspect the query execution plan (should see parallelism
for
> queries/subqueries that cost more than 5)
> or/and to take a look in Processor Usage in Performance Monitor if you can
> afford to be the only one to make traffic on the server
> at certain time
> Uzytkownik "AAAWalrus" <aaawalrus@.yahoo.com> napisal w wiadomosci
> news:8b266bc2.0311110819.3541dc88@.posting.google.com...
> > I am working with a SQL Server 7 (SP4) box with 8 processors. When I
> > bring up Enterprise Manager and go to the Processor tab under server
> > properties, I can see the configuration for the parallel execution of
> > queries. In the list box, it is set to use only 1 processor. I set
> > it to use 2 processors, and click Apply, and then OK. When I go back
> > to the dialog box, it is set to use only 1 processor again, as if I
> > had never set the option. When I run sp_configure, it would indicate
> > that I have, in fact, set the parallel execution to use 2 processors
> > (max degree of parallelism = 2). It's only from the processor tab
> > that I think I've only set it to use 1 processor.
> >
> > Is there a way I can verify that 2 processors are getting used in the
> > query parallelism? Or is it possible that another configuration
> > setting is preventing this from happening?
> >
> > Here is the complete sp_configure, if it helps:
> >
> > affinity mask 0 2147483647 0 0
> > allow updates 0 1 1 1
> > cost threshold for parallelism 0 32767 5 5
> > cursor threshold -1 2147483647 -1 -1
> > default language 0 9999 0 0
> > default sortorder id 0 255 52 52
> > extended memory size (MB) 0 2147483647 0 0
> > fill factor (%) 0 100 0 0
> > index create memory (KB) 704 1600000 0 0
> > language in cache 3 100 3 3
> > language neutral full-text 0 1 0 0
> > lightweight pooling 0 1 0 0
> > locks 5000 2147483647 0 0
> > max async IO 1 255 32 32
> > max degree of parallelism 0 32 2 2
> > max server memory (MB) 4 2147483647 5500 5500
> > max text repl size (B) 0 2147483647 65536 65536
> > max worker threads 10 1024 500 500
> > media retention 0 365 0 0
> > min memory per query (KB) 512 2147483647 1024 1024
> > min server memory (MB) 0 2147483647 5500 5500
> > nested triggers 0 1 1 1
> > network packet size (B) 512 65535 4096 4096
> > open objects 0 2147483647 0 0
> > priority boost 0 1 1 1
> > query governor cost limit 0 2147483647 0 0
> > query wait (s) -1 2147483647 -1 -1
> > recovery interval (min) 0 32767 0 0
> > remote access 0 1 1 1
> > remote login timeout (s) 0 2147483647 5 5
> > remote proc trans 0 1 0 0
> > remote query timeout (s) 0 2147483647 0 0
> > resource timeout (s) 5 2147483647 10 10
> > scan for startup procs 0 1 0 0
> > set working set size 0 1 1 1
> > show advanced options 0 1 1 1
> > spin counter 1 2147483647 20000 20000
> > time slice (ms) 50 1000 100 100
> > two digit year cutoff 1753 9999 2049 2049
> > Unicode comparison style 0 2147483647 196609 196609
> > Unicode locale id 0 2147483647 1033 1033
> > user connections 0 32767 0 0
> > user options 0 4095 0 0
>|||I am chalking this up to a bug in the SQL 7 Enterprise Manager. When
I view the same option on the SQL 7 database from SQL 2000 Enterprise
Manager (personal edition installed on my PC), it properly updates and
shows the number of processors to use on parallel queries. What makes
me a little worried, though, is that I have not found a relative MS
support article. I've been contemplating calling MS support about it,
but that's a whole other set of hassles.
Thanks for your help!
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message news:<#JdolkHqDHA.1676@.TK2MSFTNGP09.phx.gbl>...
> the best way is to use profiler, capture the classes for degree of
> parallelism.
> BTW, just having a cost of 5 does not guarentee parallism, only certain
> query plan operators can be run in parallel.
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Tomasz" <tp@.nospam.com> wrote in message
> news:efKp2EHqDHA.2444@.TK2MSFTNGP09.phx.gbl...
> > You can either inspect the query execution plan (should see parallelism
> for
> > queries/subqueries that cost more than 5)
> > or/and to take a look in Processor Usage in Performance Monitor if you can
> > afford to be the only one to make traffic on the server
> > at certain time
