Parallelism Question

If SQL Server is designed for multi processor systems, how can running
a query in parallel make such a dramatic difference to performance ?

We have a reasonably simple query which brings in data from a few none
complex views. If we run it on our 2x2.4Ghz Xeon server it takes 6
minutes plus to run. If we run this on the same server with
OPTION(MAXDOP 1) at the end of the same query it takes less than a
second.

Examining the execution plan, the only difference I have been able to
see is that parallelism is taking up 96% of the run time when using
two processors. This drops when using the one so a sort takes up the
vast majority of the time for the query to run.

OK, so running in parallel should mean that it's run in various parts
and then 'joined up' later for performance gains, but how can it get
it so wrong (timewise) ?

If this is the case, will I see a significant difference changing our
server to use a single processor, which seems completely the wrong
approach (or should I do this on each query in each app - eek) ?

Do we have a problem that we don't know about that causes it to take
this long ?

What can we do ? Ideally, using both processors would seem to be
preferrable.We've changed the server to use a single processor at the moment and
the report that my query is based on works almost instantly. We're
waiting to see what effect this has for other users, but so far,
no-one has complained.

Are we wasting a second processor ?|||"Ryan" <ryanofford@.hotmail.com> wrote in message
news:7802b79d.0312160816.68d60164@.posting.google.c om...
> We've changed the server to use a single processor at the moment and
> the report that my query is based on works almost instantly. We're
> waiting to see what effect this has for other users, but so far,
> no-one has complained.
> Are we wasting a second processor ?

No. There are definitely times it can help.

You may want to open a ticket with MS. In general, when the query optimizer
finds such a poor optimization they consider it a bug. (If you can, review
Kalen Delaney's article in this month's SQL Server Magazine.)

Parallel vs. serial plan

Hi,
What would be the advantages of lengthy report parallel vs.
serial execution, say on a 4-way SMP system, if the query
in itself is not able to produce sustained load of more
than 4 seconds on a single CPU of the system. I'm using MS
SQL Server 2000 on MS Windows 2000 with the lastest service
packs.
Many thanks,
OskOsk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
I would say that 4 seconds is a long time in the CPU world. It may not
be an issue if this is run off-hours, but during production hours, if
run many times, it could be more of an issue. Parallel executions
generally consume more CPU than their single-CPU brothers. It's just
more costly to manage parallel executions. So in your case, you are
potentially tying up one CPU out of four for four seconds, leaving the
other three to perform other tasks. With a parallel plan, depending on
how many CPUs SQL Server chooses to use, you'd end up tying up more CPU
for more than four CPU seconds, but the query could finish in 2.5
seconds.
Are you saying you have disabled parallel plans for this query using a
MAXDOP hint or have changed the corresponding server setting to prevent
parallel executions?
David Gugick
Imceda Software
www.imceda.com|||SQL-Server does not know how important a particular query is for you. It
doesn't discriminate queries that way. It just estimates (for each
query) whether executing the parallel plan is likely to be faster than
the serial plan. If it is, and the server is not too stressed, then it
will execute the parallel plan. Otherwise, it will execute the serial
plan.
However, there is a threshold below which a parallel plan will not be
considered. By default, this "cost threshold for parallelism" is set to
5. With this setting, SQL-Server will not consider a parallel plan if
the estimated execution time of the serial plan is less than 5 seconds.
Hope this helps,
Gert-Jan
Osk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
> --
> Many thanks,
> Osk|||Yes, I have changed the 'max degree of parallelism' server
setting to 1. So as I understand it now, when the optimizer
thinks the query can benefit from executing in parllel at
the same time when the rest of CPUs are more or less idle
then, despite the parallel plan being more costly in terms
of CPU time, the query will return results faster. So I
need look at what kind of load is placed on the rest of
CPUs during the time the query runs to determine whether
there will be any benefit or parallelism.

>--Original Message--
>Osk wrote:
>I would say that 4 seconds is a long time in the CPU
world. It may not
>be an issue if this is run off-hours, but during
production hours, if
>run many times, it could be more of an issue. Parallel
executions
>generally consume more CPU than their single-CPU brothers.
It's just
>more costly to manage parallel executions. So in your
case, you are
>potentially tying up one CPU out of four for four seconds,
leaving the
>other three to perform other tasks. With a parallel plan,
depending on
>how many CPUs SQL Server chooses to use, you'd end up
tying up more CPU
>for more than four CPU seconds, but the query could finish
in 2.5
>seconds.
>Are you saying you have disabled parallel plans for this
query using a
>MAXDOP hint or have changed the corresponding server
setting to prevent
>parallel executions?
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||anonymous@.discussions.microsoft.com wrote:
> Yes, I have changed the 'max degree of parallelism' server
> setting to 1. So as I understand it now, when the optimizer
> thinks the query can benefit from executing in parllel at
> the same time when the rest of CPUs are more or less idle
> then, despite the parallel plan being more costly in terms
> of CPU time, the query will return results faster. So I
> need look at what kind of load is placed on the rest of
> CPUs during the time the query runs to determine whether
> there will be any benefit or parallelism.
>
I thik you might be overthinking this a bit. Why have you decided to
change the MAX degree OF PARALLELISM on the server and not let SQL
Server manage parallel queries on its own? Occasionally, someone reports
an issue with a parallel plan which can be easily fixed with a MAXDOP
(1) in the query.
David Gugick
Imceda Software
www.imceda.com|||Well, that's how I understood it. If I'm wrong please
correct me.
I've set the server-wide MAXDOP to 1 because of two reasons:
1) to see whether placing the load of reports on a single
CPU will allow smaller queries have fulfilled their CPU
needs quicker than when they would have to wait for a
report to execute in parallel on all CPUs (although this
seems to work only in cases when the report doesn't hold
locks incompatible with other query's locks)
2) I can't provide hints at the T-SQL level yet
Thanks,
Osk

>--Original Message--
>anonymous@.discussions.microsoft.com wrote:
>I thik you might be overthinking this a bit. Why have you
decided to
>change the MAX degree OF PARALLELISM on the server and not
let SQL
>Server manage parallel queries on its own? Occasionally,
someone reports
>an issue with a parallel plan which can be easily fixed
with a MAXDOP
>(1) in the query.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||Osk wrote:
> Well, that's how I understood it. If I'm wrong please
> correct me.
> I've set the server-wide MAXDOP to 1 because of two reasons:
> 1) to see whether placing the load of reports on a single
> CPU will allow smaller queries have fulfilled their CPU
> needs quicker than when they would have to wait for a
> report to execute in parallel on all CPUs (although this
> seems to work only in cases when the report doesn't hold
> locks incompatible with other query's locks)
> 2) I can't provide hints at the T-SQL level yet
>
MAXDOP at the server level does not mean that reports are only going to
run on a single CPU. Unless what you're saying is that only one report
can run at a time and because of that it will only run on a single CPU.
MAXDOP 1 just means SQL Server will only use a single CPU for a query,
but the CPU used could be any available (maybe you already understood
that). Obviously, if 4 reports were executed simultaneously and you had
4 CPUs on the server, they could be running on different CPUs.
If you have concerns, I guess you could leave the option on, but I would
suggest you test with the default settings as well.
David Gugick
Imceda Software
www.imceda.comsql

Parallel vs. serial plan

Hi,
What would be the advantages of lengthy report parallel vs.
serial execution, say on a 4-way SMP system, if the query
in itself is not able to produce sustained load of more
than 4 seconds on a single CPU of the system. I'm using MS
SQL Server 2000 on MS Windows 2000 with the lastest service
packs.
Many thanks,
Osk
Osk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
I would say that 4 seconds is a long time in the CPU world. It may not
be an issue if this is run off-hours, but during production hours, if
run many times, it could be more of an issue. Parallel executions
generally consume more CPU than their single-CPU brothers. It's just
more costly to manage parallel executions. So in your case, you are
potentially tying up one CPU out of four for four seconds, leaving the
other three to perform other tasks. With a parallel plan, depending on
how many CPUs SQL Server chooses to use, you'd end up tying up more CPU
for more than four CPU seconds, but the query could finish in 2.5
seconds.
Are you saying you have disabled parallel plans for this query using a
MAXDOP hint or have changed the corresponding server setting to prevent
parallel executions?
David Gugick
Imceda Software
www.imceda.com
|||SQL-Server does not know how important a particular query is for you. It
doesn't discriminate queries that way. It just estimates (for each
query) whether executing the parallel plan is likely to be faster than
the serial plan. If it is, and the server is not too stressed, then it
will execute the parallel plan. Otherwise, it will execute the serial
plan.
However, there is a threshold below which a parallel plan will not be
considered. By default, this "cost threshold for parallelism" is set to
5. With this setting, SQL-Server will not consider a parallel plan if
the estimated execution time of the serial plan is less than 5 seconds.
Hope this helps,
Gert-Jan
Osk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
> --
> Many thanks,
> Osk
|||Yes, I have changed the 'max degree of parallelism' server
setting to 1. So as I understand it now, when the optimizer
thinks the query can benefit from executing in parllel at
the same time when the rest of CPUs are more or less idle
then, despite the parallel plan being more costly in terms
of CPU time, the query will return results faster. So I
need look at what kind of load is placed on the rest of
CPUs during the time the query runs to determine whether
there will be any benefit or parallelism.

>--Original Message--
>Osk wrote:
>I would say that 4 seconds is a long time in the CPU
world. It may not
>be an issue if this is run off-hours, but during
production hours, if
>run many times, it could be more of an issue. Parallel
executions
>generally consume more CPU than their single-CPU brothers.
It's just
>more costly to manage parallel executions. So in your
case, you are
>potentially tying up one CPU out of four for four seconds,
leaving the
>other three to perform other tasks. With a parallel plan,
depending on
>how many CPUs SQL Server chooses to use, you'd end up
tying up more CPU
>for more than four CPU seconds, but the query could finish
in 2.5
>seconds.
>Are you saying you have disabled parallel plans for this
query using a
>MAXDOP hint or have changed the corresponding server
setting to prevent
>parallel executions?
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>
|||anonymous@.discussions.microsoft.com wrote:
> Yes, I have changed the 'max degree of parallelism' server
> setting to 1. So as I understand it now, when the optimizer
> thinks the query can benefit from executing in parllel at
> the same time when the rest of CPUs are more or less idle
> then, despite the parallel plan being more costly in terms
> of CPU time, the query will return results faster. So I
> need look at what kind of load is placed on the rest of
> CPUs during the time the query runs to determine whether
> there will be any benefit or parallelism.
>
I thik you might be overthinking this a bit. Why have you decided to
change the MAX DEGREE OF PARALLELISM on the server and not let SQL
Server manage parallel queries on its own? Occasionally, someone reports
an issue with a parallel plan which can be easily fixed with a MAXDOP
(1) in the query.
David Gugick
Imceda Software
www.imceda.com
|||Well, that's how I understood it. If I'm wrong please
correct me.
I've set the server-wide MAXDOP to 1 because of two reasons:
1) to see whether placing the load of reports on a single
CPU will allow smaller queries have fulfilled their CPU
needs quicker than when they would have to wait for a
report to execute in parallel on all CPUs (although this
seems to work only in cases when the report doesn't hold
locks incompatible with other query's locks)
2) I can't provide hints at the T-SQL level yet
Thanks,
Osk

>--Original Message--
>anonymous@.discussions.microsoft.com wrote:
>I thik you might be overthinking this a bit. Why have you
decided to
>change the MAX DEGREE OF PARALLELISM on the server and not
let SQL
>Server manage parallel queries on its own? Occasionally,
someone reports
>an issue with a parallel plan which can be easily fixed
with a MAXDOP
>(1) in the query.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>
|||Osk wrote:
> Well, that's how I understood it. If I'm wrong please
> correct me.
> I've set the server-wide MAXDOP to 1 because of two reasons:
> 1) to see whether placing the load of reports on a single
> CPU will allow smaller queries have fulfilled their CPU
> needs quicker than when they would have to wait for a
> report to execute in parallel on all CPUs (although this
> seems to work only in cases when the report doesn't hold
> locks incompatible with other query's locks)
> 2) I can't provide hints at the T-SQL level yet
>
MAXDOP at the server level does not mean that reports are only going to
run on a single CPU. Unless what you're saying is that only one report
can run at a time and because of that it will only run on a single CPU.
MAXDOP 1 just means SQL Server will only use a single CPU for a query,
but the CPU used could be any available (maybe you already understood
that). Obviously, if 4 reports were executed simultaneously and you had
4 CPUs on the server, they could be running on different CPUs.
If you have concerns, I guess you could leave the option on, but I would
suggest you test with the default settings as well.
David Gugick
Imceda Software
www.imceda.com

Parallel vs. serial plan

Hi,
What would be the advantages of lengthy report parallel vs.
serial execution, say on a 4-way SMP system, if the query
in itself is not able to produce sustained load of more
than 4 seconds on a single CPU of the system. I'm using MS
SQL Server 2000 on MS Windows 2000 with the lastest service
packs.
--
Many thanks,
OskOsk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
I would say that 4 seconds is a long time in the CPU world. It may not
be an issue if this is run off-hours, but during production hours, if
run many times, it could be more of an issue. Parallel executions
generally consume more CPU than their single-CPU brothers. It's just
more costly to manage parallel executions. So in your case, you are
potentially tying up one CPU out of four for four seconds, leaving the
other three to perform other tasks. With a parallel plan, depending on
how many CPUs SQL Server chooses to use, you'd end up tying up more CPU
for more than four CPU seconds, but the query could finish in 2.5
seconds.
Are you saying you have disabled parallel plans for this query using a
MAXDOP hint or have changed the corresponding server setting to prevent
parallel executions?
--
David Gugick
Imceda Software
www.imceda.com|||SQL-Server does not know how important a particular query is for you. It
doesn't discriminate queries that way. It just estimates (for each
query) whether executing the parallel plan is likely to be faster than
the serial plan. If it is, and the server is not too stressed, then it
will execute the parallel plan. Otherwise, it will execute the serial
plan.
However, there is a threshold below which a parallel plan will not be
considered. By default, this "cost threshold for parallelism" is set to
5. With this setting, SQL-Server will not consider a parallel plan if
the estimated execution time of the serial plan is less than 5 seconds.
Hope this helps,
Gert-Jan
Osk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
> --
> Many thanks,
> Osk|||Yes, I have changed the 'max degree of parallelism' server
setting to 1. So as I understand it now, when the optimizer
thinks the query can benefit from executing in parllel at
the same time when the rest of CPUs are more or less idle
then, despite the parallel plan being more costly in terms
of CPU time, the query will return results faster. So I
need look at what kind of load is placed on the rest of
CPUs during the time the query runs to determine whether
there will be any benefit or parallelism.
>--Original Message--
>Osk wrote:
>> Hi,
>> What would be the advantages of lengthy report parallel vs.
>> serial execution, say on a 4-way SMP system, if the query
>> in itself is not able to produce sustained load of more
>> than 4 seconds on a single CPU of the system. I'm using MS
>> SQL Server 2000 on MS Windows 2000 with the lastest service
>> packs.
>I would say that 4 seconds is a long time in the CPU
world. It may not
>be an issue if this is run off-hours, but during
production hours, if
>run many times, it could be more of an issue. Parallel
executions
>generally consume more CPU than their single-CPU brothers.
It's just
>more costly to manage parallel executions. So in your
case, you are
>potentially tying up one CPU out of four for four seconds,
leaving the
>other three to perform other tasks. With a parallel plan,
depending on
>how many CPUs SQL Server chooses to use, you'd end up
tying up more CPU
>for more than four CPU seconds, but the query could finish
in 2.5
>seconds.
>Are you saying you have disabled parallel plans for this
query using a
>MAXDOP hint or have changed the corresponding server
setting to prevent
>parallel executions?
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||anonymous@.discussions.microsoft.com wrote:
> Yes, I have changed the 'max degree of parallelism' server
> setting to 1. So as I understand it now, when the optimizer
> thinks the query can benefit from executing in parllel at
> the same time when the rest of CPUs are more or less idle
> then, despite the parallel plan being more costly in terms
> of CPU time, the query will return results faster. So I
> need look at what kind of load is placed on the rest of
> CPUs during the time the query runs to determine whether
> there will be any benefit or parallelism.
>
I thik you might be overthinking this a bit. Why have you decided to
change the MAX DEGREE OF PARALLELISM on the server and not let SQL
Server manage parallel queries on its own? Occasionally, someone reports
an issue with a parallel plan which can be easily fixed with a MAXDOP
(1) in the query.
--
David Gugick
Imceda Software
www.imceda.com|||Well, that's how I understood it. If I'm wrong please
correct me.
I've set the server-wide MAXDOP to 1 because of two reasons:
1) to see whether placing the load of reports on a single
CPU will allow smaller queries have fulfilled their CPU
needs quicker than when they would have to wait for a
report to execute in parallel on all CPUs (although this
seems to work only in cases when the report doesn't hold
locks incompatible with other query's locks)
2) I can't provide hints at the T-SQL level yet
--
Thanks,
Osk
>--Original Message--
>anonymous@.discussions.microsoft.com wrote:
>> Yes, I have changed the 'max degree of parallelism' server
>> setting to 1. So as I understand it now, when the optimizer
>> thinks the query can benefit from executing in parllel at
>> the same time when the rest of CPUs are more or less idle
>> then, despite the parallel plan being more costly in terms
>> of CPU time, the query will return results faster. So I
>> need look at what kind of load is placed on the rest of
>> CPUs during the time the query runs to determine whether
>> there will be any benefit or parallelism.
>I thik you might be overthinking this a bit. Why have you
decided to
>change the MAX DEGREE OF PARALLELISM on the server and not
let SQL
>Server manage parallel queries on its own? Occasionally,
someone reports
>an issue with a parallel plan which can be easily fixed
with a MAXDOP
>(1) in the query.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||Osk wrote:
> Well, that's how I understood it. If I'm wrong please
> correct me.
> I've set the server-wide MAXDOP to 1 because of two reasons:
> 1) to see whether placing the load of reports on a single
> CPU will allow smaller queries have fulfilled their CPU
> needs quicker than when they would have to wait for a
> report to execute in parallel on all CPUs (although this
> seems to work only in cases when the report doesn't hold
> locks incompatible with other query's locks)
> 2) I can't provide hints at the T-SQL level yet
>
MAXDOP at the server level does not mean that reports are only going to
run on a single CPU. Unless what you're saying is that only one report
can run at a time and because of that it will only run on a single CPU.
MAXDOP 1 just means SQL Server will only use a single CPU for a query,
but the CPU used could be any available (maybe you already understood
that). Obviously, if 4 reports were executed simultaneously and you had
4 CPUs on the server, they could be running on different CPUs.
If you have concerns, I guess you could leave the option on, but I would
suggest you test with the default settings as well.
David Gugick
Imceda Software
www.imceda.com

Parallel SQL Destination Load Pre-Process error

I cannot find any information on this error. It occurs on packages that are writing to the same table using a sql server destination. I suppose it would be a good exercise in error handling, but I'd rather avoid it.

Error: Unable to prepare the SSIS bulk insert for data insertion.
Error: component "FACT_BillingLineItem Destination" (22652) failed the pre-execute phase and returned error code 0xC0202071.
The cause of these errors, I eventually determined, was that SQL Server/SSIS by default uses up system memory until it goes loopy, unless you tell it not to by configuring the SQL Server "Maximum Server Memory" property.

Parallel SQL Destination Load Pre-Process error

I cannot find any information on this error. It occurs on packages that are writing to the same table using a sql server destination. I suppose it would be a good exercise in error handling, but I'd rather avoid it.

Error: Unable to prepare the SSIS bulk insert for data insertion.
Error: component "FACT_BillingLineItem Destination" (22652) failed the pre-execute phase and returned error code 0xC0202071.The cause of these errors, I eventually determined, was that SQL Server/SSIS by default uses up system memory until it goes loopy, unless you tell it not to by configuring the SQL Server "Maximum Server Memory" property.

Parallel Processing of Partition

Guys,
I would like to thanks Elizabeth for a great document. Appreciate it.
Under " Maximize parallelism during processing", I see the following bench mark.

# of Processors

# of Partitions to be processed in parallel

4

2 – 4

8

4 – 8

16

6 – 16

In our scenario, we have a huge dataset which means processing about 20 partitions in parallel. The box we have is a beefy box DL 585 4dual core CPU with 32 GB memory and we are using the 64 bit architecture.
However when monitoring the trace I see the following:-

1 - Process

Processing of Fact 2001 Partition

8:05:43

10:11:04

25 - Execute SQL

SQL Statement

9:38:06

9:38:06

17 - Read Data

Reading Data from Fact 2001 Partition

9:38:06

10:06:20

16 - Write Data

Writing Data to Fact 2001 Partition

8:05:43

10:10:59

20 - Build Indexes

Build Indexes

10:11:00

10:11:03

28 - Build Aggregations and Indexes

Build Aggregations and Indexes

10:11:00

10:11:03

Based on the above the thread for processing the fact kicks off at 8:05am. However the actual sql to the underlying database does not get kicked off until 9:38am.

Does this mean the same thing that it is waiting for CPUs? However when I look at the perfMon stats on the Server side, the CPU usage is pretty vanilla and I do not understand how to correlate this.

Any suggestions is much appreciated.

Rgds

Hari

Running processing of 20 partitions in parallel on 4 processor box is bit of the overkill. The fact you have lots of data to process doesnt mean you should start processing many partitions in parallel.

As Perf Guide indicates, number of processors should determine number of parallel operations. If you try to start too many processing operations, you will get into behavior you observe. Say you told Analysis Server to process 20 partitions in parallel but there not enought resources. In this case operations running in parallel are going to start fighting for resoures and some of the become blocked. Analysis Server also starts many operations within sigle partition processing job in parallel. what you see is that Execute SQL and Read data started in parallel, but Write data didnt start till some data became avaliable.

You should also take a look at optimizing your SQL server database for the type of queries Analysis Server sends to speed up sending data to it.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Ahh..thanks Edward.
Regarding this "The fact you have lots of data to process doesnt mean you should start processing many partitions in parallel."

At this point, we are doing a full process on the database though it is partitioned.So by default, it first starts the threads for the cubes in parallel and then spawns new threads for measuregroups and finally partitions in parallels and this is done by default. Any ways I can control this.

We are using the biggest possible box that is available now.
Quadra-core CPUs do not come out until June of this year.
Like you say, we are also working with the DB2 team to optimize the SQL by building indexes on the partition columns.

Just

|||

Hi Edward,

I am the Windows Server SA who supports Hari's machine. We had a similar issue on a 32-bit Windows 2003 OLAP machine that was acting the same way when building cubes. Analysis Services was running out of memory but we weren't seeing any memory or processor spikes. I opened a case and the engineer explained this was due to the fact that there was a 2 GB limitation, regardless of how much RAM the server has. We tried adding the /3GB and /USERVA switches. Neither worked and the solution to get past this was to upgrade to 64-bit.

How much memory is allocated to an application in the 64-bit version of Windows 2003? Is there a way to tell how much memory it's utilizing?

In addition the OS is seeing all 32 GB of RAM, but I can't tell if it's utilizing all 32 GB. Are the /PAE, /USERVA, and/or /AWE switches relevant to Win2K3 64-bit? Do I need to add anything to the boot.ini to ensure applications are able to use all available RAM? Do you have any suggestions for optimizing performance in SQL and or Analysis Services?

Thanks - Gib

|||

On 64bit machine there is no worry about single process being able to access as much memory as your machine can give to it. No modifying flags or setting special options for have to access more memory.

The amount of memory your Analysis Server will use during processing is depenedent on the size of the objects you will be processing and fact whether you will be processing them in parallel.

I'd say if you moved to 64bit machine with 32Gb of Ram, you should be able to handle quite a few things in parallel, so just go ahead an run your processing and see how high memory utilization goes.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Edward. I'm going to shift my question to Hari's problem. Specifically in relation to your teply yesterday.

"Running processing of 20 partitions in parallel on 4 processor box is bit of the overkill. The fact you have lots of data to process doesnt mean you should start processing many partitions in parallel.

As Perf Guide indicates, number of processors should determine number of parallel operations. If you try to start too many processing operations, you will get into behavior you observe. Say you told Analysis Server to process 20 partitions in parallel but there not enought resources. In this case operations running in parallel are going to start fighting for resoures and some of the become blocked. Analysis Server also starts many operations within sigle partition processing job in parallel. what you see is that Execute SQL and Read data started in parallel, but Write data didnt start till some data became avaliable.

You should also take a look at optimizing your SQL server database for the type of queries Analysis Server sends to speed up sending data to it."

Hari added a chart that has a Processor to Parallel Partition correlation.

# of Processors

# of Partitions to be processed in parallel

4

2 – 4

8

4 – 8

16

6 – 16

We are running on an HP DL585 it has 4 Dual Core AMD Processors. That having been said can OLAP utilize them as 8 Processors or will it only see them as 4?

The other thing you had mentioned is 20 partitions is overkill. Does that mean this type of lag is to be expected? Is there any kind of tweaking that can be done either on the OS or in SQL to improve performance?

As it stands now, Hari isn't seeing any CPU or Memory spikes when the delay is occurring. If operations are blocked and fighting for resources will we see 100 % Memory and CPU utilization?

Thanks - Gib

|||

Yes, you should count a single core as a processor for the purpose of the chart you mention. DL 585 is great macine and you should count as you have 8 processors.
Therefore the per guide recommends you process 4 to 8 partitions in parallel.

The bottleneck might not be CPU or memory. It is also your relational database. You should take a look at your relational database and see how hard it is working to deliver data to Analysis Server. You might want to run database tuninig advisor to build more indexes supporting queries sent to it.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward, Do you know if tuning the relational database will enable Hari to process 20 partitions at the same time? Do you know of anyone who is doing this on a Quad Dual Core server?

According to the chart it doesn't look like this possible, even with 16 processors. I'm wondering if there is any kind of tweaking that can be done so that it's possible.

Thanks - Gib

|||

That is not going to help. Number of partitions you process in parallel depends on how many operations in parallel your hardware can perform. If you tune your system, optimize your relational database, and you still not satisfied with processing performance, you might start looking at getting newer machines with higher number of processors.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Parallel processing not supported in standard edition (use developer)

Hi,

I have a developer edition of SQL2005 upgraded to SP1 plus hotfixes on the dev box where I create my stuff.

Now, when I try to set a SSIS cube processing task to process in parallel, it tells me "Parallel processing is not supported in standard edition of analysis services."

Why?

I cannot edit the package on the enterprise edition used on production. I have to deploy as it is on dev.

Thanks,

Philippe

Hi,

The SQL Server build is 9.00.2153.00

I note that the problem exist only in the SSIS task. If I specify the parallel processing option when processing from management Studio, I do not get the error.

I bet it is a bug in SP1 or in the hotfix

Philippe

|||I have this exact same problem - I'm running Enterprise Edition, but in SSIS I get the error that "Parallel processing is not supported on STandard edition of Analysis Services". Can anyone confirm if this is a known issue and whether there is a workaround?

Parallel processing not supported in standard edition (use developer)

Hi,

I have a developer edition of SQL2005 upgraded to SP1 plus hotfixes on the dev box where I create my stuff.

Now, when I try to set a SSIS cube processing task to process in parallel, it tells me "Parallel processing is not supported in standard edition of analysis services."

Why?

I cannot edit the package on the enterprise edition used on production. I have to deploy as it is on dev.

Thanks,

Philippe

Hi,

The SQL Server build is 9.00.2153.00

I note that the problem exist only in the SSIS task. If I specify the parallel processing option when processing from management Studio, I do not get the error.

I bet it is a bug in SP1 or in the hotfix

Philippe

|||I have this exact same problem - I'm running Enterprise Edition, but in SSIS I get the error that "Parallel processing is not supported on STandard edition of Analysis Services". Can anyone confirm if this is a known issue and whether there is a workaround?

Parallel Plans

Hi,
Our production server is a Compaq Proliant with 2 CPU. How can I determine
that my queries benefit from having 2 CPU? Are parallel plans automatically
created our I must configure server options?
Any help would be greatly appreciated.
LeilaYou can check if your server is set to use parallel query plans on the
processor tab of the server properties in Enterprise Manager.
You can check if any queries with a parallel plan are executed by tracing
the DegreeOfParallelism event with SQL Profiler. See the topic "Degree of
Parallelism" in Books Online for details.
Jacco Schalkwijk
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:OmMhaHYzEHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> Our production server is a Compaq Proliant with 2 CPU. How can I determine
> that my queries benefit from having 2 CPU? Are parallel plans
> automatically
> created our I must configure server options?
> Any help would be greatly appreciated.
> Leila
>|||If you are using the database with more than one user, then you are
likely to benefit from the extra CPU even if no parallellism is chosen,
because in that case the workload can be divided over 2 CPU's instead of
just the 1.
Gert-Jan
Leila wrote:
> Hi,
> Our production server is a Compaq Proliant with 2 CPU. How can I determine
> that my queries benefit from having 2 CPU? Are parallel plans automaticall
y
> created our I must configure server options?
> Any help would be greatly appreciated.
> Leila

Parallel Plans

Hi,
Our production server is a Compaq Proliant with 2 CPU. How can I determine
that my queries benefit from having 2 CPU? Are parallel plans automatically
created our I must configure server options?
Any help would be greatly appreciated.
Leila
You can check if your server is set to use parallel query plans on the
processor tab of the server properties in Enterprise Manager.
You can check if any queries with a parallel plan are executed by tracing
the DegreeOfParallelism event with SQL Profiler. See the topic "Degree of
Parallelism" in Books Online for details.
Jacco Schalkwijk
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:OmMhaHYzEHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> Our production server is a Compaq Proliant with 2 CPU. How can I determine
> that my queries benefit from having 2 CPU? Are parallel plans
> automatically
> created our I must configure server options?
> Any help would be greatly appreciated.
> Leila
>
|||If you are using the database with more than one user, then you are
likely to benefit from the extra CPU even if no parallellism is chosen,
because in that case the workload can be divided over 2 CPU's instead of
just the 1.
Gert-Jan
Leila wrote:
> Hi,
> Our production server is a Compaq Proliant with 2 CPU. How can I determine
> that my queries benefit from having 2 CPU? Are parallel plans automatically
> created our I must configure server options?
> Any help would be greatly appreciated.
> Leila

Parallel Plans

Hi,
Our production server is a Compaq Proliant with 2 CPU. How can I determine
that my queries benefit from having 2 CPU? Are parallel plans automatically
created our I must configure server options?
Any help would be greatly appreciated.
LeilaYou can check if your server is set to use parallel query plans on the
processor tab of the server properties in Enterprise Manager.
You can check if any queries with a parallel plan are executed by tracing
the DegreeOfParallelism event with SQL Profiler. See the topic "Degree of
Parallelism" in Books Online for details.
--
Jacco Schalkwijk
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:OmMhaHYzEHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> Our production server is a Compaq Proliant with 2 CPU. How can I determine
> that my queries benefit from having 2 CPU? Are parallel plans
> automatically
> created our I must configure server options?
> Any help would be greatly appreciated.
> Leila
>|||If you are using the database with more than one user, then you are
likely to benefit from the extra CPU even if no parallellism is chosen,
because in that case the workload can be divided over 2 CPU's instead of
just the 1.
Gert-Jan
Leila wrote:
> Hi,
> Our production server is a Compaq Proliant with 2 CPU. How can I determine
> that my queries benefit from having 2 CPU? Are parallel plans automatically
> created our I must configure server options?
> Any help would be greatly appreciated.
> Leilasql

Parallel operations in SQL 2000 Standard

Hi,
I have an IIS 6.0 application on one server using database SQL Derver 2000
Standard Edition on a 2 processor machine. In server properties it is set to
use all avaliable processors for parallelism.
As I understand differences between Standard and Enterpise editions,
Standard one can only execute 2 different queries in the samem time on 2
processors. Enterprise can also divide one long lasting queries on 2
processors if it has sens of course.
I have tested it and noticed that my SQL Server Standard still uses one
processor. Can it be caused by a fact that these two concurent queries are
executed by the same one application (IIS)?
Here is my test scenario:
1. I have a .aspx page which shows results of a query (it takes about 7 s)
2. I open this page from 2 different IE windows same time (almost same, but
the delay is about 1-2s, it is the time I need to click)
3. When I look at processor history in task manager on sql server, it never
goes more then 50 % (when I have 1 graph for all of 2 processors)
Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
caused by 1 client application (IIS).
PrzemoSQL Server 2000 SE supports up to 4 processors. Do you observe the same
behavior when you execute the same 2 queries concurrently from Query
Analyzer?
Hope this helps.
Dan Guzman
SQL Server MVP
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
> Hi,
> I have an IIS 6.0 application on one server using database SQL Derver 2000
> Standard Edition on a 2 processor machine. In server properties it is set
> to
> use all avaliable processors for parallelism.
> As I understand differences between Standard and Enterpise editions,
> Standard one can only execute 2 different queries in the samem time on 2
> processors. Enterprise can also divide one long lasting queries on 2
> processors if it has sens of course.
> I have tested it and noticed that my SQL Server Standard still uses one
> processor. Can it be caused by a fact that these two concurent queries are
> executed by the same one application (IIS)?
> Here is my test scenario:
> 1. I have a .aspx page which shows results of a query (it takes about 7 s)
> 2. I open this page from 2 different IE windows same time (almost same,
> but
> the delay is about 1-2s, it is the time I need to click)
> 3. When I look at processor history in task manager on sql server, it
> never
> goes more then 50 % (when I have 1 graph for all of 2 processors)
> Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
> caused by 1 client application (IIS).
> Przemo|||Try disconnecting one of the connections and reconnect or adda new
connection and then try it again. Connections are bound to a UMS which is
basically tied to a processor. The connections get assigned in a round
robin fashion. If you have 3 connections it could have gone like this.
Connection 1 - Attached to UMS 1
Connection 2 - Attached to UMS 2
Connection 3 - Attached to UMS 1
If you run 2 queries, one on Connection 1 and the other on Connection 3 they
may share the same processor. If the optimizer chose to not use parallelism
for your query the third connection could simply be waiting on the first.
--
Andrew J. Kelly SQL MVP
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
> Hi,
> I have an IIS 6.0 application on one server using database SQL Derver 2000
> Standard Edition on a 2 processor machine. In server properties it is set
> to
> use all avaliable processors for parallelism.
> As I understand differences between Standard and Enterpise editions,
> Standard one can only execute 2 different queries in the samem time on 2
> processors. Enterprise can also divide one long lasting queries on 2
> processors if it has sens of course.
> I have tested it and noticed that my SQL Server Standard still uses one
> processor. Can it be caused by a fact that these two concurent queries are
> executed by the same one application (IIS)?
> Here is my test scenario:
> 1. I have a .aspx page which shows results of a query (it takes about 7 s)
> 2. I open this page from 2 different IE windows same time (almost same,
> but
> the delay is about 1-2s, it is the time I need to click)
> 3. When I look at processor history in task manager on sql server, it
> never
> goes more then 50 % (when I have 1 graph for all of 2 processors)
> Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
> caused by 1 client application (IIS).
> Przemo|||Thank you.
This is the reason. I have tested and it works ok. Simple I have some
coincidence.
Przemo
"Andrew J. Kelly" wrote:

> Try disconnecting one of the connections and reconnect or adda new
> connection and then try it again. Connections are bound to a UMS which is
> basically tied to a processor. The connections get assigned in a round
> robin fashion. If you have 3 connections it could have gone like this.
> Connection 1 - Attached to UMS 1
> Connection 2 - Attached to UMS 2
> Connection 3 - Attached to UMS 1
> If you run 2 queries, one on Connection 1 and the other on Connection 3 th
ey
> may share the same processor. If the optimizer chose to not use paralleli
sm
> for your query the third connection could simply be waiting on the first.
> --
> Andrew J. Kelly SQL MVP
>
> "Przemo" <Przemo@.discussions.microsoft.com> wrote in message
> news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
>
>

Parallel operations in SQL 2000 Standard

Hi,
I have an IIS 6.0 application on one server using database SQL Derver 2000
Standard Edition on a 2 processor machine. In server properties it is set to
use all avaliable processors for parallelism.
As I understand differences between Standard and Enterpise editions,
Standard one can only execute 2 different queries in the samem time on 2
processors. Enterprise can also divide one long lasting queries on 2
processors if it has sens of course.
I have tested it and noticed that my SQL Server Standard still uses one
processor. Can it be caused by a fact that these two concurent queries are
executed by the same one application (IIS)?
Here is my test scenario:
1. I have a .aspx page which shows results of a query (it takes about 7 s)
2. I open this page from 2 different IE windows same time (almost same, but
the delay is about 1-2s, it is the time I need to click)
3. When I look at processor history in task manager on sql server, it never
goes more then 50 % (when I have 1 graph for all of 2 processors)
Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
caused by 1 client application (IIS).
Przemo
SQL Server 2000 SE supports up to 4 processors. Do you observe the same
behavior when you execute the same 2 queries concurrently from Query
Analyzer?
Hope this helps.
Dan Guzman
SQL Server MVP
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
> Hi,
> I have an IIS 6.0 application on one server using database SQL Derver 2000
> Standard Edition on a 2 processor machine. In server properties it is set
> to
> use all avaliable processors for parallelism.
> As I understand differences between Standard and Enterpise editions,
> Standard one can only execute 2 different queries in the samem time on 2
> processors. Enterprise can also divide one long lasting queries on 2
> processors if it has sens of course.
> I have tested it and noticed that my SQL Server Standard still uses one
> processor. Can it be caused by a fact that these two concurent queries are
> executed by the same one application (IIS)?
> Here is my test scenario:
> 1. I have a .aspx page which shows results of a query (it takes about 7 s)
> 2. I open this page from 2 different IE windows same time (almost same,
> but
> the delay is about 1-2s, it is the time I need to click)
> 3. When I look at processor history in task manager on sql server, it
> never
> goes more then 50 % (when I have 1 graph for all of 2 processors)
> Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
> caused by 1 client application (IIS).
> Przemo
|||Try disconnecting one of the connections and reconnect or adda new
connection and then try it again. Connections are bound to a UMS which is
basically tied to a processor. The connections get assigned in a round
robin fashion. If you have 3 connections it could have gone like this.
Connection 1 - Attached to UMS 1
Connection 2 - Attached to UMS 2
Connection 3 - Attached to UMS 1
If you run 2 queries, one on Connection 1 and the other on Connection 3 they
may share the same processor. If the optimizer chose to not use parallelism
for your query the third connection could simply be waiting on the first.
Andrew J. Kelly SQL MVP
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
> Hi,
> I have an IIS 6.0 application on one server using database SQL Derver 2000
> Standard Edition on a 2 processor machine. In server properties it is set
> to
> use all avaliable processors for parallelism.
> As I understand differences between Standard and Enterpise editions,
> Standard one can only execute 2 different queries in the samem time on 2
> processors. Enterprise can also divide one long lasting queries on 2
> processors if it has sens of course.
> I have tested it and noticed that my SQL Server Standard still uses one
> processor. Can it be caused by a fact that these two concurent queries are
> executed by the same one application (IIS)?
> Here is my test scenario:
> 1. I have a .aspx page which shows results of a query (it takes about 7 s)
> 2. I open this page from 2 different IE windows same time (almost same,
> but
> the delay is about 1-2s, it is the time I need to click)
> 3. When I look at processor history in task manager on sql server, it
> never
> goes more then 50 % (when I have 1 graph for all of 2 processors)
> Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
> caused by 1 client application (IIS).
> Przemo
|||Thank you.
This is the reason. I have tested and it works ok. Simple I have some
coincidence.
Przemo
"Andrew J. Kelly" wrote:

> Try disconnecting one of the connections and reconnect or adda new
> connection and then try it again. Connections are bound to a UMS which is
> basically tied to a processor. The connections get assigned in a round
> robin fashion. If you have 3 connections it could have gone like this.
> Connection 1 - Attached to UMS 1
> Connection 2 - Attached to UMS 2
> Connection 3 - Attached to UMS 1
> If you run 2 queries, one on Connection 1 and the other on Connection 3 they
> may share the same processor. If the optimizer chose to not use parallelism
> for your query the third connection could simply be waiting on the first.
> --
> Andrew J. Kelly SQL MVP
>
> "Przemo" <Przemo@.discussions.microsoft.com> wrote in message
> news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
>
>

Parallel operations in SQL 2000 Standard

Hi,
I have an IIS 6.0 application on one server using database SQL Derver 2000
Standard Edition on a 2 processor machine. In server properties it is set to
use all avaliable processors for parallelism.
As I understand differences between Standard and Enterpise editions,
Standard one can only execute 2 different queries in the samem time on 2
processors. Enterprise can also divide one long lasting queries on 2
processors if it has sens of course.
I have tested it and noticed that my SQL Server Standard still uses one
processor. Can it be caused by a fact that these two concurent queries are
executed by the same one application (IIS)?
Here is my test scenario:
1. I have a .aspx page which shows results of a query (it takes about 7 s)
2. I open this page from 2 different IE windows same time (almost same, but
the delay is about 1-2s, it is the time I need to click)
3. When I look at processor history in task manager on sql server, it never
goes more then 50 % (when I have 1 graph for all of 2 processors)
Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
caused by 1 client application (IIS).
PrzemoSQL Server 2000 SE supports up to 4 processors. Do you observe the same
behavior when you execute the same 2 queries concurrently from Query
Analyzer?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
> Hi,
> I have an IIS 6.0 application on one server using database SQL Derver 2000
> Standard Edition on a 2 processor machine. In server properties it is set
> to
> use all avaliable processors for parallelism.
> As I understand differences between Standard and Enterpise editions,
> Standard one can only execute 2 different queries in the samem time on 2
> processors. Enterprise can also divide one long lasting queries on 2
> processors if it has sens of course.
> I have tested it and noticed that my SQL Server Standard still uses one
> processor. Can it be caused by a fact that these two concurent queries are
> executed by the same one application (IIS)?
> Here is my test scenario:
> 1. I have a .aspx page which shows results of a query (it takes about 7 s)
> 2. I open this page from 2 different IE windows same time (almost same,
> but
> the delay is about 1-2s, it is the time I need to click)
> 3. When I look at processor history in task manager on sql server, it
> never
> goes more then 50 % (when I have 1 graph for all of 2 processors)
> Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
> caused by 1 client application (IIS).
> Przemo|||Try disconnecting one of the connections and reconnect or adda new
connection and then try it again. Connections are bound to a UMS which is
basically tied to a processor. The connections get assigned in a round
robin fashion. If you have 3 connections it could have gone like this.
Connection 1 - Attached to UMS 1
Connection 2 - Attached to UMS 2
Connection 3 - Attached to UMS 1
If you run 2 queries, one on Connection 1 and the other on Connection 3 they
may share the same processor. If the optimizer chose to not use parallelism
for your query the third connection could simply be waiting on the first.
--
Andrew J. Kelly SQL MVP
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
> Hi,
> I have an IIS 6.0 application on one server using database SQL Derver 2000
> Standard Edition on a 2 processor machine. In server properties it is set
> to
> use all avaliable processors for parallelism.
> As I understand differences between Standard and Enterpise editions,
> Standard one can only execute 2 different queries in the samem time on 2
> processors. Enterprise can also divide one long lasting queries on 2
> processors if it has sens of course.
> I have tested it and noticed that my SQL Server Standard still uses one
> processor. Can it be caused by a fact that these two concurent queries are
> executed by the same one application (IIS)?
> Here is my test scenario:
> 1. I have a .aspx page which shows results of a query (it takes about 7 s)
> 2. I open this page from 2 different IE windows same time (almost same,
> but
> the delay is about 1-2s, it is the time I need to click)
> 3. When I look at processor history in task manager on sql server, it
> never
> goes more then 50 % (when I have 1 graph for all of 2 processors)
> Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
> caused by 1 client application (IIS).
> Przemo|||Thank you.
This is the reason. I have tested and it works ok. Simple I have some
coincidence.
Przemo
"Andrew J. Kelly" wrote:
> Try disconnecting one of the connections and reconnect or adda new
> connection and then try it again. Connections are bound to a UMS which is
> basically tied to a processor. The connections get assigned in a round
> robin fashion. If you have 3 connections it could have gone like this.
> Connection 1 - Attached to UMS 1
> Connection 2 - Attached to UMS 2
> Connection 3 - Attached to UMS 1
> If you run 2 queries, one on Connection 1 and the other on Connection 3 they
> may share the same processor. If the optimizer chose to not use parallelism
> for your query the third connection could simply be waiting on the first.
> --
> Andrew J. Kelly SQL MVP
>
> "Przemo" <Przemo@.discussions.microsoft.com> wrote in message
> news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
> > Hi,
> >
> > I have an IIS 6.0 application on one server using database SQL Derver 2000
> > Standard Edition on a 2 processor machine. In server properties it is set
> > to
> > use all avaliable processors for parallelism.
> > As I understand differences between Standard and Enterpise editions,
> > Standard one can only execute 2 different queries in the samem time on 2
> > processors. Enterprise can also divide one long lasting queries on 2
> > processors if it has sens of course.
> > I have tested it and noticed that my SQL Server Standard still uses one
> > processor. Can it be caused by a fact that these two concurent queries are
> > executed by the same one application (IIS)?
> > Here is my test scenario:
> > 1. I have a .aspx page which shows results of a query (it takes about 7 s)
> > 2. I open this page from 2 different IE windows same time (almost same,
> > but
> > the delay is about 1-2s, it is the time I need to click)
> > 3. When I look at processor history in task manager on sql server, it
> > never
> > goes more then 50 % (when I have 1 graph for all of 2 processors)
> >
> > Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
> > caused by 1 client application (IIS).
> >
> > Przemo
>
>

Parallel Install 2005 CTP & 2000 Leads to Connection Problems

Hey All,
More fun and games with SQL Server 2005.
Mike: thanks for your help before on the Beta. I was able to uninstall and
regain connectivity. I didn't have to wipe the box, but came close!!
Here is a new twist:
Previous situation:
- One data server
- 2 instances: SQL Server 2000 in a named instance; SQL Server 2005 Beta 2
in the default instance.
- Access to the legacy database (2000) is controlled through a trusted
connection, using an Active Directory Group (MSA Users). This group is
granted public role access with various pre-defined rights on the objects.
- Access to the beta is for a website we are developing, and is done
through a SQL Server connection. This connection is granted public role,
with limited access to objects--mostly stored procedure executions only.
- Everything running well.
Now:
- SQL Server Beta 2 expired.
- Uninstalled and cleaned SQL Server 2005 Beta 2.
- Installed SQL Server 2005 April CTP.
- Databases are running well, however:
- Trusted connections to the legacy database are all messed up: the Windows
group controlling access to the SQL 2000 database still exists, but members
of the group can no longer connect. Some weird behavior ensues:
* Some get an 18456. Messages include: "Cannot find the specified server"
* Some can connect via the IP, but not the DNS
DANGEROUS, but working (WHY?)--
* When I add the users to a completely unrelated AD group (with no
particular access to SQL Server, AND I make group associated with the trusted
connection members of the local Administrators group on the data box, THEN
they can connect. If I remove the user from the unrelated group OR I remove
the trusted group from the local Administrators group , they cannot connect.
Of course, this is less than desirable. What happened to the security?
HELP!!
Mike--I see you live in Zurich. I once lived in Yverdon.
Graeme Martin
I spoke with some folks at Microsoft who were very helpful. Just thought I
would share with you what we found--
1. The named instance (2000) wasn't showing up to users trying to connect:
* Check SQL Browser service is running.
* Check the following key in the registry:
HKLM/Software/Microsoft/Microsoft SQL Server/90/SQL Browser/SsrpListener
(REG_DWORD). This value needs to be (1).
2. The user was denied access from the trusted group. Run this SQL:
USE master
GO
SELECT name, denylogin, hasaccess FROM syslogins
-- denylogon should be 0; hasaccess should be 1 for your logins.
3. Use sp_grantlogin 'DOMAINNAME\username' to add logins. This SP will
ensure the proper fields are filled out.
4. Still can't figure out why the website won't show up, but I think it's an
unrelated issue.
Thanks all! Hope this helps you too!
Graeme Martin
"The Oracle" wrote:

> Hey All,
> More fun and games with SQL Server 2005.
> Mike: thanks for your help before on the Beta. I was able to uninstall and
> regain connectivity. I didn't have to wipe the box, but came close!!
> Here is a new twist:
> Previous situation:
> - One data server
> - 2 instances: SQL Server 2000 in a named instance; SQL Server 2005 Beta 2
> in the default instance.
> - Access to the legacy database (2000) is controlled through a trusted
> connection, using an Active Directory Group (MSA Users). This group is
> granted public role access with various pre-defined rights on the objects.
> - Access to the beta is for a website we are developing, and is done
> through a SQL Server connection. This connection is granted public role,
> with limited access to objects--mostly stored procedure executions only.
> - Everything running well.
> Now:
> - SQL Server Beta 2 expired.
> - Uninstalled and cleaned SQL Server 2005 Beta 2.
> - Installed SQL Server 2005 April CTP.
> - Databases are running well, however:
> - Trusted connections to the legacy database are all messed up: the Windows
> group controlling access to the SQL 2000 database still exists, but members
> of the group can no longer connect. Some weird behavior ensues:
> * Some get an 18456. Messages include: "Cannot find the specified server"
> * Some can connect via the IP, but not the DNS
> DANGEROUS, but working (WHY?)--
> * When I add the users to a completely unrelated AD group (with no
> particular access to SQL Server, AND I make group associated with the trusted
> connection members of the local Administrators group on the data box, THEN
> they can connect. If I remove the user from the unrelated group OR I remove
> the trusted group from the local Administrators group , they cannot connect.
> Of course, this is less than desirable. What happened to the security?
> HELP!!
> Mike--I see you live in Zurich. I once lived in Yverdon.
> --
> Graeme Martin
>

Parallel Install 2005 CTP & 2000 Leads to Connection Problems

Hey All,
More fun and games with SQL Server 2005.
Mike: thanks for your help before on the Beta. I was able to uninstall and
regain connectivity. I didn't have to wipe the box, but came close!!
Here is a new twist:
Previous situation:
- One data server
- 2 instances: SQL Server 2000 in a named instance; SQL Server 2005 Beta 2
in the default instance.
- Access to the legacy database (2000) is controlled through a trusted
connection, using an Active Directory Group (MSA Users). This group is
granted public role access with various pre-defined rights on the objects.
- Access to the beta is for a website we are developing, and is done
through a SQL Server connection. This connection is granted public role,
with limited access to objects--mostly stored procedure executions only.
- Everything running well.
Now:
- SQL Server Beta 2 expired.
- Uninstalled and cleaned SQL Server 2005 Beta 2.
- Installed SQL Server 2005 April CTP.
- Databases are running well, however:
- Trusted connections to the legacy database are all messed up: the Windows
group controlling access to the SQL 2000 database still exists, but members
of the group can no longer connect. Some weird behavior ensues:
* Some get an 18456. Messages include: "Cannot find the specified server"
* Some can connect via the IP, but not the DNS
DANGEROUS, but working (WHY?)--
* When I add the users to a completely unrelated AD group (with no
particular access to SQL Server, AND I make group associated with the truste
d
connection members of the local Administrators group on the data box, THEN
they can connect. If I remove the user from the unrelated group OR I remove
the trusted group from the local Administrators group , they cannot connect.
Of course, this is less than desirable. What happened to the security?
HELP!!
Mike--I see you live in Zurich. I once lived in Yverdon.
--
Graeme MartinI spoke with some folks at Microsoft who were very helpful. Just thought I
would share with you what we found--
1. The named instance (2000) wasn't showing up to users trying to connect:
* Check SQL Browser service is running.
* Check the following key in the registry:
HKLM/Software/Microsoft/Microsoft SQL Server/90/SQL Browser/SsrpListener
(REG_DWORD). This value needs to be (1).
2. The user was denied access from the trusted group. Run this SQL:
USE master
GO
SELECT name, denylogin, hasaccess FROM syslogins
-- denylogon should be 0; hasaccess should be 1 for your logins.
3. Use sp_grantlogin 'DOMAINNAME\username' to add logins. This SP will
ensure the proper fields are filled out.
4. Still can't figure out why the website won't show up, but I think it's an
unrelated issue.
Thanks all! Hope this helps you too!
Graeme Martin
"The Oracle" wrote:

> Hey All,
> More fun and games with SQL Server 2005.
> Mike: thanks for your help before on the Beta. I was able to uninstall an
d
> regain connectivity. I didn't have to wipe the box, but came close!!
> Here is a new twist:
> Previous situation:
> - One data server
> - 2 instances: SQL Server 2000 in a named instance; SQL Server 2005 Beta
2
> in the default instance.
> - Access to the legacy database (2000) is controlled through a trusted
> connection, using an Active Directory Group (MSA Users). This group is
> granted public role access with various pre-defined rights on the objects.
> - Access to the beta is for a website we are developing, and is done
> through a SQL Server connection. This connection is granted public role,
> with limited access to objects--mostly stored procedure executions only.
> - Everything running well.
> Now:
> - SQL Server Beta 2 expired.
> - Uninstalled and cleaned SQL Server 2005 Beta 2.
> - Installed SQL Server 2005 April CTP.
> - Databases are running well, however:
> - Trusted connections to the legacy database are all messed up: the Windo
ws
> group controlling access to the SQL 2000 database still exists, but member
s
> of the group can no longer connect. Some weird behavior ensues:
> * Some get an 18456. Messages include: "Cannot find the specified serve
r"
> * Some can connect via the IP, but not the DNS
> DANGEROUS, but working (WHY?)--
> * When I add the users to a completely unrelated AD group (with no
> particular access to SQL Server, AND I make group associated with the trus
ted
> connection members of the local Administrators group on the data box, THEN
> they can connect. If I remove the user from the unrelated group OR I remo
ve
> the trusted group from the local Administrators group , they cannot connec
t.
> Of course, this is less than desirable. What happened to the security?
> HELP!!
> Mike--I see you live in Zurich. I once lived in Yverdon.
> --
> Graeme Martin
>sql

Parallel Index Operations

Got the following error messages when rebuilding indexes on SQL Server 2000 + SP3 server.
WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF latch.
Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
Refer to KB-810195, but it should be corrected in SP3. My server has already service pack 3 installed.
Johnny
We have seen this in relation to IO problems, have you checked the windows
system event log?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:087B2B6A-32A4-4C14-841D-4FE2993B8E75@.microsoft.com...
> Got the following error messages when rebuilding indexes on SQL Server
2000 + SP3 server.
> WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF
latch.
> Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
> Refer to KB-810195, but it should be corrected in SP3. My server has
already service pack 3 installed.
> Johnny
>
|||There is no errors in the system & application event log around the time of running index rebuild.

Parallel Index Operations

Got the following error messages when rebuilding indexes on SQL Server 2000
+ SP3 server.
WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF latch.
Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
Refer to KB-810195, but it should be corrected in SP3. My server has alread
y service pack 3 installed.
JohnnyWe have seen this in relation to IO problems, have you checked the windows
system event log?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:087B2B6A-32A4-4C14-841D-4FE2993B8E75@.microsoft.com...
> Got the following error messages when rebuilding indexes on SQL Server
2000 + SP3 server.
> WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF
latch.
> Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
> Refer to KB-810195, but it should be corrected in SP3. My server has
already service pack 3 installed.
> Johnny
>|||There is no errors in the system & application event log around the time of
running index rebuild.

Parallel Index Operations

Got the following error messages when rebuilding indexes on SQL Server 2000 + SP3 server.
WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF latch.
Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
Refer to KB-810195, but it should be corrected in SP3. My server has already service pack 3 installed.
JohnnyWe have seen this in relation to IO problems, have you checked the windows
system event log?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:087B2B6A-32A4-4C14-841D-4FE2993B8E75@.microsoft.com...
> Got the following error messages when rebuilding indexes on SQL Server
2000 + SP3 server.
> WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF
latch.
> Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
> Refer to KB-810195, but it should be corrected in SP3. My server has
already service pack 3 installed.
> Johnny
>|||There is no errors in the system & application event log around the time of running index rebuild.