Bit - loader

Resource Governor, una interesante opción de SQL Server


Netmind - Resource Governor, una interesante opción de SQL Server    Artículo | Microsoft SQL Server 2016
Xavier Saladié | 11/07/18

Una interesante opción de SQL Server

Hace algunas versiones que Microsoft introdujo Resource Governor, una interesantísima opción de configuración en SQL Server, pero dado que últimamente me han preguntado en varias ocasiones sobre su funcionamiento, me he decidido a escribir unas líneas al respecto.

 

Resource Governor, ¿qué es?

Para aquell@s que no la conozcáis, Resource Governor (sólo disponible en Ediciones Enterprise), permite asignar límites al uso de determinados recursos. Eso nos permitirá gestionar adecuadamente la coexistencia de conexiones VIP (con alta demanda de CPU, acceso a disco y/o memoria RAM) con conexiones mucho menos exigentes. El objetivo es que podamos asignar a la instancia de base de datos una cantidad de recursos que, en lugar de ser proporcional al número de procesos que se ejecuten en paralelo, lo sea a la criticidad de sus tareas o a cualquier otro criterio que necesitemos aplicar.

El procedimiento, en esencia, pasa por crear uno o varios alias configurados con una horquilla de valores (máximo y mínimo) para recursos tales como Memoria RAM, Procesador e IOPS. Una vez definido(s), crearemos una función que se ejecutará en el instante en que el cliente se esté conectando al servidor. En dicha función, deberemos validar la conexión entrante en base a una serie de criterios de nuestra elección (login, nombre de base de datos de destino, fecha/hora,…) para asignarla a uno de los alias anteriormente creados. De este modo la conexión se encaja en un subconjunto de recursos que es el encargado de determinar el rendimiento de las actividades que se lleven a cabo desde dicha sesión.

 

Ejemplo con Pool de Recursos en SQL Server

Pasemos a crear un ejemplo práctico para verlo en funcionamiento, empezaremos creando un Pool de Recursos (el alias al que hacía referencia anteriormente).

 

USE master;

GO

CREATE RESOURCE POOL LtdPool WITH

(      MAX_IOPS_PER_VOLUME = 10,

       MIN_IOPS_PER_VOLUME = 1);

GO

 

A pesar de que el Pool de recursos puede hacer referencia hasta a 6 valores diferentes (IOPS, CPU y RAM cada uno de ellos con sus valores máximo y mínimo), para simplificar el ejemplo y comprobar el efecto más fácilmente solo he incluido la configuración relacionada con IOPS y la he limitado, de manera deliberada, a un valor extremadamente bajo.

Una vez creado el pool de recursos debemos asignarle (al menos) un Workload Group (grupo de cargas de trabajo).

 

CREATE WORKLOAD GROUP LtdWorkload USING LtdPool;

GO

A continuación, deberemos crear una función de clasificación. La función será la responsable de devolver como resultado el nombre del Workload Group al que queremos asignar la conexión entrante. El criterio de clasificación permite comprobar varios aspectos de la conexión para asignarla al grupo de cargas de trabajo deseado.

 

CREATE FUNCTION dbo.ClassifierFunction() RETURNS SYSNAME WITH SCHEMABINDING AS

BEGIN

IF ORIGINAL_DB_NAME() = 'Maps'

BEGIN

RETURN 'LtdWorkload'

END

RETURN 'default'

END;

GO

La función debe devolver un resultado de tipo sysname que coincida con el nombre de un Workload Group existente. Dado que siempre existe un Workload Group llamado default, la función del script del ejemplo sobre estas líneas tan sólo ubica en el grupo LtdWorkload las conexiones entrantes cuyo contexto predeterminado sea el de la base de datos llamada Maps, en caso contrario la conexión se alojará en el grupo por defecto.

 

Finalmente, solo queda activar Resource Governor e indicarle el nombre de nuestra función de clasificación.

 

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.ClassifierFunction);




ALTER RESOURCE GOVERNOR RECONFIGURE;


Detalle de Resource Governor en el Explorador de Objetos de SSMS.
Detalle de Resource Governor en el Explorador de Objetos de SSMS.

 

El comportamiento de las consultas se puede comprobar fácilmente, tan sólo hay que asegurarse de conectarnos directamente a la base de datos que se valida en la función de clasificación, en este caso Maps. Recordad que, una vez la conexión ha sido establecida, no es posible reubicarla en otro Workload Group, de ahí la importancia de que, en el ejemplo, la conexión sea directamente contra Maps.

 

Nueva conexión a la base de datos Maps en SSMS
Nueva conexión a la base de datos Maps en SSMS

 

Para comprobar el comportamiento restrictivo de los límites que nos hemos impuesto en nuestra conexión probamos la siguiente consulta que recupera todos los datos de la tabla dbo.world. La tabla es de pequeño tamaño ya que solamente ocupa 6400 KB.

El comportamiento de la consulta es el siguiente, en el caso de que la información de la tabla no se encuentre en la caché, el acceso físico al disco estará limitado por las IOPS definidas en el Workload Group asociado a la conexión y, por tanto, el tiempo de respuesta estará condicionado por el límite en la cantidad de operaciones por segundo. Si la información ya estaba en la caché, será devuelta directamente desde allí y, al no necesitar IO desde el disco, la respuesta será mucho más rápida, ya que los límites no tendrán efecto por no implicarse el disco en la operación. Veamos ambas posibilidades a continuación.

 

-- Primera Ejecución que sirve los datos desde disco

SET STATISTICS TIME ON

GO

SELECT * FROM dbo.world



Tiempo de respuesta tras la lectura física (disco)
Tiempo de respuesta tras la lectura física (disco)

 

-- Segunda Ejecución que sirve los datos desde caché

SELECT * FROM dbo.world

GO


Tiempo de respuesta tras la lectura 100% lógica (caché)
Tiempo de respuesta tras la lectura 100% lógica (caché)

 

Como puede apreciarse en los mensajes de la segunda ejecución, a pesar de que las lecturas lógicas son las mismas que en la primera ejecución, las físicas y las read-ahead (que son las que implican al disco) están a cero. El resultado se obtiene en bastante menos de 300 milisegundos. Unas 35 veces más rápido. Parece evidente que los límites de IOPS no se tienen en cuenta cuando se leen los datos desde la caché.

En el caso de que la ejecución la llevemos a cabo desde una conexión alojada en el Workload Group llamado default, el tiempo empleado se mantiene estable alrededor de 250 ms incluso descartando la caché. Ya que en este caso las IOPS no están limitadas en absoluto.

 

Resultados de la ejecución desde el Workload Group llamado default
Resultados de la ejecución desde el Workload Group llamado default

 

En escenarios en los que las necesidades de las conexiones sean claramente distintas y podamos anticiparlas basándonos en alguna característica de la conexión, la utilidad de Resource Governor es inmensa, aunque hay que recordar que, desafortunadamente, solo está disponible en la versión top de SQL Server.

 

Consideraciones en la función de clasificación

Hay que tener en cuenta que la función de clasificación juega un papel especialmente crítico en el rendimiento del servidor y, de hecho, Microsoft recomienda tener en cuentas las siguientes consideraciones:

  • Si la función debe recuperar una determinada información de configuración (por ejemplo para asignar un pool u otro en función de la hora de la conexión) se aconseja que dichos valores estén hard-coded en el código de la función siempre que sea posible. Solo se debe acceder a una tabla de parámetros en el caso de que sea estrictamente necesario (por ejemplo por temas relacionados con la necesidad frecuente de modificar dichas configuraciones).
  • Si a pesar de lo recomendado en el punto anterior, necesitamos crear una tabla de configuración:
    • La tabla debe crearse en la base de datos master, ya que la conexión aún no ha sido completamente establecida y el resto de base de datos no están accesibles.
    • Debe limitarse la IO de la tabla y evitar JOINS a toda costa.
    • Debe incluirse la cláusula WITH NOLOCK para evitar que los bloqueos en la tabla afecten negativamente al tiempo de respuesta.
    • En la sintaxis de la consulta debe incluirse siempre el nombre del esquema antes del nombre de la tabla (aunque la tabla haya sido creada en el esquema dbo).

 

Errores de código

Además de las anteriores recomendaciones, me gustaría agregar una que he sufrido personalmente: asegúrate de que tu función de clasificación no contenga ningún error en el código, ya que, en el caso de que la función no llegue a terminar de ejecutarse por algún motivo, tu conexión será rechazada. Con ello te enfrentarás a un pequeño problema, para deshabilitarla debes conectarte al servidor pero, si hay un error en la función de clasificación y tu conexión es rechazada se acaban tus opciones. Aunque, buenas noticias, hay dos maneras de conectarse a un servidor SQL sin que se ejecute la función de clasificación asociada a Resource Governor.

La primera es utilizar la conexión administrativa dedicada (DAC). Lo más sencillo es hacerlo desde SSMS, para ello solo necesitas prefijar el nombre del servidor con ADMIN: pero recuerda que debes cancelar el cuadro de diálogo que aparece al arrancar SSMS y acceder desde el botón Nueva Consulta en la barra de herramientas, ya que DAC solo admite una conexión por instancia y si te conectas como lo haces regularmente, ya gastas la conexión en el explorador de objetos del panel izquierdo.

 

Resource Governor, una interesante opción de SQL Server 5
Conexión DAC desde SSMS

 

La segunda manera de acceder al servidor es bastante más engorrosa ya que requiere arrancarlo en modo de usuario único. Para ello debemos acceder a los servicios de Windows para detener SQL Server.

 

Resource Governor, una interesante opción de SQL Server 6
Servicios en el panel de control

 

A continuación, desde las propiedades del servicio agregaremos los parámetros de inicio -m -f

 

Resource Governor, una interesante opción de SQL Server 7
Preparando el servicio para arrancar en modo usuario único

 

Después de aceptar la ventana, podemos iniciar el servicio normalmente, aunque, al haber arrancado en modo de usuario único, el servidor solamente aceptará una conexión. De modo que,  para establecerla, deberemos regresar a SSMS, cancelar la ventana de inicio y utilizar el botón Nueva Consulta de la barra de herramientas para conectarnos a nuestro SQL Server.

Llegados a este punto podremos deshabilitar Resource Governor con la instrucción siguiente:

 

ALTER RESOURCE GOVERNOR DISABLE;

Finalmente deberemos reiniciar el servicio desde el panel de control (ya sin los parámetros -m -f) y corregir el error de la función de clasificación para volver a intentarlo.

Como puedes ver, vale la pena asegurarse de que el código de la función de clasificación se comporte adecuadamente ya que, en caso de necesitar corregirlo, podrías encontrarte con la necesidad de detener el servicio.

Si te interesa probarlo para verlo en acción y no tienes la versión Enterprise, puedes hacerlo descargándote la versión Trial directamente desde Microsoft, dispondrás de todas sus funcionalidades durante 180 días.

 

Autor del artículo

Resource Governor, una interesante opción de SQL Server 8

Xavier Saladié es Lead Expert en SQL Server y Desarrollo en .NET en Netmind. Es instructor acreditado por Microsoft desde el año 2005 y de ITIL desde el año 2013. Dispone de las certificaciones ITIL Expert, MCSE Cloud Platform and Infrastructure, MCSE Data Platform, MCSE Business Intelligence, MCPD Windows Developer, MCPD Web Developer, MCITP Sharepoint Administrator, MS Microsoft Project 2016 y MCSA SQL Server. Dispone de más de 20 años de experiencia en formación y ha dirigido múltiples proyectos de consultoría y optimización para arquitecturas de soluciones para varias organizaciones.

 

 


Xavier Saladié


Entradas relacionadas

Cursos relacionados
Nuestro sitio utiliza cookies para análisis. Si no estás seguro de ello, echa un vistazo a nuestra política de privacidad.