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外鍵怎么設置， 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。 這就提出了一個問題。
使用sql查看服務器上的所有數據庫、 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可用性組無法啟動新的工作線程，因為我們已經用完了最大數量的工作線程。 一旦工作線程耗盡，您將收到超時錯誤消息。
oracle怎么查看sql性能。 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系統存儲過程來檢查配置的值。
sql數據庫怎么創建表， 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，請使用以下公式
sql查看數據庫中的表， MAX Worker threads = 512 + ((logical CPUS’s – 4) * 32
最大輔助線程= 512 +（（邏輯CPUS – 4）* 32
A SQL Server Always On availability group requires the following worker threads at a high level.
一個SQL Server Always On可用性組在較高級別上需要以下工作線程。
sql創建數據庫語句？ 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數據庫簡介？ SQL Server Always On可用性組中的輔助線程的最小數量取決于以下參數。
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
備份數據庫的sql語句、 Let’s say we have the following set for Always On Availability Group.
假設我們為Always On Availability組設置了以下設置。
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
sqlserver看正在運行的進程。 最小工作線程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組中添加數據庫時需要考慮這一點。
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.
您可以在下面找到此擴展事件中捕獲的重要事件。
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中的工作線程。
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.
右鍵單擊擴展事件會話，并針對以下條件過濾結果。
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.
它顯示過濾的擴展事件以及有關工作線程的信息。 我們將在本文的后半部分詳細介紹有關數據的信息。
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擴展事件會話來檢索信息。
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作業，并定期收集表中的數據以跟蹤工作線程。
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 snodesFROM ServerDiagnosticsdataWHERE 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.
我們得到的數據如下圖所示。
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.
在下面的屏幕截圖中，我們可以看到組件狀態為警告，因為我們創建的工作線程多于最大線程數，并且空閑線程數也非常低。
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查看數據庫線程數
版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间：8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态
