Forum Discussion
Parallized Stored Procedure executed synchron
Hi,
we used a foreach loop to do calculations with a stored procedure. This used to work very well but for no reason the time went from some minutes to some hours and sometimes we get error messages like this:
Execution fail against sql server. Please contact SQL Server team if you need further support. Sql error number: 10928. Error Message: Resource ID : 1. The request limit for the database is 400 and has been reached. See 'https://docs.microsoft.com/azure/azure-sql/database/resource-limits-logical-server' for assistance.
After some investigation in the sql server we recognized, that the 50 queries that should be run in parallel seem to ran synchoniously in azure SQL. There is always only one query running. All others are suspendet.
I know this is the ADF forum. But probably someome can tell me if this is an ADF or SQL Server issue and what could it cause.
Regards
Holger
Dixus, the default max degree of parallelism (MAXDOP) parameter for Azure SQL Databases is 8, so if your database is configured with this value, it may be causing you to exceed the threshold of 400 when executing 50 queries in parallel. If all queries need to run concurrently, first attempt to use an adjusted MAXDOP query hint at the query level to override the database setting, and then consider updating the database scoped configuration.
If the queries can be grouped into smaller batches run sequentially, then you can also update the Batch count property of the For Each activity.
- ericpasztor
Microsoft
Dixus, the default max degree of parallelism (MAXDOP) parameter for Azure SQL Databases is 8, so if your database is configured with this value, it may be causing you to exceed the threshold of 400 when executing 50 queries in parallel. If all queries need to run concurrently, first attempt to use an adjusted MAXDOP query hint at the query level to override the database setting, and then consider updating the database scoped configuration.
If the queries can be grouped into smaller batches run sequentially, then you can also update the Batch count property of the For Each activity.
