I faced an issue in Azure SQL, I have written 4 stored procedures - let's say sp1, sp2, sp3, sp4 (all the procedures are read-only procedures).
Individually when I run all those stored procedures serially, like this:
exec sp1 (it took time 3s)
go
exec sp2 (it took time 4s)
go
exec sp3 (it took time 6s)
go
exec sp4 (it took time 2s)
That means all the stored procedures return a result set within 15 seconds, but I want to make a parallel call (concurrent) to all the stored procedures so that these stored procedures return result within 6 seconds (as 6 seconds is the highest time a single stored procedure) from an application and I used async calling method to call those stored procedures but what I found it took 15 seconds fully in time parallel call as well .
Though I found from log of Azure SQL all the 4 stored procedures running start time with millisecond difference that means though it calling parallel but it took longer execution time. Then again I changed the calling method and made synchronous call in that time I found 3,4,6,2 , so net result is same both in synchronous and parallel call.
Here my question is there any setting in Azure SQL that is increasing the execution time in times of parallel call?
I used MAXDOP=8