Re: MS SQL 7.0 HANGS

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_at_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?
Received on Tue Jun 29 1999 - 08:00:12 CDT