sql查看数据库线程数_SQL Server始终在可用性组数据库上的最大辅助线程

sql查看数据库线程数

This article gives an overview of the Max Worker Threads for the SQL Server Always On Availability Group databases.

本文概述了SQL Server Always On可用性组数据库的Max Worker线程。

SQL Server Always On Availability Group is a widely accepted feature to implement high availability and disaster recovery solution (HADR). It is available from SQL Server 2012 onwards.

SQL Server Always On可用性组是一种广泛接受的功能，用于实现高可用性和灾难恢复解决方案（HADR）。 从SQL Server 2012起可以使用它。

Please make sure you have good knowledge of SQL Server Always On and its implementations before you read this article. You can go through High availability articles at SQLShack.

在阅读本文之前，请确保您对SQL Server Always On及其实现有很好的了解。 您可以阅读SQLShack上的高可用性文章。

Suppose you have configured SQL Server Always On Availability Group for your critical databases. You have a few databases configured in HADR mode. Now, your application team wants to create lots of databases in this AG instance and configure for the Always On. It raises a question.

假设您已经为关键数据库配置了SQL Server Always On可用性组。 您有一些以HADR模式配置的数据库。 现在，您的应用程序团队希望在此AG实例中创建许多数据库，并配置为Always On。 这就提出了一个问题。

• What is the maximum number of availability groups? 可用性组的最大数量是多少？
• How many databases can we add per the availability group? 每个可用性组可以添加多少个数据库？

As per Microsoft documentation, there is no enforced limit, and it depends!

根据Microsoft文档 ，没有强制执行的限制，具体取决于！

One obvious question can come in your mind – If Microsoft does not put any hard limit, we can add as many databases and availability groups we required to do. It does not get any negative impact on server performance, CPU, Memory, workload etc.

您可能会想到一个明显的问题–如果Microsoft没有设置任何硬性限制，我们可以添加所需要做的尽可能多的数据库和可用性组。 它不会对服务器性能，CPU，内存，工作负载等产生任何负面影响。

We need to consider the system resources and performance to add new AG databases. Apart from this, we also need to consider the requirement and usage of the worker threads.

我们需要考虑系统资源和性能以添加新的AG数据库。 除此之外，我们还需要考虑工作线程的需求和使用情况。

Let’s take this conversation ahead and relate it with the following error message. In this screenshot, SQL Server notified that SQL Server Always On Availability Group could not start a new worker thread because we have exhausted the max number of worker threads. You get a timeout error message once the worker thread is exhausted.

让我们继续进行此对话，并将其与以下错误消息相关联。 在此屏幕快照中，SQL Server通知SQL Server Always On可用性组无法启动新的工作线程，因为我们已经用完了最大数量的工作线程。 一旦工作线程耗尽，您将收到超时错误消息。

Once we start an instance of SQL Server, it creates the worker threads according to the server logical processors. The default value is zero that shows that it is allowed to use maximum worker threads.

一旦启动SQL Server实例，它将根据服务器逻辑处理器创建工作线程。 默认值为零，表示允许使用最大工作线程。

We can also check the configured value using the sp_configure system stored procedure.

我们还可以使用sp_configure系统存储过程来检查配置的值。

In the following table, we can see the number of worker threads according to the CPU core count.

在下表中，我们可以根据CPU核心数看到工作线程数。

You can use the following formula to calculate the maximum number of threads.

您可以使用以下公式来计算最大线程数。

• If CPU count is greater than 4 and less than 64, use the following formula

  如果CPU计数大于4且小于64，请使用以下公式

  MAX Worker threads = 512 + ((logical CPUS’s – 4) * 16

  最大辅助线程= 512 +（（逻辑CPUS – 4）* 16

• If CPU count is greater than 64, use the following formula

  如果CPU计数大于64，请使用以下公式

  MAX Worker threads = 512 + ((logical CPUS’s – 4) * 32

  最大辅助线程= 512 +（（逻辑CPUS – 4）* 32 </