MAXDOP = 1, sugerencias de consulta y umbral de costo para el paralelismo

Te damos la bienvenida a nuestra página web, en este sitio vas a encontrar la solucíon de lo que andabas buscando.

Solución:

Si una instancia tiene MAXDOP establecido en 1 y las sugerencias de consulta se utilizan para permitir que las consultas específicas vayan en paralelo, ¿el valor de Umbral de costo para el paralelismo sigue siendo utilizado por SQL para decidir si realmente va en paralelo o no?

Respuesta sencilla: sí.

Detalles

Hay un par de cosas separadas que suceden aquí, que es importante separar:

1. Cuál es el eficaz máximo grado de paralelismo disponible a una consulta?

   Los contribuyentes a esto son (en general en orden de importancia):

   • Gobernador de recursos MAX_DOP ajuste
   • Sugerencia de consulta MAXDOP ajuste
   • los max degree of parallelism opción de configuración de la instancia

   Los detalles se explican en la configuración del “grado máximo de paralelismo” del servidor, el MAX_DOP del gobernador de recursos y la sugerencia de consulta MAXDOP: ¿cuál debe usar SQL Server? por Jack Li, ingeniero sénior de escalamiento para el servicio y soporte al cliente de Microsoft SQL Server. La siguiente tabla se reproduce desde ese enlace:

   

2. ¿Usará un plan de consulta paralelismo?

   El optimizador de consultas de SQL Server siempre encuentra un plan de serie primero*.

   Entonces sí:

   • Se justifica una mayor optimización; y
   • El costo del mejor plan serial excede el cost threshold for parallelism valor de configuración

   …el optimizador tratar para encontrar un plan paralelo.

   Entonces sí:

   • Se encuentra un plan paralelo (es decir, es posible); y
   • El costo del plan paralelo es menor que el mejor plan serial

   …se producirá un plan paralelo.

Nota: los cost threshold for parallelism solo afecta si el optimizador busca un plan paralelo. Una vez que se almacena en caché un plan paralelo, se ejecutará utilizando el paralelismo cuando se reutilice (siempre que haya subprocesos disponibles) independientemente de la configuración de CTFP.

---

Ejemplos

Para ambos ejemplos, con la instancia maxdop 1 y la sugerencia de consulta maxdop 2, el DOP efectivo disponible es 2. Si se elige un plan paralelo, utilizará DOP 2.

Ejemplo 1

Dado un CTFP de 50 y un plan de serie más barato encontrado costo de 30, SQL Server no tratar de encontrar un plan paralelo. Se producirá un plan en serie.

Ejemplo 2

Dado un CTFP de 50 y un plan de serie más barato encontrado costo de 70, SQL Server tratar para encontrar un plan paralelo. Si este plan (si se encuentra) tiene un costo inferior a 70 (el costo del plan en serie), se producirá un plan paralelo.

---

_{El resultado final de la optimización de consultas es siempre un único plan en caché: serie o paralela. El optimizador encuentra solo un plan serial en buscar0 (TP) y buscar1 (QP) fases.}

_{Eso mayo luego (como se describe) vuelva a ejecutar buscar1 con el requisito de producir un plan paralelo. Luego se elige entre serie y paralelo en función del mejor costo total del plan hasta el momento. Esa elección es vinculante en caso de que la optimización pase a buscar2 (Optimización completa). Cada fase de optimización considera muchas alternativas, pero el resultado de una etapa es siempre un mejor plan único, que es en serie o en paralelo.}

_{Escribí sobre algo de esto en Mito: SQL Server almacena en caché un plan en serie con cada plan paralelo}

Ejemplo 1 Instancia Maxdop: 1 CTFP: 50 Sugerencia de consulta: Maxdop=2 Costo de consulta: 30

La sugerencia de consulta MAXDOP anula el grado máximo de configuración de paralelismo en toda la instancia, pero dado que CTPF es 50 y el costo de la consulta es 30, puede optar por el plan en serie.

Ejemplo 2 Instancia Maxdop: 1 CTFP: 50 Sugerencia de consulta: Maxdop=2 Costo de consulta: 70

Aquí nuevamente, el grado máximo de paralelismo se tomará como 2 ya que la sugerencia MAXDOP está ahí, pero CTFP se tomará como 50 y la consulta, si es posible, como Paul mencionó, puede ejecutarse en paralelo.

Si una instancia tiene MAXDOP establecido en 1 y las sugerencias de consulta se usan para permitir que consultas específicas vayan en paralelo, ¿SQL todavía usa el valor Umbral de costo para el paralelismo para decidir si realmente va en paralelo o no?

La sugerencia de MAXDOP anulará la configuración amplia de la instancia del grado máximo de paralelismo.

Citando de la sugerencia MAXDOP docs.microsoft

Número MAXDOP Se aplica a: SQL Server 2008 a SQL Server 2017.

Anula la opción de configuración de grado máximo de paralelismo de sp_configure y Resource Governor para la consulta que especifica esta opción. La sugerencia de consulta MAXDOP puede exceder el valor configurado con sp_configure. Si MAXDOP supera el valor configurado con el regulador de recursos, el motor de base de datos usa el valor MAXDOP del regulador de recursos,

Más adelante puedes encontrar las anotaciones de otros sys admins, tú todavía puedes insertar el tuyo si lo deseas.

---

---

Utiliza Nuestro Buscador