【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;

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