【SQLServer】max worker threads參數配置

查看和設置max worker threads

USE master; //選中你想設置max worker threads的數據庫。master表示在實例級別進行設置
GO
EXEC sp_configure 'show advanced option'//顯示當前的高級選項設置
GO
EXEC sp_configure 'show advanced option', '1'; //開啟高級選項
GO
RECONFIGURE WITH OVERRIDE; //重新配置修改
GO
EXEC sp_configure 'show advanced option' //確認
GO
EXEC sp_configure //顯示所有高級選項的值
GO
EXEC sp_configure 'max worker threads' //顯示當前max worker threads的值
GO
EXEC sp_configure 'max worker threads', 3500; //設置max worker threads的值
GO
RECONFIGURE WITH OVERRIDE; //重新配置修改
GO 
EXEC sp_configure 'max worker threads' //確認
GO
EXEC sp_configure 'show advanced option', '0'; //關閉高級選項
GO

 

查看當前的連接和Max Worker Threads

SELECT
	DB_NAME ( dbid ) AS DBName,
	COUNT( dbid ) AS NumberOfConnections,
	loginame AS LoginName 
FROM
	sys.sysprocesses 
WHERE
	dbid > 0 
GROUP BY
	dbid,
	loginame;
	
SELECT
	SUM( current_workers_count ) AS [ Current worker thread ] 
FROM
	sys.dm_os_schedulers;

　　

數據庫鏡像和alwayson的max worker threads的最佳實踐
·避免max worker threads耗光，不要創建超過10個AG和100個數據庫。這表示每個AG10個數據庫，不過這只是建議
·如果max worker threads耗光，如果是虛擬機，增加處理器的個數
·與運行鏡像和可用性組的數據庫進行過多的數據庫整合可能會導致max worker threads耗盡。

估算Max Worker Thread的使用

SELECT
	scheduler_id,
	current_tasks_count,
	current_workers_count,
	active_workers_count,
	work_queue_count 
FROM
	sys.dm_os_schedulers 
WHERE
	STATUS = 'Visible ONLINE';

　　

查看是哪些系統任務在使用線程

SELECT
	is_preemptive,
	state,
	last_wait_type,
	count(*) AS NumWorkers 
FROM
	sys.dm_os_workers 
GROUP BY
	state,
	last_wait_type,
	is_preemptive 
ORDER BY
	count(*) DESC;

　　

//www.cnblogs.com/abclife/p/16683673.html

Tags:

VirMach 便宜 VPS

QNews

QNews