best - ¿Cuál es el propósito de usar OPTION(MAXDOP 1) en SQL Server?
option sql server (5)
Nunca entendí claramente el uso de MAXDOP. Sé que hace que la consulta sea más rápida y que es el último elemento que puedo usar para la Optimización de consultas.
Sin embargo, mi pregunta es cuándo y dónde es más adecuado usar en una consulta.
Este es un truco general sobre Paralelismo en SQL Server, es posible que no conteste su pregunta directamente.
De Books Online, en MAXDOP:
Establece la cantidad máxima de procesadores que el procesador de consultas puede usar para ejecutar una sola instrucción de índice. Se pueden usar menos procesadores dependiendo de la carga de trabajo del sistema actual.
Vea el blog de Rickie Lee sobre el paralelismo y el tipo de espera de CXPACKET. Es bastante interesante.
En general, en una base de datos OLTP, mi opinión es que si una consulta es tan costosa que debe ejecutarse en varios procesadores, la consulta debe volverse a escribir en algo más eficiente.
¿Por qué obtienes mejores resultados al agregar MAXDOP (1)? Difícil de decir sin los planes de ejecución reales, pero podría ser tan simple como que el plan de ejecución sea totalmente diferente que sin la OPCIÓN, por ejemplo, usar un índice diferente (o más probable) UNIRSE de manera diferente, utilizando uniones MERGE o HASH.
Como algo aparte, MAXDOP aparentemente puede usarse como una solución a un error potencialmente desagradable:
Hay un par de errores de parallización en el servidor SQL con una entrada anormal. OPTION (MAXDOP 1) los esquivará.
EDITAR: viejo. Mis pruebas se realizaron en gran medida en SQL 2005. La mayoría parece no existir más, pero de vez en cuando cuestionamos la suposición cuando SQL 2014 hace algo tonto y volvemos a la vieja usanza y funciona. Nunca logramos demostrar que no solo se trataba de una mala generación de planes en casos más recientes, ya que se puede confiar en SQL Server para que funcione correctamente en las versiones más nuevas. Como todos los casos fueron consultas obligatorias de IO, MAXDOP 1 no duele.
Agregando mis dos centavos, basado en un problema de rendimiento que observé.
Si las consultas simples se clasifican innecesariamente, puede traer más problemas que resolver uno. Sin embargo, antes de agregar MAXDOP a la consulta como solución "instintiva", hay algunas configuraciones de servidor para verificar.
En Jeremiah Peschka: cinco configuraciones de SQL Server para cambiar , MAXDOP y "UMBRAL DE COSTOS PARA EL PARALELISMO" ( CTFP ) se mencionan como configuraciones importantes para verificar.
Nota: Paul White mencionó la max server memory como una configuración para verificar, en respuesta al problema de rendimiento después de la migración de SQL Server 2005 a 2012 . Un buen artículo kb para leer es Usar grandes cantidades de memoria puede resultar en un plan ineficiente en SQL Server
Jonathan Kehayias: Ajustar el ''umbral de costo para el paralelismo'' de Plan Cache ayuda a encontrar un buen valor para CTFP .
¿Por qué se ignora el umbral de costo para el paralelismo?
Aaron Bertrand - Seis razones por las que debería estar nervioso acerca del paralelismo tiene una discusión sobre algún escenario donde MAXDOP es la solución.
Los componentes que inhiben el paralelismo se mencionan en Paul White: Forzar un plan de ejecución de consultas paralelas
Como mencionó Kaboing, MAXDOP(n) realidad controla la cantidad de núcleos de CPU que se utilizan en el procesador de consultas.
En un sistema completamente inactivo, SQL Server intentará llevar las tablas a la memoria lo más rápido posible y unirlas en la memoria. Podría ser que, en tu caso, es mejor hacer esto con una sola CPU. Esto puede tener el mismo efecto que usar OPTION (FORCE ORDER) que fuerza al optimizador de consultas a usar el orden de las uniones que ha especificado. En algunos casos, he visto OPTION (FORCE PLAN) reducir una consulta de 26 segundos a 1 segundo de tiempo de ejecución.
Books Online continúa diciendo que los valores posibles para MAXDOP son:
0: utiliza la cantidad real de CPU disponibles en función de la carga de trabajo del sistema actual. Este es el valor predeterminado y la configuración recomendada.
1 - Suprime la generación de plan paralelo. La operación se ejecutará en serie.
2-64 - Limita la cantidad de procesadores al valor especificado. Se pueden usar menos procesadores dependiendo de la carga de trabajo actual. Si se especifica un valor mayor que la cantidad de CPU disponibles, se usa la cantidad real de CPU disponibles.
No estoy seguro de cuál es el mejor uso de MAXDOP , sin embargo, adivinaría que si tiene una tabla con 8 particiones, querría especificar MAXDOP(8) debido a limitaciones de E / S, pero Podría estar equivocado.
Aquí hay algunos enlaces rápidos que encontré sobre MAXDOP :