【SQLServer】max worker threads参数配置

2022-09-12 12:53 abce 阅读(499) 评论(0) 编辑收藏举报

查看和设置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;