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

• 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.

  您可能已经注意到实例的这些文件。

Double click on the package0_eventfile

双击package0_eventfile

It launches another window to display the event session details

它启动另一个窗口以显示事件会话详细信息

Right-click on the extended event session and filter the results for the following conditions.

右键单击扩展事件会话，并针对以下条件过滤结果。

• The component should be equal to QUERY_PROCESSING
  组件应等于QUERY_PROCESSING
• The name should be equal to sp_server_diagnostics
  名称应等于sp_server_diagnostics

It displays the filtered extended event with information about the worker threads. We will cover the information about the data in detail in the later part of the article.

它显示过滤的扩展事件以及有关工作线程的信息。 我们将在本文的后半部分详细介绍有关数据的信息。

使用查询获取SQL Server Always On可用性组工作线程的数据
(Using Query to fetch data for the SQL Server Always On Availability Group worker threads
)

We can use t-SQL code to fetch details for the worker threads. We can use the following methods to retrieve the information using the dynamic management views or system_health extended event session.

我们可以使用t-SQL代码获取工作线程的详细信息。 我们可以使用以下方法通过动态管理视图或system_health扩展事件会话来检索信息。

使用sys.dm_os_schedulers动态管理视图 (Using the sys.dm_os_schedulers dynamic management view)

We can use the sys.dm_os_schedulers dynamic management view to check the current value for the maximum worker threads.

我们可以使用sys.dm_os_schedulers动态管理视图来检查最大工作线程的当前值。

DECLARE @max INT;
SELECT @max = max_workers_count
FROM sys.dm_os_sys_info;
SELECT GETDATE() AS 'CurrentDate', 
       @max AS 'TotalThreads', 
       SUM(active_Workers_count) AS 'CurrentThreads', 
       @max - SUM(active_Workers_count) AS 'AvailableThreads', 
       SUM(runnable_tasks_count) AS 'WorkersWaitingForCpu', 
       SUM(work_queue_count) AS 'RequestWaitingForThreads', 
       SUM(current_workers_count) AS 'AssociatedWorkers'
FROM sys.dm_os_Schedulers
WHERE STATUS = 'VISIBLE ONLINE';

You can create a SQL Agent job and collect the data in a table regularly to track the worker threads.

您可以创建一个SQL Agent作业，并定期收集表中的数据以跟踪工作线程。

使用system_health扩展事件获取信息 (Fetch information using the system_health extended event )

In the GUI method, we can view the data; however, it is not possible to get this data in an excel sheet. To do the investigation or get the trend of worker threads utilization, we want to extract this data in an excel sheet. Let’s look at the way to get this data from the system_health extended event using t-SQL.

在GUI方法中，我们可以查看数据； 但是，无法在Excel工作表中获取此数据。 为了进行调查或获取工作线程利用率的趋势，我们希望将这些数据提取到excel工作表中。 让我们看一下使用t-SQL从system_health扩展事件中获取此数据的方法。

Step 1: Create an intermittent table to fetch all records from the system_health event session

步骤1：创建一个间歇性表以从system_health事件会话中获取所有记录

In this step, we want to pull all event session information into a dataimport table. We need to specify the location of the log folder containing these files.

在此步骤中，我们希望将所有事件会话信息拉入数据导入表。 我们需要指定包含这些文件的日志文件夹的位置。

SELECT [object_name], 
       CAST(event_data AS XML) AS c1
INTO dataimport
FROM sys.fn_xe_file_target_read_file('C:\MSSQL13.MSSQLSERVER\MSSQL\Log\system_health*.xel', NULL, NULL, NULL);
GO

Step 2: Create another table to filter the records for the sp_server_diagnostics output

步骤2：创建另一个表以筛选sp_server_diagnostics输出的记录

In this step, we will filter the data from table 1 for the sp_server_diagnostics result and insert filtered data into another table ServerDiagnosticsdata.

在此步骤中，我们将为sp_server_diagnostics结果过滤表1中的数据，并将过滤后的数据插入另一个表ServerDiagnosticsdata中。

SELECT c1.value('(event/data[@name="component"]/text)[1]', 'varchar(100)') AS SdComponent, 
       c1
INTO ServerDiagnosticsdata
FROM dataimport
WHERE object_name = 'sp_server_diagnostics_component_result';

Step 3: Filter the records for the QUERY_PROCESSING object

步骤3：筛选QUERY_PROCESSING对象的记录

In SQL Server, extended event session capture worker thread information in an XML format. In this step, we create a table to hold the XML data and insert the data into it for the QUERY_PROCESSING object.

在SQL Server中，扩展事件会话以XML格式捕获工作线程信息。 在此步骤中，我们将创建一个表来保存XML数据，并将该数据插入到QUERY_PROCESSING对象的表中。

CREATE TABLE [dbo].[QryProcessingOutput]
([c1] [XML] NULL
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO
INSERT INTO QryProcessingOutput(c1)
       SELECT c1 AS snodes
       FROM ServerDiagnosticsdata
       WHERE SdComponent = 'QUERY_PROCESSING';

Step 4: Retrieve the information from the ServerDiagnosticsdata table for the max worker threads

步骤4：从ServerDiagnosticsdata表中检索最大工作线程数的信息

We have the data in the ServerDiagnosticsdata table but the data is in the XML format. We cannot directly fetch the required data. We need to fetch a particular event and convert it into an appropriate data type.

我们在ServerDiagnosticsdata表中有数据，但是数据是XML格式。 我们无法直接获取所需的数据。 我们需要获取一个特定事件并将其转换为适当的数据类型。

The extended event captures the information in the XML format as shown below.

扩展事件捕获XML格式的信息，如下所示。

Execute the following code to get these required columns data and insert it into a WorkerThreadData table.

执行以下代码以获取这些必需的列数据并将其插入到WorkerThreadData表中。

SELECT c1.value('(./event/@timestamp)[1]', 'datetime') AS utctimestamp, 
       DATEADD(hh, +5.30, c1.value('(./event/@timestamp)[1]', 'datetime')) AS [timestamp], 
       c1.value('(event/data[@name="component"]/text)[1]', 'varchar(100)') AS [component_name], 
       c1.value('(event/data[@name="state"]/text)[1]', 'varchar(100)') AS [component_state], 
       c1.value('(./event//data[@name="data"]/value/queryProcessing/@maxWorkers)[1]', 'int') AS maxworkers, 
       c1.value('(./event//data[@name="data"]/value/queryProcessing/@workersCreated)[1]', 'int') AS workerscreated, 
       c1.value('(./event//data[@name="data"]/value/queryProcessing/@workersIdle)[1]', 'int') AS workersIdle, 
       c1.value('(./event//data[@name="data"]/value/queryProcessing/@tasksCompletedWithinInterval)[1]', 'int') AS tasksCompletedWithinInterval, 
       c1.value('(./event//data[@name="data"]/value/queryProcessing/@oldestPendingTaskWaitingTime)[1]', 'bigint') AS oldestPendingTaskWaitingTime, 
       c1.value('(./event//data[@name="data"]/value/queryProcessing/@pendingTasks)[1]', 'int') AS pendingTasks
INTO [WorkerThreadData]
FROM ServerDiagnosticsdata;

Step 5: Retrieve the information from the ServerDiagnosticsdata table for the SQL Server Always On Availability Group max worker threads

步骤5：从ServerDiagnostics数据表中检索有关SQL Server Always On可用性组最大工作线程的信息

SELECT  [utctimestamp]
      ,[timestamp]
      ,[component_name]
      ,[component_state]
      ,[maxworkers]
      ,[workerscreated]
      ,[workersIdle]
      ,[tasksCompletedWithinInterval]
      ,[oldestPendingTaskWaitingTime]
      ,[pendingTasks]
        FROM  [WorkerThreadData]
order by [timestamp]  desc

We get the data as shown in the following image.

我们得到的数据如下图所示。

扩展事件会话返回的输出列的详细信息 (Details of the output columns returned from the extended event session)

• Component_State: It gives you information that the worker thread is right, or it is about to utilized thoroughly Component_State：它为您提供信息，表明工作线程正确，或者将被彻底利用

In the following screenshot, we can see that component state is Warning because we have more worker threads created than the max number of threads and the idle thread count is also very low.

在下面的屏幕截图中，我们可以看到组件状态为警告，因为我们创建的工作线程多于最大线程数，并且空闲线程数也非常低。

• MaxWorkers: Max worker threads value depends upon the number of processors as defined in the beginning ting of the article. In my example, I have eight processors; therefore, it gives maximum worker threads 576 MaxWorkers ：最大工作线程值取决于本文开头定义的处理器数量。 在我的示例中，我有八个处理器； 因此，它提供最大的工作线程数576
• Workercreated: this number shows the worker thread created by the application Workercreated：此数字显示应用程序创建的工作线程
• oldestPendingTaskWaitingTime: if there is any pending task and it is holding the worker threads, it shows the oldest pending task counts. Ideally, its value should be zero oldestPendingTaskWaitingTime：如果有任何待处理任务，并且该任务包含工作线程，它将显示最旧的待处理任务计数。 理想情况下，其值应为零
• Pending Tasks: It also shows the pending tasks counts, its value also should be zero for a healthy environment 待处理的任务：它还显示待处理的任务计数，对于健康的环境，其值也应为零

结论 (Conclusion)

In this article, we understood the concept of max worker threads and its relation with the databases, secondary replica instances in the SQL Server Always On availability group. You should monitor the worker thread utilization proactively and investigate it to avoid any connectivity issues due to that.

在本文中，我们了解了最大工作线程的概念及其与数据库，SQL Server Always On可用性组中的辅助副本实例的关系。 您应该主动监视工作线程的利用率并进行调查，以免造成任何连接问题。

翻译自: https://www.sqlshack.com/max-worker-threads-for-sql-server-always-on-availability-group-databases/

sql查看数据库线程数