Solucionar problemas del regulador de recursos

Si no hay ninguna UDF clasificadora disponible para la clasificación, todas las sesiones irán automáticamente al grupo de cargas de trabajo predeterminado. Después de crear una UDF clasificadora, tiene que comprobar que se registra con el regulador de recursos y que la configuración en memoria se actualiza.

Crear, registrar y habilitar una UDF clasificadora es un proceso que consta de tres pasos:

• En primer lugar, es necesario crear la función.

  CREATE FUNCTION function_name() RETURNS <something> 
  WITH SCHEMABINDING
  

• En segundo lugar, hay que registrar la función con el regulador de recursos.

  ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=schema_name.function_name)
  

• En tercer lugar, hay que actualizar la configuración en memoria del regulador de recursos.

  ALTER RESOURCE GOVERNOR RECONFIGURE
  

Lo primero que hay que hacer al solucionar problemas de clasificación es comprobar que la función que se creó está registrada con el regulador de recursos y que la configuración se actualizó. Utilice la consulta siguiente para obtener el nombre de esquema (schema_name) y el nombre de la función clasificadora (function_name) para la UDF clasificadora que el regulador de recursos usa actualmente.

USE master
SELECT 
      object_schema_name(classifier_function_id) AS [schema_name],
      object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration

Puede utilizar el enfoque anterior para solucionar problemas en un escenario en el que se cambió la UDF clasificadora, pero donde el regulador de recursos todavía use la lógica de función anterior para clasificar las sesiones. Este comportamiento indica que los cambios que realizó no se aplicaron a la configuración en memoria.

Puede crear una función clasificadora muy compleja que no genere los resultados esperados o que use en gran medida los recursos. Si ha aplicado los métodos básicos de solución de problemas, tiene que comprobar que la lógica de la función es apropiada. El peor escenario es aquel en el que un error en la codificación provoca un bucle sin fin o una consulta descontrolada.

Puede utilizar una conexión de administrador dedicada (DAC) para solucionar los problemas de una función clasificadora mal escrita, porque no está sujeta a clasificación y se puede utilizar mientras el regulador de recursos está ejecutándose y clasificando las sesiones entrantes. Para obtener más información, vea Usar una conexión de administrador dedicada.

Al consultar lo siguiente, obtiene información sobre la función clasificadora:

• sys.dm_exec_query_stats. (contiene información de la instrucción pero no la función real)

• sys.dm_exec_sql_text (se utiliza junto con sql_handle obtenido de sys.dm_exec_query_stats)

• Clase de eventos PreConnect:Starting (proporciona el identificador y el nombre de la función clasificadora)

En este escenario, un usuario informa de la degradación del rendimiento y se sospecha que el número de solicitudes está limitado.

En primer lugar, hay que comprobar si se ha configurado explícitamente una limitación del número de solicitudes para el grupo en el que se encuentra el usuario. Para ello, se comprueba la pertenencia al grupo del usuario con el fin de averiguar si el valor de GROUP_MAX_REQUESTS está o no habilitado. Si GROUP_MAX_REQUESTS no está habilitado, no hay ninguna limitación explícita en el número de solicitudes. Debería seguir investigando mediante este procedimiento.

• Consulte sys.dm_os_waiting_tasks para comprobar si hay alguna solicitud esperando en el tipo de espera RESMGR_THROTTLED. La presencia de este tipo de espera indica la limitación del número de solicitudes.

• Inicie el Monitor de rendimiento y recopile datos con los contadores Solicitudes en cola y Solicitudes activas. Un valor distinto de cero en Solicitudes en cola indica la limitación de las solicitudes.

• Compruebe si el valor de Solicitudes activas coincide con el valor de GROUP_MAX_REQUESTS. Si el valor de Solicitudes activas es mayor que el de GROUP_MAX_REQUESTS, el grupo puede tener solicitudes que no se puedan limitar (por ejemplo, las transacciones abiertas).

• Si el valor de Solicitudes en cola es cero, compruebe el valor de Solicitudes activas de todos los grupos de cargas de trabajo que comparten el mismo grupo de recursos de servidor porque el grupo se puede haber sobrecargado con demasiadas solicitudes.

Si tiene una directiva que se controla a través de la generación de eventos del regulador de recursos, puede utilizar el evento que se genera cuando se alcanza el límite máximo de la CPU.

En este escenario, desea determinar si el límite de CPU máximo (REQUEST_MAX_CPU_TIME_SEC) que configuró para detectar que una consulta usa demasiada CPU es muy bajo.

Las acciones siguientes le ayudarán a validar el valor de límite de CPU.

• Inicie una sesión de Seguimiento SQL y recopile el evento Umbral de CPU superado. Cuando una solicitud del usuario alcanza el límite de uso de CPU máximo, el servidor genera automáticamente un evento de Seguimiento SQL. Si su valor es demasiado bajo, se generará una gran cantidad de estos eventos.

• Inicie el Monitor de rendimiento y recopile datos con el contador Tiempo máximo de CPU por solicitud (ms). Puede utilizar el valor de este contador como guía para establecer el límite adecuado para el grupo de cargas de trabajo.

En este escenario, sospecha que el ancho de banda de CPU está limitado porque el contador de rendimiento CPU usage % está cercano al valor MAX_CPU_PERCENT del regulador de recursos. La consulta siguiente devuelve el valor del porcentaje de uso de la CPU para todos los grupos de cargas de trabajo y grupos de recursos de servidor de una instancia de SQL Server.

select * from sys.dm_os_performance_counters where counter_name = 'cpu usage %'

Para obtener más información, vea sys.dm_os_performance_counters (Transact-SQL).

Puede determinar si el ancho de banda de la CPU está limitado o no realizando las comprobaciones siguientes en el sistema.

• Compruebe la utilización de la CPU del servidor total. Si una carga distinta de SQL Server está activa actualmente, puede afectar a la consulta cuyos problemas está solucionando.

• Compruebe la distribución del uso de la CPU entre los grupos de recursos de servidor. Un grupo de recursos de servidor podría estar limitado porque otro grupo tenga un valor mínimo alto configurado para el uso de CPU. Compare los contadores de la utilización de CPU esperada (utilización de la CPU calculada) con respecto a la utilización de la CPU real.

• Compruebe los grupos de cargas de trabajo asignados al grupo de recursos de servidor en cuestión. La carga de otros grupos de cargas de trabajo puede afectar a los usuarios que comparten el mismo grupo.

• Compruebe la distribución del uso de la CPU a través de los programadores. La consulta que está investigando podría estar colocada en un programador que contenga consultas de ejecución prolongada. En este caso, la consulta puede parecer que está limitada, pero el problema real es la distribución desigual de la carga entre los programadores.

• Compruebe los posibles casos en los que otras sesiones bloquean la carga de trabajo en lugar de estar limitada por la configuración del regulador de recursos.

• Compruebe el número de sesiones que ejecutan actualmente consultas en el sistema. A medida que el número de solicitudes de ejecución simultáneas crece, SQL Server intenta asegurarse de que todas ellas están recibiendo al menos alguna cantidad de tiempo de CPU para evitar el colapso de la CPU.

En este escenario, sospecha que el tamaño de memoria concedida está haciendo que una consulta se ejecute despacio.

El regulador de recursos aplica el límite máximo de memoria de consulta reduciendo la memoria concedida de forma que las consultas grandes puedan caber dentro del límite. Si una consulta es menor que el 100% de la memoria concedida, puede ser necesario retirar los datos temporales y escribirlos en el disco, lo que puede tener un efecto notable en el rendimiento.

Es necesario determinar el porcentaje de consultas grandes para establecer el límite de tamaño de consulta máximo apropiado. Los procedimientos siguientes ayudarán a determinar los mejores valores:

• Consulte sys.dm_exec_query_memory_grants para comprobar el estado actual de las concesiones de memoria. La columna ideal_memory_kb muestra la cantidad ideal según la estimación de la cardinalidad. La columna requested_memory_kb muestra la cantidad pedida que se podría haber reducido después de alcanzar el límite de consulta máximo. Si requested_memory_kb es significativamente menor que ideal_memory_kb, la consulta puede terminar rebosando con frecuencia (suponiendo que la estimación de la cardinalidad sea correcta).

• Inicie el Monitor de rendimiento y recopile datos con el contador Concesiones con memoria reducida/s. El valor de este contador representa la tasa del recuento de la concesión de memoria que recibió menos de la cantidad ideal después de llegar al límite máximo de tamaño de la solicitud. Las consultas grandes pueden ejecutarse mucho más lentamente que aquellas con una cantidad ideal porque necesitan retirarse al disco para mantenerse dentro de los límites de memoria.

Para aliviar un problema de concesión de memoria, puede que haya que aumentar el límite de tamaño del grupo o el límite del tamaño máximo de la memoria.

En este escenario, se produce un error en una consulta con un error de tiempo de espera de concesión de memoria.

Tanto el número total de solicitudes de concesión de memoria activas como los límites de memoria especificados en el grupo de recursos de servidor y en el grupo de cargas de trabajo pueden representar un rol en los tiempos de espera de concesión de memoria. Si varios grupos de recursos comparten un único grupo de recursos de servidor, el número de consultas simultáneas en otros grupos también puede afectar a los tiempos de espera de concesión de memoria.

Las acciones siguientes ayudarán a determinar la mejor configuración de los grupos de recursos de servidor:

• Consulte sys.dm_exec_query_memory_grants para ver el número de concesiones de memoria y las consultas de espera de este grupo.

• Consulte sys.dm_exec_query_resource_semaphores para comprobar la memoria concedida total y el destino.

Si la utilización de memoria concedida es mayor que el espacio de memoria disponible, puede considerar aumentar el límite de tamaño del grupo de recursos de servidor.

Se produce un error de memoria insuficiente en una consulta.

Métodos básicos de solución de problemas

Solución avanzada de problemas

En este escenario, sospecha que una consulta se está ejecutando despacio debido a un plan de consulta poco óptimo. El optimizador de consultas puede generar un plan de consulta poco óptimo si no tiene bastante memoria debido a un valor de límite de memoria baja para un grupo de recursos de servidor.

Las acciones siguientes ayudarán a determinar la mejor configuración de los grupos de recursos de servidor:

• Obtenga los datos del contador Optimizaciones de consultas/s para comprobar si el grupo de cargas de trabajo tiene un número alto o no de compilaciones de consultas.

• Obtenga los datos del contador Planes poco óptimos/s para comprobar si el optimizador de consultas genera con frecuencia planes poco óptimos.

Si se da alguna de las condiciones anteriores, considere aumentar el límite de memoria del grupo de recursos de servidor.