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.) 2 13638
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.)
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.)
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Jim Garrison |
last post by:
Scenario:
1) Create a GLOBAL TEMPORARY table and populate it with
one (1) row.
2) Join that table to another with about 1 million rows.
The join condition selects a few hundred rows.
...
|
by: opt_inf_env |
last post by:
Hello,
On my server users have access to MySQL database (through PHP). What I
would like to avoid is parallel execution of commands. I mean, if one
user run some sequence of command the sequence...
|
by: Bing Wu |
last post by:
Hi,
Has anyone got problem with this on large table. It seems it take ages to return the result ( half an hour :-( ).
SELECT COUNT(*) FROM COORDINATE
A strange thing is the tempory space...
|
by: Mark D Powell |
last post by:
SQL Server 2000 SP3A
Last week one of our processes starting issuing or suffering deadlock
detected errors every 15 minutes or so.
I have read several articles at MS on the subject. I set a...
|
by: parallelpython |
last post by:
Has anybody tried to run parallel python applications?
It appears that if your application is computation-bound using 'thread'
or 'threading' modules will not get you any speedup. That is because...
|
by: HC |
last post by:
Hello, all, I started out thinking my problems were elsewhere but as I
have worked through this I have isolated my problem, currently, as a
difference between MSDE and SQL Express 2005 (I'll just...
|
by: traravind |
last post by:
Hi all,
I am a newbie to perl programming and am already loving it.. I have a problem with my code in research and am trying to improve the performance by parallel processing some blocks of...
|
by: oskhan |
last post by:
Hello Everyone,
I have a little different problem and I though anyone might give me any idea that what is going wrong.
I have a view which consists of 3 tables linked by UNION ALL, overall...
|
by: Jim Kennedy |
last post by:
If you are firing that many queries you better be using bind variables and
parsing the query once and rebinding, and executing many times and NOT
closing the cursor. Doing that will help you...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |