paralelismo en sql server
TRANSCRIPT
Enrique Catalá Bañuls
▪ Ingeniero Informático
▪ Microsoft Data PlatformMVP
▪ Mentor en SolidQ▪ Tuning y alta disponibilidad
[email protected] www.solidq.com
@enriquecatala www.enriquecatala.com
[email protected] | @enriquecatala
SOLIDQ SUMMIT MADRID 2017
El tiempo computacional suele ser mas elevado, pero el tiempo efectivo suele ser menor
[email protected] | @enriquecatala
SOLIDQ SUMMIT MADRID [email protected] | @enriquecatala
SOLIDQ SUMMIT MADRID 2017
Symmetric
Multiprocessing System
(SMP)
Todas las CPU comparten
la misma memoria
Sin particionado HW para
acceso a memoria
Típico en computadoras
de consumo
Memory
CPU CPU CPU CPU
System bus
CPU CPU CPU CPU
SMP architecture
CPU
CPU
CPU
CPU
F
S
B
Main
Memory
[email protected] | @enriquecatala
SOLIDQ SUMMIT MADRID 2017
Non-Uniform
Memory Access
Nodos
conectados por
bus compartido
Típico en
servidores de
gama media-alta
Shared Bus
Memory
Controller
CPU CPU CPU CPU
Memory
Controller
CPU CPU CPU CPU
Memory
Controller
CPU CPU CPU CPU
Memory
Controller
CPU CPU CPU CPU
Node Controller Node Controller Node Controller Node Controller
[email protected] | @enriquecatala
SOLIDQ SUMMIT MADRID 2017
Massively Parallel
Processing
Nodos de
computación con
SQL conectados
Arquitecturas DW
petabyte (PDW,
Azure SQLDW)
[email protected] | @enriquecatala
SOLIDQ SUMMIT MADRID 2017
SQLOS
• Crea un scheduler para
cada CPU lógica
• Es como un gestor de
recursos…OS
• User-mode
Scheduler
• Como una CPU lógica
usada por los workers
Worker
• Son como “threads”
lógicos
Task
• Unidad de trabajo para
un worker (sentencia
simple)
[email protected] | @enriquecatala
SOLIDQ SUMMIT MADRID 2017
Basada en prioridades de procesos
p.ej: SQLCLR
Competitiva
Basada en listas de espera
p.ej: T-SQL
Cooperativa
[email protected] | @enriquecatala
SOLIDQ SUMMIT MADRID 2017
Multi-InstanciasTener varias instancias en el
mismo HW
LicenciamientoAfinar qué cores queremos
asignar de los licenciados
[email protected] | @enriquecatala
SOLIDQ SUMMIT MADRID 2017
Auto afinado al inicio de instancia
Hasta 30% beneficio
CPU > 8 cores HT
Se previenen mezclas logical-only o physical only nodes
SOLIDQ SUMMIT MADRID 2017
if(best_plan_for_now.cost<1) return(best_plan_for_now)
else if(
and best_plan.cost > threshold for parallelism)
return(MIN(create_paralel_plan().cost, best_plan_for_now))
Configuración
• A todos los niveles
• Instancia, BBDD,
conexión y query
Finalidad
• Limita
explicitamente el
nº de CPUs que un
worker puede usar
para una tarea
Recomendación
• En entorno OLTP
recomendado 1
• Afinar
agregaciones y op.
mantenimiento a 0
[email protected] | @enriquecatala
SOLIDQ SUMMIT MADRID 2017
if(best_plan_for_now.cost<1) return(best_plan_for_now)
else if(MAXDOP>0
and )
return( )
Configuración
• A nivel de
instancia
• Sp_configure
Finalidad
• Cambiar
estadísticamente
el nº de planes de
ejecución
Recomendación
• En entorno OLTP
mínimo subir a 50
[email protected] | @enriquecatala
SOLIDQ SUMMIT MADRID 2017
Hard
ware
NUMA
SMP
Instancia
Soft-NUMA
DOP
Cost threshold for
parallelism
Max worker
threads
-P
BBDD DOP
Conexión Resource Governor
Query
ENABLE_PARALLEL_PLAN_PREFERENCE
MAXDOP
Limitaciones de
implementacion
Funciones
CROSS APPLY
…
QUERY
RESOURCE
GOVERNOR
BBDD
INSTANCIA
SOLIDQ SUMMIT MADRID [email protected] | @enriquecatala
SOLIDQ SUMMIT MADRID 2017
Hash
• Los valores de
filas obtienen
hash y cada hilo
se responsabiliza
de un rango
hash
Round-robin
• Los valores de
las filas se
envían al
siguiente hilo de
la lista
Broadcast
• Todas las filas se
envian a todos
los hilos
Range
• Determina a que
hilo enviar la fila
evaluando una
funcion de
rango sobre una
columna
• Rara y usada en
algunos parallel
index recreation
Demand
• Se usa un modo
pull en lugar de
push como en
las otras.
• Envia la fila al
thread que se la
está pidiendo
• Aparece en
tablas
particionadas
SOLIDQ SUMMIT MADRID 2017
Consume múltiples fuentes
y produce multiples fuentesNo se modifican las filas
Se reducen filas si aparece
un operador bitmap
[email protected] | @enriquecatala
SOLIDQ SUMMIT MADRID 2017
Consume múltiples hilos
y produce un único hiloCombina resultados
Es el que genera el
famoso CXPACKET wait
SOLIDQ SUMMIT MADRID 2017
Type# producer
threads# consumer
threadsGather
StreamsDOP 1
RepartitionStreams
DOP DOP
DistributeStreams
1 DOP
SOLIDQ SUMMIT MADRID 2017
Mala configuración DOP
Mala configuración de affinidad de procesador
Estadísticas desactualizadas
• Generalmente problemas HW o mala configuración HW
Cambios de context excesivos
Malas arquitecturas de aplicación que estresan la CPU
SOLIDQ SUMMIT MADRID 2017
• MAXDOP = 0
• Threshold for parallelism = 5
Nunca usar la configuración por defecto
• MAXDOP = 1
• Threshold for parallelism = 50
OLTP puro
• MAXDOP = #’Phisical cores’
DW
• MAXDOP <= #physical_cores_numa_node
Regla de oro
wait type name wait time (ms) requests
CXPACKET 786556034 128110444
LATCH_EX 255701441 155553913
ASYNC_NETWORK_IO 129888217 19083082
PAGEIOLATCH_SH 83672746 2813207
WRITELOG 70634742 48398646
SOS_SCHEDULER_YIELD 47697175 176871743
SOLIDQ SUMMIT MADRID 2017
• ALTER INDEX, Statistics operations …
• Agregaciones (AVG, MAX,…)
• Recuerda que existe Resource Governor
¿Cuándo aplicar MAXDOP?
• Recomendación = #physical_cores
• Sistemas OLTP deberian configurarse a 1
• Siempre que veamos alto % de esperas CXPACKET
¿Cuándo aplicar “max degree of parallelism”?
• Cuando quieras cambiar el nº de operaciones paralelas estadísticamente
¿Cuándo aplicar “cost threshold for parallelism?