Diagnostico y resolución de problemas en SQL Server30-04-2014

Enrique Catalá BañulsMVP | MCT | MCITP | MCTSMentor SolidQPASS Spain, Guse.NET(@enriquecatala)www.enriquecatala.com

2

Agenda

1. Diagnostico de problemas en SQL Server• Waitstats• Caché de procesos• Tempdb • Indexación• Detección de consultas ineficientes

2. Resolución de problemas reales• Particionado y agregaciones• Paralelismo y consumos de CPU• Serialización en vistas indexadas• Encriptación

3

Diagnostico de problemasHerramientas

Extended eventsPerformance monitorProfilerDMVs• Waits statistics• FileIO

Revisión completa con HealthCheck

4

WaitstatsCaso real

CXPACKET; 54%

LATCH_EX; 17%

ASYNC_NETWORK_IO; 9%

PAGEIOLATCH_SH; 6%

WRITELOG; 5%

SOS_SCHEDULER_YIELD; 3%

Total esperas

CXPACKETLATCH_EXASYNC_NETWORK_IOPAGEIOLATCH_SHWRITELOGSOS_SCHEDULER_YIELDBACKUPIOSLEEP_BPOOL_FLUSHCMEMTHREADLCK_M_UPAGEIOLATCH_EXBACKUPBUFFERASYNC_IO_COMPLETIONBACKUPTHREADLCK_M_XPAGELATCH_EXLCK_M_SEXECSYNCIO_COMPLETIONLCK_M_ISSQLTRACE_LOCKLATCH_SHPAGELATCH_SHPAGEIOLATCH_UPPAGELATCH_UP

sys.dm_os_latch_stats

5

Cache de procesosEstado inicial

Cacheobjecttype ObjType Nº de entradas % de entradas Memoria (kb) % uso memoriaCompiled Plan Proc 39.727 9,77 317.816 9,77Compiled Plan Trigger 153 0,04 1.224 0,04Compiled Plan Adhoc 294.421 72,43 2.355.368 72,43Compiled Plan Prepared 56.607 13,93 452.856 13,93Extended Proc Proc 19 0 152 0Parse Tree UsrTab 139 0,03 1.112 0,03Parse Tree Check 110 0,03 880 0,03Parse Tree View 15.379 3,78 123.032 3,78

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

6

Caché de procesosEstado inicial

15183 planes de ejecución con una única entrada1172Mb de 3560Mb no se reutilizan

7

Caché de procesosSolución “de emergencia” propuesta

“optimize for adhoc workload”“forced parametrization” para las BBDD relevantes

8

Cache de procesosDespués del cambio

Cacheobjecttype

ObjType

Nº de entradas

% de entradas

Memoria (kb)

% uso memoria

Compiled Plan Proc 49668 10,78 397344 10,8Compiled Plan Trigger 24 0,01 192 0,01Compiled Plan Adhoc 198899 43,17 1591192 43,25Compiled Plan Prepare

d191353 41,54 1530824 41,61

Extended Proc Proc 11 0 88 0Parse Tree View 20337 4,41 162696 4,42Parse Tree Check 48 0,01 384 0,01Parse Tree UsrTab 673 0,15 5384 0,15

9

Cache de procesosDespués del cambio

uses number_ocurrenciescacheobjtype percentage_uses percentage_memory_KB1 6583 Compiled Plan 10,56 30,571 6 Parse Tree 0,01 0,011 13123 Compiled Plan Stu 21,05 0,002 3525 Compiled Plan 5,66 8,042 653 Parse Tree 1,05 2,363 2710 Compiled Plan 4,35 4,693 11 Parse Tree 0,02 2,853 1 Compiled Plan Stu 0,00 0,024 139 Parse Tree 0,22 0,434 2163 Compiled Plan 3,47 0,005 1998 Compiled Plan 3,21 1,985 41 Parse Tree 0,07 0,346 3578 Compiled Plan 5,74 2,036 333 Parse Tree 0,53 1,066 2 Extended Proc 0,00 0,007 2164 Compiled Plan 3,47 1,497 14 Parse Tree 0,02 0,048 1010 Compiled Plan 1,62 0,908 118 Parse Tree 0,19 0,369 1113 Compiled Plan 1,79 0,819 8 Parse Tree 0,01 0,02

10 836 Compiled Plan 1,34 0,68

10

Caché de procesosForced parametrization

“forced parametrization” no siempre ayudaEjemplo: Número de parámetros variableSelect * from tabla where param1 in (1,2,3,...,10) and param2 in (1,2,3,...,10) and param3 in (1,2,3,...,10)1000 parametrizaciones posibles baja probabilidad de reutilización del plan

11

Caché de procesosForced parametrization

“forced parametrization” no siempre ayuda (II)Ejemplo: Rangos de fechasSelect * from tabla where fechainicio between '20130601' and '20130602'SPs / Optimize for / Optimize for unknown / Planes de guiado…

12

Tempdb Mala configuración ¿Alguien sabe a qué es esto debido?

13

IndexacionCuando 5437 índices duplicados no usados…deberían decirnos “BASTA!”

14

Detección de consultas ineficientes¿Por qué es importante? TSQL-CSI

El escenario siempre es tan complejo que nadie sabe la causa de dónde está el problemaMétodo infalible: La agregación de consultas• Encontrar patrones T-SQL que producen mayor presión a SQL Server• No buscamos la consulta lenta, buscamos el patrón de consultas que

mas hace sufrir al servidor

Generalmente el cliente siempre se lleva sorpresas

Consulta A: Tiempo de ejecución 5s y 5 ejecuciones en 10 minutos Consulta B: Tiempo de ejecución 300ms y 1000 ejecuciones en 10 minutos

15

Detección de consultas ineficientes¿En qué nos fijamos? TSQL-CSI

11%1%

18%

69%

0%0%0% 1%

% 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

Database Name Cached Pages Memory (MB)BBDD1 588.870 4600,55BBDD2 98.906 772,7tempdb 2.889 22,57msdb 1.149 8,98BBDD3 327 2,55BBDD4 174 1,36BBDD5 138 1,08master 54 0,42BBDD6 35 0,27BBDD7 30 0,23model 1 0,01AdventureWorks 1 0,01ReportServer 1 0,01AdventureWorksDW 1 0,01ReportServerTempDB 1 0,01

16

Agenda

1. Diagnostico de problemas en SQL Server• Waitstats• Caché • Tempdb • Indexación• Detección de consultas ineficientes

2. Resolución de problemas reales• Particionado y agregaciones• Paralelismo y consumos de CPU• Serialización en vistas indexadas• Encriptación

17

DEMOParalelismo y particionado

Icon de tecnologíaIcon de tecnologíaIcon de tecnología

18

Vistas indexadas

Queries de tipo analítico sobre un modelo normalizado• Data marts• Data warehouses• Data mining

Operaciones candidatas • Joins y agregaciones de tablas grandes• Agregaciones dinámicas sobre agregaciones previas

Evaluar el coste/beneficio

19

Serialización en vistas indexadas

Una vista indexada es un tipo de índice muy distinto al restoEscenarios con un ratio de lectura respecto a escrituras elevadoSi es posible, reducir la vista indexada a un subconjunto de la vista original• Disminuir el número de tablas implicadas en la vista suele

reducir la frecuencia de actualización de éstaPotenciales zonas críticas + serialización de las actualizaciones• Escenarios con escritura controlada Concurrencia baja• Importante si la operación forma parte de otro proceso más

complejo• Timeout Rollback Reintentos = Combinación explosiva

21

Vistas indexadasResultados demo (Tamaño del batch y número de vistas)

22

Vistas indexadasResultados demo

23

Vistas indexadasResultados demo

24

Vistas indexadasResultados demo

25

Vistas indexadasResultados demo

26

Vistas indexadasResultados vistas indexadas

Si tenemos vistas indexadas• Si las inserciones son de 1 única fila• Tiempo de respuesta empeorará con la concurrencia

• Especialmente comparados con el escenario sin vista indexada• Tiempo total disminuirá con mayores grados de concurrencia• Conclusión: Nos conviene paralelizar si el tiempo total del proceso es crítico

• Si las inserciones son de bastantes filas (>1000)• Tiempo respuesta muy variable si añadimos concurrencia• Tiempo total muy similar entre distintos grados de concurrencia

• Zona crítica tiene un peso importante en el plan de ejecución• Conclusión: Conviene orquestar y serializar las operaciones

27

Vistas indexadasRecomendaciones vistas indexadas

Minimizar su uso dentro de lo posibleUtilizarlas únicamente en escenarios muy favorables a su uso• Tener claras sus limitaciones e impacto en operaciones DML• Utilizar la herramienta apropiada ETL+DW, Analysis services, PowerPivot

…

Reducir la concurrencia de las operaciones masivas• Procesos batch/sincronizados• Utilizar una vista indexada particionada y alineada (2008+)• Muy restrictivo Alineación de todos índices/tablas, agregar solo a nivel de 1 partición• No suele ser aplicable cuando queremos agrupaciones distintas

• Crear un particionado no nativo adaptado a la carga

28

Vistas indexadasEsquema de un único nivel

Tabla particionada

Vista indexada

particionada

Query 1

Que

ry

3

Query

2

Que

ry

4

Inse

rt 1 Insert 3

Inse

rt 2

Insert 4

Tabla 1

Vista indexad

a 1

Query 1 Que

ry

4

Inse

rt

1

Tabla 2 Tabla 3 Tabla 4

Vista indexad

a 2

Vista indexad

a 3

Vista indexad

a 4

Vista particionada

Inse

rt

2

Inse

rt

3

Inse

rt

4

29

Vista particionadaEsquema de dos niveles

Tabla 1

Vista indexada 3_1

Query 1

Que

ry

3Q

uery

2Que

ry

4

Inse

rt

1

Tabla 2 Tabla 3 Tabla 4

Vista indexada 3_2

Vista indexada 4_1

Vista indexada 4_2

Vista particionada

Inse

rt

2

Inse

rt

3

Inse

rt

4

Vista indexada 1_1

Vista indexada 1_2

Vista indexada 2_1

Vista indexada 2_2

30

EncriptaciónClásica y transparente (TDE)

TDE• Sencilla de implementar, activar y listo• Rendimiento bueno si tenemos que encriptar toda la base de datos• No permite trasladar a la capa de aplicación la encriptación de datos

Clásica• Mayor coste de CPU en el servidor de base de datos escalabilidad• Encriptación en la capa de negocio

• Dificultad para implementar los cambios en código necesarios• Problemas si los campos encriptados se usan en búsquedas • Indexación alternativa

31

Encriptación clásica

Service master keyMaster KeyCertificate• DECRYPTBYCERT • ENCRYPTBYCERT • Encriptación asimétrica no recomendable por rendimiento

Symmetric KeyOPEN SYMMETRIC KEY + CLOSE SYMMETRIC KEY • DECRYPTBYKEY• ENCRYPTBYKEY

DecryptByKeyAutoCert • Equivale a OPEN SYMMETRIC KEY + DECRYPTBYKEY + CLOSE SYMMETRIC KEY• Ojo con encapsularla dentro de una función escalar y llamarla N veces

32

DEMOTécnicas de indexación con columnas encriptadas

Icon de tecnologíaIcon de tecnologíaIcon de tecnología

33

Indexación de columnas encriptadasResultados demo

Con una buena estrategia auxiliar, el coste de desencriptar un registro es casi despreciable

34

Indexación de columnas encriptadasResultados demo

Para rangos con muchos registros el rendimiento va a ser un problema

35

Manténgase conectado a nosotros!

Visítenos en http://globalspanish.sqlpass.org

/SpanishPASSVC

lnkd.in/dtYBzev

Programa de Reconocimiento

Programa de Voluntario Sobresaliente

• PASS le invita a nominar a su voluntario favorito para ser “Voluntario Sobresaliente del Mes”

• Enviar nominaciones en todo momento a: VolunteerRecognition@sqlpass.org Favor proveer:• Información de contacto del nominado,• una lista breve de los programas de PASS que a participado el nominado• los años que lleva activo en la comunidad• una corta descripción por el cual considera que esta persona debe ser reconocida

• Los nominados seleccionados serán anunciados en la edición del boletín PASS Connector y recibirán un certificado de apreciación.

JOIN US for our second annual event to get the best learning for analyzing, managing, and sharing business information and insights through the Microsoft Data Platform of technologies.

http://www.sqlpass.org/summit/2014

November 4 - 7 | Seattle, WA

Manténganse Conectados!

• Solicite su suscripción gratuita en sqlpass.org

• Linked In: Professional Association for SQL Server• Facebook: Professional Association for SQL Server Group• Twitter: @SQLPASS• The PASS Blog: sqlpass.org