¡OPTIMIZACIÓN! Lo que siempre has querido saber para exprimir SQL

Server Enrique Catala Bañuls Mentor SolidQ ecatala@solidq.com

Ingeniero Informático

Mentor en SolidQ

Microsoft Technical Ranger

Colaborador destacado en MSDN Spain

Microsoft Active Professional 2010

Microsoft Certified Trainer

Microsoft Certified IT Professional Database Developer 2008

Database Administrator 2008

Arquitecto de entre otros Health Check y SCODA

Motero y piloto de ULM

Enrique Catalá Bañuls

Configuraciones avanzadas de SQL Server NUMA Threads vs fibers IO Affinity Mask Max Degree of parallelism Configuracion de memoria Tempdb

Configuraciones avanzadas de base de datos Log transacciones Date correlation optimization Parametrization

Patrones para Developers Reducción de idas y venidas mediante el uso de TVP Las funciones en SQL Server, el desastre y su solución

Agenda

Configuraciones avanzadas de SQL Server

NUMA

Threads vs fibers

IO Affinity Mask

Max Degree of parallelism

Tempdb

Agenda

Non-Uniform Memory Access

Particionado hardware donde cada nodo a grandes rasgos tiene subconjunto de CPU, memoria

Interesa no salir del nodo

Cada nodo NUMA tiene su propio lazywritter y su puerto para finalización de E/S (el network listener)

NUMA

SQL Server detecta automáticamente la configuración NUMA

Pensada para mejorar la escalabilidad de sistemas multiprocesador

Minimiza la latencia de acceso a memoria

No hay que modificar aplicaciones ni tocar nada

Podemos afinar: máscara de afinidad y Soft-NUMA

NUMA

Segmentamos afinidad de procesador (no memoria)

Cada nodo numa software contiene Lazywritter y proceso E/S

Diferencia obvia con NUMA hardware:

En Soft-NUMA seguiremos teniendo un único nodo de memoria

Solo provee afinidad a nivel de CPU

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 2 TO 5

Si vemos esperas CXPACKET y LAZYWRITTER unidas, debemos configurarlo (escenario no NUMA)

Segmentación multi-instancia

NUMA: Soft-NUMA

En entornos computacionales hay que dar solución al problema de la multitarea (más procesos que CPU concurrentemente)

El scheduler o programador se encarga de agendar a cada proceso un tiempo finito de acceso a recursos de la máquina para ejecución

Su eficiencia depende de: Nº de procesos simultaneos a agendar Latencia (tiempo entre que se solicita petición y se sirve) Prioridades e imparcialidad entre semejantes al asignar tiempo CPU

SQL Server tiene su propio scheduler En windows los procesos poseen acceso exclusivo a recursos generalmente El scheduler de SQL Server facilita la cooperación entre procesos Mucho mas escalable en tareas de SQL Server pero mas complejo de diseñar

Thread vs fibers: Scheduler

El scheduler puede ser visto como una CPU lógica Un worker está asociado a un scheduler y solo a uno

Haciendo una comparación, seria como los procesos en windows, pero que corren sobre el scheduler

Pueden ser thread o fiber (hilo o fibra) El scheduler se encarga de crear y destruir workers

Se pueden explícitamente fijar mediante la máscara de afinidad a una CPU concreta

Se crean si el scheduler recibe petición de tarea a lanzar y no hay workers inactivos

Se destruyen despues de 15 minutos de inactividad o si hay presión de memoria

En un sistema x64 cada worker usa como mínimo 2Mb Cada core (sea o no hyperthreading) posee 1 worker al arrancar (OFFLINE si

se ha quitado de su máscara de afinidad)

Thread vs fibers: SQL Server Workers

Varias fibras pueden correr sobre un único hilo ya que son muy ligeras (aprox 10 fibras/thread)

«lightweight pooling option» a 1, activa uso de fibras

Optimización de escenarios (aumento 15% rendimiento):

Processor time: ~80%

Context switches: >20k/sec

ADVERTENCIA no son siempre la mejor opción:

No se soporta CLR heterogeneo con consultas, SQL Mail, SQLXML, …

En escenarios como linked servers hay sobrecarga porque se obliga a convertir la tarea a hilo

Thread vs fibers

Optimización escenarios con alto consumo de CPU por E/S

Cada operación E/S en SQL Server necesita finalización Validación de bytes transferidos, no errores en SO, correcto nº de

página, checksum válido,…

En definitiva, consumo de CPU

La mascara de afinidad de E/S sirve para direccionar las operaciones E/S a un scheduler oculto con un lazywriter que que solo hace operaciones E/S

Cuidado al configurar la mascara y la máscara de IO

MAL BIEN

IO Affinity Mask

Siempre hay que afinar, sobre todo en sistemas OLTP

Como norma general:

Nunca exceder en MAXDOP el nº de hilos físicos por nodo NUMA

Aproximadamente 6ms por petición y más de 200h computacionales

NUMA y max degree of parallelism

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

Se carga tabla en buffer cache NUMA 0

Cell 0: ms nodo NUMA 0

Cell 1: ms nodo NUMA 1

Cell 2: ms nodo NUMA 2

www.solidq.com

NUMA y max degree of parallelism

Habilitar “Lock Pages in memory” siempre Se habilita mediante política a nivel de máquina para el usuario del

servicio

Evita swapping de RAM a disco

Trace flag “mágico” 834 (Large Page Allocation)

Solo recomendable en Win 2008 R2 y SQL 2008 R2

Medias de 10% de mejora de rendimiento obtenidas

Tiene un gran pero…

Configuracion de memoria

El “borrador” de SQL Server…no es solo donde van las #tablas

Precrear a un mínimo de 2xMemoria física

El nº de ficheros de datos debe ser exáctamente igual nº de hilos CPU IMPORTANTE: Todos precreados al mismo tamaño…TODOS

Es buena idea separar tempdb de la lun de datos Excepcionalmente, si es un entorno OLTP altamente transaccional,

poner los datos y tempdb en la misma LUN para mejorar aleatoriedad (ojo hablamos de sistemas de almacenamientos con mucho spindle)

Tempdb

Configuraciones avanzadas de BBDD

Log de transacciones

Date correlation optimization

Parametrization

Agenda

Siempre almacenamiento dedicado exclusivo para el log de transaciones Todo pasa por él

Es secuencial No crees más de un fichero, no sirve de nada…

Interesa máximo rendimiento en escrituras

Cuidado con la fragmentación! Precrearlo con tamaño suficiente para que nunca crezca y si lo hace, que

lo haga a intervalos gordos.

Ultima salida…recrear el log, asique no dejes que esto te ocurra

Revisa usando “dbcc loginfo”

Log de transacciones

Configuración a nivel de base de datos

Optimizar equi-joins entre dos tablas con date o datetime correladas a las que aplicamos filtro

Muy util en escenarios típicos de reporting o datawarehousing

ALTER DATABASE tu_Base_de_Datos

SET DATE_CORRELATION_OPTIMIZATION ON;

Date Correlation Optimization

SQL Server mantendrá estadísticas de correlación entre ambas tablas

El efecto será que interna y transparentemente se crearán vistas indexadas con la información necesaria

SQL Server gestionará todas las casuísticas

Añadirá nuevas estructuras cuando se cumplan

Deshabilitará cuando algo deje de cumplirse

Se encargará de ver si la consulta es mas eficiente usando la información extra calculada

Date Correlation Optimization: Beneficios

Debe existir clave ajena de una columna entre las tablas

Ambas tablas deben tener columnas datetime NOT NULL

Al menos una de las columnas debe ser la clave principal de un índice clústered

Ambas tablas deben tener el mismo propietario

PRECAUCIÓN: En tablas altamente modificadas puede existir detrimento de rendimiento por el mantenimiento interno que producen las estadísticas

Date Correlation Optimization: Restricciones

DATE CORRELATION OPTIMIZATION

DEMO

Parametrización: Mejorar escenarios ad-hoc (I)

Optimize for ad-hoc workloads

A nivel de instancia de SQL Server

Solo útil para consultas ad-hoc livianas

Almacena un pequeño código auxiliar en lugar del plan completo (300 bytes vs >15k)

Minimiza presión sobre el cache de planes de ejecución

Solo si se detecta reutilización, se recompila y almacena en cache

Mejorar escenarios ad-hoc (II)

Force parametrization

A nivel de base de datos

Ocurre por cada statement. Cada batch puede tener múltiples statements autoparametrizados

Fuerza la parametrización de toda sentencia SELECT, UPDATE, INSERT y DELETE

En ciertos escenarios, mejora enormemente el rendimiento a base de reutilización de planes de ejecución

PRECAUCIÓN: Testear siempre el entorno antes de poner en producción. Puede que salgamos perdiendo…

Excepciones Statements dentro de SP, funciones,… Si ANSI_PADDING o ANSI_NULL = OFF Statements que referencian variables Statements en cursor

Datos reales Antes

Después

10%0%

72%

14%

0%0%

0%

4%

% of memory used

Compiled Plan Proc

Compiled Plan Trigger

Compiled Plan Adhoc

Compiled Plan Prepared

Extended Proc Proc

Parse Tree UsrTab

Parse Tree Check

Parse Tree View

uses number_ocurrenciescacheobjtype percentage_memory_KB

1 6583 Compiled Plan 30,57

1 6 Parse Tree 0,01

1 13123 Compiled Plan Stu 0,00

2 3525 Compiled Plan 8,04

2 653 Parse Tree 2,36

3 2710 Compiled Plan 4,69

3 11 Parse Tree 2,85

3 1 Compiled Plan Stu 0,02

4 139 Parse Tree 0,43

4 2163 Compiled Plan 0,00

5 1998 Compiled Plan 1,98

5 41 Parse Tree 0,34

6 3578 Compiled Plan 2,03

6 333 Parse Tree 1,06

6 2 Extended Proc 0,00

7 2164 Compiled Plan 1,49

7 14 Parse Tree 0,04

8 1010 Compiled Plan 0,90

8 118 Parse Tree 0,36

9 1113 Compiled Plan 0,81

9 8 Parse Tree 0,02

10 836 Compiled Plan 0,68

Más del 55% de la memoria de planes

de ejecución reutilizado menos de 10

veces

Patrones para Developers

Reducción de idas y venidas mediante el uso de TVP

Las funciones en SQL Server, el desastre y su solución

Agenda

Escenarios

Actualización en lotes del servidor

Parámetros en lote para usar en consultas

Pasar una tabla entre rutinas

Migración de otras bases de datos

Los datos almacenados son tabulares!

Criterio común

Gran cantidad de datos pasados desde el cliente al servidor

Aplicación de lógica de negocio antes de actualizar datos de forma persistente

Ej. Data mining, sistemas de inventariado, herramientas ETL

TVP (Parámetros de tabla)

Empaquetado de lógica de negocio Mejor modelo de programación

Procesamos transacciones en orden de llegada

Mejor manejabilidad (procedimiento almacenado lo puede hacer todo)

Rendimiento Reducción de idas y vueltas al servidor

Operaciones basadas en conjuntos

Transporte de datos eficiente

Tipado Fuerte

TVP (Parámetros de tabla)

TVP (Parámetros de tabla)

“Aprovecha las características TVP y MERGE en tus aplicaciones” http://msdn.microsoft.com/es-es/sqlserver

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

Funciones en SQL Server

FUNCIONES EN SQL SERVER.

EL DESASTRE Y SU SOLUCIÓN

DEMO

Configuraciones avanzadas de SQL Server NUMA Threads vs fibers IO Affinity Mask Max Degree of parallelism Configuracion de memoria Tempdb

Configuraciones avanzadas de base de datos Log transacciones Date correlation optimization Parametrization

Patrones para Developers Reducción de idas y venidas mediante el uso de TVP Las funciones en SQL Server, el desastre y su solución

Agenda

¿ PREGUNTAS ?

ecatala@solidq.com

http://blogs.solidq.com/ElRinconDelDBA/default.aspx