Re: MS SQL 7.0 HANGS

>>This amount of memory is not user configurable and is based on total
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.

>> 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

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_at_email.msn.com

An Unhappy Microsoft Customer <unhappy_at_microsoft.com> wrote in message news:930607826.300.90_at_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?
>
>
>
Received on Tue Jun 29 1999 - 06:35:38 CDT