Re: MS SQL 7.0 HANGS

Another possibility you might consider.... If your problem is that (during a not busy time) a use sends a query which benefits from parallelism. SQL then uses all processors for the parallel query (which is a BIG query). Then when other users try to get a query on the system ..... nothing (because BIG query is still running)...

You can limit the number of processors that are allowed to be busy on a single parallel query... In SEM , server properties for your server -> memory -> parallelism .... save a proc or two for other folks...

SQL automatically prefers to have *different* users on each processor
(inter-query parallelism), than many processors working on a single query
while other users wait (intra-query parallelism).... However (unless *you* limit the processors as above) if the system is NOT busy and a query would benefit from parallelism... then SQL will schedule is parallel... Of course the goal of parallelism is to use all available resources to speed this particular query... which is often contradictory to max throughput concerns...

An earlier posting mentioned Sybase query governor... don't get all excited.... What Sybase does is kill your process if you use more than X proc time or io time...Now pick X.... It has to be big enough for your largest query...and that is a pretty big door...

Try limiting the procs for parallelism and see if that helps..

--
Wayne Snyder
MCSE, MCT, SQL Server MVP
IKON Education Services - Charlotte, NC
(Please respond to newsgroup unles otherwise requested.)
Received on Tue Jun 29 1999 - 08:03:14 CDT