• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6324
  • Last Modified:

MAXDOP 1 OPTION

I've got a reporting procedure which, when invoked, truncates a table, immediately fills that table, and then runs a report based on the data in the table.  Individually, both procs work fine.  They work well together, too.  BUT every third or fourth time I run the reporting procedure, it errors out with this:

Msg 8650, Level 13, State 1, Server SQLSERVER08, Line 1 Intra-query parallelism caused your server command (process ID #23) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).
Msg 8650, Level 13, State 1, Server SQLSERVER08, Line 1 Intra-query parallelism caused your server command (process ID #23) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).

Unfortunately, the report that it runs is shipped out via email, and if I get the intra-query error, that's what the recipients will get, as opposed to the expected report.  (I know this, it's happened to me in test.)  

So -- I used the MAXDOP option, but possibly just incorrectly, cuz it still happens.  My question is two - fold.  First, can somebody help me w/correcting MAXDOP such that this does not re-curr?  (i've copied in both procs)  And secondly, i've got a few emails that are sent out on a daily basis w/reports like this.  What's the right way to prevent the email distribution if/when an error is the endresult of the procedure being run?  The first question is critical, the second is not quite so urgent at all.

Please advise.

create proc usp_Report
AS

SET NOCOUNT ON

TRUNCATE TABLE dba_stat.dbo.Stats
EXEC dba_stat.dbo.usp_InsStats

--Number of trades/volume of all shares traded
SELECT CONVERT(char(10),dateadd(dd,-0, GetDate()),101) AS [Date],+'  '+LTRIM(LEFT(CONVERT(char(16),CAST(SUM(CountOftrades) AS MONEY),1),13)) AS 'Total Trades',+'  '+RIGHT('     ' + ISNULL(CONVERT(varchar(20), Sum(TotalUSD) ,1),0.00),20) AS 'Total $  '
FROM dba_stat.dbo.stats

--STOCKS VOLUME
SELECT 'Total # Trades - '+LTRIM(LEFT(CONVERT(char(16),CAST(SUM(CountOfTrades) AS MONEY),1),13)),+''+
'Stocks Volume -   '+LTRIM(LEFT(CONVERT(char(16),CAST(SUM(Volume) AS MONEY),1),13)),+''+
'Total $  - '+RIGHT('     ' + ISNULL(CONVERT(varchar(20),SUM(TotalUSD) ,1),0.00),20)
FROM dba_stat.dbo.stats
WHERE destination IN ('IIII','DDDD','CCCC')

--STOCKS VOLUME -- trades and unit volume for stocks and futures separately
--Number of trades by endpoint (ECN/exchange)/--Volume of shares/contracts by endpoint.
select Destination as EndPoint,+''+RTRIM(LEFT(CONVERT(char(16),CAST(SUM(countOfTrades) AS MONEY),1),13)) AS '   #Trades',+''+
RTRIM(LEFT(CONVERT(char(16),CAST(SUM(Volume) AS MONEY),1),13)) AS 'Volume    ',+''+
RIGHT('         ' + ISNULL(CONVERT(varchar(20),SUM(totalUSD),1),0.00),20) AS 'Total $   '
FROM dba_stat.dbo.stats
--where timeofexecution between dateadd(day,-0, cast(convert(char(10),getdate(),120) + ' 00:00:00' as datetime) )
--and dateadd(day,-0, cast(convert(char(10),getdate(),120) + ' 23:59:59' as datetime))      
WHERE destination IN ('IIII','DDDD','CCCC')
GROUP BY destination
ORDER BY destination

--FUTURES volume
SELECT 'Total # Trades - '+LTRIM(LEFT(CONVERT(char(16),CAST(SUM(CountOftrades) AS MONEY),1),13)), +''+
'Futures Volume - ' + LTRIM(LEFT(CONVERT(char(16),CAST(Sum(Volume) AS MONEY),1),13)),+ ''+
'Total $  - '+RIGHT('     ' + ISNULL(CONVERT(varchar(20), Sum(TotalUSD) ,1),0.00),20)
FROM dba_stat.dbo.stats
WHERE destination in ('XXXX','MMMM','TTTT','LLLL','SSSS','NNNN')

SELECT Destination as EndPoint,+''+RTRIM(LEFT(CONVERT(char(16),CAST(SUM(countOfTrades) AS MONEY),1),13)) AS '   #Trades',+''+
RTRIM(LEFT(CONVERT(char(16),CAST(SUM(Volume) AS MONEY),1),13)) AS 'Volume    ',+''+
RIGHT('        '+ISNULL (CONVERT(varchar(20),SUM(TotalUSD),1),0.00),20) AS 'Total $   '
FROM dba_stat.dbo.stats
WHERE Destination IN ('MMMM','TTTT')
GROUP BY Destination

UNION

SELECT Destination as EndPoint,RTRIM(LEFT(CONVERT(char(16),CAST(SUM(countOfTrades) AS MONEY),1),13)) AS '   #Trades',+''+
RTRIM(LEFT(CONVERT(char(16),CAST(SUM(Volume) AS MONEY),1),13)) AS 'Volume    ',
RIGHT('        '+ISNULL (CONVERT(varchar(20),SUM(TotalUSD),1),0.00),20) AS 'Total $   '
FROM dba_stat.dbo.stats
WHERE destination = 'LLLLL'
GROUP BY Destination

UNION

SELECT Destination as EndPoint,RTRIM(LEFT(CONVERT(char(16),CAST(SUM(countOfTrades) AS MONEY),1),13)) AS '   #Trades',+''+
RTRIM(LEFT(CONVERT(char(16),CAST(SUM(Volume) AS MONEY),1),13)) AS 'Volume   ',
RIGHT('        '+ISNULL (CONVERT(varchar(20),SUM(TotalUSD),1),0.00),20) AS 'Total $   '
FROM dba_stat.dbo.stats
WHERE Destination IN('XXXX','SSSS')
GROUP BY Destination

UNION

SELECT Destination as EndPoint,RTRIM(LEFT(CONVERT(char(16),CAST(SUM(countOfTrades) AS MONEY),1),13))AS '   #Trades',+''+
RTRIM(LEFT(CONVERT(char(16),CAST(SUM(Volume) AS MONEY),1),13))AS 'Volume   ',
RIGHT('        '+ISNULL (CONVERT(varchar(20),SUM(TotalUSD),1),0.00),20) AS 'Total $   '
FROM dba_stat.dbo.stats
WHERE Destination = 'NNNN'
GROUP BY Destination
OPTION (MAXDOP 1)

--CASH/FIXED INCOME volume
SELECT 'Total # Trades - '+LTRIM(LEFT(CONVERT(char(16),CAST(SUM(CountOftrades) AS MONEY),1),13)), +''+
'Fixed Income Volume - '+LTRIM(LEFT(CONVERT(char(16),CAST(Sum(Volume) AS MONEY),1),13)),+''+
'Total $  - '+RIGHT('   ' + ISNULL(CONVERT(varchar(20), Sum(TotalUSD) ,1),0.00),20) --AS 'Total $   '
FROM dba_stat.dbo.stats
WHERE destination in ('BBBB','PPPP')

SELECT Destination as EndPoint,RTRIM(LEFT(CONVERT(char(16),CAST(SUM(countOfTrades) AS MONEY),1),13))AS '   #Trades',+''+
RTRIM(LEFT(CONVERT(char(16),CAST(SUM(Volume) AS MONEY),1),13))AS 'Volume   ',
RIGHT('         ' + ISNULL(CONVERT(varchar(20),SUM(TotalUSD),1),0.00),20) AS 'Total $   '
FROM dba_stat.dbo.stats
WHERE Destination IN ('BBBB','PPPP')
GROUP BY Destination
ORDER BY Destination


SET NOCOUNT OFF

GO
0
RLLewis
Asked:
RLLewis
  • 4
  • 4
1 Solution
 
Kevin3NFCommented:
I may be nuts, but I believe each individual SELECT needs the OPTION (MAXDOP 1) hint.  Alternatively, you could set it at the server level and be done with it.
0
 
RLLewisAuthor Commented:
so are you saying the option needs to be in there 7 times?
0
 
Kevin3NFCommented:
From books omline:


MAXDOP number

Overrides the max degree of parallelism configuration option (of sp_configure) only for the query specifying this option. All semantic rules used with max degree of parallelism configuration option are applicable when using the MAXDOP query hint. For more information, see max degree of parallelism Option.



0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
RLLewisAuthor Commented:
yes, i've read bol.  but i've also changed maxdop in the proc as you've suggested, and it still happens.  not 2 minutes ago i ran the reporting proc successfully, just now i ran again and it failed w/this:

Msg 8650, Level 13, State 1, Server SQLSERVER08, Line 1 Intra-query parallelism caused your server command (process ID #62) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).

i have OPTION (MAXDOP 1) in there 7 times --  under each of the selects and the union
so what else can i do?
0
 
Kevin3NFCommented:
repost your SQL as is
0
 
RLLewisAuthor Commented:
I figured it out --- I am running the proc on a v2000 sp3 box, but it is remotely hitting a base v7 instance.  the intra-query parallelism issue was not resolved i believe until v2K sp3.  thank you for your help, though.  
0
 
RLLewisAuthor Commented:
how will i have this closed?
0
 
Kevin3NFCommented:
http://www.experts-exchange.com/Community_Support/

post a new question there asking that this be closed and the points refunded since you answered it yourself
0
 
PAQ_ManCommented:
Question Closed, 500 points refunded.
PAQ_Man
Community Support Moderator
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now