Rendimiento de Microsoft TM SQL Server 2000 Microsoft

Rendimiento de Microsoft® TM SQL Server 2000 Microsoft Corporation

Lo que vamos a cubrir u u u Bloqueos Procesador de consultas Optimización de consultas Configuración del sistema Monitoreo de rendimiento

Prerrequisitos de la sesión u Esta sesión asume que usted comprende los aspectos básicos de Ø Ø Ø Windows® 2003 Server/Windows® 2000 Server SQL Server 2000 Monitor del sistema Nivel 200

Agenda u u u Bloqueos Procesador de consultas Optimización de consultas Configuración del sistema Monitoreo del rendimiento

Administrador de bloqueos Qué hace por usted u u u Adquiere y libera bloqueos Mantiene la compatibilidad entre los modos de bloqueos Resuelve interbloqueos Escala bloqueos Utiliza 2 sistemas de bloqueos Ø Ø Bloqueos de datos compartidos Pestillos internos para datos internos y concurrencia de índices

Bloqueos Granularidad de bloqueos para los datos del usuario Tabla Página Fila

Niveles de aislamiento de bloqueos u Soporta los 4 niveles de aislamiento ANSI e ISO Ø Ø Seriable Lectura repetible Lectura confirmada - predeterminado Lectura no confirmada

Bloqueos Tipos de bloqueo en los datos del usuario Compartidos u u Se adquieren automáticamente cuando se leen los datos Aplica a la Tabla, Página, Clave o fila del Índice. Varios procesos pueden mantener un bloqueo compartido en los mismos datos. No se puede bloquear exclusivamente mientras está en modo de bloqueo compartido* *A menos que sea el mismo proceso que sostiene el bloqueo compartido

Bloqueos Tipos de bloqueo en los datos del usuario Exclusivos u u u Se adquieren automáticamente cuando se modifican los datos Sólo un proceso puede mantenerse a la vez sobre cualquier dato. Se mantiene hasta el final de una Transacción Se rechazarán todas las demás solicitudes de bloqueo por otros procesos. Se pueden utilizar ayudas de Consulta para decidir si se leen los datos asegurados

Bloqueos Tipos de bloqueo en los datos del usuario Actualización u u u Un híbrido de compartidos y exclusivos Se adquieren cuando se requiere una búsqueda antes de cualquier modificación a los datos Permite que otros sigan leyendo mientras se aplica el bloqueo Necesita un bloqueo exclusivo para modificar los datos Los datos pueden tener varios bloqueos compartidos pero sólo un bloqueo de actualización

Bloqueos Tipos de bloqueo en los datos del usuario Intentos u u u No es un modo de bloqueo real, sólo un calificador, es decir, Intento de bloqueo de actualización Es utilizado por SQL como un indicador de bloqueo de recursos Muestra si un subcomponente esta bloqueado (Por ejemplo si una fila se esta actualizando (exclusivo) muestra un Intento de Exclusivo sobre la tabla)

Bloqueos Tipos de bloqueo en los datos del usuario Especial u 3 modos especiales Ø Ø Ø Estabilidad del esquema – se utiliza cuando las consultas se modifican, evita los bloqueos de “Modificación al esquema” Modificación del esquema – se utiliza cuando se modifican las estructuras de la tabla Modificación Masiva– se utiliza con el comando BULK INSERT o BCP.

Bloqueos Ver información del bloqueo - Tipo Abrev Recurso Código interno Descripción / ejemplo DB TAB Base de datos Tabla 2 5 ID de la tabla EXT Extensión 8 Número de archivo / página PAG Página 6 Número de archivo / página 1: 104 KEY Clave 7 Valor controlado ac 0001 a 10 a 00 AC Fila 9 APP Aplicación 10 Número de archivo / página / ranura 1: 151: 4 Control del nombre de la aplicación MYpr 8 dea 261775902 1: 96

Bloqueos Ver información del bloqueo - Modo Abreviación Modo Código interno S Compartido 4 X Exclusivo 6 U Actualizar 5 IS Intento compartido 7 IU Intento actualización 8 IX Intento Exclusivo 9 SIX Compartido con Intento exclusivo 11 Sch-S Estabilidad del esquema 2 Sch-M Modificación al esquema 3 BU Actualización masiva 13

Arquitectura de bloqueos Tabla de control del bloqueo Bloque de recurso del bloqueo Convertir Bloque dueño del bloqueo Obtener Esperar Bloque dueño del bloqueo Bloque de recurso del bloqueo Convertir Obtener Esperar Todos los bloqueos propietarios de la misma transacción se enlazan entre si

Bloqueos Fila contra nivel de página u Requerimientos del bloqueo Ø Ø Ø u u u Cada bloqueo – 32 bytes Cada proceso que mantiene un bloqueo – 32 bytes Cada proceso que espera un bloqueo – 32 bytes Nivel de página – 8 k Nivel de fila – depende del tamaño de la Fila Cuál utilizar depende de la aplicación

Demostración 1 Ver los bloqueos de SQL Server

Agenda u u u Bloqueos Procesador de consultas Ajuste de consultas Configuración del sistema Monitoreo del rendimiento

Procesador de consultas Proceso de compilación de sentencias Analizar la sentencia Crear un árbol de secuencias Normalizar el árbol Sí Compilar en procedimientos de sentencia de TSQL No ¿ Sentencia de SQL DML? Crear una gráfica de consulta Optimizar y crear el plan Compilar en procedimientos las sentencias de TSQL

Procesador de Consultas Optimización de una sentencia Optimizador de plan trivial No Simplificación Carga de estadísticas Optimizador basado en costo: Fases 1 a n-1 Encontrado plan barato? Si No Plan encontrado? Si Costo de plan mas barato > que umbral de paralelismo? 1 Si No Optimización completa para ejecución en paralelo Optimización completa para ejecución en serie 1 Plan de salida

Procesador de consultas Cómo funciona el optimizador u u u Análisis de consultas Selección de índices Selección de combinación (join) Ø Ø Ø Iteración anidada (Nested iteration) Separación (Hashing) Mezcla (Merging)

Procesador de consultas Memoria caché – costeo de un plan de consultas ¿Ad hoc y barato de compilar? Sí Sin caché No Ubicar memoria del caché del búfer ¿Tipo de plan? Ad-hoc Establecer el costo inicial al costo de creación Establecer el costo inicial en cero Colocar el plan en caché

Procesador de consultas Flujo de Compilación Y Ejecución No Plan en Cache? Si Recuperar plan desde cache Interpretar/normalizar Si Compilar TSQL Optimizar sentencias Poner plan en cache El Plan necesita recompilación? No Esperar que el planificador de memoria autorice Abrir (activar) plan Ejecutar el plan hasta el final

Demostración 2 Analizador de consultas Obtener estadísticas sobre una consulta Utilizar el plan gráfico Utilizar el plan estimado

Agenda u u u Bloqueos Procesador de consultas Ajuste de consultas Configuración del sistema Monitoreo del rendimiento

Ajuste de consultas Ajuste – Cuándo empezar u u u Empiece al principio Considere el rendimiento antes de que escriba su primer línea de código Asegúrese de haber establecido una buena estructura de base de datos Desarrolle lo que parezcan ser índices útiles Asegúrese que todo el análisis se realice con una carga de trabajo representativa

Ajuste de consultas Diseño de la aplicación y la base de datos u u u Proporciona la mayor ganancia en rendimiento Normalice Evalúe sus transacciones críticas Mantenga compactas las extensiones de la fila de la tabla y las extensiones clave Cree índices útiles Evaluación comparativa, Prototipo y Prueba

Ajuste de consultas Asistente de ajuste de índices SQL Server Carga de trabajo (filtrada) en as l ult rea ns o Co mp tie Re co m de end aj ac us io te ne s Creación y ajuste de índices Perfilador SQL

Ajuste de consultas Monitorear rendimiento de consultas u STATISTICS – Input/Output Ø Ø u u Lecturas lógicas Lecturas físicas Lecturas avanzadas de lectura Cuenta de exploración STATISTICS - Time SHOWPLAN Ø Showplan_Text, Showplan_All, Graphical Showplan

Ajuste de consultas Sugerencias de consulta u u Las sugerencias de consulta se deben utilizar para casos especiales – no como un procedimiento operativo estándar Tipos de consulta: Ø Ø Sugerencias de unión Sugerencias de índices Sugerencias de bloqueos Sugerencias de procesamientos

Ajuste de consultas Bloqueos e Interbloqueos – Cómo resolverlos u u Mantenga las transacciones lo más cortas posibles Nunca agregue una pausa dentro de una transacción para entradas del usuario Cuando procesa un conjunto de resultados, procese todas las filas tan pronto como sea posible Para aplicaciones de scrolling de registros, considere utilizar cursores control de concurrencia optimista

Ajuste de consultas interbloqueos – Cómo resolverlos u u u Para evitar interbloqueos en los ciclos, haga que los procesos accedan a los recursos en un orden consistente. Reduzca el nivel de aislamiento de la transacción si es posible para la aplicación. Para evitar interbloqueos de conversión, serialice explícitamente el acceso a un recurso.

Ajuste de consultas Ajuste de rendimiento u Un enfoque paso por paso Ø Recopile información acerca del comportamiento de la aplicación Ø Ø Analice la información Ø Ø Ø Utilice el Perfilador SQL Analizador de consultas Asistente de ajuste de índices Aplique cambios Ø Ø Asistente de ajuste de índices Administrador empresarial

Demostración 3 Simulador de carga

Demostración 4 Asistente de ajuste de índices Analizar la carga de trabajo Ver reportes Aplicar índices sugeridos

Agenda u u u Bloqueos Procesador de consultas Ajuste de consultas Configuración del sistema Monitoreo del rendimiento

Configuración del sistema Asignación de recursos y ubicación de los archivos del sistema u u Asegúrese de que el rendimiento de los datos maximizados para las aplicaciones de la red se seleccione para los servicios de archivo e impresión No ubique los archivos de SQL Server en la misma unidad que PAGEFILE. sys

Configuración del sistema Configurar los recursos de SQL u Sp_configure Ø EXEC sp_configure Ø u RECONFIGURAR Ø Con sobrecarga Administrador empresarial

Configuración del sistema Configurar los recursos de SQL u u u Memoria mínima del servidor y memoria máxima del servidor Configurar el tamaño del conjunto del trabajo Memoria de consulta mínima

Configuración del sistema Configurar los recursos de SQL u Programar Ø Ø u Agrupamiento de peso ligero Máscara de afinidad Inicio de prioridad Hilos máximos para el trabajador Opciones E/S del disco Ø Intervalo de recuperación

Configuración del sistema Configurar los recursos de SQL u Opciones de procesamiento de consultas Ø Ø Ø Memoria mínima por consulta Memoria para crear índices Espera de consulta Límite de costo de gobernador de consulta Máximo grado de paralelismo

Configuración del sistema Configurar los recursos de SQL u Opciones de la base de datos Ø Ø Ø Sólo lectura Usuario único Autocierre Autoreducción Creación automática de estadísticas Actualización automática de estadísticas

Configuración del sistema Configurar los recursos de SQL u Administrador de búfer Ø Ø Opción “Pintable” Monitoreo del rendimiento Ø Ø SQLPERF(WAITSTATS) SQLPERF(LRUSTATS)

Demostración 5 Configuración del sistema SQL Administrador empresarial sp_configure

Agenda u u u Bloqueos Procesador de consultas Ajuste de consultas Configuración del sistema Monitoreo del rendimiento

Monitoreo del rendimiento Cómo utilizar el Perfilador de SQL u u Monitoree eventos del servidor Capture instrucciones de SQL Identifique consultas con rendimiento bajo Reproduzca los problemas para su diagnóstico

Monitoreo del rendimiento Cuándo utilizar el Perfilador de SQL u u Depurar T-SQL o procedimientos almacenados Monitorear el rendimiento de SQL Server para ajustar las cargas de trabajo Capturar los escenarios de interbloqueos Eventos de reproducción capturados

Monitoreo del rendimiento Perfilador SQL u u u Categorías de eventos Columnas de datos Filtros

Demostración 6 Perfilador SQL Rastrear consultas en una base de datos Crear asistente de rastreo

Monitoreo del rendimiento Procedimientos almacenados del sistema u SQL Trace Ø Ø Ø u sp_trace_create sp_trace_setevent sp_trace_setfilter sp_trace_setstatus sp_trace_generateevent SQLDIAG

Demostración 7 Procedimientos de diagnóstico del sistema

Monitoreo del rendimiento Uso del monitor del sistema u u u Monitorea todo el rendimiento del sistema Contadores de SQL

Monitoreo del rendimiento Monitor del sistema u Monitorea varias computadoras simultáneamente u Ve y cambia gráficos para reflejar la actividad actual u Exporta datos a hojas de cálculo o aplicaciones de la base de datos u Agrega alertas al sistema para notificarle de posibles problemas

Monitoreo del rendimiento Monitor del sistema u Ejecuta una aplicación cuando un valor del contador excede el valor definido por el usuario u Desarrolla archivos de registro u Anexa archivos de registro para formar un fichero a largo plazo u Ve los reportes actuales de actividades u Genera reportes a partir de los archivos de registro existentes u Guarda gráficos, alertas, registros o configuraciones de reportes

Monitoreo del rendimiento Monitor del sistema – Contadores del sistema u Sistema: Ø u Cambios/seg del contexto Procesador: Ø Ø Tiempo de %Processor Tiempo de %Privileged Tiempo de %User Extensión de la consulta del procesador

Monitoreo del rendimiento Monitor del sistema – Contadores del sistema u SQL Server: Administrador de memoria: Ø u Proceso: Ø u Memoria total del servidor (KB) Contador del conjunto de trabajo para la instancia de SQL Server Administrador del búfer de SQL Server: Ø Coeficiente de la memoria caché del búfer

Monitoreo del rendimiento Monitor del sistema – Contadores del sistema u Memoria: Ø u SQLServer: Bases de datos Ø u Páginas/seg Transacciones/seg Disco físico: Ø Transferencias/seg del disco

Demostración 8 Monitor del sistema Configurar monitor del sistema

Resumen de la sesión u u SQL Server 2000 puede utilizar varias técnicas para mejorar el rendimiento de las aplicaciones Las técnicas incluyen: Ø Ø Diseño de la aplicación y de la base de datos Ajuste de consultas Configuración del sistema Monitoreo del rendimiento

Para mayores informes… u Sitio Web Tech. Net en u Microsoft Official Curricula (MOC) www. microsoft. com/technet Ø Ø Curso # 2071 – Consultas de Microsoft SQL Server 2000 con Transact-SQL Curso # 2072 – Administrar una base de datos Microsoft SQL Server 2000 u Microsoft Press u “Inside SQL Server 2000”, Microsoft Press, 2000. , o: http: //mspress. microsoft. com/books/4297. htm “Microsoft® SQL Server™ 2000 Resource Kit”, http: //www. mspress. microsoft. com/PROD/BOOKS/4939. HTM “Microsoft® SQL Server™ 2000 Reference Library “, http: //mspress. microsoft. com/prod/books/5001. htm “Microsoft® SQL Server™ 2000 Administrator's Companion”, http: //mspress. microsoft. com/books/4519. htm “Microsoft® Back. Office® 4. 5 Resource Kit ”, Microsoft Press, 1999, o: http: //mspress. microsoft. com/prod/books/2483. htm u u

Capacitación Recursos de capacitación para los profesionales de informática u Consultas de Microsoft SQL Server 2000 con Transact-SQL Ø Curso # 2071 — Dos días — Dirigido por instructor o aprendizaje por correo electrónico Ø u Disponible: a través de MS CTECs en su área Administrar una base de datos Microsoft SQL Server 2000 Ø Ø Curso # 2072— Cinco días — Dirigido por instructor Disponible: a través de MS CTECs en su área Para localizar al proveedor de capacitación para este curso, visite mcspreferral. microsoft. com/default. asp Microsoft Certified Technical Education Centers (CTECs) son socios premier de Microsoft para servicios de capacitación

¿Dónde puedo obtener Tech. Net? u u u Visite Tech. Net Online en www. microsoft. com/technet Regístrese para Tech. Net Flash www. microsoft. com/technet/register/flash. asp Únase al foro Tech. Net Online en www. microsoft. com/technet/discuss Conviértase en un suscriptor de Tech. Net en technetbuynow. one. microsoft. com Asista a más eventos Tech. Net

Conviértase en un Microsoft Certified Systems Engineer u ¿Qué es MCSE? Ø u ¿Cómo me puedo convertir en un Windows 2000 MCSE? Ø Ø Ø u Certificación Premier para profesionales que analizan los requerimientos de negocios y diseñan e implementan la infraestructura para soluciones de negocios con base en el software del servidor Microsoft. Pasar 4 exámenes básicos Pasar 1 examen de diseño Pasar 2 exámenes opcionales a partir de una lista completa ¿Dónde obtengo mayores informes? Ø Para mayores informes acerca de los requerimientos, exámenes y opciones de capacitación para la certificación, visite www. microsoft. com/mcp

u u u u u u u u USE master GO CREATE procedure sp_lock 2 @spid 1 int = NULL, comprueban los bloqueos */ @spid 2 int = NULL bloqueos */ as /* id del proceso de servidor para el que se /* otros id de proceso para el que comprobar los set nocount on /* ** Muestra los bloqueos para ambos parámetros. ** No muestra los bloqueos de master, tempdb, model o msdb */ if @spid 1 is not NULL begin select convert (smallint, , req_spid) As spid, convert(char(20), db_name(rsc_dbid)) )) As [Database ], convert(char [Database], rsc_objid As Obj. Id, rsc_indid As Ind. Id, substring (v. name, , 1, 4) As Type, substring (rsc_text, , 1, 16) as Resource, substring (u. name, , 1, 8) As Mode, ( Mode, substring (x. name, , 1, 5) As Status from master. dbo. syslockinfo, master. dbo. spt_values v, master. dbo. spt_values x, master. dbo. spt_values u where master. dbo. syslockinfo. rsc_type = v. number and v. type = 'LR' and master. dbo. syslockinfo. req_status = x. number and x. type = 'LS' and master. dbo. syslockinfo. req_mode + 1 = u. number and u. type = 'L' and rsc_dbid >=5

u u u u and req_spid in (@spid 1, , @spid 2) end /* ** Sin parámetros, muestra todos los bloqueos. */ else begin select convert (smallint, , req_spid) As spid, convert(char(20), db_name(rsc_dbid)) )) As [Database ], convert(char(20), db_name(rsc_dbid [Database], rsc_objid As Obj. Id, , Obj. Id rsc_indid As Ind. Id, substring (v. name, , 1, 4) As Type, substring (rsc_text, , 1, 16) as Resource, substring (u. name, , 1, 8) As Mode, substring (x. name, , 1, 5) As Status ( from u u master. dbo. syslockinfo, master. dbo. spt_values v, master. dbo. spt_values x, master. dbo. spt_values u where master. dbo. syslockinfo. rsc_type = v. number and v. type = 'LR' and master. dbo. syslockinfo. req_status = x. number and x. type = 'LS' and master. dbo. syslockinfo. req_mode + 1 = u. number and u. type = 'L' and rsc_dbid >=5 order by spid u u u u u end u return (0) -- sp_lock 2