Re: MS SQL 7.0 HANGS

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

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... Received on Tue Jun 29 1999 - 07:05:23 CDT