Establecer la opción de configuración del servidor Máximo de subprocesos de trabajo

  • Artículo

En este tema se describe cómo establecer la opción de configuración del servidor número máximo de subprocesos de trabajo en SQL Server mediante SQL Server Management Studio o Transact-SQL. La opción máximo de subprocesos de trabajo configura el número de subprocesos de trabajo que están disponibles para SQL Server procesos. SQL Server usa los servicios de subprocesos nativos de los sistemas operativos para que uno o varios subprocesos admitan cada red que SQL Server admita simultáneamente, otro subproceso controla los puntos de comprobación de la base de datos y un grupo de subprocesos controla a todos los usuarios. El valor predeterminado de máximo de subprocesos de trabajo es 0. Esto permite a SQL Server configurar automáticamente el número de subprocesos de trabajo en el inicio. El valor predeterminado es el más adecuado para la mayor parte de los sistemas. No obstante, dependiendo de la configuración del sistema, el uso de un valor concreto para máximo de subprocesos de trabajo en ocasiones puede mejorar el rendimiento.

En este tema

Antes de empezar

Limitaciones y restricciones

  • Si el número real de solicitudes de consulta es inferior al número establecido en la opción de máximo de subprocesos de trabajo, un subproceso controla cada solicitud de consulta. Sin embargo, si el número real de solicitudes de consulta supera la cantidad establecida en máximos subprocesos de trabajo, SQL Server agrupa los subprocesos de trabajo para que el siguiente subproceso de trabajo disponible pueda controlar la solicitud.

Recomendaciones

  • Esta opción es una opción avanzada y solo debe cambiarla un administrador de base de datos experimentado o un técnico de SQL Server certificado.

  • La agrupación de subprocesos permite optimizar el rendimiento cuando un gran número de clientes se conecta al servidor. Normalmente, se crea un subproceso del sistema operativo independiente para cada solicitud de la consulta. Sin embargo, cuando hay cientos de conexiones al servidor, el uso de un subproceso por solicitud de consulta puede consumir grandes cantidades de recursos del sistema. La opción de máximo de subprocesos de trabajo permite que SQL Server cree un grupo de subprocesos de trabajo para atender un gran número de solicitudes de consulta, lo que mejora el rendimiento.

  • En la tabla siguiente se muestra el número configurado automáticamente de subprocesos de trabajo máximo para varias combinaciones de CPU y versiones de SQL Server.

    Número de CPU Equipo de 32 bits Equipo de 64 bits
    <= 4 procesadores 256 512
    8 procesadores 288 576
    16 procesadores 352 704
    32 procesadores 480 960
    64 procesadores 736 1472
    128 procesadores 4224 4480
    256 procesadores 8320 8576

    Nota:

    Para obtener recomendaciones sobre el uso de más de 64 CPU, vea Prácticas recomendadas para ejecutar SQL Server en equipos que tienen más de 64 CPU.

    Advertencia

    Se recomienda 1024 como el número máximo de subprocesos de trabajo para una instancia de SQL Server que se ejecuta en un equipo de 32 bits.

  • Si todos los subprocesos de trabajo están activos con consultas de ejecución prolongada, puede parecer que SQL Server no responde hasta que finaliza un subproceso de trabajo y vuelve a estar disponible. Aunque no se trata de un defecto, puede que a veces este comportamiento no sea deseable. Si un proceso parece no responder y no se pueden procesar nuevas consultas, conéctese a SQL Server mediante la conexión de administrador dedicada (DAC) y finalice el proceso. Para impedir este comportamiento, aumente el número máximo de subprocesos de trabajo.

La opción de configuración del servidor max worker threads no tiene en cuenta los subprocesos necesarios para todas las tareas del sistema como grupos de disponibilidad, Service Broker, administrador de bloqueos y otros. Si se supera el número de subprocesos configurados, la siguiente consulta proporcionará información sobre las tareas del sistema que han creado los subprocesos adicionales.

SELECT  
s.session_id,  
r.command,  
r.status,  
r.wait_type,  
r.scheduler_id,  
w.worker_address,  
w.is_preemptive,  
w.state,  
t.task_state,  
t.session_id,  
t.exec_context_id,  
t.request_id  
FROM sys.dm_exec_sessions AS s  
INNERJOIN sys.dm_exec_requests AS r  
    ON s.session_id = r.session_id  
INNER JOIN sys.dm_os_tasks AS t  
    ON r.task_address = t.task_address  
INNER JOIN sys.dm_os_workers AS w  
    ON t.worker_address = w.worker_address  
WHERE s.is_user_process = 0;

Seguridad

Permisos

De forma predeterminada, todos los usuarios tienen permisos de ejecución en sp_configure sin ningún parámetro o solo con el primero. Para ejecutar sp_configure con ambos parámetros y cambiar una opción de configuración, o para ejecutar la instrucción RECONFIGURE, un usuario debe tener el permiso ALTER SETTINGS en el servidor. Los roles fijos de servidor sysadmin y serveradmin tienen el permiso ALTER SETTINGS de forma implícita.

Uso de SQL Server Management Studio

Para configurar la opción de máximo de subprocesos de trabajo

  1. En el Explorador de objetos, haga clic con el botón derecho en un servidor y seleccione Propiedades.

  2. Haga clic en el nodo Procesadores .

  3. En el cuadro Número máximo de subprocesos de trabajo , escriba o seleccione un valor de 128 a 32767.

    Utilice la opción max worker threads para configurar el número de subprocesos de trabajo disponibles para procesos de SQL Server . El valor predeterminado de la opción max worker threads es el óptimo para la mayor parte de los sistemas. No obstante, dependiendo de la configuración del sistema, el uso de un valor inferior para el máximo de subprocesos de trabajo puede mejorar el rendimiento a veces.

Usar Transact-SQL

Para configurar la opción de máximo de subprocesos de trabajo

  1. Conéctese con el Motor de base de datos.

  2. En la barra Estándar, haga clic en Nueva consulta.

  3. Copie y pegue el siguiente ejemplo en la ventana de consulta y haga clic en Ejecutar. En este ejemplo se muestra cómo usar sp_configure para configurar la opción max worker threads en 900.

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'max worker threads', 900 ;  
GO  
RECONFIGURE;  
GO

Para obtener más información, vea Opciones de configuración de servidor (SQL Server).

Seguimiento: Después de configurar la opción de máximo de subprocesos de trabajo

El cambio surtirá efecto inmediatamente sin necesidad de reiniciar el motor de base de datos.

Consulte también

RECONFIGURE (Transact-SQL)
Opciones de configuración de servidor (SQL Server)
sp_configure (Transact-SQL)
Conexión de diagnóstico para administradores de bases de datos