Is parallelism only triggered for select statements or do
inserts/updates/deletes also go thru parallelism such as
update a
set col1 = 1
from a join b
on a.col2=b.col3 ............
or
delete from a join b on a.col1=b.col2 ........
If so, and if we want to disable parallelism, how can we do so for
deletes/updates.. I guess Im asking if i just place the option (maxdop 1)
hint at the end of the statement ?From Books Online , "Degree of Parallelism" topic
"The INSERT, UPDATE, and DELETE operators are executed serially; however,
the WHERE clause of either an UPDATE or DELETE, or SELECT portion of an
INSERT statement may be executed in parallel. The actual data changes are
then serially applied to the database."
You can disable parallelism for INSERT, UPDATE and DELETE where relevant, in
the same way as for SELECT, with OPTION (MAXDOP 1).
--
Jacco Schalkwijk
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23U6JVjvSFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Is parallelism only triggered for select statements or do
> inserts/updates/deletes also go thru parallelism such as
> update a
> set col1 = 1
> from a join b
> on a.col2=b.col3 ............
> or
> delete from a join b on a.col1=b.col2 ........
> If so, and if we want to disable parallelism, how can we do so for
> deletes/updates.. I guess Im asking if i just place the option (maxdop 1)
> hint at the end of the statement ?
>
>
>|||Parallelism can also be set globally for the server in SEM>
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23U6JVjvSFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Is parallelism only triggered for select statements or do
> inserts/updates/deletes also go thru parallelism such as
> update a
> set col1 = 1
> from a join b
> on a.col2=b.col3 ............
> or
> delete from a join b on a.col1=b.col2 ........
> If so, and if we want to disable parallelism, how can we do so for
> deletes/updates.. I guess Im asking if i just place the option (maxdop 1)
> hint at the end of the statement ?
>
>
>
Showing posts with label maxdop. Show all posts
Showing posts with label maxdop. Show all posts
Saturday, February 25, 2012
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...
>
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...
>
Subscribe to:
Posts (Atom)