Enable parallel query processing in Azure SQL database
Does anyone know how to enable parallel query processing in Azure SQL Database. I'm trying to debug some queries which are running acceptable on my laptop (sql server 2017) but are very slow in azure SQL db. The pricing tier for azure database is Standard S2: 50 DTUs. The only difference I saw in query plan used on my laptop is Distribute Streams, Repartition Streams, and Gather Streams. How can I enable these in azure? I couldn't found any documentation regarding these options in azure database.
Update
Since was not able to obtain the same query plan in azure, I checked the number of cpu cores of the azure sql database. It seems that for Standard S2 pricing tier is just one core. To see the number of cores in azure database I used this query: (reference: http://www.nikoport.com/2015/03/19/azure-sqldatabase-v12-premium-editions-and-available-cores/)
select * from sys.dm_os_schedulers where status = 'VISIBLE ONLINE' and is_online = 1
On azure I got just one entry, while on my laptop I have 8 cpu cores. If I run the query with OPTION (MAXDOP 1) I got the same execution plan on my machine
azure azure-sql-database asked Nov 12 at 13:54
JamesKovary
63
add a comment |
Does anyone know how to enable parallel query processing in Azure SQL Database. I'm trying to debug some queries which are running acceptable on my laptop (sql server 2017) but are very slow in azure SQL db. The pricing tier for azure database is Standard S2: 50 DTUs. The only difference I saw in query plan used on my laptop is Distribute Streams, Repartition Streams, and Gather Streams. How can I enable these in azure? I couldn't found any documentation regarding these options in azure database.
Update
Since was not able to obtain the same query plan in azure, I checked the number of cpu cores of the azure sql database. It seems that for Standard S2 pricing tier is just one core. To see the number of cores in azure database I used this query: (reference: http://www.nikoport.com/2015/03/19/azure-sqldatabase-v12-premium-editions-and-available-cores/)
select * from sys.dm_os_schedulers where status = 'VISIBLE ONLINE' and is_online = 1
On azure I got just one entry, while on my laptop I have 8 cpu cores. If I run the query with OPTION (MAXDOP 1) I got the same execution plan on my machine
azure azure-sql-database asked Nov 12 at 13:54
JamesKovary
63
add a comment |
Does anyone know how to enable parallel query processing in Azure SQL Database. I'm trying to debug some queries which are running acceptable on my laptop (sql server 2017) but are very slow in azure SQL db. The pricing tier for azure database is Standard S2: 50 DTUs. The only difference I saw in query plan used on my laptop is Distribute Streams, Repartition Streams, and Gather Streams. How can I enable these in azure? I couldn't found any documentation regarding these options in azure database.
Update
Since was not able to obtain the same query plan in azure, I checked the number of cpu cores of the azure sql database. It seems that for Standard S2 pricing tier is just one core. To see the number of cores in azure database I used this query: (reference: http://www.nikoport.com/2015/03/19/azure-sqldatabase-v12-premium-editions-and-available-cores/)
select * from sys.dm_os_schedulers where status = 'VISIBLE ONLINE' and is_online = 1
On azure I got just one entry, while on my laptop I have 8 cpu cores. If I run the query with OPTION (MAXDOP 1) I got the same execution plan on my machine
azure azure-sql-database asked Nov 12 at 13:54
JamesKovary
63
Does anyone know how to enable parallel query processing in Azure SQL Database. I'm trying to debug some queries which are running acceptable on my laptop (sql server 2017) but are very slow in azure SQL db. The pricing tier for azure database is Standard S2: 50 DTUs. The only difference I saw in query plan used on my laptop is Distribute Streams, Repartition Streams, and Gather Streams. How can I enable these in azure? I couldn't found any documentation regarding these options in azure database.
Update
Since was not able to obtain the same query plan in azure, I checked the number of cpu cores of the azure sql database. It seems that for Standard S2 pricing tier is just one core. To see the number of cores in azure database I used this query: (reference: http://www.nikoport.com/2015/03/19/azure-sqldatabase-v12-premium-editions-and-available-cores/)
select * from sys.dm_os_schedulers where status = 'VISIBLE ONLINE' and is_online = 1
On azure I got just one entry, while on my laptop I have 8 cpu cores. If I run the query with OPTION (MAXDOP 1) I got the same execution plan on my machine
azure azure-sql-database azure azure-sql-database asked Nov 12 at 13:54
JamesKovary
63
asked Nov 12 at 13:54
JamesKovary
63
edited Nov 13 at 10:22
asked Nov 12 at 13:54
JamesKovary
63
asked Nov 12 at 13:54
JamesKovary
63
asked Nov 12 at 13:54
JamesKovary
63
63
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Parallelism is enabled by default in Azure SQL Database. If you're is not enabled, it's possible that it was disabled through the ALTER DATABASE SCOPED CONFIGURATION command. Use that same command to enable parallelism.
answered Nov 12 at 14:26
Grant Fritchey
1,473109
Thank you! I checked that settings, default value was 0, I also tried other values, but the execution plan is the same. Is there anything else I could check to see why parallelism is not used? The database is the same, is a copy from azure.
– JamesKovary
Nov 12 at 20:27
What about the cost threshold for parallelism. You can't affect that in Azure. Also, the Azure optimizer is different (not radically, but possibly enough) from the 2017 optimizer. However, I'll be there are more differences than just a parallel vs. a non-parallel plan.
– Grant Fritchey
Nov 13 at 16:17
I missed Conor's answer the first time. It's the correct one. You should mark it as such.
– Grant Fritchey
Nov 13 at 16:18
add a comment |
SQL Azure has reservation sizes that are (at the low-end) less than a full core and at the high-end contain multiple cores. There are actually two pricing models right now in SQL Azure:
* Basic/Standard/Premium
* v-Core based
The Basic/Standard/Premium model had more-or-less fixed fractions of CPU, memory, IOPS, and storage. The v-core based model exposes a bit more of the hardware (you can see the CPU generations and choose which ones you want) and you can purchase storage/iops separately.
You were using an S2 database. Basic and Standard (up to ~S3) are really only selling you a fraction of a core at those price points. Premium P1 is about a core, and the v-core model sells you 1 or more cores.
If you want parallel query plans, you need to have a reservation size that is greater than a single core.
answered Nov 13 at 11:52
Conor Cunningham MSFT
68336
Thank you @Conor! It is tricky to explain to a manager that the S2 database from azure is slowly than my standard development laptop :) What I was not able to find is an official documentation from Microsoft with these information. Of course if I look on the options now and compare the prices with 2 v-Core it is clear that on S3 you cannot have more that one core.
– JamesKovary
Nov 13 at 15:10
The premium/standard/basic model was targeted at selling a transaction throughput rate for an OLTP workload. So, most of the resources were abstracted away as a result. The new v-core model is more explicit. One detail: In the business critical model you are paying for (generally) 4x the core count in SQL Azure vs. a single VM. This is because you get multiple replicas (different machines) built-in with your database to provide high-availability. You can look at sys.dm_os_schedulers in any reservation to determine if you have > 1 to enable parallelism or not.
– Conor Cunningham MSFT
Nov 15 at 2:57
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53263659%2fenable-parallel-query-processing-in-azure-sql-database%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Parallelism is enabled by default in Azure SQL Database. If you're is not enabled, it's possible that it was disabled through the ALTER DATABASE SCOPED CONFIGURATION command. Use that same command to enable parallelism.
answered Nov 12 at 14:26
Grant Fritchey
1,473109
Thank you! I checked that settings, default value was 0, I also tried other values, but the execution plan is the same. Is there anything else I could check to see why parallelism is not used? The database is the same, is a copy from azure.
– JamesKovary
Nov 12 at 20:27
What about the cost threshold for parallelism. You can't affect that in Azure. Also, the Azure optimizer is different (not radically, but possibly enough) from the 2017 optimizer. However, I'll be there are more differences than just a parallel vs. a non-parallel plan.
– Grant Fritchey
Nov 13 at 16:17
I missed Conor's answer the first time. It's the correct one. You should mark it as such.
– Grant Fritchey
Nov 13 at 16:18
add a comment |
Parallelism is enabled by default in Azure SQL Database. If you're is not enabled, it's possible that it was disabled through the ALTER DATABASE SCOPED CONFIGURATION command. Use that same command to enable parallelism.
answered Nov 12 at 14:26
Grant Fritchey
1,473109
Thank you! I checked that settings, default value was 0, I also tried other values, but the execution plan is the same. Is there anything else I could check to see why parallelism is not used? The database is the same, is a copy from azure.
– JamesKovary
Nov 12 at 20:27
What about the cost threshold for parallelism. You can't affect that in Azure. Also, the Azure optimizer is different (not radically, but possibly enough) from the 2017 optimizer. However, I'll be there are more differences than just a parallel vs. a non-parallel plan.
– Grant Fritchey
Nov 13 at 16:17
I missed Conor's answer the first time. It's the correct one. You should mark it as such.
– Grant Fritchey
Nov 13 at 16:18
add a comment |
Parallelism is enabled by default in Azure SQL Database. If you're is not enabled, it's possible that it was disabled through the ALTER DATABASE SCOPED CONFIGURATION command. Use that same command to enable parallelism.
answered Nov 12 at 14:26
Grant Fritchey
1,473109
Parallelism is enabled by default in Azure SQL Database. If you're is not enabled, it's possible that it was disabled through the ALTER DATABASE SCOPED CONFIGURATION command. Use that same command to enable parallelism.
answered Nov 12 at 14:26
Grant Fritchey
1,473109
answered Nov 12 at 14:26
Grant Fritchey
1,473109
answered Nov 12 at 14:26
Grant Fritchey
1,473109
answered Nov 12 at 14:26
Grant Fritchey
1,473109
1,473109
Thank you! I checked that settings, default value was 0, I also tried other values, but the execution plan is the same. Is there anything else I could check to see why parallelism is not used? The database is the same, is a copy from azure.
– JamesKovary
Nov 12 at 20:27
What about the cost threshold for parallelism. You can't affect that in Azure. Also, the Azure optimizer is different (not radically, but possibly enough) from the 2017 optimizer. However, I'll be there are more differences than just a parallel vs. a non-parallel plan.
– Grant Fritchey
Nov 13 at 16:17
I missed Conor's answer the first time. It's the correct one. You should mark it as such.
– Grant Fritchey
Nov 13 at 16:18
add a comment |
Thank you! I checked that settings, default value was 0, I also tried other values, but the execution plan is the same. Is there anything else I could check to see why parallelism is not used? The database is the same, is a copy from azure.
– JamesKovary
Nov 12 at 20:27
What about the cost threshold for parallelism. You can't affect that in Azure. Also, the Azure optimizer is different (not radically, but possibly enough) from the 2017 optimizer. However, I'll be there are more differences than just a parallel vs. a non-parallel plan.
– Grant Fritchey
Nov 13 at 16:17
I missed Conor's answer the first time. It's the correct one. You should mark it as such.
– Grant Fritchey
Nov 13 at 16:18
Thank you! I checked that settings, default value was 0, I also tried other values, but the execution plan is the same. Is there anything else I could check to see why parallelism is not used? The database is the same, is a copy from azure.
– JamesKovary
Nov 12 at 20:27
Thank you! I checked that settings, default value was 0, I also tried other values, but the execution plan is the same. Is there anything else I could check to see why parallelism is not used? The database is the same, is a copy from azure.
– JamesKovary
Nov 12 at 20:27
What about the cost threshold for parallelism. You can't affect that in Azure. Also, the Azure optimizer is different (not radically, but possibly enough) from the 2017 optimizer. However, I'll be there are more differences than just a parallel vs. a non-parallel plan.
– Grant Fritchey
Nov 13 at 16:17
What about the cost threshold for parallelism. You can't affect that in Azure. Also, the Azure optimizer is different (not radically, but possibly enough) from the 2017 optimizer. However, I'll be there are more differences than just a parallel vs. a non-parallel plan.
– Grant Fritchey
Nov 13 at 16:17
I missed Conor's answer the first time. It's the correct one. You should mark it as such.
– Grant Fritchey
Nov 13 at 16:18
I missed Conor's answer the first time. It's the correct one. You should mark it as such.
– Grant Fritchey
Nov 13 at 16:18
add a comment |
SQL Azure has reservation sizes that are (at the low-end) less than a full core and at the high-end contain multiple cores. There are actually two pricing models right now in SQL Azure:
* Basic/Standard/Premium
* v-Core based
The Basic/Standard/Premium model had more-or-less fixed fractions of CPU, memory, IOPS, and storage. The v-core based model exposes a bit more of the hardware (you can see the CPU generations and choose which ones you want) and you can purchase storage/iops separately.
You were using an S2 database. Basic and Standard (up to ~S3) are really only selling you a fraction of a core at those price points. Premium P1 is about a core, and the v-core model sells you 1 or more cores.
If you want parallel query plans, you need to have a reservation size that is greater than a single core.
answered Nov 13 at 11:52
Conor Cunningham MSFT
68336
Thank you @Conor! It is tricky to explain to a manager that the S2 database from azure is slowly than my standard development laptop :) What I was not able to find is an official documentation from Microsoft with these information. Of course if I look on the options now and compare the prices with 2 v-Core it is clear that on S3 you cannot have more that one core.
– JamesKovary
Nov 13 at 15:10
The premium/standard/basic model was targeted at selling a transaction throughput rate for an OLTP workload. So, most of the resources were abstracted away as a result. The new v-core model is more explicit. One detail: In the business critical model you are paying for (generally) 4x the core count in SQL Azure vs. a single VM. This is because you get multiple replicas (different machines) built-in with your database to provide high-availability. You can look at sys.dm_os_schedulers in any reservation to determine if you have > 1 to enable parallelism or not.
– Conor Cunningham MSFT
Nov 15 at 2:57
add a comment |
SQL Azure has reservation sizes that are (at the low-end) less than a full core and at the high-end contain multiple cores. There are actually two pricing models right now in SQL Azure:
* Basic/Standard/Premium
* v-Core based
The Basic/Standard/Premium model had more-or-less fixed fractions of CPU, memory, IOPS, and storage. The v-core based model exposes a bit more of the hardware (you can see the CPU generations and choose which ones you want) and you can purchase storage/iops separately.
You were using an S2 database. Basic and Standard (up to ~S3) are really only selling you a fraction of a core at those price points. Premium P1 is about a core, and the v-core model sells you 1 or more cores.
If you want parallel query plans, you need to have a reservation size that is greater than a single core.
answered Nov 13 at 11:52
Conor Cunningham MSFT
68336
Thank you @Conor! It is tricky to explain to a manager that the S2 database from azure is slowly than my standard development laptop :) What I was not able to find is an official documentation from Microsoft with these information. Of course if I look on the options now and compare the prices with 2 v-Core it is clear that on S3 you cannot have more that one core.
– JamesKovary
Nov 13 at 15:10
The premium/standard/basic model was targeted at selling a transaction throughput rate for an OLTP workload. So, most of the resources were abstracted away as a result. The new v-core model is more explicit. One detail: In the business critical model you are paying for (generally) 4x the core count in SQL Azure vs. a single VM. This is because you get multiple replicas (different machines) built-in with your database to provide high-availability. You can look at sys.dm_os_schedulers in any reservation to determine if you have > 1 to enable parallelism or not.
– Conor Cunningham MSFT
Nov 15 at 2:57
add a comment |
SQL Azure has reservation sizes that are (at the low-end) less than a full core and at the high-end contain multiple cores. There are actually two pricing models right now in SQL Azure:
* Basic/Standard/Premium
* v-Core based
The Basic/Standard/Premium model had more-or-less fixed fractions of CPU, memory, IOPS, and storage. The v-core based model exposes a bit more of the hardware (you can see the CPU generations and choose which ones you want) and you can purchase storage/iops separately.
You were using an S2 database. Basic and Standard (up to ~S3) are really only selling you a fraction of a core at those price points. Premium P1 is about a core, and the v-core model sells you 1 or more cores.
If you want parallel query plans, you need to have a reservation size that is greater than a single core.
answered Nov 13 at 11:52
Conor Cunningham MSFT
68336
SQL Azure has reservation sizes that are (at the low-end) less than a full core and at the high-end contain multiple cores. There are actually two pricing models right now in SQL Azure:
* Basic/Standard/Premium
* v-Core based
The Basic/Standard/Premium model had more-or-less fixed fractions of CPU, memory, IOPS, and storage. The v-core based model exposes a bit more of the hardware (you can see the CPU generations and choose which ones you want) and you can purchase storage/iops separately.
You were using an S2 database. Basic and Standard (up to ~S3) are really only selling you a fraction of a core at those price points. Premium P1 is about a core, and the v-core model sells you 1 or more cores.
If you want parallel query plans, you need to have a reservation size that is greater than a single core.
answered Nov 13 at 11:52
Conor Cunningham MSFT
68336
answered Nov 13 at 11:52
Conor Cunningham MSFT
68336
answered Nov 13 at 11:52
Conor Cunningham MSFT
68336
answered Nov 13 at 11:52
Conor Cunningham MSFT
68336
68336
Thank you @Conor! It is tricky to explain to a manager that the S2 database from azure is slowly than my standard development laptop :) What I was not able to find is an official documentation from Microsoft with these information. Of course if I look on the options now and compare the prices with 2 v-Core it is clear that on S3 you cannot have more that one core.
– JamesKovary
Nov 13 at 15:10
The premium/standard/basic model was targeted at selling a transaction throughput rate for an OLTP workload. So, most of the resources were abstracted away as a result. The new v-core model is more explicit. One detail: In the business critical model you are paying for (generally) 4x the core count in SQL Azure vs. a single VM. This is because you get multiple replicas (different machines) built-in with your database to provide high-availability. You can look at sys.dm_os_schedulers in any reservation to determine if you have > 1 to enable parallelism or not.
– Conor Cunningham MSFT
Nov 15 at 2:57
add a comment |
Thank you @Conor! It is tricky to explain to a manager that the S2 database from azure is slowly than my standard development laptop :) What I was not able to find is an official documentation from Microsoft with these information. Of course if I look on the options now and compare the prices with 2 v-Core it is clear that on S3 you cannot have more that one core.
– JamesKovary
Nov 13 at 15:10
The premium/standard/basic model was targeted at selling a transaction throughput rate for an OLTP workload. So, most of the resources were abstracted away as a result. The new v-core model is more explicit. One detail: In the business critical model you are paying for (generally) 4x the core count in SQL Azure vs. a single VM. This is because you get multiple replicas (different machines) built-in with your database to provide high-availability. You can look at sys.dm_os_schedulers in any reservation to determine if you have > 1 to enable parallelism or not.
– Conor Cunningham MSFT
Nov 15 at 2:57
Thank you @Conor! It is tricky to explain to a manager that the S2 database from azure is slowly than my standard development laptop :) What I was not able to find is an official documentation from Microsoft with these information. Of course if I look on the options now and compare the prices with 2 v-Core it is clear that on S3 you cannot have more that one core.
– JamesKovary
Nov 13 at 15:10
Thank you @Conor! It is tricky to explain to a manager that the S2 database from azure is slowly than my standard development laptop :) What I was not able to find is an official documentation from Microsoft with these information. Of course if I look on the options now and compare the prices with 2 v-Core it is clear that on S3 you cannot have more that one core.
– JamesKovary
Nov 13 at 15:10
The premium/standard/basic model was targeted at selling a transaction throughput rate for an OLTP workload. So, most of the resources were abstracted away as a result. The new v-core model is more explicit. One detail: In the business critical model you are paying for (generally) 4x the core count in SQL Azure vs. a single VM. This is because you get multiple replicas (different machines) built-in with your database to provide high-availability. You can look at sys.dm_os_schedulers in any reservation to determine if you have > 1 to enable parallelism or not.
– Conor Cunningham MSFT
Nov 15 at 2:57
The premium/standard/basic model was targeted at selling a transaction throughput rate for an OLTP workload. So, most of the resources were abstracted away as a result. The new v-core model is more explicit. One detail: In the business critical model you are paying for (generally) 4x the core count in SQL Azure vs. a single VM. This is because you get multiple replicas (different machines) built-in with your database to provide high-availability. You can look at sys.dm_os_schedulers in any reservation to determine if you have > 1 to enable parallelism or not.
– Conor Cunningham MSFT
Nov 15 at 2:57
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53263659%2fenable-parallel-query-processing-in-azure-sql-database%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
