尽管连接数远低于资源限制，但达到了Azure Sql请求限制 [英] Azure Sql request limit reached although the number of connections is well below the resource limit

问题描述

我们有一些Java应用程序通过JDBC使用通用的Azure Sql数据库.每个应用程序都有到该数据库的连接池.池中的连接数是有限的，因此来自所有应用程序的连接总数远低于数据库的

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:资源ID:1. 数据库的请求限制为200，并且已达到.看 ' http://go.microsoft.com/fwlink/?LinkId=267637 '寻求帮助

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

• 我们已经使用分析器验证了应用程序打开的连接没有超出允许的数量.
• 我们还验证了正在运行的应用程序数量正确，因此连接数应低于资源限制.
• 我们注意到的一件奇怪的事是sp_who显示了许多连接，而Login列为空:
• 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?

与SQL Server的 MaxDOP 参数有关的一种理论

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

显然，如果此参数大于1，则Sql Server可能在每个查询中使用多个工作线程.我们认为sp_who中没有Login值的那些行对应于其中一个连接使用的此类附加工作线程.这样，与连接相比，工作线程可能会多得多，因此，即使连接数受到限制，我们也会使所有工作线程饱和.

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.

显然，Azure Sql的默认最大并行度设置为0(无限制)，并且只能通过支持票证进行更改

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

Edit3:

又一个证据.我们已经在master数据库上执行了SELECT * FROM sys.resource_stat.我们看到max_worker_percent有时会达到100％.我们真的使工作线程饱和了.

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.

推荐答案

Sql DB支持更改Max DOP设置.您可以通过ALTER DATABASE SCOPED CONFIGURATION T-SQL进行更改 https://msdn.microsoft.com/en-us/library/mt629158.aspx

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

这篇关于尽管连接数远低于资源限制，但达到了Azure Sql请求限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！