Enable parallel query processing in Azure SQL database

user_3728723 0
Viewed 2761

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-sql-database azure
3 Answers

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.

user_67241750

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.

user_7938960

Azure SQL DB as well SQL Server will decide if your query should run with parallelism. Parallelism in SQL Server generally has to reach a cost threshold (See CostThresholdForParallelism supported in sql server only). I have seen queries never run with parallelism enabled. However, you can force parallelism with a hint.

OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))
user_141848120
Related