MS SQL 7.0 HANGS

Issue--
We are experiencing apparent server hangs. Microsoft has traced the
problem to a complex query that is being run that uses parallelism and the
result set is being processed slowly (one row every 500 ms or so) The total
wall time that the query is open could be in excess of 30 minutes.
Facts--
MS SQL 7.0 allocates various resources, including memory for running
queries. This amount of memory is not user configurable and is based on
total memory in the server.
Complex queries, especially where the query plan indicates parallelism,
can use up a substantial amount of these resources.
If a complex query result is open without leaving sufficient resources
for other queries MS SQL 7.0 will not be able to process additional queries.
The server will appear "hung" to users until the complex query result set is
processed and closed.
Microsoft does not believe this is a flaw in MS SQL 7.0. We strongly
believe this is a serious bug.
These complex queries were not a problem under MS SQL 6.5 because of
it's simpler query engine
Microsoft claims the problem is "poorly written queries". We believe no
user query, no matter how poorly written, should be able to "hang" an
enterprise class database server (if that's what you consider MS SQL
Server). We would expect results like this from MS Access, not from MS SQL
server.
Discussion--
Is this a bug?Depends how you define a bug. It may be designed to work that way. Therefore
not a bug. But the design is wrong. One user should not hang an enterprise
system. Surely end of story. Whatever the query. Disconnect the user even,
but don't bring the whole thing to its knees! I'm having similar problems on
6.5 with one global variable! I am sure we made a mistake with its use, but
one thing I do know, it should just reject that transaction (or give it much
less resources) and be able to supply the other users with a normal service.
Coming from an ORACLE background, I was prepared to knock SQL, but now I am
impressed, no more blisters on my typing fingers, but these areas surely
need looking at Microsoft.
Max Akbar <Maximum2000@.msn.com> wrote in message
news:#rku4Otw#GA.398@.cpmsnbbsa02...
> I don't think this is a bug!
> Common sense tells anyone who is involved in DBA work that if there is any
> task that requires time like your task 30 min adequate time should be
> allocated or the task should be run off hours. If you have to run this
query
> then you should think about multi processor server or a hardware that can
> handle such a query the fault may not be the software (again common
sense).
> It would be nice to see a sample of your extreme query so others could
help
> you, or agree with you.
> <Cha Ching> that was my two cents,
> -Max
>
> An Unhappy Microsoft Customer <unhappy@.microsoft.com> wrote in message
> news:930607826.300.90@.news.remarQ.com...
the[vbcol=seagreen]
> total
> parallelism,
resources[vbcol=seagreen]
> queries.
set[vbcol=seagreen]
> is
strongly[vbcol=seagreen]
believe[vbcol=seagreen]
> no
> SQL
>|||> > Discussion--[vbcol=seagreen]
Of course it's a bug. If the database was being used to host a data warehou
se/mart system then the programmers and DBAs
would have little control over the SQL being used - it would be user generat
ed, probably via a client tool.|||Please try to use OPTION (MAXDOP 1)
This helps you to use a single processor.
Sometimes parallelism does not give advantages on SQL7...
Think also that when using parallelism the indexes and joins are performed
differently allocating a lot of memory and scanning a lot of tables.
The best way to check this query is to look into the execution plan for a
"TOP 1" query; then the execution plan for the query without TOP.
"An Unhappy Microsoft Customer" <unhappy@.microsoft.com> wrote in message
news:930607826.300.90@.news.remarQ.com...
> Issue--
> We are experiencing apparent server hangs. Microsoft has traced the
> problem to a complex query that is being run that uses parallelism and the
> result set is being processed slowly (one row every 500 ms or so) The
total
> wall time that the query is open could be in excess of 30 minutes.
> Facts--
> MS SQL 7.0 allocates various resources, including memory for running
> queries. This amount of memory is not user configurable and is based on
> total memory in the server.
> Complex queries, especially where the query plan indicates
parallelism,
> can use up a substantial amount of these resources.
> If a complex query result is open without leaving sufficient resources
> for other queries MS SQL 7.0 will not be able to process additional
queries.
> The server will appear "hung" to users until the complex query result set
is
> processed and closed.
> Microsoft does not believe this is a flaw in MS SQL 7.0. We strongly
> believe this is a serious bug.
> These complex queries were not a problem under MS SQL 6.5 because of
> it's simpler query engine
> Microsoft claims the problem is "poorly written queries". We believe
no
> user query, no matter how poorly written, should be able to "hang" an
> enterprise class database server (if that's what you consider MS SQL
> Server). We would expect results like this from MS Access, not from MS
SQL
> server.
> Discussion--
> Is this a bug?
>
>|||"Adriano Trevisan" <trevisan.adriano_at_tin.it> wrote in message news:<eu1D55zXEHA.2816@.TK2M
SFTNGP11.phx.gbl>...
> Please try to use OPTION (MAXDOP 1)
> This helps you to use a single processor.
> Sometimes parallelism does not give advantages on SQL7...
>
[][vbcol=seagreen]
> no
> SQL
Why are you responding to a message dated in the last millenium (1999)?
Why cross post this to the ORACLE groups?
(note ORACLE group removed from my post)