TÉCNICAS PARA LA OPTIMIZACIÓN DE BASES DE DATOS
ESTELLA DEL CARMEN SALEME LUGO
TUTOR: ING ANDRÉS JULIÁN HOYOS CAICEDO
SENA ESPECIALIZACIÓN EN GESTIÓN Y SEGURIDAD DE BASES DE DATOS FEBRERO DE 2016
TÉCNICAS PARA LA OPTIMIZACIÓN DE BASES DE DATOS Las técnicas para la optimización de las bases de datos está dada bajo varios parámetros, primeramente hay que realizar una supervisión con el f in de evaluar el rendimiento de un servidor con el fin de detectar y aislar los procesos que causan problemas y por ende realizar el respectivo seguimiento de las tendencias de rendimiento. Hay que tener en cuenta que la evaluación continua del rendimiento de la base de datos ayuda a minimizar los tiempos de respuesta y a maximizar el rendimiento, obteniendo como resultado un rendimiento óptimo. Es necesario analizar a fondo los requisitos de las aplicaciones, comprender la estructura lógica y física de los datos y evaluar el uso de la base de datos para la toma de decisiones en las organizaciones. El SGBD SQL Server y SO Microsoft Windows, nos facilitan herramientas que supervisan y nos muestran las condiciones actuales de la base de datos y realizan un seguimiento de ese rendimiento a medida que este cambia. Hay que conocer el modo de supervisar SQL Server, ya que de esta forma nos puede ayudar en varios aspectos como son: Determinar si el rendimiento se puede mejorar; evaluar la actividad de los usuarios y solucionar problemas o depurar componentes de aplicaciones, como procedimientos almacenados. Existen muchas tareas de supervisión y optimización del rendimiento, entre ell as podemos detallar las siguientes:
Descripción de la tarea Proporciona los pasos necesarios para supervisar eficazmente cualquier componente de SQL Server. Enumera las herramientas de supervisión y optimización de SQL Server. Proporciona información acerca de cómo establecer una línea base de rendimiento. Describe cómo aislar problemas de rendimiento de base de datos. Describe cómo supervisar y seguir el rendimiento del servidor para identificar cuellos de botella. Describe cómo usar SQL Server y las herramientas de supervisión de rendimiento y actividad de Windows. Describe cómo mostrar y guardar planes de ejecución en un archivo de formato XML.
Tema Supervisar los componentes de SQL Server Herramientas de supervisión optimización del rendimiento Establecer una rendimiento
línea
base
y
del
Aislar problemas de rendimiento Identificar los cuellos de botella Supervisión de la actividad rendimiento del servidor
y
Mostrar y guardar planes de ejecución
De acuerdo a todo lo planteado anteriormente, es claro que la supervisión es importante, ya que SQL Server ofrece un servicio en un entorno dinámico. Los datos de la aplicación cambian. El tipo de acceso que requieren los usuarios cambia. La forma de conexión de los usuarios cambia. También pueden cambiar los tipos de aplicaciones que tienen acceso a SQL Server, pero SQL Server administra automáticamente los recursos del sistema, como la memoria y el espacio en disco, para minimizar la necesidad de optimizar manualmente el sistema. La supervisión permite a los administradores identificar las tendencias de funcionamiento para determinar si es necesario realizar cambios. Por todo esto, para supervisar cualquier componente de SQL Server en forma eficaz es necesario realizar los siguientes pasos: 1) Definir los objetivos de supervisión; 2) Seleccionar las herramientas apropiadas; 3) Identificar los componentes que se desean supervisar; 4) Seleccionar métricas para dichos componentes; 5) Supervisar el servidor y por último, Analizar los datos.
HERRAMIENTAS DE SUPERVISIÓN Y OPTIMIZACIÓN DEL RENDIMIENTO SQL Server proporciona un conjunto de herramientas para supervisar los eventos de este SGBD y para optimizar el diseño de la base de datos física. La elección de la herramienta depende del tipo de supervisión u optimización que se realice y de los eventos particulares que se supervisen. A continuación se describen las herramientas de supervisión y optimización de SQL Server:
Herramienta
Descripción
El SQL Server Profiler realiza un seguimiento de los eventos de procesos del motor, como el inicio de un lote o una transacción, que permite supervisar la actividad del servidor y de la base de datos (por sp_trace_setfilter ejemplo, interbloqueos, errores irrecuperables o (Transact-SQL) actividad de inicio de sesión). Puede capturar datos de SQL Server Profiler en un archivo o una tabla de SQL Server para su análisis posterior y también puede reproducir paso a paso los eventos capturados en SQL Server para ver qué sucedió exactamente. Distributed Replay de Microsoft SQL Server puede usar SQL Server varios equipos para reproducir los datos de Distributed Replay seguimiento, simulando una carga de trabajo crítica. La función principal del Monitor de sistema es hacer un seguimiento del uso de los recursos, como el número de solicitudes de página del administrador de búfer activas, que permite supervisar el rendimiento y la Supervisar el uso de actividad del servidor mediante el uso de objetos y recursos (Monitor de contadores predefinidos o contadores definidos por el sistema) usuario para supervisar eventos. El Monitor de sistema (Monitor de rendimiento en Microsoft Windows NT 4.0) recopila contadores y porcentajes en lugar de datos acerca de los eventos (por ejemplo, uso de la memoria,
número de transacciones activas, número de bloqueos bloqueados o actividad de la CPU). Puede establecer umbrales en contadores específicos para generar alertas que notifiquen a los operadores. El Monitor de sistema funciona en los sistemas operativos Microsoft Windows Server y Windows. Puede supervisar (remota o localmente) una instancia de SQL Server en Windows NT 4.0 o posterior. La diferencia clave entre el SQL Server Profiler y el Monitor de sistema es que el SQL Server Profiler supervisa los eventos del motor de base de datos, mientras que el Monitor de sistema supervisa el uso de los recursos asociado con los procesos del servidor. El Monitor de actividad de SQL Server Management Studio muestra información gráfica acerca de: Los procesos que se ejecutan en una instancia Abrir el Monitor de de SQL Server. actividad (SQL Los procesos bloqueados. Server Management Bloqueos. Studio) La actividad de los usuarios. Esto resulta útil para vistas ad hoc de la actividad actual. Procedimientos almacenados de Transact-SQL que crean, filtran y definen trazas: sp_trace_create (Transact-SQL) SQL, seguimiento sp_trace_generateevent (Transact-SQL) sp_trace_setevent (Transact-SQL) sp_trace_setfilter (Transact-SQL) sp_trace_setstatus (Transact-SQL) El registro de eventos de aplicación de Windows proporciona una imagen global de los eventos que ocurren en todos los sistemas operativos Windows Server y Windows, así como de los eventos de SQL Server, el Agente SQL Server y la búsqueda de texto Registros de errores completo. Contiene información acerca de los eventos de SQL Server que no está disponible en ningún otro lugar. Puede utilizar la información del registro de errores para solucionar problemas relacionados con SQL Server. Los siguientes procedimientos almacenados del sistema de SQL Server suponen una alternativa muy eficaz para realizar muchas tareas de supervisión: Procedimientos Procedimiento almacenados del Descripción almacenado sistema (TransactSQL) Notifica información de sp_who instantáneas acerca de los (Transact-SQL) usuarios y procesos actuales de
DBCC SQL)
(Transact-
Funciones integradas (Transact-SQL)
Marcas seguimiento (Transact-SQL)
de
Asistente para la optimización de motor de base de datos
SQL Server, incluida la información sobre la instrucción que se ejecuta actualmente y si la instrucción está bloqueada. Proporciona información de instantánea acerca de bloqueos, sp_lock incluidos los identificadores de (Transact-SQL) objeto y de índice, el tipo de bloqueo y el tipo o recurso al que se aplica el bloqueo. Muestra una estimación de la sp_spaceused cantidad actual de espacio en (Transact-SQL) disco que utiliza una tabla (o toda la base de datos). Muestra estadísticas que incluyen el uso de la CPU, el uso de E/S y sp_monitor el tiempo de inactividad desde la (Transact-SQL) última vez que se ejecutó sp_monitor. Las instrucciones DBCC (Comandos de consola de base de datos) permiten comprobar las estadísticas de rendimiento y la coherencia lógica y física de una base de datos. Las funciones integradas muestran estadísticas de instantáneas acerca de la actividad de SQL Server desde el inicio del servidor; estas estadísticas se almacenan en contadores de SQL Server predefinidos. Por ejemplo, @@CPU_BUSY contiene el tiempo que la CPU ha estado ejecutando código de SQL Server, @@CONNECTIONS contiene el número de conexiones o intentos de conexiones de SQL Server y @@PACKET_ERRORS contiene el número de paquetes de red generados en conexiones de SQL Server. Las marcas de seguimiento muestran información acerca de una actividad específica en el servidor para diagnosticar problemas o causas de bajo rendimiento (por ejemplo, cadenas de interbloqueos). El Asistente para la optimización de motor de base de datos analiza los efectos en el rendimiento de las instrucciones Transact-SQL ejecutadas en las bases de datos que desea optimizar. El Asistente para la optimización de motor de base de datos proporciona recomendaciones para agregar, quitar o modificar índices, vistas indizadas y particiones.
A continuación se realizará en forma detallada como se hace la elección de la herramienta de supervisión, ya que esto depende del evento o de la actividad que se va a supervisar. Evento o actividad
SQL Server Profiler
Análisis de tendencias
Si
Reproducción de los eventos capturados
Sí (desde un equipo único)
Supervisión ad hoc Generación de alertas Interfaz gráfica Uso en aplicaciones personalizadas
Distributed Replay
Monitor de sistema
Monitor de actividad
TransactSQL
Registros de errores
Si
Si
Si
Si Sí (desde varios equipos)
Si Si Si
Si
Si
Sí 1
Si Si
ESTABLECER UNA LÍNEA BASE DEL RENDIMIENTO Para determinar si el sistema SQL Server funciona de forma óptima, tome medidas del rendimiento a intervalos regulares, incluso cuando no existan problemas, para establecer una línea base del rendimiento del servidor. Compare cada conjunto de medidas nuevo con las medidas tomadas anteriormente. Las áreas siguientes afectan al rendimiento de SQL Server: • • • • • • • •
•
Recursos del sistema (hardware) Arquitectura de red Sistema operativo Aplicaciones de bases de datos Aplicaciones cliente Como mínimo, utilice las medidas de línea base para determinar: Las horas con el máximo y el mínimo nivel de funcionamiento. Tiempos de respuesta de comandos de procesamiento por lotes o consultas de producción. Tiempos de finalización de operaciones de copias de seguridad y restauración de bases de datos
Cuando haya establecido la línea base para el rendimiento del servidor, compare las estadísticas de la línea base con el rendimiento actual del servidor. Unas cifras demasiado elevadas o demasiado reducidas con respecto a la línea base indican que hay que realizar una investigación más detallada. Pueden indicar áreas que hay que optimizar o volver a configurar. Por ejemplo, si aumenta el tiempo necesario para ejecutar un conjunto de consultas, examine las consultas
para determinar si puede volver a escribirlas o si es necesario agregar estadísticas de columnas u otros índices.
AISLAR PROBLEMAS DE RENDIMIENTO A menudo suele ser más efectivo utilizar conjuntamente varias herramientas de Microsoft SQL Server o Microsoft Windows para aislar los problemas de rendimiento de una base de datos que usar solo una herramienta cada vez. Por ejemplo, la característica Plan de ejecución gráfico, denominada también plan de presentación, le ayuda a reconocer los interbloqueos en una sola consulta. Sin embargo, puede reconocer más fácilmente otros problemas de rendimiento si utiliza conjuntamente las características de supervisión de SQL Server y Windows. El SQL Server Profiler puede utilizarse para supervisar y solucionar problemas de Transact-SQL o problemas relacionados con las aplicaciones. Asimismo, puede utilizar el Monitor de sistema para supervisar problemas relativos al hardware y otros problemas relacionados con el sistema. Puede supervisar las siguientes áreas para solucionar problemas: •
• • • •
• •
Procedimientos almacenados de SQL Server o lotes de instrucciones Transact-SQL enviadas por aplicaciones de usuarios. Actividad de los usuarios, como bloqueos o interbloqueos. Actividad del hardware, como el uso de los discos. Algunos problemas posibles son: Errores de programación de aplicaciones debidos a instrucciones Transact-SQL escritas incorrectamente. Errores de hardware, como los relativos a discos o a la red. Bloqueo excesivo debido a un diseño incorrecto de la base de datos.
HERRAMIENTAS RENDIMIENTO
PARA
SOLUCIONAR
PROBLEMAS
COMUNES
DE
Igual de importante es la correcta selección del problema de rendimiento que desea que cada herramienta supervise u optimice. La herramienta y la utilidad dependen del tipo de problema de rendimiento que desee resolver. En los temas siguientes se describen diversas herramientas de supervisión y optimización y los problemas que ayudan a solucionar. -
Identificar los cuellos de botella
El acceso simultáneo a recursos compartidos causa cuellos de botella. En general, los cuellos de botella están presentes en todos los sistemas de software y son inevitables. Sin embargo, la demanda excesiva de recursos compartidos causa un tiempo de respuesta largo, y debe identificarse y corregirse. Entre las causas de estos cuellos de botella se incluyen:
•
•
• •
Recursos insuficientes que requieren componentes adicionales o actualizados. Recursos del mismo tipo que no distribuyen de forma equilibrada las cargas de trabajo; por ejemplo, cuando un recurso monopoliza un disco. Recursos que funcionan incorrectamente. Recursos mal configurados. -
Analizar cuellos de botella
La duración excesiva de varios eventos es un indicador de cuello de botella que puede corregirse. Por ejemplo: •
•
Otros componentes pueden evitar que la carga alcance este componente, lo que aumenta el tiempo que se tarda en completar la carga. Las solicitudes de cliente pueden tardar más tiempo debido a una congestión de la red.
A continuación se indican cinco áreas clave que hay que supervisar para realizar un seguimiento del rendimiento del servidor e identificar cuellos de botella.
Posible área del cuello de botella Uso de la memoria
Uso de la CPU Entrada/salida (E/S) de disco Conexiones de usuario Bloqueos de cierre
Efectos en el servidor Si no se asignó o no hay disponible suficiente memoria para Microsoft SQL Server, el rendimiento disminuirá. Los datos se deben leer en el disco, y no directamente en la caché de datos. Los sistemas operativos Microsoft Windows realizan una paginación excesiva intercambiando datos con el disco cuando son necesarias las páginas. Un uso excesivo continuo de la CPU puede indicar que las consultas de Transact-SQL deben optimizarse o que es necesaria una actualización de la CPU. Las consultas de Transact-SQL se pueden optimizar para reducir la E/S innecesaria; por ejemplo, mediante el uso de índices. Puede haber demasiados usuarios obteniendo acceso al servidor de forma simultánea, lo que disminuye el rendimiento. Las aplicaciones diseñadas incorrectamente pueden causar simultaneidad de obstáculos y bloqueos, lo que genera tiempos de respuesta más largos y un menor rendimiento de las transacciones.
