Planes de ejecución II

Enrique Catalá Bañuls

REL- 414

Mentor – Área relacional MCT – MCTS – MCITP – MAP 2010

ecatala@solidq.com

α Comprendamos estadísticas

α Esa magia

α Operadores β Spool

β Stream aggregate

α Escenarios avanzados β Contradiction detection

β Particionado

β Funciones

β Servidores vinculados

β Optimizacion por estadísticas

Objetivos de la sesión

α Densidad β Medida de cuantos duplicados hay por columna

β Densidad = 1/frecuencia

γ Alta densidad -> poco valor único

α Frecuencia β Numero de valores únicos en una columna

α Selectividad β Tambien es medida de unicidad

γ Alta selectividad -> pocos valores

β Se suele utilizar para representar predicados

α Cardinalidad β Es el concepto clave que hay que entender y surge de todos los

anteriores

β Número de filas devueltos por un operador

β Esto nos dara idea de por qué no va bien un plan de ejecucion

La magia Conceptos estadísticos básicos

Estadísticas y planes de ejecución

Planes de ejecución ¿Qué es eso?

Optimizador de consultas

Sentencia SQL Plan de ejecución Mágia

α Se detectan contradicciones

α Se reescribe la consulta simplificada β Agrupación de joins según su cardinalidad

β Se encuentran contradicciones en consultas que impidan su funcionamiento

La magia Simplification

α Existen consultas tan simples, que no es necesario optimizacion alguna

β Solo tienen un plan de ejecución válido

β No se leen ni las estadísticas

α Su «Query tree» está prefijado y si aparece, simplemente se evalua su plan único sin más

α Si una consulta se optimiza y después se determina que es trivial, siguientes consultas similares serán tratadas así

La magia Trivial Plan

α Stage 0 β Reglas básicas de evaluacion usando hash y nested join

β Si el coste del plan es menor a 0.2 usar este plan

α Stage 1 β Explorar mas reglas incluso alterando el orden de los join

α Stage 2 β Explorar todas las opciones y optar por el plan menos

costoso tras un nº limitado de exploraciones

α CUIDADO, TIEMPO LIMITADO!!! (timeout)

La magia Exploration

if(best_plan_for_now.cost<1) return(best_plan_for_now)

else if(MAXDOP>1

and best_plan.cost > threshold for parallelism)

return(MIN(create_paralel_plan().cost, best_plan_for_now))

α Pero cuidado porque en cada join, se incrementa exponencialmente el nº de soluciones posibles

La magia… Timeout!!

La magia

11

Query

Execution

Engine

Query

Optimizer Parser

SELECT

Average(Rating)

FROM Reviews

WHERE MID = 932

Logical

operator tree

Avg (Rating)

Select

MID = 932

Reviews

Query Plan #1

Avg_agg

[Cnt, Sum]

Scan

Reviews

Filter

MID = 932

Avg_agg

[Cnt, Sum]

Index Lookup

MID = 932

MID

Index

Reviews

Query Plan #2

or

α El optimizador utiliza dos tipos de clave β Tiempo E/S: Coste de leer páginas de un subsistema de disco

β Tiempo CPU: Coste de aplicar predicados y tuplas en memoria

Estimacion de costes

Las malas estadísticas hacen explotar nuestras SAN

Timeouts

Operadores

α Agrupa filas por una o varias columnas para aplicar funciones de agregado.

α La entrada del operador, requiere la entrada ordenada por las columnas de agrupación

β Si no lo están, se forzará un operador Sort como entrada

α Generalmente se pueden optimizar con índices nonclustered usando columnas incluidas

Operadores Stream Aggregate

α Existen varios tipos según su especialización

α Todos ellos hacen lo mismo conceptualmente β Leen todas las filas de un input

β Las almacenan en memoria o en disco

β Permiten a otros operadores leer de dicha cache

α Sirven generalmente para: β Optimizacion de escenarios donde una subexpresion compleja

se utiliza varias veces

β Mantenimiento de la consistencia transaccional: Aseguran que leen toda la entrada antes de devolver salida

α Curiosidad: El common subexpression spool es el único operador que puede enchufar datos a varios operadores diferentes a la vez

β Aunque realmente se realiza en serie (1 llena, los demas consumen secuencialmente)

Operadores Spools

α Escanea el input contando cuantas filas hay, devolviendo el nº SIN DATOS

α Se usa preferiblemente en NOT EXIST

α Es la alternativa eficiente de un Left anti semi join

Spools Row count spool

α index spool

β Se suele llamar indice al vuelo porque crea un indice al vuelo con lo que entra

α Lazy spool

β Se suele llamar así (lazy = lento), porque cachea el resultado de ejecución INMEDIATAMENTE anterior

γ Cuando la siguiente fila tiene el mismo valor, el valor se toma de lazy index spool (Rewind)

γ Cuando la siguiente fila difiere, se intenta obtener del acumulado que existe en lazy index spool, pero si no, se intenta mirar en los operadores anteriores (Rebind)

Spools Eager/Lazy Index Spool

α Los worktables son objetos temporales creados por SQL Server producidos por:

β Spooling, para almacenamiento de datos intermedios durante consultas

β DBCC CHECKDB o DBCC CHECKTABLE

β Trabajo con XML o variables varchar(max)

β Procesamiento de objetos Service broker

β Trabajo con cursores keyset o estaticos

α Ocurren en tempdb

α Sus metadatos estan en memoria pero sus datos pueden estar en tempdb

α Son objetos internos

Worktables ¿Qué son? ¿debemos temerlos?

Stream aggregate

Spool operators

• Row Count Spool

• Table Spool

• Eager/Lazy Spool

• Index Spool

α En las fases de simplificacion a la hora de trabajar el query optimizer se puede descartar directamente la ejecucion

α Se sabe claramente que ha ocurrido porque aparece el operador Constant Scan

Escenarios avanzados Contradiction detection

α Internamente, existe una columna PartitionID que se crea al particionar

α Aumento de rendimiento es interesante en operaciones de agregado y operadores tipo Scan

β Búsquedas adaptadas al particionado

β Estrategias de plan de ejecución en paralelo

α Vistas de índices alineadas con el particionado β Alternancia junto con la partición

β Cambio sencillo entre particiones

α Múltiples hilos en consultas que involucran recorridos de más de una partición

β SQL Server 2005 solo 1 hilo, cuidado!!!

Escenarios avanzados Particionado

α Grave problema de rendimiento «desapercibido»

α Un login de servidor vinculado debe ser sysadmin, db_owner o db_ddadmin

α Si no lo es, no se pueden utilizar las estadísticas

α Se estima un valor no real de las estadísticas para el plan de ejecución

Escenarios avanzados Servidores vinculados

α Podemos definir las funciones de usuario en: β Funciones inline

β Funciones multi-statement

α Tendamos a eliminar las funciones…¿pero todas?

α Problema: β No son visibles en los planes de ejecución gráficos

β Producen malísimas estimaciones estadísticas que derivan en inadecuados usos de NESTED LOOPS

β El código se interpreta en cada llamada (si no se usa bien)

β Por último y más importante: NO ES POSIBLE PARALELISMO

Escenarios avanzados Funciones

α Existen los mismos operadores para planes de ejecución paralelos

α Se identifican con una doble flecha

α Solo se opta por ellos cuando el plan de ejecución supera su coste «cost threshold of parallelism»

Escenarios avanzados Paralelismo

α En sistemas OLTP puros, se suele premiar serializabilidad β Pocos sistemas son OLTP puros

α SQL Server por defecto utiliza todos los cores disponibles para resolver planes de ejecución paralelos

α La idea es utilizar los cores extras, para reducir el tiempo de respuesta utilizando multiples cpus

β El tiempo computacional suele ser mas elevado, pero el tiempo efectivo suele ser menor

Escenarios avanzados Paralelismo

α Configuracion de servidor «Max degree of parallelism» β Valor predeterminado a 0

β En el 99% de escenarios se debe ajustar

α Configuraicon de servidor «Cost threshold of parallelism» β Valor predeterminado a 5

β No suele ser habitual modificarlo

α Cláusula MAXDOP β Modifica el comportamiento puntual de la cláusula a la que se aplica

β Imprescindible en escenarios donde se ha ajustado «Max degree of parallelism»

α Resource Governor β A traves de la configuración MAXDOP del workload group se

puede/debe ajustar siempre que toquemos «Max degree of parallelism»

β Nos ayudará a predefinir configuraciones MAXDOP

Paralelismo Metodos para controlarlo

α Mejora prácticamente lineal con el nº de CPU para operaciones parallel scans

Paralelismo Beneficios

α Las esperas se propagan con facilidad debido al modelo productor-consumidor

Paralelismo Inconvenientes

α No hay una solución maestra!!

α Si observas esperas CXPACKET reduce MAXDOP β En OLTP puro pensar en 1 suele ser correcto

α Considera Resource Governor

α Si ves planes de ejecucion suboptimos, considera actualizar estadísticas

α Re escribe la consulta para hacerla mas eficiente

Escenarios avanzados Paralelismo: Recomendaciones

Si quieres disfrutar de las mejores sesiones de

nuestros mentores de España y Latino América,

ésta es tu oportunidad.

http://summit.solidq.com/madrid/