Módulo 7

1. Módulo 7 Optimización de performance

2. TEMARIO • Ciclo de vida de la consulta • Procesamiento del lenguaje • Nuevas extensiones • Recompilación • Plan Cache • Dynamic Management Views • Detección de Blocking y Deadlocks • Mejoras del Profiler • Database Tuning Advisor

3. Bind, Expand Views Found Compiled Plan Found Executable Plan Auto-Param Language Processing (Parse/Bind, Statement/Batch Execution, Plan Cache Management) Parse Lookup in Plan Cache Query Optimization (Plan Generation, View Matching, Statistics, Costing) Query Execution (Query Operators, Memory Grants, Parallelism, Showplan) Generate Executable Plan Query Optimization Fix Memory Grant & DoP Execute Return Plans to Cache Ciclo de vida de la consulta New Statement Not Found

4. Generación del plan de consultas

5. Compilación y ejecución request plan cache query compilation parse / optimize - lookup in cache; if fail, compile metadata indices / stats - validate plan; if fail, re-compile query execution storage - execute

6. Representación de la consulta • SQL es el lenguaje estándar front-end • Bloque de consulta: SELECT SUM(T.a) FROM T, R WHERE T.b = R.b AND R.c = 5 GROUP BY T.c GroupBy T.c, sum(T.a) GroupBy T.c, sum(T.a) Filter (T.b=R.b and R.c = 5) Join (T.b=R.b) Cross product T Filter (R.c = 5) T R R

7. Agg(S.b: count(*)) Algebrize Filter (S.a = T.a AND T.b > 5) Join T (objid = 4369…) S (objid = 2560…) Procesamiento del lenguaje SELECT S.b, count(*) FROM S, T WHERE S.a = T.a AND T.b > 5 GROUP BY S.b

8. Procesamiento del lenguaje • En algunos casos, SQL Server puede remplazar una constante in-lined con un parámetro generado • Solo cuando un valor de un parámetro particular no impacta en la selección del plan • Dependiente de un esquema físico (i.e. índices) • Query Optimizer es consultado para chequear esta condición • Ejemplo SELECT * FROM CUSTOMERS WHERE CustomerID = ‘foo’ and CustomerID is unique and clustering key • Muchas construcciones excluyen auto-parametrización: • IN, UNION, TOP, OR • Sub-query • GROUP BY, HAVING • Index or Table hints • … • Multiples tablas en clausula FROM • Funciones Table-valued function / variables de Tablas • Consultas via clausula OPTION

9. Hash Agg(S.b: count(*)) Algebrize Query optimizer NestedLoops (Outer Ref. T.a) Agg(S.b: count(*)) • Generate alternatives • Estimate cost Filter (S.a = T.a AND T.b > 5) Index Seek S.a = T.a (objid = …, indid = 3) Index Seek T.b > 5 (objid = …, indid = 2) Join T (objid = 4369…) S (objid = 2560…) Metadata • Indices • Statistics • Constraints Output Input Optimización del la consulta SELECT S.b, count(*) FROM S, T WHERE S.a = T.a AND T.b > 5 GROUP BY S.b

10. Rank and rowid functions Window aggregates (OLAP extensions) Pivot/Unpivot Common table expr and recursive query Table sampling DML with outputs XQuery Partitioned tables Query notifications Indices on computed columns Enhanced view matching Nuevas extensiones • On-line index create and rebuild • Join reordering • Outer-joins • Sub-queries • Aggregation • Union • Index plans • Large IN lists

11. Recompilación – SQL Server 2000 big delay request result request result request result recompile SP execute stmt stats refresh execute stmt data change threshold reached decide to recompile

12. Recompilación – SQL Server 2005 request result request result request result execute stmt execute stmt recompile stmt data change threshold reached decide to refresh stats new stats decide to recompile async stats refresh

13. User Control – Optimizer Hints • Valores de parámetro para optimización • SELECT … • OPTION(OPTIMIZE FOR @X = 5) • Recompilar • SELECT … • OPTION(RECOMPILE) • Incrementando el control del planes “param sniffing,” • Sintonización fina de stored procedures

14. Ejecución del la consulta • Ejecución de consulta esta basada en planes ejecutables • Plan ejecutable deriva de un plan compilado • La mayoría de los planes ejecutables pueden ser cacheados • Algunas excepciones – e.g. planes en paralelo no son “chacheables”

15. Plan Cache • Planes Compilados Almacenados así como los Planes Ejecutables • Busca primero un plan ejecutable • Planes se almacenan para diferentes clases de sentencias • Consultas Ad-hoc • sp_executesql • Stored Procedures • EXEC (‘str’) • Consultas preparadas

16. Dynamic Management Views (DMV) • Muchas DMVs a través del server • Texto no disponible • Internamente implementado en código SQL Server • Datos recuperados directamente desde estructuras de memoria interna

17. Detección de Blocking y Deadlocks • Salida simple para cada par de blocking spids • Para múltiples blocking spids habrá múltiples eventos y salidas

18. Mejoras del Profiler • SQL Server 2000 profiler scripts corre sin cambios sobre 2005 • Usa definiciones basadas en XML • Guarda un showplan en formato XML • Guarda resultados en formato XML • Puede reproducir uno o mas rollover files • Permite perfil de: • Analysis Services • Data Transformation Services

19. Database Tuning Advisor • Puede ser lanzado desde • SQL Server Management Studio • Profiler • Importa definiciones de sesiones grabadas previamente • Almacenadas en formato XML