El paralelismo es una característica poderosa en SQL Server que permite que las consultas utilicen múltiples hilos, lo que resulta en una ejecución más rápida de las consultas. En este artículo, exploraremos el concepto de paralelismo y discutiremos cómo optimizar su uso en su entorno de SQL Server.

¿Qué es el paralelismo?

El paralelismo es una característica en SQL Server que permite que las consultas se procesen simultáneamente por múltiples hilos. Al dividir la carga de trabajo entre múltiples hilos, el paralelismo puede mejorar significativamente el rendimiento de las consultas y reducir el tiempo de ejecución.

Influencia en el paralelismo

Existen varios factores que influyen en la decisión de SQL Server de utilizar el paralelismo y determinar el grado de paralelismo:

• Umbral de costo para el paralelismo: Esta configuración a nivel de servidor especifica el costo mínimo de la consulta requerido para que el optimizador considere el paralelismo. Las consultas con un costo por debajo de este umbral no utilizarán el paralelismo.
• Grado máximo de paralelismo: Esta configuración a nivel de servidor define el número máximo de procesadores que una consulta paralela puede utilizar. Limita el grado de paralelismo para todas las consultas.
• OPTION (MAXDOP X): Esta sugerencia de consulta le permite anular las dos configuraciones anteriores especificando el grado máximo de paralelismo para una consulta específica. Se puede utilizar para reducir el grado de paralelismo para consultas con un costo por encima del umbral.

Es importante tener en cuenta que la configuración óptima para el paralelismo puede variar según la carga de trabajo de su servidor, el número de núcleos y la configuración NUMA. No hay una solución única para todos, y se recomienda comprender el impacto del paralelismo en su entorno específico.

Uso de DMVs para optimizar el paralelismo

SQL Server proporciona vistas de administración dinámica (DMVs) que ofrecen información valiosa para tomar decisiones informadas sobre el paralelismo. Un enfoque útil es utilizar las vistas sys.dm_exec_cached_plans y sys.dm_exec_query_plan para recuperar información de la caché de planes para consultas paralelas.

Agrupando la información expuesta por StatementSubTreeCost, puede analizar la distribución de los costos de las consultas y determinar si el valor predeterminado del umbral de costo para el paralelismo de 5 es adecuado para su entorno.

Aquí hay un ejemplo de código que muestra cómo recopilar y analizar información de planes paralelos:

/* -----------------------------------------------------------------
Umbral de costo para el paralelismo
----------------------------------------------------------------- */
-- Establecer contexto de base de datos
USE master;

-- Crear tabla
IF NOT EXISTS (
    SELECT 1
    FROM sys.objects
    WHERE [object_id] = OBJECT_ID('dbo.PlanCacheForMaxDop')
        AND [type] = 'U'
)
CREATE TABLE master.dbo.PlanCacheForMaxDop (
    CompleteQueryPlan XML,
    StatementText VARCHAR(4000),
    StatementOptimizationLevel VARCHAR(25),
    StatementSubTreeCost FLOAT,
    ParallelSubTreeXML XML,
    UseCounts INT,
    PlanSizeInBytes INT
);
ELSE
-- Si la tabla existe, truncarla antes de poblarla
TRUNCATE TABLE master.dbo.PlanCacheForMaxDop;

-- Recopilar información de planes paralelos
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
INSERT INTO master.dbo.PlanCacheForMaxDop
SELECT
    query_plan AS CompleteQueryPlan,
    n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText,
    n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel,
    n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost,
    n.query('.') AS ParallelSubTreeXML,
    ecp.usecounts,
    ecp.size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1;

-- Devolver información de planes paralelos
SELECT
    CompleteQueryPlan,
    StatementText,
    StatementOptimizationLevel,
    StatementSubTreeCost,
    ParallelSubTreeXML,
    UseCounts,
    PlanSizeInBytes
FROM master.dbo.PlanCacheForMaxDop;

-- Devolver información de planes paralelos agrupada
SELECT
    MAX(
        CASE
            WHEN StatementSubTreeCost BETWEEN 1 AND 5 THEN '1-5'
            WHEN StatementSubTreeCost BETWEEN 5 AND 6 THEN '5-6'
            WHEN StatementSubTreeCost BETWEEN 6 AND 7 THEN '6-7'
            -- Agregar más rangos según sea necesario
            ELSE CAST(StatementSubTreeCost AS VARCHAR(100))
        END
    ) AS StatementSubTreeCost,
    COUNT(*) AS countInstance
FROM master.dbo.PlanCacheForMaxDop
GROUP BY
    CASE
        WHEN StatementSubTreeCost BETWEEN 1 AND 5 THEN 2.5
        WHEN StatementSubTreeCost BETWEEN 5 AND 6 THEN 5.5
        WHEN StatementSubTreeCost BETWEEN 6 AND 7 THEN 6.5
        -- Agregar más rangos según sea necesario
        ELSE StatementSubTreeCost
    END;

Al analizar los resultados de la consulta anterior, puede obtener información sobre la distribución de los costos de las consultas y tomar decisiones informadas sobre el ajuste del umbral de costo para el paralelismo.

Conclusión

El paralelismo es una característica poderosa en SQL Server que puede mejorar significativamente el rendimiento de las consultas. Al comprender los factores que influyen en el paralelismo y utilizar las DMVs para analizar los costos de las consultas, puede optimizar la configuración de paralelismo para su entorno específico.

Recuerde, no hay una solución única para todos cuando se trata de paralelismo. Es esencial evaluar el impacto del paralelismo en su carga de trabajo y ajustar el umbral de costo y el grado máximo de paralelismo en consecuencia.

¡Feliz optimización!

Chris