Problem with Parallel Query Execution

[Neil Ginsberg]

I have a SQL 7 db with a union query (view), and I'm getting the error, "The
query processor could not start the necessary thread resources for parallel
query execution." This union query has been in place for about two years now
with no problems until just now, though I haven't changed anything. Also, I
have a local copy of the database on my machine, and the query runs fine.

As noted, I haven't changed anything in the query, nor in the SQL settings.
There is a network administrator, so it's possible that he may have changed
a setting, but I don't know what. The query is reproduced below. Any ideas
as to what's going on would be appreciated.

Neil

Main query:
SELECT Tmp.INVCUST, Tmp.SDNBR, Tmp.SDBOOK, Tmp.SDIVCLN,
Tmp.SDPAID, Tmp.SDPRICE, Tmp.SDCOPIES, Tmp.Location,
INVTRY.AUTHILL1, Tmp.INVDATE, INVTRY.SaleSrc,
INVTRY.HoldInit
FROM (SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
SDPAID, SDPRICE, SDCOPIES, 'P' AS Location
FROM vwInvoiceDet
UNION ALL
SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
SDPAID, SDPRICE, SDCOPIES, 'N' AS Location
FROM vwInvoiceDetN
UNION ALL
SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
SDPAID, SDPRICE, SDCOPIES, 'M' AS Location
FROM vwInvoiceDetM) Tmp INNER JOIN
dbo.INVTRY ON Tmp.SDBOOK = dbo.INVTRY.[Index]

vwInvoiceDet:
SELECT tabInvoice.INVDATE, tabInvoice.INVCUST,
SALEDET.SDNBR, SALEDET.SDBOOK, SALEDET.SDINVNUM,
SALEDET.SDPRICE, SALEDET.SDPAID, SALEDET.SDCOPIES,
SALEDET.SDIVCLN, tabInvoice.INVNBR, SALEDET.SDID
FROM dbo.tabInvoice INNER JOIN
dbo.SALEDET ON
dbo.tabInvoice.INVNBR = dbo.SALEDET.SDNBR

(vwInvoiceDetN and vwInvoiceDetM are similar to vwInvoiceDet.)

[Vinod Kumar]

SQL makes a parallel query plan at optimization time. When you tried to run
the query, maybe not all of the processors were available OR there were not
enough threads available ... Perhaps setting MAXDOP to 2 or 3 might help..
This can be done on Enterprise manager, right click your server and go to
the Properties item.. (MAX Degree of Parallelism.)

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

"Neil Ginsberg" <nr*@nrgconsult.com> wrote in message
news:0D*****************@newsread3.news.pas.earthl ink.net...

I have a SQL 7 db with a union query (view), and I'm getting the error, "The query processor could not start the necessary thread resources for parallel query execution." This union query has been in place for about two years now with no problems until just now, though I haven't changed anything. Also, I have a local copy of the database on my machine, and the query runs fine.

As noted, I haven't changed anything in the query, nor in the SQL settings. There is a network administrator, so it's possible that he may have changed a setting, but I don't know what. The query is reproduced below. Any ideas
as to what's going on would be appreciated.

Neil

Main query:
SELECT Tmp.INVCUST, Tmp.SDNBR, Tmp.SDBOOK, Tmp.SDIVCLN,
Tmp.SDPAID, Tmp.SDPRICE, Tmp.SDCOPIES, Tmp.Location,
INVTRY.AUTHILL1, Tmp.INVDATE, INVTRY.SaleSrc,
INVTRY.HoldInit
FROM (SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
SDPAID, SDPRICE, SDCOPIES, 'P' AS Location
FROM vwInvoiceDet
UNION ALL
SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
SDPAID, SDPRICE, SDCOPIES, 'N' AS Location
FROM vwInvoiceDetN
UNION ALL
SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
SDPAID, SDPRICE, SDCOPIES, 'M' AS Location
FROM vwInvoiceDetM) Tmp INNER JOIN
dbo.INVTRY ON Tmp.SDBOOK = dbo.INVTRY.[Index]

vwInvoiceDet:
SELECT tabInvoice.INVDATE, tabInvoice.INVCUST,
SALEDET.SDNBR, SALEDET.SDBOOK, SALEDET.SDINVNUM,
SALEDET.SDPRICE, SALEDET.SDPAID, SALEDET.SDCOPIES,
SALEDET.SDIVCLN, tabInvoice.INVNBR, SALEDET.SDID
FROM dbo.tabInvoice INNER JOIN
dbo.SALEDET ON
dbo.tabInvoice.INVNBR = dbo.SALEDET.SDNBR

(vwInvoiceDetN and vwInvoiceDetM are similar to vwInvoiceDet.)

[Neil Ginsberg]

I tried it just now, after hours, with no one on the system, and the results
were the same.

In any case, I think I resolved it. I stopped the SQL Server and then
restarted it, and the problem cleared up. So I don't know what was going on,
but stopping and restarting definitely cleared up whatever it was.

Thanks,

Neil

"Vinod Kumar" <vinodk_sct@NO_SPAM_hotmail.com> wrote in message
news:cu**********@news01.intel.com...

SQL makes a parallel query plan at optimization time. When you tried to
run
the query, maybe not all of the processors were available OR there were
not
enough threads available ... Perhaps setting MAXDOP to 2 or 3 might help..
This can be done on Enterprise manager, right click your server and go to
the Properties item.. (MAX Degree of Parallelism.)

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

"Neil Ginsberg" <nr*@nrgconsult.com> wrote in message
news:0D*****************@newsread3.news.pas.earthl ink.net...

I have a SQL 7 db with a union query (view), and I'm getting the error,

"The

query processor could not start the necessary thread resources for

parallel

query execution." This union query has been in place for about two years

now

with no problems until just now, though I haven't changed anything. Also,

I

have a local copy of the database on my machine, and the query runs fine.

As noted, I haven't changed anything in the query, nor in the SQL

settings.

There is a network administrator, so it's possible that he may have

changed

a setting, but I don't know what. The query is reproduced below. Any
ideas
as to what's going on would be appreciated.

Neil

Main query:
SELECT Tmp.INVCUST, Tmp.SDNBR, Tmp.SDBOOK, Tmp.SDIVCLN,
Tmp.SDPAID, Tmp.SDPRICE, Tmp.SDCOPIES, Tmp.Location,
INVTRY.AUTHILL1, Tmp.INVDATE, INVTRY.SaleSrc,
INVTRY.HoldInit
FROM (SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
SDPAID, SDPRICE, SDCOPIES, 'P' AS Location
FROM vwInvoiceDet
UNION ALL
SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
SDPAID, SDPRICE, SDCOPIES, 'N' AS Location
FROM vwInvoiceDetN
UNION ALL
SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
SDPAID, SDPRICE, SDCOPIES, 'M' AS Location
FROM vwInvoiceDetM) Tmp INNER JOIN
dbo.INVTRY ON Tmp.SDBOOK = dbo.INVTRY.[Index]

vwInvoiceDet:
SELECT tabInvoice.INVDATE, tabInvoice.INVCUST,
SALEDET.SDNBR, SALEDET.SDBOOK, SALEDET.SDINVNUM,
SALEDET.SDPRICE, SALEDET.SDPAID, SALEDET.SDCOPIES,
SALEDET.SDIVCLN, tabInvoice.INVNBR, SALEDET.SDID
FROM dbo.tabInvoice INNER JOIN
dbo.SALEDET ON
dbo.tabInvoice.INVNBR = dbo.SALEDET.SDNBR

(vwInvoiceDetN and vwInvoiceDetM are similar to vwInvoiceDet.)