Opciones de configuración avanzada de paralelismo
Introducción
Hoy continuaré
escribiendo acerca de Microsoft SQL Server Opciones de Configuración, que iniciamos en el
mes de diciembre, después de algunos días de ausencia, hoy escribiré acerca de
las opciones de configuración avanzada de paralelismo, que como sabemos son
opciones relacionadas con el procesamiento de las consultas. Debemos primero
saber que es el paralelismo en Microsoft SQL Server. Para ello se debe especificar que el
paralelismo es una característica de Microsoft SQL Server, la cual permite que las
consultas costosas utilicen más hilos para completar el proceso de forma más
rápida. Para lograr esto, el optimizador de consultas primero determina el
costo de una consulta en función de la configuración del umbral de costo para
el paralelismo (Cost Threshold for Parallelism) establecida a nivel de
Instancia de Microsoft SQL Server. Este umbral de costo para el paralelismo es realmente una
estimación (definida en segundos) que el optimizador de consultas ha
determinado tomará una declaración en función de un plan de ejecución estimado
(o en caché). Generalmente, las consultas que califican para el paralelismo son
consultas con alto uso de IO. En un proceso normal, una fuente de datos se lee
utilizando un único SPID (ID de proceso del servidor) y se envía usando el
mismo SPID. Cuando se usa paralelismo, se utilizan varios SPID para leer una
fuente de datos (conocido como distribución de flujos), luego se puede realizar
una operación en las secuencias y luego se recopilan las secuencias. La
siguiente figura ilustra este proceso, tomado de un artículo de Derek Dieter
denominado “What is
Parallelism?”:
No siempre
el paralelismo puede ser una buena idea a ser utilizada. En muchos sistemas OLTP que se utilizan para facilitar
muchas solicitudes de usuario, generalmente el paralelismo suele ser una
indicación de consultas y / o consultas mal escritas que requieren la definición
adecuada de índices. A menudo, el paralelismo puede causar problemas en los
servidores que tienen muchos procesadores que también tienen problemas de IO en
disco. El motivo es que el paralelismo generará tantos SPID como procesadores.
Para asegurarse
que el paralelismo está ocurriendo en el sistema se ejecuta sp_who2 y ve el
mismo SPID en la lista más de una vez. Esto indica que una consulta grande se divide en varias
secuencias solo para volver a unirse en un único resultado más adelante.
Algunas secuencias de la consulta pueden finalizar sus operaciones antes de que
se completen otras secuencias de la consulta lo que da como resultado un tipo
de espera identificada como CX_Packet. Cuando este tipo de espera es común,
entonces está esperando que finalicen las transmisiones paralelas.
Una vez que
hemos definido que es el paralelismo y como lo usa Microsoft SQL Server, procederemos a
indicar cuales son las opciones de configuración avanzadas de paralelismo, de
acuerdo con Microsoft SQL Server estas son:
Nombre de opción
|
Descripción
|
cost threshold for parallelism
|
umbral de costo para el
paralelismo
|
locks
|
Número de bloqueos para todos los
usuarios
|
max degree of parallelism
|
grado máximo de paralelismo
|
query wait (s)
|
tiempo máximo para esperar
memoria de consulta (s)
|
Como ya
hemos indicado, para las opciones de configuración en Microsoft SQL Server que se
identifican como avanzadas, debe utilizarse el procedimiento almacenado de
sistema sp_configure, el cual sirve
para habilitar la posibilidad de despliegue de estas opciones avanzadas.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE
GO
Opción cost threshold for parallelism
Establezca el valor de la opción de umbral de costo para paralelismo con
el fin de especificar el umbral en el que Microsoft SQL Server crea y ejecuta planes
paralelos para las consultas. Recordemos que Microsoft SQL Server crea y ejecuta un plan
paralelo para una consulta solo en el caso de que el costo estimado para
ejecutar un plan en serie para la misma consulta sea mayor que el valor
establecido en el umbral de costo para el paralelismo. El costo en este caso se
refiere a un tiempo estimado en segundos que se requiere para ejecutar el plan
de serie en una configuración de hardware específica. Se recomienda que se establezca
el valor de umbral de costo para el paralelismo en multiprocesadores simétricos
únicamente.
La opción de umbral de costo para paralelismo se puede establecer en
cualquier valor entre 0 y 32767. El valor predeterminado, cuando se instala la
instancia de Microsoft SQL Server es 5. Es conveniente modificar el valor cuando se
tienen procesadores con varios núcleos, de tal forma que pueda llevarse a cabo
el multiprocesamiento simétrico.
Microsoft SQL Server ignora el valor del umbral de costo para paralelismo en las
siguientes condiciones:
- Su computadora tiene solo un procesador.
- Solo una única CPU está disponible para Microsoft SQL Server debido a la opción de configuración de máscara de afinidad.
- La opción grado máximo de paralelismo se establece en 1.
Para
cambiar el valor de esta opción de configuración usando el procedimiento
almacenado se ejecuta el siguiente comando:
sp_configure 'cost threshold for paralellism', 20
GO
RECONFIGURE
Opción locks
Cuando una
instancia de Microsoft SQL Server es instalada se inicia con un valor configurado para bloqueos
en 0, de tal forma que el administrador de bloqueos adquiere suficiente memoria
del motor de base de datos para un grupo inicial de 2,500 estructuras de
bloqueo. A medida que se use y se agota el grupo de bloqueos, se adquiere memoria
adicional para el grupo de estructuras. Generalmente, si es requerida más
memoria para el grupo de bloqueo que la está disponible en el conjunto de
memoria del Motor de base de datos, y en el caso de que haya más memoria
disponible (el umbral máximo de memoria del servidor no se ha alcanzado), entonces
el motor de base de datos asigna memoria dinámicamente para satisfacer la
solicitud de bloqueos. Sin embargo, si la asignación de esa memoria causa paginación
en el nivel de sistema operativo, no se asignará más espacio de bloqueo. El
grupo de bloqueo dinámico no adquiere más del 60 por ciento de la memoria
asignada al Motor de base de datos. Una vez que el grupo de bloqueo ha
alcanzado el 60% de la memoria adquirida por una instancia del Motor de base de
datos, o no hay más memoria disponible en la computadora, las solicitudes
adicionales de bloqueos generan un error.
Permitir
que Microsoft SQL Server use bloqueos dinámicamente es la configuración recomendada. No
obstante, puede establecerse un valor para bloqueos y anular la capacidad de Microsoft SQL Server para asignar dinámicamente los recursos de bloqueo. Cuando el valor
de los bloqueos se configura en un valor distinto de 0, el Motor de base de
datos no puede asignar más bloqueos que el valor especificado. Será necesario aumentar
este valor si Microsoft SQL Server muestra un mensaje indicando que se ha excedido la
cantidad de bloqueos disponibles. Debido a que cada bloqueo consume memoria (96
bytes por bloqueo), el aumentar este valor puede requerir aumentar la cantidad
de memoria dedicada al servidor.
Nota:
Microsoft ha indicado que ésta característica
se eliminará en una versión futura de Microsoft SQL Server, por lo que se
recomienda no usarla.
El siguiente
ejemplo muestra cómo establecer el valor de locks en 20,000
sp_configure 'locks', 20000
GO
RECONFIGURE
GO
Opción max degree of paralellism
Cuando Microsoft SQL
Server se ejecuta en un equipo con más de un microprocesador o CPU, se detecta
el mejor grado de paralelismo, es decir, el número de procesadores empleados
para ejecutar una declaración única, para cada ejecución de plan paralelo. Es
posible usar la opción de máximo grado de paralelismo para limitar la cantidad
de procesadores que se utilizarán en la ejecución de planes paralelos.
Si se desea
permitir que el Microsoft SQL Server determine el grado máximo de paralelismo, se debe establecer
esta opción en 0, el valor predeterminado. Establecer el valor del grado máximo
de paralelismo en 0 permite que Microsoft SQL Server utilice todos los procesadores
disponibles hasta un máximo de 64 procesadores. Si se desea suprimir la
generación de planes paralelos, será necesario establecer el valor de máximo
grado de paralelismo en 1.
Establecer
el valor en un número mayor que 1 restringe el número máximo de procesadores que
serán utilizados por una única ejecución de consulta. El valor máximo para la
configuración de grado de paralelismo está controlado por la edición de Microsoft SQL
Server, tipo de CPU y sistema operativo. Si se especifica un valor mayor que la
cantidad de procesadores disponibles, se usa la cantidad real de procesadores
disponibles. Si el equipo tiene solo un procesador, se ignora el grado máximo
de paralelismo.
El
siguiente ejemplo permite establecer el valor del máximo grado de paralelismo
en 8
sp_configure 'max degree of paralellism', 8
GO
RECONFIGURE
WITH OVERRIDE
GO
Opción query wait
La opción de
configuración de espera de consulta (query wait) indica el tiempo, en segundos
(de 0 a 2147483647), que una consulta debe espera por los recursos antes de que
se agote el tiempo de espera. El valor predeterminado para esta opción es -1, esto
significa que el tiempo de espera se calcula como 25 veces en relación al costo
estimado de la consulta. Es importante mencionar que una transacción que
contiene una consulta de espera puede contener bloqueos mientras la consulta
espera por memoria. Disminuir el tiempo de espera de una consulta reduce la
probabilidad de los interbloqueos. Eventualmente, se terminará una consulta en
espera y se liberarán los bloqueos de la transacción. Sin embargo, aumentar el
tiempo máximo de espera puede aumentar la cantidad de tiempo para que finalice
la consulta.
Nota:
Microsoft recomienda que no se lleven a cabo cambios
en al valor de esta opción de configuración.
El
siguiente ejemplo permite establecer el valor de espera de consulta en 7500
segundos.
sp_configure 'query wait(s)', 7500
GO
RECONFIGURE
WITH OVERRIDE
GO
Comentarios
Se han
visto cada una de las opciones de configuración avanzada de paralelismo, estas
mismas opciones aparecen en la pestaña de avanzadas de la ventana de
propiedades del servidor, como se puede observar en la siguiente imagen:
Si bien, es
posible realizar el cambio en esta ventana, ya que los valores se muestran y
están habilitados, es preferible llevar a cabo la modificación de estos valores
con el procedimiento almacenado de sistema, de esta forma se puede mantener un
control apropiado sobre los valores indicados en la opción de configuración.