Azure Sql request limit reached although the number of connections is well below the resource limit

Question

We have a few Java applications that use a common Azure Sql database through JDBC. Each application has a pool of connections to that database. The number of connections in the pool is limited, so the total number of connections from all applications is well below the database's resource limits.

Lately we've been getting these types of exceptions quite frequently:

com.microsoft.sqlserver.jdbc.SQLServerException: Resource ID : 1. The request limit for the database is 200 and has been reached. See 'http://go.microsoft.com/fwlink/?LinkId=267637' for assistance

• We've verified using a profiler that the applications don't open more connections than they are allowed.
• We've also verified that that the correct number of applications is running, so the number of connections should be below the resource limit.
• One odd thing we've noticed was that sp_who shows a large number of connections whose Login column is empty:

Any guesses as to what could be the cause of these errors?

Edit:

One theory we have has to do with Sql Server's MaxDOP parameter.

Apparently, if this parameter is above 1, it's possible for Sql Server to use more than one worker thread per query. We think that those rows in sp_who without a Login value correspond to such additional worker threads used by one of the connections. That way, there could be (much) more worker threads than connections, so even though the number of connections is limited, we're saturating all worker threads.

Edit2:

Apparently Azure Sql's default Max Parallelism is set to 0 (unlimited), and can be changed only via a support ticket.

Edit3:

One more piece of evidence. We've executed SELECT * FROM sys.resource_stat on the master database. We're seeing max_worker_percent hit 100% from time to time. We really are saturating the worker threads.

Solution 1

Sql DB supports altering the Max DOP settings. You can change it by ALTER DATABASE SCOPED CONFIGURATION T-SQL https://msdn.microsoft.com/en-us/library/mt629158.aspx

Solution 2

It seems that we were on the right track regarding the MaxDop parameter mentioned in the edits to the question.

Sql server sometime uses more than one worker thread to execute a query so the number of connections doesn't always correspond to the number of execution threads. In our case, a query we've been executing periodically would sometimes spawn tens of worker threads, saturating the worker thread pool (since several copies of it would run simultaneously)

Since Azure SQL doesn't support setting this parameter globally, our solution was to add OPTION (MAXDOP 1) to the offending query so its execution would be more predictable (at the cost of parallelism and speed).

We haven't seen the "resource limit" exception since.

Solution 3

The default behaviour is going to change in August of 2020:

Changing default MAXDOP in Azure SQL Database

To improve customer workload performance and reduce unnecessary resource utilization, the default MAXDOP setting for new databases in Azure SQL Database is changing from the previous default of 0 (unlimited) to 8.

---

How can I change MAXDOP for an existing database?

MAXDOP can be changed using the ALTER DATABASE SCOPED CONFIGURATION statement in the scope of a database.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;

---

What are the symptoms of excessive query parallelism in Azure SQL Database?

In Azure SQL Database, one common symptom of excessive parallelism is exceeding the resource governance limits on the number of worker threads. When this happens, error 10928, “Resource ID : 1. The request limit for the database is N and has been reached” is raised, where N stands for the worker thread limit for the database or elastic pool (note, however, that there are other possible causes for this error).

Comments

[Sirisha Chamarthi]

It does support . .sqlindepth.com/changing-max_dop-setting-in-sql-azure-db-v12

[Trilok Pathak]

@malt : You need to alter the database like ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1; Or you have taken another approach(as you have mentioned add Option(MAXDOP 1) to query), Please clarify in detail what you have done. Thanks !