Por: Ahmad Yaseen / Actualizado: 2018-03-30 / Comentarios (3) | Relacionado: Más > Optimización de consultas
Seminario web gratuito de MSSQLTips: Indexación para el rendimiento: Estrategias de consulta para maximizar los índices de SQL Server
Pocas cosas influyen tanto en la velocidad y el rendimiento de las sentencias SQL dentro de SQL Server como en los índices. Esta sesión le ayudará a comprender los aspectos básicos del rendimiento de las consultas en relación con los índices de su base de datos de producción. Le enseñaremos a determinar si está utilizando buenas estrategias de indexación y el impacto directo que su estrategia de indexación tiene en las consultas.
Problema
Sé que SQL Server puede ejecutar consultas en paralelo, pero ¿cómo puedo saber que una consulta utiliza un plan de ejecución en paralelo y cómo puedo obtener información sobre el número de CPU utilizadas cuando una consulta se ejecuta en paralelo? Echa un vistazo a este consejo para saber cómo hacerlo.
Solución
El concepto de paralelismo proviene de dividir una tarea grande en tareas más pequeñas, donde cada tarea pequeña se asigna a una persona específica, o procesador en el caso de una consulta SQLServer, para realizar parte de la tarea principal. Por último, los resultados parciales de cada pequeña tarea se combinarán en un único resultado final. Puede imaginar cuánto más rápido se puede hacer esto y la ganancia de rendimiento que puede obtener al ejecutar varias tareas en serie al mismo tiempo en paralelo, en comparación con ejecutar una gran tarea en serie.
Antes de pasar por la punta, es beneficioso comprender una serie de términos técnicos relacionados con el concepto de paralelismo en SQL Server:
- El Programador es el procesador físico o lógico responsable de programar la ejecución de los subprocesos de SQL Server.
- El Worker es el hilo que está vinculado a un programador para formar una tarea específica.
- El Grado de paralelismo es el número de trabajadores, o el número de procesadores, que se asignan al plan paralelo para realizar la tarea del trabajador.
- El Grado máximo de Paralelismo (MAXDOP) es una opción de nivel de servidor,base de datos o consulta que se utiliza para limitar el número de procesadoresque el plan paralelo puede usar. El valor predeterminado de MAXDOP es 0, en el que el motor del servidor SQL puede usar todos los procesadores disponibles, hasta 64, en la ejecución paralela de consultas. Establecer la opción MAXDOP en 1 evitará el uso de más de un procesadoren la ejecución de la consulta, lo que significa que el motor de SQL Server utilizará un plan serial para ejecutar la consulta. La opción MAXDOP puede tomar un valor de hasta 32767, donde el motor SQL Server utilizará todos los procesadores de servidor disponibles en la ejecución del plan paralelo si el valor MAXDOP excede el número de procesadores disponibles en el servidor. Si el servidor SQL está instalado en un solo servidor de procesos, se ignorará el valor de MAXDOP.
- La tarea es una pequeña pieza de trabajo que se asigna a un trabajador específico.
- El Contexto de ejecución es el límite en el que se ejecuta cada tarea individual.
- El Proveedor de páginas paralelas es una parte del motor de almacenamiento de servidores SQL que distribuye los conjuntos de datos solicitados por la consulta dentro de los trabajadores participantes.
- El Exchange es el componente que conectará los diferentes contextos de ejecución involucrados en el plan paralelo de la consulta, para obtener el resultado final.
La decisión de utilizar o no un plan paralelo para ejecutar la consulta depende de múltiples factores. Por ejemplo, SQL Server debe instalarse en un servidor multiprocesador, el número de subprocesos solicitado debe estar disponible para ser satisfecho, la opción de grado máximo de paralelismo no está establecida en 1 y el costo de la consulta excede el Umbral de Costo configurado previamente para Parallelismvalue.
El objetivo de este consejo es recuperar la información de los hilos de consulta paralelos del plan de ejecución real de la consulta. Para forzar al Motor de SQL Server a ejecutar la consulta enviada usando un plan paralelo, estableceremos el Umbral de Costo para el valor de Paralelismo en 0, para asegurarnos de que en todos los casos el costo de la consulta supere el Umbral de Costo para el valor de Paralelismo y mantendremos la opción de Grado Máximo de Paralelismo con su valor predeterminado 0, para permitir que el motor de SQL Server use todos los procesadores disponibles mientras ejecuta nuestra consulta, que son 4 procesadores en mi máquina utilizados para esta demostración.
El umbral de coste para el valor de paralelismo se puede establecer mediante SQL Server ManagementStudio, conectándose a la instancia de SQL Server, haciendo clic con el botón derecho en el nombre de la instancia y eligiendo la opción Propiedades. Desde la página Avanzada de la ventana Propiedades del servidor, desplácese hacia abajo hasta la sección Paralelismo, desde donde puede anular el valor predeterminado de la opción Umbral de coste para Paralelismo, que es 5, o el Grado máximo de Paralelismo, si es necesario, como se muestra en la captura de pantalla siguiente:
También puede anular el valor predeterminado del Umbral de Coste para Paralelismo Utilizando sp_configure. Para poder cambiar el Umbral de Costo para el valor de Paralelismo,debe usar sp_configure para habilitar primero la Opción Mostrar Avanzado, como se muestra en el script a continuación:
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;GOEXEC sp_configure 'cost threshold for parallelism', 0;RECONFIGURE;GOEXEC sp_configure 'show advanced options', 0;RECONFIGURE;
Demostración del Plan de Ejecución en paralelo
El servidor ahora está configurado para la demostración. Vamos a crear una nueva tabla usando la instrucción Crear TABLA T-SQL como se muestra a continuación:
USE MSSQLTipsDemoGOCREATE TABLE ParallelDemo( ID INT IDENTITY (1,1) PRIMARY KEY, FirstName NVARCHAR (200), LastName NVARCHAR (200), PhoneNumber VARCHAR(50), BirthDate DATETIME, Address NVARCHAR(MAX) )
Una vez creada la tabla, la rellenaremos con registros de 100K utilizando las instrucciones Insert INTO a continuación:
INSERT INTO ParallelDemo VALUES ('John','Horold','+96255889955','1987-01-08','Jordan - Amman - Mecca Street - Building 77')GO 50000 INSERT INTO ParallelDemo VALUES ('Michel','Anglo','+96255776655','1985-06-02','Jordan - Amman - Mecca Street - Building 74')GO 50000
Ahora queremos habilitar el «Incluir Plan de Ejecución Real» y luego ejecutar la siguiente consulta de SELECCIÓN:
SELECT , , , , , FROM .. WHERE Address LIKE '%AMM%' ORDER BY BirthDate desc
Después de que se complete la ejecución, podemos verificar el plan de ejecución generado a partir de ejecutar la consulta, verá que el motor de SQL Server decide usar un plan paralelo para ejecutar la consulta, debido al hecho de que el costo de esa consulta excede el umbral de costo para el valor de paralelismo.
Podemos ver a continuación que el plan de ejecución es un plan paralelo, debido al Operador de Paralelismo y también a los círculos amarillos con dos flechas debajo de cada operador que se ejecutan con paralelismo.
El plan de ejecución no es solo un gráfico, contiene información valiosa que describe el proceso de ejecución de la consulta enviada. Para ver esta información,haga clic con el botón derecho en el nodo SELECCIONAR en el plan de ejecución y elija la opción Propiedades. En la ventana Propiedades, puede ver el número de procesadores que se utilizan para procesar la consulta enviada desde el atributo Grado de paralelismo, en la sección Miscelánea como se muestra a continuación:
En esta misma ventana, la información sobre los hilos de consulta paralelos también se muestra en la sección ThreadStat. En esa sección, puede comprobar el atributo ramas de consulta paralelas, que muestra el número de rutas de ejecución concurrentes dentro del plan de ejecución de la consulta, el ID de nodo NUMA, el número de subprocesos paralelos reservados para el código numérico especificado bajo el atributo ReservedThreads y el número de subprocesos utilizados durante la ejecución de la consulta, bajo el atributo UsedThreads.
Desde la ventana de propiedades, puede deducir que el motor de SQL Server utilizó 4 procesadores para ejecutar la consulta. Además, el plan de ejecución de consultas tiene una ruta de ejecución, en la que se reservan 4 subprocesos y se utilizan para ejecutar la consulta enviada utilizando un plan paralelo.
La misma información también se puede derivar del plan de ejecución XML, mirando en la sección ThreadStat, como se muestra a continuación:
También puede profundizar para comprobar la E/S y los recursos de CPU consumidos por cada hilo. Haga clic con el botón derecho en el nodo de Análisis de índice agrupado en el plan de ejecución y elija Propiedades. Desde la ventana Propiedades mostrada, puede ampliar el nodo de estadísticas de E / S real para verificar el número de operaciones de lectura lógicas y físicas realizadas por cada subproceso mientras ejecuta la consulta. Además, también puede verificar el número de filas que se recuperan de cada subproceso, el tiempo de CPU consumido por cada subproceso y, finalmente, el tiempo de ejecución que transcurre por cada subproceso para realizar su tarea, como se muestra a continuación:
Los resultados coinciden con la ventana SELECCIONAR propiedades de nodo, donde hay 4 subprocesos de trabajo que participaron en la ejecución de las tareas de consulta y un subproceso de coordinador que coordina entre los subprocesos de trabajo.
Tenga en cuenta que la información anterior solo se puede recuperar del Plan de ejecución Real que se genera después de ejecutar la consulta. Si intenta buscar esa información del Plan de Ejecución Estimado, sin ejecutar la consulta, no se mostrará ninguna información, ya que la consulta debe ejecutarse para especificar el número de procesadoresque se asignarán a la consulta de los procesadores disponibles durante la ejecución de la consulta, como se muestra a continuación para el Plan de Ejecución Estimado.
Obtener más Información de paralelismo de SQL Server
Mientras se ejecuta la consulta, simplemente puede ejecutar el comando sp_who2 con el ID de consulta para obtener información sobre los subprocesos que se utilizan para ejecutar la consulta enviada durante la ejecución de la consulta, como se muestra a continuación:
En el plan de ejecución, cada operador de ese plan tenía un número asignado y un programador que se utiliza para ejecutarlo. Esta información se puede recuperar utilizando el sistema.dm_os_tasks vista de catálogo del sistema y uniéndola con sys. dm_os_workers y sys. dm_exec_query_profiles vistas de catálogo del sistema, como en el script T-SQL a continuación:
SELECT OSTSK.scheduler_id, qp.node_id, qp.physical_operator_nameFROM sys.dm_os_tasks OSTSKLEFT JOIN sys.dm_os_workers OSWRK on OSTSK.worker_address=OSWRK.worker_addressLEFT JOIN sys.dm_exec_query_profiles qp on OSWRK.task_address=qp.task_addressWHERE OSTSK.session_id=58ORDER BY scheduler_id, node_id;
Ejecutando el script anterior mientras se ejecuta la instrucción SELECT con el Plan ActualExecution habilitado, el resultado nos mostrará que los tres operadores del plan: Sort,Filter y Clustered Index Escanean con el ID de cada operador que se muestra al lado. Esto se ejecuta cuatro veces, utilizando los cuatro programadores. El operador de paralelismo con ID igual a 0 será ejecutado una vez por el cuarto programador para conectar todos los contextos de ejecución en el último paso, como se muestra a continuación:
A continuación, he agregado los valores de nodo que corresponden con los resultados de la consulta anteriores con el plan de ejecución para ayudar a ilustrar mejor.
Pasos siguientes
- Consulte los siguientes recursos:
- Especificar el grado máximo de Paralelismo en SQL Server para una consulta.
- Qué configuración de MAXDOP se debe usar para SQL Server.
- Cómo forzar un Plan de Ejecución en paralelo en SQL Server 2016.
- SQL Server 2016 DBCC CHECKDB con MAXDOP
Última Actualización: 2018-03-30
Sobre el autor
Ahmad Yaseen es un DBA de SQL Server con una licenciatura en ingeniería informática y experiencia en desarrollo de. NET.Ver todos mis consejos
Recursos relacionados
- Más Consejos para desarrolladores de bases de datos…
