maxdop 1 doesn't seem to work inside batch

I have several dynamically-generated queries that I would like to run with
the option(maxdop 1). However, if I put the queries with the option in eithe
r
Exec or sp_executesql, the option is ignored and the query runs with
parallelism (I've seen the degrees of parallelism and execution plan for thi
s
in profiler). Is there any way to do this without having to configure the
server setting?
These queries actually run inside a stored procedure, so if there is a
maxdop setting for the entire stored procedure and all the batches it
executes, that would be even better.Can you post the actual sp? The hint should affect everything in the
statement butnot the entire sp.
Andrew J. Kelly SQL MVP
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:FDD1E453-77BF-45DF-A73C-6F39D9EDB6FC@.microsoft.com...
>I have several dynamically-generated queries that I would like to run with
> the option(maxdop 1). However, if I put the queries with the option in
> either
> Exec or sp_executesql, the option is ignored and the query runs with
> parallelism (I've seen the degrees of parallelism and execution plan for
> this
> in profiler). Is there any way to do this without having to configure the
> server setting?
> These queries actually run inside a stored procedure, so if there is a
> maxdop setting for the entire stored procedure and all the batches it
> executes, that would be even better.|||tBackground: this query is fetching data from views (vStatsX). Each view is
the union of a simple SELECT from two similar tables. The query with the
maxdop option is executed WITH the option enabled in query analyzer(QA). If
I
enclose the query in an EXECUTE or sp_ExecuteSQL in QA the option is ignored
.
As I am calling this query in the sp using sp_ExecuteSQL, I'm running into
the same problem where the option is ignored.
Here is the generated query, from the profiler SP:StmtStarting:
SELECT @.Completed=@.Completed +IsNull(Count(Sup.SessionID),0),
@.Duration=@.Duration+IsNull(sum(Sup.Seconds),0)
FROM vStats4 Sup LEFT JOIN vStats1 S1 on Sup.SessionID=S1.SuperSessionID
LEFT JOIN vStats2 S2 on Sup.SessionID=S2.SuperSessionID
LEFT JOIN vStats3 S3 on Sup.SessionID=S3.SuperSessionID
WHERE Sup.date between @.dateStart and @.dateEnd and Sup.Escalated=0 and
Sup.IsAbandoned=0
AND IsNull(S1.Escalated,0)=0 AND IsNull(S2.Escalated,0)=0 AND
IsNull(S3.Escalated,0)=0
AND (Sup.Problems>0 or Sup.Questions>0 or Sup.AMSSEarches>0 OR S1.Problems>0
or S1.Questions>0 or S1.AMSSearches>0
OR S2.Problems>0 or S2.Questions>0 or S2.AMSSearches>0 OR
S3.Problems>0 or S3.Questions>0 or S3.AMSSearches>0)
OPTION(MAXDOP 1)
Here's the Profiler Output following the SP:stmtStarting:
Degree of Parallelism (7.0 Insert)
Execution Tree
--
Compute Scalar(DEFINE:([Expr1027]=[@.Completed]+i
snull([Expr1025], 0),
[Expr1028]=[@.duration]+isnull([Expr1026]
, 0)))
|--Compute Scalar(DEFINE:([Expr1025]=Convert([globa
lagg1030]),
[Expr1026]=If ([globalagg1032]=0) then NULL else [globalagg1034]))
|--Stream Aggregate(DEFINE:([globalagg1030]=SUM([p
artialagg1029]),
[globalagg1032]=SUM([partialagg1031]), [globalagg1034]=SUM([partialagg1033])
))
|--Concatenation
|--Compute Scalar(DEFINE:([partialagg1029]=[partial
agg1029]))
| |--Stream Aggregate(DEFINE:([partialagg1029]=Count
(*),
[partialagg1033]=SUM([Stats1].[Seconds])))
| |--Clustered Index
Scan(OBJECT:([noHoldCust18].[dbo].[Stats1].[MSCCSPK_20041015040614432]),
WHERE:((((([Stats1].[SuperSessionID]=NULL AND [Stats1].[Date]>=[@.dateStart])
AND [Stats1].[Date]<=[@.dateEnd]) AND (([Stats1].[Problems]>0 OR
[Stats1].[Questions]>0) OR [Stats1].[AMSSearches]>0)) AND
Convert([Stats1].[Escalated])=0) AND Convert([Stats1].[IsAbandoned])=0))
|--Compute Scalar(DEFINE:([partialagg1029]=[partial
agg1029]))
|--Stream Aggregate(DEFINE:([partialagg1029]=Count
(*),
[partialagg1033]=SUM([Stats1Archive].[Seconds])))
|--Clustered Index
Scan(OBJECT:([noHoldCust18].[dbo].[Stats1Archive].[MSCCSPK_20041015040714695]),
WHERE:((((([Stats1Archive].[SuperSessionID]=NULL AND
[Stats1Archive].[Date]>=[@.dateStart]) AND [Stats1Archive].[Date]<=[@.dateEnd])
AND (([Stats1Archive].[Problems]>0 OR [Stats1Archive].[Questions]>0) OR
[Stats1Archive].[AMSSearches]>0)) AND Convert([Stats1Archive].[Escalated])=0)
AND Convert([Stats1Archive].[IsAbandoned])=0))|||Here is how I am executing the query from within the sp:
exec sp_Executesql @.strSQL,N'@.Completed int OUTPUT,@.duration int output,
@.DateStart datetime, @.DateEnd datetime',
@.Completed output,@.duration output,@.DateStart,@.DateEnd|||I think I need more sleep...
It looks like the option IS being used in the batch calls. I reviewed the
execution plan generated when I purposely omit the option in my query and it
is different than the execution plan generated when I include the option.
This execution plan (without the option) explicitly mentions parallelism
whereas the other one does not. I'm obviously not an expert in this area but
I hope I'm on the right track now.
ompute Scalar(DEFINE:([Expr1102]=[@.Completed]+i
snull([Expr1100], 0),
[Expr1103]=[@.duration]+isnull([Expr1101]
, 0)))
|--Compute Scalar(DEFINE:([Expr1100]=Convert([globa
lagg1105]),
[Expr1101]=If ([globalagg1107]=0) then NULL else [globalagg1109]))
|--Stream Aggregate(DEFINE:([globalagg1105]=SUM([p
artialagg1104]),
[globalagg1107]=SUM([partialagg1104]), [globalagg1109]=SUM([partialagg1108])
))
|--Parallelism(Gather Streams)
|--Compute Scalar(DEFINE:([partialagg1104]=[partial
agg1104]))
etc...|||Yes I saw no indication of parallelism in the other plan. Just a note that
your where clause is almost forcing index scans. You might want to see if
you can't optimize that query.
Andrew J. Kelly SQL MVP
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:5F333045-B692-415F-9DFC-2B21FABBA6EB@.microsoft.com...
>I think I need more sleep...
> It looks like the option IS being used in the batch calls. I reviewed the
> execution plan generated when I purposely omit the option in my query and
> it
> is different than the execution plan generated when I include the option.
> This execution plan (without the option) explicitly mentions parallelism
> whereas the other one does not. I'm obviously not an expert in this area
> but
> I hope I'm on the right track now.
> ompute Scalar(DEFINE:([Expr1102]=[@.Completed]+i
snull([Expr1100], 0),
> [Expr1103]=[@.duration]+isnull([Expr1101]
, 0)))
> |--Compute Scalar(DEFINE:([Expr1100]=Convert([globa
lagg1105]),
> [Expr1101]=If ([globalagg1107]=0) then NULL else [globalagg1109]))
> |--Stream Aggregate(DEFINE:([globalagg1105]=SUM([p
artialagg1104]),
> [globalagg1107]=SUM([partialagg1104]),
> [globalagg1109]=SUM([partialagg1108])))
> |--Parallelism(Gather Streams)
> |--Compute
> Scalar(DEFINE:([partialagg1104]=[partial
agg1104]))
> etc...
>