sql查看数据库线程数_SQLServer始终在可⽤性组数据库上的
最⼤辅助线程
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 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 , there is no enforced limit, and it depends!
根据 ，没有强制执⾏的限制，具体取决于！
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 Ser
ver 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
Note: We cannot install SQL Server 2016 or later on 32-bit servers. Therefore, you should consider 64-bit systems only.注意 ：我们⽆法在32位服务器上安装SQL Server 2016或更⾼版本。 因此，您应该只考虑64位系统。
⼯作线程和SQL Server Always On可⽤性组 (Worker threads and SQL Server Always On Availability Groups )
A SQL Server Always On availability group requires the following worker threads at a high level.
⼀个SQL Server Always On可⽤性组在较⾼级别上需要以下⼯作线程。
SQL Server can use the Max Worker threads – 40 worker threads in Always on Availability Groups
SQL Server可以使⽤Max Worker线程– Always on可⽤性组中的40个Worker线程
Availability Group does not use a single worker thread for an idle SQL instance
可⽤性组不将单个⼯作线程⽤于空闲SQL实例
One worker thread for the Log capture process
⼀个⼯作线程⽤于⽇志捕获过程
One Worker threads for each secondary database for the log send
每个辅助数据库的⼀个辅助线程⽤于⽇志发送
One worker thread for each secondary database for the redo
每个辅助数据库有⼀个⼯作线程⽤于重做
The secondary replica also requires a worker thread for the backups on the primary replica.
辅助副本还需要⼀个⼯作线程来进⾏主副本上的备份。
If any thread is inactive for around 15 seconds, SQL Server releases that worker thread to reuse it.
如果任何线程在15秒钟内处于⾮活动状态，则SQL Server释放该⼯作线程以重新使⽤它。
The minimum number of worker threads in SQL Server Always On Availability Group depends on the following parameters. SQL Server Always On可⽤性组中的辅助线程的最⼩数量取决于以下参数。
Number of the availability groups
可⽤性组数
Number of databases in each availability groups
每个可⽤性组中的数据库数
The number of secondary replicas
辅助副本数
We can use the following formula to calculate minimum thread requirements in Always On.
我们可以使⽤以下公式在Always On中计算最⼩线程需求。
Min Worker thread AG = D * (LCWT + (LSWT * SRC) +MHWT
最⼩⼯作线程AG = D *（LCWT +（LSWT * SRC）+ MHWT
Let’s say we have the following set for Always On Availability Group.
假设我们为Always On Availability组设置了以下设置。
1. One Primary Replica
⼀个主副本
2. Two Secondary Replica ( DC and DR replica)
两个辅助副本（DC和DR副本）
3. One Availability Group
⼀个可⽤性组
4. Ten Databases
⼗个数据库
Let’s use the formula mentioned above to calculate the minimum number of worker threads in AG.
让我们使⽤上⾯提到的公式来计算AG中⼯作线程的最⼩数量。
Min Worker thread AG = (D * (LCWT + (LSWT * SRC))) +MHWT
Min Worker thread AG = (10 * (1 + (1*2))) + 1
Min Worker thread AG = 31
最⼩⼯作线程AG =（D *（LCWT +（LSWT * SRC）））+ MHWT
最⼩⼯作线程AG =（10 *（1 +（1 * 2）））+ 1
最⼩辅助线程AG = 31
Let’s increase the database count to 200 and see the number of threads for AG.
让我们将数据库数量增加到200，然后查看AG的线程数。
Min Worker thread AG = (200 * (1 + (1*2))) + 1
Min Worker thread AG = 601
最⼩⼯作线程AG =（200 *（1 +（1 * 2）））+ 1
最⼩辅助线程AG = 601
Let’s again increase the database count to 500 and see the number of threads for AG.
让我们再次将数据库数量增加到500，然后查看AG的线程数。
Min Worker thread AG = (500 * (1 + (1*2))) + 1
Min Worker thread AG = 1501
最⼩⼯作线程AG =（500 *（1 +（1 * 2）））+ 1
最⼩辅助线程AG = 1501
You can see here the minimum requirement for the worker threads is 1501. SQL Server will require additional worker threads depending upon the database workload as well. In this case, if you have a SQL Server with 16 cores or even 32, you will have a shortage of worker threads. You might not face this issue if most of the databases are idle, but you need to consider this point while adding databases in the AG group.
您可以在此处看到⼯作线程的最低要求为1501。SQLServer还将需要其他⼯作线程，具体取决于数据库⼯作负载。 在这种情况下，如果您SQL Server具有16个核⼼甚⾄32个核⼼，则⼯作线程将不⾜。 如果⼤多数数据库处于空闲状态，则可能不会遇到此问题，但是在AG组中添加数据库时需要考虑这⼀点。
监视SQL Server Always On可⽤性组中的辅助线程 (Monitor Worker thread in SQL Server Always On Availability Group)
In SQL Server, under the extended event session, we can system_health extended event session.
在SQL Server中，在扩展事件会话下，我们可以使⽤system_health扩展事件会话。
It is a default extended event and automatically gets a restart on each instance with the start of SQL Services.
这是默认的扩展事件，并且在启动SQL Services时⾃动在每个实例上重新启动。
You can find below important events captured in this extended event.
您可以在下⾯到此扩展事件中捕获的重要事件。
Deadlock details
死锁详细信息
The output of system diagnostic and health event data using the sp_server_diagnostics
使⽤sp_server_diagnostics输出系统诊断和运⾏状况事件数据
It gives the information about sessions holding locks for more than 30 seconds
它提供有关持有锁超过30秒的会话的信息
We can check the information about the worker threads, their utilization
我们可以检查有关⼯作线程，其利⽤率的信息
Ring buffer events capture information about the memory broker, connectivity and scheduler
环形缓冲区事件捕获有关内存代理，连接和调度程序的信息
A critical error having severity greater than or equals to 20
严重性⼤于或等于20的严重错误
We can use both the GUI and t-SQL method to monitor the worker thread in SQL Server Always On.
我们可以同时使⽤GUI和t-SQL⽅法来监视SQL Server Always On中的⼯作线程。
检查SQL Server Always On可⽤性组中的辅助线程的GUI⽅法 (GUI Method to check Worker threads in SQL Server Always On Availability Group)
We can view the live data for the extended event session. Right-click on system_health extended event and Watch Live Data
我们可以查看扩展事件会话的实时数据。 右键单击system_health扩展事件并观看实时数据
By default, SQL Server stores the information about this extended event session in an event file. This event file is located in the log folder of the SQL instance
默认情况下，SQL Server将有关此扩展事件会话的信息存储在事件⽂件中。 此事件⽂件位于SQL实例的⽇志⽂件夹中
You might have noticed these files for your instance.
sql连接不上服务器
您可能已经注意到实例的这些⽂件。
Double click on the package0_eventfile
双击package0_eventfile
It launches another window to display the event session details
它启动另⼀个窗⼝以显⽰事件会话详细信息

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。