reordering in paralellism-multiprocessor machine

Excuse me if this is a double post!
Consider these queries:
1) select C,D,E,F from T where (A - B) >= 0
2) select C,D,E,F from T where B >= 0 and B <10
3) select * from T where (A - B) >= 0
4) select * from T where B >= 0 and B <10
Description of the table 'T':
256 columns
No identity column defined on T
columns Z,Y,X have nonclustered on PRIMARY
column C, being the primary key column , has the index: clustered, unique,
primary key located on PRIMARY
A is of type int and B is of type decimal.
No. of rows in the table: 100406
Process followed for replication: run these queries in a MULTI PROCESSOR
machine. See the execution plan.
Issue:
Queries 1, 3, 4 execute by the serial plan, whereas query 2 executes by
parallel plan. Still worse, the order in which the records are returned are
not the same each time query 2 is run. When tried with the hint option
(MAXDOP 1), the query 2 executes in serial mode, without re-arrangement. Need
an answer for the rearrangement of the rows resulting in case of query 2.This has been discussed ad-nauseum. The order of returned rows is never
guaranteed unless the query includes an order by clause.
"Saravana" <Saravana@.discussions.microsoft.com> wrote in message
news:6B76B67A-AD98-415D-8296-9619039A7377@.microsoft.com...
> Excuse me if this is a double post!
> Consider these queries:
> 1) select C,D,E,F from T where (A - B) >= 0
> 2) select C,D,E,F from T where B >= 0 and B <10
> 3) select * from T where (A - B) >= 0
> 4) select * from T where B >= 0 and B <10
> Description of the table 'T':
> 256 columns
> No identity column defined on T
> columns Z,Y,X have nonclustered on PRIMARY
> column C, being the primary key column , has the index: clustered, unique,
> primary key located on PRIMARY
> A is of type int and B is of type decimal.
> No. of rows in the table: 100406
> Process followed for replication: run these queries in a MULTI PROCESSOR
> machine. See the execution plan.
> Issue:
> Queries 1, 3, 4 execute by the serial plan, whereas query 2 executes
by
> parallel plan. Still worse, the order in which the records are returned
are
> not the same each time query 2 is run. When tried with the hint option
> (MAXDOP 1), the query 2 executes in serial mode, without re-arrangement.
Need
> an answer for the rearrangement of the rows resulting in case of query 2.
>|||Scott,
Isn't there any other way of stopping this reordering: tweaking of the
tables, or the columns..
In other words: what is the exact quantitative statistic that is considered
by the server for executing a query paralelly or serially.. i came to know
about execution costs, but i am not able to get any useful information from
the execution plans from the query anaylzer. What i am more concerned is that
query 2 is re-ordered whereas query 1 is ordered...
Thanks.
"Scott Morris" wrote:
> This has been discussed ad-nauseum. The order of returned rows is never
> guaranteed unless the query includes an order by clause.
>|||As I said - no order by clause, no guarantee. There is a thread in
.progamming with the subject "order by and identity" that was active
yesterday. You might find the discussion interesting as it deals with the
same basic issue. You can search the newsgroups for discussions / rants on
this FAQ.
"Saravana" <Saravana@.discussions.microsoft.com> wrote in message
news:0FD75B28-2543-4128-8F6F-E888F5110751@.microsoft.com...
> Scott,
> Isn't there any other way of stopping this reordering: tweaking of the
> tables, or the columns..
> In other words: what is the exact quantitative statistic that is
considered
> by the server for executing a query paralelly or serially.. i came to know
> about execution costs, but i am not able to get any useful information
from
> the execution plans from the query anaylzer. What i am more concerned is
that
> query 2 is re-ordered whereas query 1 is ordered...
> Thanks.
> "Scott Morris" wrote:
> > This has been discussed ad-nauseum. The order of returned rows is never
> > guaranteed unless the query includes an order by clause.
> >
>|||Scott is 100% correct. If you want a specific order to be guaranteed for
the returned data you MUST specify an order by clause, period, end of story.
--
Andrew J. Kelly SQL MVP
"Saravana" <Saravana@.discussions.microsoft.com> wrote in message
news:0FD75B28-2543-4128-8F6F-E888F5110751@.microsoft.com...
> Scott,
> Isn't there any other way of stopping this reordering: tweaking of the
> tables, or the columns..
> In other words: what is the exact quantitative statistic that is
> considered
> by the server for executing a query paralelly or serially.. i came to know
> about execution costs, but i am not able to get any useful information
> from
> the execution plans from the query anaylzer. What i am more concerned is
> that
> query 2 is re-ordered whereas query 1 is ordered...
> Thanks.
> "Scott Morris" wrote:
>> This has been discussed ad-nauseum. The order of returned rows is never
>> guaranteed unless the query includes an order by clause.
>

reordering in paralellism-multiprocessor machine

Excuse me if this is a double post!
Consider these queries:
1) select C,D,E,F from T where (A - B) >= 0
2) select C,D,E,F from T where B >= 0 and B <10
3) select * from T where (A - B) >= 0
4) select * from T where B >= 0 and B <10
Description of the table 'T':
256 columns
No identity column defined on T
columns Z,Y,X have nonclustered on PRIMARY
column C, being the primary key column , has the index: clustered, unique,
primary key located on PRIMARY
A is of type int and B is of type decimal.
No. of rows in the table: 100406
Process followed for replication: run these queries in a MULTI PROCESSOR
machine. See the execution plan.
Issue:
Queries 1, 3, 4 execute by the serial plan, whereas query 2 executes by
parallel plan. Still worse, the order in which the records are returned are
not the same each time query 2 is run. When tried with the hint option
(MAXDOP 1), the query 2 executes in serial mode, without re-arrangement. Need
an answer for the rearrangement of the rows resulting in case of query 2.
This has been discussed ad-nauseum. The order of returned rows is never
guaranteed unless the query includes an order by clause.
"Saravana" <Saravana@.discussions.microsoft.com> wrote in message
news:6B76B67A-AD98-415D-8296-9619039A7377@.microsoft.com...
> Excuse me if this is a double post!
> Consider these queries:
> 1) select C,D,E,F from T where (A - B) >= 0
> 2) select C,D,E,F from T where B >= 0 and B <10
> 3) select * from T where (A - B) >= 0
> 4) select * from T where B >= 0 and B <10
> Description of the table 'T':
> 256 columns
> No identity column defined on T
> columns Z,Y,X have nonclustered on PRIMARY
> column C, being the primary key column , has the index: clustered, unique,
> primary key located on PRIMARY
> A is of type int and B is of type decimal.
> No. of rows in the table: 100406
> Process followed for replication: run these queries in a MULTI PROCESSOR
> machine. See the execution plan.
> Issue:
> Queries 1, 3, 4 execute by the serial plan, whereas query 2 executes
by
> parallel plan. Still worse, the order in which the records are returned
are
> not the same each time query 2 is run. When tried with the hint option
> (MAXDOP 1), the query 2 executes in serial mode, without re-arrangement.
Need
> an answer for the rearrangement of the rows resulting in case of query 2.
>
|||Scott,
Isn't there any other way of stopping this reordering: tweaking of the
tables, or the columns..
In other words: what is the exact quantitative statistic that is considered
by the server for executing a query paralelly or serially.. i came to know
about execution costs, but i am not able to get any useful information from
the execution plans from the query anaylzer. What i am more concerned is that
query 2 is re-ordered whereas query 1 is ordered...
Thanks.
"Scott Morris" wrote:

> This has been discussed ad-nauseum. The order of returned rows is never
> guaranteed unless the query includes an order by clause.
>
|||As I said - no order by clause, no guarantee. There is a thread in
..progamming with the subject "order by and identity" that was active
yesterday. You might find the discussion interesting as it deals with the
same basic issue. You can search the newsgroups for discussions / rants on
this FAQ.
"Saravana" <Saravana@.discussions.microsoft.com> wrote in message
news:0FD75B28-2543-4128-8F6F-E888F5110751@.microsoft.com...
> Scott,
> Isn't there any other way of stopping this reordering: tweaking of the
> tables, or the columns..
> In other words: what is the exact quantitative statistic that is
considered
> by the server for executing a query paralelly or serially.. i came to know
> about execution costs, but i am not able to get any useful information
from
> the execution plans from the query anaylzer. What i am more concerned is
that
> query 2 is re-ordered whereas query 1 is ordered...
> Thanks.
> "Scott Morris" wrote:
>
|||Scott is 100% correct. If you want a specific order to be guaranteed for
the returned data you MUST specify an order by clause, period, end of story.
Andrew J. Kelly SQL MVP
"Saravana" <Saravana@.discussions.microsoft.com> wrote in message
news:0FD75B28-2543-4128-8F6F-E888F5110751@.microsoft.com...
> Scott,
> Isn't there any other way of stopping this reordering: tweaking of the
> tables, or the columns..
> In other words: what is the exact quantitative statistic that is
> considered
> by the server for executing a query paralelly or serially.. i came to know
> about execution costs, but i am not able to get any useful information
> from
> the execution plans from the query anaylzer. What i am more concerned is
> that
> query 2 is re-ordered whereas query 1 is ordered...
> Thanks.
> "Scott Morris" wrote:
>

Reoprting Server cannot be viewed by the client machines

I have set up a RS on my local machine and it can be viewed by the machine. However this cant be viewed by the client machines ..wonder what could be the settings that need this to work.

Regards

Alu

Hi,

Can you just explain little more on your probelm. If i am correct, are you getting any ASP.Net page error while trying to view the report from client machine?

If it is so, then try out the following,

In your client Internet Explorer, just delete all cookies and offline contents. after that do the following.

Tools --> Internet Option --> Setting(Temp internet files) --> select Every vist to the page.

Now you should be able to view the reports from your client machine. I dont know the root cause for this porbelm. I have experienced the same and tried for various options of Internet explorer and find it working.

Good Luck,

|||

hmm tried and seems not working..it applies to all client machines thus i assume the problem lies in the setting from the server itself?

Regards

Alu

|||

are you using the http://machinename/reports/ to access the report manager? if yes, try using http://ip_address/reports/

if this gets you there, then try pinging the machine name from a client and see what it renders to for an ip address.

do thise test, post the results, and we will continue troubleshooting from there.

|||

Have tried both but in vain. Furthermore I found that the IIS hosted in my XP Pro machine could not be accessed from any other client machines. I am installing RS in my local XP Pro machines and i could see my Report Manager. In the first place IIS should show "Under Construction..." if access from client but it's not. How to i troubleshoot IIS?....

Regards

Alu

|||

I have managed to remove the firewall and IIS problem has cleared. However, now when i access the report server..it prompts me for password. Currently in my settings (report service confguration) my Windows Service Identity uses Local System. How do I disable the prompting of password? Thanks.

Regards

Alu

Reoprting Server cannot be viewed by the client machines

I have set up a RS on my local machine and it can be viewed by the machine. However this cant be viewed by the client machines ..wonder what could be the settings that need this to work.

Regards

Alu

Hi,

Can you just explain little more on your probelm. If i am correct, are you getting any ASP.Net page error while trying to view the report from client machine?

If it is so, then try out the following,

In your client Internet Explorer, just delete all cookies and offline contents. after that do the following.

Tools --> Internet Option --> Setting(Temp internet files) --> select Every vist to the page.

Now you should be able to view the reports from your client machine. I dont know the root cause for this porbelm. I have experienced the same and tried for various options of Internet explorer and find it working.

Good Luck,

|||

hmm tried and seems not working..it applies to all client machines thus i assume the problem lies in the setting from the server itself?

Regards

Alu

|||

are you using the http://machinename/reports/ to access the report manager? if yes, try using http://ip_address/reports/

if this gets you there, then try pinging the machine name from a client and see what it renders to for an ip address.

do thise test, post the results, and we will continue troubleshooting from there.

|||

Have tried both but in vain. Furthermore I found that the IIS hosted in my XP Pro machine could not be accessed from any other client machines. I am installing RS in my local XP Pro machines and i could see my Report Manager. In the first place IIS should show "Under Construction..." if access from client but it's not. How to i troubleshoot IIS?....

Regards

Alu

|||

I have managed to remove the firewall and IIS problem has cleared. However, now when i access the report server..it prompts me for password. Currently in my settings (report service confguration) my Windows Service Identity uses Local System. How do I disable the prompting of password? Thanks.

Regards

Alu