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[vbcol=seagreen]
> 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
> no
> SQL
>
|||> > Discussion--[vbcol=seagreen]
Of course it's a bug. If the database was being used to host a data warehouse/mart system then the programmers and DBAs
would have little control over the SQL being used - it would be user generated, probably via a client tool.
|||roy_harvey@.snet.net (Roy Harvey) wrote:

>So, if boost priority was off all along what else can you do? You
>actually have the ability to limit the maximum degree of parallelism
>of any single SQL query. If you have a quad processor machine, and
>can't live with a single query using four processors, you should get
>relief by setting the maximum parallelism to three. See the Processor
>tab of Properties again.
I missed that the max degree of parallelism can even be set for the
individual query. See the SELECT documentation under "OPTION
(MAXDOP)".
Roy
|||See in line comments...
Tony Rogerson MCP,SQL Server MVP
Torver Computer Consultants Ltd.
Home page: www.sql-server.co.uk (under construction)
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.
This is configurable, the default is to let SQL Server handle memory
requirements dynamically, however you can configure it - right click on the
server in enterprise manager, select properties and look at the memory
options tab.

> Complex queries, especially where the query plan indicates
parallelism,
> can use up a substantial amount of these resources.
Complex queries will always use resource, the approach to getting good
performance out of any database be it SQL Server, DB2 etc.. is to make sure
your database design supports the type of processing your application needs
to do.

> 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.
This is completely incorrect, a query may well hog the system in terms of
slowing it down, it just means other queries will also run slowly. Another
symptom of this would be blocking, if your long running query is modifying
data then it could be blocking other users out which to the user would look
like the system is hung.

> The server will appear "hung" to users until the complex query result set
is
> processed and closed.
Run sp_lock and sp_who and make sure the other users aren't blocking. If
they are you need to question why the query is running for such as long
period of time, there are some extremely useful tuning documents available
through the web site www.microsoft.com\sql

> 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.
Please post back (mail me directly) your full database schema and point out
which stored procedure is causing the bottleneck, also include the
sp_configure information
Thanks,
Tony...
|||SQL Server does use resource management, in 7.0 there is also a query
governor - this is available in enterprise manager, right click on the
server, select properties, server settings .. 'user query governor to
prevent queries exceeding specified cost'.
Tony Rogerson MCP,SQL Server MVP
Torver Computer Consultants Ltd.
Home page: www.sql-server.co.uk (under construction)
Steve Cogorno <cogorno@.netcom.com> wrote in message
news:7l93po$jo5@.dfw-ixnews9.ix.netcom.com...[vbcol=seagreen]
> In article <930607826.300.90@.news.remarQ.com>,
> An Unhappy Microsoft Customer <unhappy@.microsoft.com> wrote:
no[vbcol=seagreen]
SQL
> Well, it isn't a bug. Microsoft never claimed to have resource controls.
> But, I agree with you that resource management is essential if you want to
be
> able to provide a guaranteed level of service.
> Sybase Adaptive Server has this feature. It is called the Resource
> Governor. If you consider this essential functionality, perhaps you should
> look at changing database engines.
> --
> Steve
> cogorno@.netcom.com
|||Unhappy,
Looking at this from a distance I see you reporting two problems. One
is a pig of a query that needs a lot of attention from a DBA to find
out why it is such a resource hog and so painfully slow, and to fix
it. The second is that this query is taking such a large percentage
of available resources.
As to the query... Have you looked at its use - or perhaps lack of
use - of indexes? Double checked that no join tests were missed? Run
Performance Monitor to see what resources are bottlenecking? When I
find one of our programmers running a really bad query like this they
get a phone call and we sit down to talk about what they are trying to
do.
Perhaps if you post the query and related information in the newsgroup
someone will have some suggestions.
As to SQL Server allocating resources... I wonder why you picked
memory as being the issue. Assuming the server has a reasonable
amount of memory so that there is a decent size for the data cache,
other SQL commands should be able to share memory alright. Have you
been down in the details with Performance Monitor and actually seen
that memory is the problem? If so I'd love to know what measures you
are tracking so I can use them myself!
I'm a bit confused about what you wish to configure in terms of
memory. Despite what you have claimed, you have quite a bit of
control over how much memory SQL Server uses. You can sit a minimum
and a maximum for dynamic memory useage, fix the memory size to an
unchanging number, and even lock down physical memory. (In EM right
click the server, choose Properties, and see the Memory tab.)
But I don't see the point of trying to limit the memory allocated to
SQL Server when you say that the problem is that other SQL queries are
not being given resources. All that would do is limit even further
the memory resource available for the other queries. And most of the
memory SQL Server is using is not allocated to any specific query, it
is the data cache. The data cache is more or less managed on an LRU
basis and any query has a good shot at getting its share.
Rather than memory I would be looking at CPU as the bottleneck. First
double check that boost priority has not been set (see the same
window, Processor tab.) If it was you can probably solve the whole
thing by turning it off (but how often is life that simple?)
So, if boost priority was off all along what else can you do? You
actually have the ability to limit the maximum degree of parallelism
of any single SQL query. If you have a quad processor machine, and
can't live with a single query using four processors, you should get
relief by setting the maximum parallelism to three. See the Processor
tab of Properties again.
I hope some of this helps.
Roy
"An Unhappy Microsoft Customer" <unhappy@.microsoft.com> wrote:

>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?
|||[vbcol=seagreen]
memory in the server.
This is not the case; memory configuration & allocation is managed until
such time as you choose to override. The option
to allow adjustment is found easily with the server memory properties tab in
enterprise manager.
[vbcol=seagreen]
no[vbcol=seagreen]
I would question why user queries are allowed direct query access to the
server. Query submission to a server should be managed, whatever enviroment.
There are tools that allow you to monitor and solve these problems within
SQL Server, SQL Profiler and graphical showplan analyszer are a good start.
I would also point you the following white paper:
http://www.microsoft.com/sql/70/whpprs/perftun.htm
I would also suggest reading SQL books online, it does address your
concerns.
You may also want to review your hardware configuration, troubleshoot for
memory problems etc... Compaq, for example has an excellent resource for
configuring SQL Server 7.0 in different enviroments.
If you do follow the suggestions and you still have problems then post a
clear example of how to reproduce, detailing your enviroment.
Best Regards
Trevor Dwyer - SQL Server MVP
tdwyer@.email.msn.com
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?
>
>
|||In article <930607826.300.90@.news.remarQ.com>,
An Unhappy Microsoft Customer <unhappy@.microsoft.com> wrote:
> 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?
Well, it isn't a bug. Microsoft never claimed to have resource controls.
But, I agree with you that resource management is essential if you want to be
able to provide a guaranteed level of service.
Sybase Adaptive Server has this feature. It is called the Resource
Governor. If you consider this essential functionality, perhaps you should
look at changing database engines.
Steve
cogorno@.netcom.com
|||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[vbcol=seagreen]
> 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
> no
> SQL
>