【SQLServer】max worker threads参数配置

查看和设置max worker threads

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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的使用

?

1 2 3 4 5 6 7 8 9 10

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

　　

查看是哪些系统任务在使用线程

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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