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: [email protected] 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
Top Related