Re: MS SQL 7.0 HANGS

There is a setting with SQL Server 7.0 which allows you to configure the maximum degree of paralellism in a given query. eg. If your query is maxing out 4 processors, drop this config value back to 2 or 3.

Quoting MS SQL Server Books Online:

"How to configure the number of processors available for parallel queries (Enterprise Manager)

    To configure the number of processors available for parallel queries:

    Right-click a server; then click Properties.     Click the Processor tab.
    Under Parallelism, select the number of processors to execute queries in parallel.

    By default, all available processors are used. "

If a query is going to be hanging a SQL Server then there are four possible issues:

1. Need more engines. (Not common - mainly relevant when doing lots of floating point arithmetic during the query.)
2. Need more RAM. (Possible if the server is running with a low cache-hit ratio during bad queries)
3. Need to investigate I/O through-put capacity of server. (High cache-hit, low-CPU and still slow)
4. Need to really look at how the query is structured and re-evaluate the advice Microsoft give you in an objective light. Believe it or not, some of their techs actually know the product pretty well.... <grin>

My $0.02 worth
--
Jeremy Huppatz
Database Administrator
Macquarie Bank Limited
jhuppatz_at_macquarie.com.au

The views expressed above are not espoused on behalf of Macquarie Bank Limited.
MBL cannot be held liable for any idiocy on my behalf.

David Owen <weedwiper_at_my-deja.com> wrote in message news:wkzozzfx65.fsf_at_CHY79.i-did-not-set--mail-host-address--so-shoot-me...
> "Max Akbar" <Maximum2000_at_msn.com> writes:
>
> > I don't think this is a bug!
>
> Come on now, this is a Micro$oft product :-)
>
> >
> > 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).
>
> I do not know how M$ SQL Server 7 handles its processes (I presume
> that it is still similar to Sybase and that it is thread based and
> non-pre-emptive (can a word have two hyphens?)). If that is so, then
> this is a very peculiar situation. Unless every last bit of data is
> cached and the process sends and receives *no* data, then the process
> will send itself to sleep and give control back to the kernel when it
> requires some form of I/O, at which point a separate process should be
> able to be scheduled. That is certainly how Sybase works,
> multi-engined or not. I know that, in theory, it would be possible
> for a piece of T-SQL to cause a server to appear hung, but it would
> have to be tight loop that did not require any interaction or any data
> from anywhere but cache. I suspect a bug.
>
> Cha Ching!
>
> dowen
>
>
> --
> David Owen 7, Stradbrooke Rise SW, Calgary, AB, Canada
> dso@<s.p.a.m-me-not>home.com
> You needn't be well to wealthy, but you gotta be whole to be holy.
> Greg Lake, Works Vol 1
Received on Sun Aug 29 1999 - 04:36:36 CDT