Mixing
MS SQL Server: Do multiple queries in a batch ever execute in parallel and if so what happens when the second query is dependent on the first?
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
3
down vote
favorite
If I have a batch of two different SELECT statements, is it possible for them to be executed in parallel (if the SQL Optimizer deems it to be the most efficient execution method)?
If the first SELECT statement is selecting into a temp table and the second SELECT statement is then inserting into that same temp table, does that prevent it from being possible for the two statements to be ran in parallel?
(I'm guessing the answers are yes and yes :).
sql-server sql-server-2008-r2 sql-server-2017 temp-tables parallelism
asked Aug 22 at 14:26
J.D.
38029
add a comment |Â
up vote
3
down vote
favorite
If I have a batch of two different SELECT statements, is it possible for them to be executed in parallel (if the SQL Optimizer deems it to be the most efficient execution method)?
If the first SELECT statement is selecting into a temp table and the second SELECT statement is then inserting into that same temp table, does that prevent it from being possible for the two statements to be ran in parallel?
(I'm guessing the answers are yes and yes :).
sql-server sql-server-2008-r2 sql-server-2017 temp-tables parallelism
asked Aug 22 at 14:26
J.D.
38029
The answer "no" is not specific to SQL Server. Any relational database system that respects the rules of ACID is required to execute statements in one batch sequentially, or at least behave as if they do. In practice, it would take a very clever engine indeed to parallelize a batch (as opposed to an individual statement) without this being noticeable, but if it existed, your hypothetical second scenario would be no cause for worry either -- the rules would forbid the first statement from seeing the results of the second.
– Jeroen Mostert
Aug 22 at 15:13
@JeroenMostert - you should make that an answer. It contains useful details.
– Max Vernon
Aug 22 at 16:21
add a comment |Â
up vote
3
down vote
favorite
up vote
3
down vote
favorite
If I have a batch of two different SELECT statements, is it possible for them to be executed in parallel (if the SQL Optimizer deems it to be the most efficient execution method)?
If the first SELECT statement is selecting into a temp table and the second SELECT statement is then inserting into that same temp table, does that prevent it from being possible for the two statements to be ran in parallel?
(I'm guessing the answers are yes and yes :).
sql-server sql-server-2008-r2 sql-server-2017 temp-tables parallelism
asked Aug 22 at 14:26
J.D.
38029
If I have a batch of two different SELECT statements, is it possible for them to be executed in parallel (if the SQL Optimizer deems it to be the most efficient execution method)?
If the first SELECT statement is selecting into a temp table and the second SELECT statement is then inserting into that same temp table, does that prevent it from being possible for the two statements to be ran in parallel?
(I'm guessing the answers are yes and yes :).
sql-server sql-server-2008-r2 sql-server-2017 temp-tables parallelism
asked Aug 22 at 14:26
J.D.
38029
asked Aug 22 at 14:26
J.D.
38029
asked Aug 22 at 14:26
J.D.
38029
asked Aug 22 at 14:26
J.D.
38029
38029
The answer "no" is not specific to SQL Server. Any relational database system that respects the rules of ACID is required to execute statements in one batch sequentially, or at least behave as if they do. In practice, it would take a very clever engine indeed to parallelize a batch (as opposed to an individual statement) without this being noticeable, but if it existed, your hypothetical second scenario would be no cause for worry either -- the rules would forbid the first statement from seeing the results of the second.
– Jeroen Mostert
Aug 22 at 15:13
@JeroenMostert - you should make that an answer. It contains useful details.
– Max Vernon
Aug 22 at 16:21
add a comment |Â
The answer "no" is not specific to SQL Server. Any relational database system that respects the rules of ACID is required to execute statements in one batch sequentially, or at least behave as if they do. In practice, it would take a very clever engine indeed to parallelize a batch (as opposed to an individual statement) without this being noticeable, but if it existed, your hypothetical second scenario would be no cause for worry either -- the rules would forbid the first statement from seeing the results of the second.
– Jeroen Mostert
Aug 22 at 15:13
@JeroenMostert - you should make that an answer. It contains useful details.
– Max Vernon
Aug 22 at 16:21
The answer "no" is not specific to SQL Server. Any relational database system that respects the rules of ACID is required to execute statements in one batch sequentially, or at least behave as if they do. In practice, it would take a very clever engine indeed to parallelize a batch (as opposed to an individual statement) without this being noticeable, but if it existed, your hypothetical second scenario would be no cause for worry either -- the rules would forbid the first statement from seeing the results of the second.
– Jeroen Mostert
Aug 22 at 15:13
The answer "no" is not specific to SQL Server. Any relational database system that respects the rules of ACID is required to execute statements in one batch sequentially, or at least behave as if they do. In practice, it would take a very clever engine indeed to parallelize a batch (as opposed to an individual statement) without this being noticeable, but if it existed, your hypothetical second scenario would be no cause for worry either -- the rules would forbid the first statement from seeing the results of the second.
– Jeroen Mostert
Aug 22 at 15:13
@JeroenMostert - you should make that an answer. It contains useful details.
– Max Vernon
Aug 22 at 16:21
@JeroenMostert - you should make that an answer. It contains useful details.
– Max Vernon
Aug 22 at 16:21
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
7
down vote
accepted
Batch statements are only ever executed serially in the order they appear in the batch.
Now, if you have two statements sent to the server by two different batches, they will run independently and essentially simultaneously (locking and latching aside).
Take for example the following code:
CREATE TABLE #t
(
i int
);
INSERT INTO #t (i) VALUES (0);
The CREATE TABLE always runs prior to the INSERT INTO statement. Consider this:
SELECT 1;
SELECT 2;
The above code will always be ran in order, i.e. SELECT 1 runs first, then after it completes, SELECT 2 runs.
There are multiple ways independent batches can be ran simultaneously, including the use of Multiple Active Result Sets, or MARS, however none of those affect the serial processing of statements within a single batch.
answered Aug 22 at 14:28
Max Vernon
48.9k13106206
Might want to say something about MARS.
– Sean Gallardy
Aug 22 at 14:58
yah, I considered adding details about edge cases, including MARS, but the question is if a batch is processed serially. I guess it wouldn't hurt to add a link to MARS
– Max Vernon
Aug 22 at 15:00
1
Also with MARS execution is interleaved, not parallel. So the session is only executing a single statement at a time, but switch to run a new statement before the results from a previous statement have been sent to the client. See docs.microsoft.com/en-us/sql/relational-databases/native-client/…
– David Browne - Microsoft
Aug 22 at 23:13
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
7
down vote
accepted
Batch statements are only ever executed serially in the order they appear in the batch.
Now, if you have two statements sent to the server by two different batches, they will run independently and essentially simultaneously (locking and latching aside).
Take for example the following code:
CREATE TABLE #t
(
i int
);
INSERT INTO #t (i) VALUES (0);
The CREATE TABLE always runs prior to the INSERT INTO statement. Consider this:
SELECT 1;
SELECT 2;
The above code will always be ran in order, i.e. SELECT 1 runs first, then after it completes, SELECT 2 runs.
There are multiple ways independent batches can be ran simultaneously, including the use of Multiple Active Result Sets, or MARS, however none of those affect the serial processing of statements within a single batch.
answered Aug 22 at 14:28
Max Vernon
48.9k13106206
Might want to say something about MARS.
– Sean Gallardy
Aug 22 at 14:58
yah, I considered adding details about edge cases, including MARS, but the question is if a batch is processed serially. I guess it wouldn't hurt to add a link to MARS
– Max Vernon
Aug 22 at 15:00
1
Also with MARS execution is interleaved, not parallel. So the session is only executing a single statement at a time, but switch to run a new statement before the results from a previous statement have been sent to the client. See docs.microsoft.com/en-us/sql/relational-databases/native-client/…
– David Browne - Microsoft
Aug 22 at 23:13
add a comment |Â
up vote
7
down vote
accepted
Batch statements are only ever executed serially in the order they appear in the batch.
Now, if you have two statements sent to the server by two different batches, they will run independently and essentially simultaneously (locking and latching aside).
Take for example the following code:
CREATE TABLE #t
(
i int
);
INSERT INTO #t (i) VALUES (0);
The CREATE TABLE always runs prior to the INSERT INTO statement. Consider this:
SELECT 1;
SELECT 2;
The above code will always be ran in order, i.e. SELECT 1 runs first, then after it completes, SELECT 2 runs.
There are multiple ways independent batches can be ran simultaneously, including the use of Multiple Active Result Sets, or MARS, however none of those affect the serial processing of statements within a single batch.
answered Aug 22 at 14:28
Max Vernon
48.9k13106206
Might want to say something about MARS.
– Sean Gallardy
Aug 22 at 14:58
yah, I considered adding details about edge cases, including MARS, but the question is if a batch is processed serially. I guess it wouldn't hurt to add a link to MARS
– Max Vernon
Aug 22 at 15:00
1
Also with MARS execution is interleaved, not parallel. So the session is only executing a single statement at a time, but switch to run a new statement before the results from a previous statement have been sent to the client. See docs.microsoft.com/en-us/sql/relational-databases/native-client/…
– David Browne - Microsoft
Aug 22 at 23:13
add a comment |Â
up vote
7
down vote
accepted
up vote
7
down vote
accepted
Batch statements are only ever executed serially in the order they appear in the batch.
Now, if you have two statements sent to the server by two different batches, they will run independently and essentially simultaneously (locking and latching aside).
Take for example the following code:
CREATE TABLE #t
(
i int
);
INSERT INTO #t (i) VALUES (0);
The CREATE TABLE always runs prior to the INSERT INTO statement. Consider this:
SELECT 1;
SELECT 2;
The above code will always be ran in order, i.e. SELECT 1 runs first, then after it completes, SELECT 2 runs.
There are multiple ways independent batches can be ran simultaneously, including the use of Multiple Active Result Sets, or MARS, however none of those affect the serial processing of statements within a single batch.
answered Aug 22 at 14:28
Max Vernon
48.9k13106206
Batch statements are only ever executed serially in the order they appear in the batch.
Now, if you have two statements sent to the server by two different batches, they will run independently and essentially simultaneously (locking and latching aside).
Take for example the following code:
CREATE TABLE #t
(
i int
);
INSERT INTO #t (i) VALUES (0);
The CREATE TABLE always runs prior to the INSERT INTO statement. Consider this:
SELECT 1;
SELECT 2;
The above code will always be ran in order, i.e. SELECT 1 runs first, then after it completes, SELECT 2 runs.
There are multiple ways independent batches can be ran simultaneously, including the use of Multiple Active Result Sets, or MARS, however none of those affect the serial processing of statements within a single batch.
answered Aug 22 at 14:28
Max Vernon
48.9k13106206
edited Aug 22 at 15:02
answered Aug 22 at 14:28
Max Vernon
48.9k13106206
answered Aug 22 at 14:28
Max Vernon
48.9k13106206
answered Aug 22 at 14:28
Max Vernon
48.9k13106206
48.9k13106206
Might want to say something about MARS.
– Sean Gallardy
Aug 22 at 14:58
yah, I considered adding details about edge cases, including MARS, but the question is if a batch is processed serially. I guess it wouldn't hurt to add a link to MARS
– Max Vernon
Aug 22 at 15:00
1
Also with MARS execution is interleaved, not parallel. So the session is only executing a single statement at a time, but switch to run a new statement before the results from a previous statement have been sent to the client. See docs.microsoft.com/en-us/sql/relational-databases/native-client/…
– David Browne - Microsoft
Aug 22 at 23:13
add a comment |Â
Might want to say something about MARS.
– Sean Gallardy
Aug 22 at 14:58
yah, I considered adding details about edge cases, including MARS, but the question is if a batch is processed serially. I guess it wouldn't hurt to add a link to MARS
– Max Vernon
Aug 22 at 15:00
1
Also with MARS execution is interleaved, not parallel. So the session is only executing a single statement at a time, but switch to run a new statement before the results from a previous statement have been sent to the client. See docs.microsoft.com/en-us/sql/relational-databases/native-client/…
– David Browne - Microsoft
Aug 22 at 23:13
Might want to say something about MARS.
– Sean Gallardy
Aug 22 at 14:58
Might want to say something about MARS.
– Sean Gallardy
Aug 22 at 14:58
yah, I considered adding details about edge cases, including MARS, but the question is if a batch is processed serially. I guess it wouldn't hurt to add a link to MARS
– Max Vernon
Aug 22 at 15:00
yah, I considered adding details about edge cases, including MARS, but the question is if a batch is processed serially. I guess it wouldn't hurt to add a link to MARS
– Max Vernon
Aug 22 at 15:00
1
1
Also with MARS execution is interleaved, not parallel. So the session is only executing a single statement at a time, but switch to run a new statement before the results from a previous statement have been sent to the client. See docs.microsoft.com/en-us/sql/relational-databases/native-client/…
– David Browne - Microsoft
Aug 22 at 23:13
Also with MARS execution is interleaved, not parallel. So the session is only executing a single statement at a time, but switch to run a new statement before the results from a previous statement have been sent to the client. See docs.microsoft.com/en-us/sql/relational-databases/native-client/…
– David Browne - Microsoft
Aug 22 at 23:13
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f215601%2fms-sql-server-do-multiple-queries-in-a-batch-ever-execute-in-parallel-and-if-so%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
The answer "no" is not specific to SQL Server. Any relational database system that respects the rules of ACID is required to execute statements in one batch sequentially, or at least behave as if they do. In practice, it would take a very clever engine indeed to parallelize a batch (as opposed to an individual statement) without this being noticeable, but if it existed, your hypothetical second scenario would be no cause for worry either -- the rules would forbid the first statement from seeing the results of the second.
– Jeroen Mostert
Aug 22 at 15:13
@JeroenMostert - you should make that an answer. It contains useful details.
– Max Vernon
Aug 22 at 16:21