LABORATORIO 10. Concurrencia y bloqueos en el SMBD SQL Server

GUÍA DE LABORATORIO Nº 10 Actividad de Proyecto No. 5: Desarrollar mantenimiento preventivo, correctivo o proactivo para garantizar niveles de servicio requeridos por la organización.

FAVA - Formación en Ambientes Virtuales de Aprendizaje

SENA - Servicio Nacional de Aprendizaje

Concurrencia y bloqueos en el SMBD SQL Server

Estructura de contenidos. 1. Introducción..............................................................................3 2. Objetivos...................................................................................4 3. Consideraciones.........................................................................5 4. Procedimiento............................................................................6 4.1. Actividad actual de SQL Server.............................................6 4.2. Manejo de Transacciones.....................................................6 4.3. Bloqueos...........................................................................8 4.3.1. Abra una nueva consulta...........................................8 4.3.2. Analizar el siguiente script.........................................8 4.4. JMeter............................................................................. 10 5. Evidencias a entregar................................................................13

2 FAVA - Formación en Ambientes Virtuales de Aprendizaje

SENA - Servicio Nacional de Aprendizaje

Concurrencia y bloqueos en el SMBD SQL Server

1. Introducción. En este laboratorio se realizaran actividades en las que se aborda situaciones problemáticas que le ayudaran al administrador de bases de datos SQL Server 2008 R2 a desarrollar habilidades que le permitan realizar un control adecuado sobre la concurrencia y bloqueos en la base de datos. Los DBA tienen la responsabilidad de mantener disponibles los servicios que presta la base de datos, para ello deben controlar el comportamiento de los procesos que acceden de forma concurrente sobre datos compartidos y los bloqueos que estos generan sobre los mismos.

3 FAVA - Formación en Ambientes Virtuales de Aprendizaje

SENA - Servicio Nacional de Aprendizaje

Concurrencia y bloqueos en el SMBD SQL Server

2. Objetivos. Objetivo General. Supervisar y realizar ajustes sobre los parámetros que permiten controlar el comportamiento de los procesos concurrentes y bloqueos, con el fin de mantener disponible la base de datos y optimizar el desempeño del SMBD SQL Server 2008 R2. Objetivos Específicos. • Reconocer las herramientas que son útiles para la supervisión de transacciones y bloqueos en el SMBD SQL Server 2008 R2. • Utilizar las herramientas de supervisión para monitorear el comportamiento de las transacciones y bloqueos en el SMBD SQL Server 2008 R2. • Administrar los recursos utilizados por las transacciones y bloqueos para mantener disponible los servicios de la base de datos SQL Server 2008 R2.

4 FAVA - Formación en Ambientes Virtuales de Aprendizaje

SENA - Servicio Nacional de Aprendizaje

Concurrencia y bloqueos en el SMBD SQL Server

3. Consideraciones. Antes de realizar los procedimientos planteados en este laboratorio, se sugiere revisar los siguientes aspectos: Ítem Soporte Teórico

Descripción Previo al desarrollo de este laboratorio debe haber revisado el objeto de aprendizaje Concurrencia, transacciones, accesos y bloqueos. También el Manual de Instalación y ejecución de Apache Jmetter. Manejo de sentencias SQL.

Productos requeridos

• Base de datos de la Secretaria de salud de la alcaldía de San Antonio del SENA o en su defecto el script de construcción de esta base de datos. • Script de consultas suministrado, para ejecutar en el Apache Jmeter.

Herramientas SW

Se requiere tener instaladas y configuradas las siguientes herramientas para desarrollar este laboratorio. • Máquina virtual proporcionada en la plataforma, con el SMBD SQL Server 2008 R2 instalado. • Apache Jmeter, el enlace de descarga se encuentra en el manual suministrado en la carpeta del laboratorio. • JDBC SQL Server 2008 R2 para la configuración del Apache Jmeter.

5 FAVA - Formación en Ambientes Virtuales de Aprendizaje

SENA - Servicio Nacional de Aprendizaje

Concurrencia y bloqueos en el SMBD SQL Server

4. Procedimiento. El laboratorio está compuesto por cuatro partes fundamentales que permitirán abordar los principales aspectos que deben ser tenidos en cuenta para realizar un adecuado tratamiento de los procesos concurrentes y los bloqueos que estos generan. Las actividades que se van a realizar son ejercicios prácticos para el manejo de las transacciones y bloqueos. Se debe entregar el código generado por el aprendiz para evidenciar la práctica del laboratorio. 4.1. Actividad actual de SQL Server. Utilice SQL Server Management Studio para ver la siguiente información acerca de la actividad actual de SQL Server. Generar un informe sobre los siguientes puntos para la consulta de transacciones y bloqueos se utiliza para estos puntos la base de datos de la Alcaldía – Secretaria de Salud, mínimo un ejemplo de cada categoría o impresión de pantalla identificando. • Conexiones de usuario y bloqueos actuales. • Número de proceso, estado, bloqueos y comandos que los usuarios están ejecutando. • Objetos bloqueados y tipos de bloqueos existentes. • Monitor de actividad • Transacciones bloqueadas y de bloqueo actuales. • Usuarios conectados actualmente en una instancia de SQL Server y la última instrucción ejecutada. • Bloqueos activos. • I nformes 4.2. Manejo de Transacciones. Para el manejo de transacciones se utilizan las siguientes sentencias:

6 FAVA - Formación en Ambientes Virtuales de Aprendizaje

SENA - Servicio Nacional de Aprendizaje

Concurrencia y bloqueos en el SMBD SQL Server

• BEGIN TRANSACTION: Inicio de una transacción. • @@TRANCOUNT: Número de Transacciones actualmente activas. • COMMIT TRANSACTION: Final de una transacción correcta. • ROLLBACK TRANSACTION: Revierte una transacción. • SET TRANSACTION ISOLATION LEVEL: Controlar el comportamiento de los bloqueos en el nivel de sesión. • sp_lock: ver la información de los bloqueos. 4.2.1. Abra la máquina virtual donde tiene instalado Windows server 2003 sp 2 y SQL Server 2008 R2, después inicio -> todos los programas -> SQL Server 2008 R2 -> SQL Server Management Studio. Nos conectamos al servidor -> en la parte izquierda -> base de datos -> la Base de datos de la Secretaria de salud nombre “SecSalud”. Elaborar 2 transacciones diferentes insertando, actualizando o eliminando registro de esta base de datos, todas estas transacciones deben quedar abiertas, tomar impresione de pantalla de la consulta de la variable que almacena las transacciones activas y copiar el código de las 2 transacciones como evidencia. 4.2.2. Abrir una consulta nueva en SQL Server 2008 R2 y escribir el siguiente comando COMMIT TRANSACTION y verificar de nuevo la cantidad de transacciones activas y determinar que hace este comando en la base de datos SecSalud. 4.2.3. Que sucede al hacer una consulta de todos los datos de la tabla eps si anteriormente se ejecuta el siguiente comando. BEGIN TRANSACTION INSERT INTO EPS (ideps, nombre, estadoeps) VALUES (15,’confisena’,1)

Para la cancelación de la transacción anterior ¿qué comando se debe utilizar?. 4.2.4. Que le falta a la siguiente transacción para que se efectúen los cambios en la base de datos Secretaria de Salud.

7 FAVA - Formación en Ambientes Virtuales de Aprendizaje

SENA - Servicio Nacional de Aprendizaje

Concurrencia y bloqueos en el SMBD SQL Server

BEGIN TRANSACTION INSERT INTO persona (idpersona, tipodeidentificacion, nombre, apellido, fechaNacimiento, sexo) VALUES (1112548, 1, ‘Pedro’, ‘Garcia’, 1982-01-27, ’M’) INSERT INTO EPS (ideps, nombre, estadoeps) VALUES (16,’confinacional’,4) 4.2.5. En el siguiente cuadro especificar para cada tipo de transacción si es implícita, explicita o automática. Script BEGIN TRANSACTION INSERT INTO cliente (cedula, nombre) VALUES (1,’sena’) COMMIT TRANSACTION INSERT INTO cliente (cedula, nombre) VALUES (1,’sena’) INSERT INTO cliente (cedula, nombre) VALUES (1,’sena’) COMMIT TRANSACTION

Tipo de Transacción

4.3. Bloqueos. 4.3.1. Abra una nueva consulta. Use la base de datos Secretaria de Salud En una nueva consulta ejecute sp_lock y revise los resultados. Abra informe de transacciones de bloqueo para verificar que no hay ningún bloqueo activo. Clic derecho en su bd -> informe -> informe estándar -> Todas las transacciones de bloqueo. 4.3.2. Analizar el siguiente script. Use SecSalud: BEGIN TRAN PRINT ‘Transacciones Activas’ SELECT @@TRANCOUNT SELECT * FROM EPS

8 FAVA - Formación en Ambientes Virtuales de Aprendizaje

SENA - Servicio Nacional de Aprendizaje

Concurrencia y bloqueos en el SMBD SQL Server

PRINT ‘Actualizamos la tabla EPS’ SELECT * FROM EPS UPDATE EPS SET nombre = ‘SALUDCOOPx’ WHERE nombre = ‘SALUDCOOP’ PRINT ‘Miramos la actualización’ SELECT * FROM EPS WHERE ideps = 1 PRINT ‘Transacciones Activas’ PRINT @@TRANCOUNT SELECT * FROM EPS PRINT ‘Identificador de bloqueo‘ SELECT @@spid

Al ejecutar este script tener abierta las siguientes consultas: SELECT * FROM EPS Informe de todas las Transacciones Informe de todas las transacciones de bloqueo Sp_lock ¿Qué pasa con cada consulta? ¿Hay errores? Si es así, ¿Cómo se solucionarían? 3.3 Ahora ejecute 3 transacciones diferentes de lectura y escritura. • La primera transacción de solo lectura. • La segunda transacción es de escritura de la tabla EPS. • La tercera es de escritura y lectura de la tabla PERSONA. Genere un informe de transacciones, bloqueos y ejecute el procedimiento sp_lock con las siguientes consultas: SELECT * FROM EPS SELECT * FROM Persona

Analice lo que ocurre con ellas.

9 FAVA - Formación en Ambientes Virtuales de Aprendizaje

SENA - Servicio Nacional de Aprendizaje

Concurrencia y bloqueos en el SMBD SQL Server

4.4. JMeter. Ingrese al Apache Jmeter y cree un nuevo plan de pruebas que se denomine con su nombre y apellido “nombre_apellido” y que tenga por comentario “Secretaria de salud”. Posteriormente cree un grupo de hilos con una conexión a la base de datos “secsalud” y tres peticiones JBDC en las que utilice una de las siguientes consultas para cada solicitud. Consulta 1: Listado con el número de identificación, nombre y apellido de las personas, además del nombre de la EPS y la fecha de ingreso y salida. SELECT p.idpersona, p.nombre, p.apellido, e.Nombre, h.fechaingreso, h.fecharetiro FROM persona p INNER JOIN historialpersona h on p.idpersona=h.idpersona INNER JOIN eps e on e.ideps=h.ideps ORDER BY p.apellido

Consulta 2: Listado en que se visualiza las EPS con el nombre de los servicios que presta y el costo de cada uno ellos. BEGIN; SELECT e.nombre, t.descripcion, s.detalle, s.valor FROM eps e INNER JOIN servicioeps s on e.ideps=s.ideps INNER JOIN tipoServicio t on s.idtiposervicio=t.idtiposervicio ORDER BY e.nombre COMMIT;

Consulta 3: Listado con el número de identificación, nombre y apellido de las personas, además del tipo de afiliación que tiene. BEGIN; SELECT p.idpersona, p.nombre, p.apellido, t.descripcion FROM persona p INNER JOIN historialpersona h ON p.idpersona=h.idpersona INNER JOIN tipoafiliado t ON h.tipoafiliado=t.idtipoafiliado ORDER BY t.descripcion; COMMIT;

10 FAVA - Formación en Ambientes Virtuales de Aprendizaje

SENA - Servicio Nacional de Aprendizaje

Concurrencia y bloqueos en el SMBD SQL Server

Por ultimo cree un receptor gráfico de resultados y comience a variar los valores de entrada del plan de pruebas de acuerdo con la siguiente tabla. Ejecute cada plan de prueba y registre los datos que se solicitan en la tabla.

Importante: Cuando este ejecutando cada plan de prueba mantenga las mismas condiciones en cuanto a hardware y aplicaciones en memoria, esto influye en la obtención de datos consistentes de la prueba. Realice un análisis de los datos registrados en la tabla en donde establezca la cantidad de usuarios concurrentes que puede atener el sistema computacional sin degradar su rendimiento. Analice cual es la mejor estrategia a seguir, frente a un posible aumento de usuarios en los limites en que el sistema comienza a comprometer su rendimiento. Algunas que puede considerar son: • Limitar cantidad de usuarios concurrentes. • Aumento de la capacidad del servidor. • Aumento del tamaño de la tabla de bloqueos.

11 FAVA - Formación en Ambientes Virtuales de Aprendizaje

SENA - Servicio Nacional de Aprendizaje

1 2 3 4 5 6 7 8 9 10 11

Plan de prueba Nº

10 25 50 100 250 500 1000 1500 2000 3000 4000

Nº de hilos

10 10 10 10 10 10 10 10 10 10 10

5 5 5 5 5 5 5 5 5 5 5

Periodo de Nº de subida en peticiones (s)

No. de muestras

Desviación

Rendimiento /Minuto

Media Mediana

Concurrencia y bloqueos en el SMBD SQL Server

12

FAVA - Formación en Ambientes Virtuales de Aprendizaje

SENA - Servicio Nacional de Aprendizaje

Concurrencia y bloqueos en el SMBD SQL Server

5. Evidencias a entregar. Informe que contenga los siguientes puntos: 1. Script con nueve (9) transacciones que definan sentencias SQL de tipo DML inherentes a las tablas de la base de datos de la secretaría de salud de la alcaldía “San Antonio del Sena”. Las tres (3) primeras transacciones reunirán cada una no menos de cinco (5) sentencias, que deberán ser confirmadas en su totalidad. Las siguientes tres (3) transacciones ejecutarán las acciones programadas y sin que sean confirmadas se deberán deshacer. Las transacciones restantes guardarán parcialmente el resultado de sus operaciones ante la presencia de errores que no permitan una ejecución completa. 2. Análisis de cuatro de las transacciones elaboradas en el punto anterior con el apoyo de las herramientas del SMBD Oracle, identificando los objetos que se bloquean, los tipos de bloqueos y los usuarios, transacciones o procesos causantes de estos bloqueos. 3. Apreciaciones del rendimiento de Oracle a través de la base de datos de la alcaldía evaluando el comportamiento del sistema a través de las simulaciones con el utilitario Apache Jmeter y las herramientas del SMBD. Este punto debe tratar situaciones como: • Número de usuarios concurrentes hasta generar bloqueos en el sistema: en este punto usted debe verificar en qué momento el sistema se bloquea a causa de superar los límites por número de peticiones de usuarios procesadas en un mismo instante de tiempo. Se debe mostrar en el informe las capturas de pantallas que evidencian las acciones asociadas al plan de prueba con Jmeter modificando el número de usuarios y explicando por cada una el comportamiento derivado. • Manifestaciones de bloqueos: en este ítem se debe documentar la forma como se muestran los bloqueos y los informes que genera Jmeter indicando la no respuesta del sistema. De acuerdo a lo convenido en los planes de prueba, presentar apreciaciones de cada una de las variables analizadas y el resultado obtenido. • ¿Qué acciones se pueden emprender una vez se producen bloqueos? Los bloqueos generan el aumento en los tiempos de respuesta del sistema, provocando inconformidad en los usuarios que pueden ver afectado su trabajo, es por eso que se debe presentar en el informe

13 FAVA - Formación en Ambientes Virtuales de Aprendizaje

SENA - Servicio Nacional de Aprendizaje

Concurrencia y bloqueos en el SMBD SQL Server

las acciones de tipo preventivo y correctivo a seguir por el DBA de la Secretaría de Salud una vez se generan bloqueos, o en su defecto la forma de validar los mismos indicando el tiempo máximo que podría estar un usuario sin respuesta del sistema, manteniendo coherencia con los acuerdos de niveles de servicio establecidos. • Limitar cantidad de usuarios: En este ítem del informe se debe presentar una política de manejo del sistema indicando a los usuarios lo que puede suceder si se superan los límites para recibir peticiones y entregar respuestas en un instante de tiempo determinado. De igual forma se requiere exponer con claridad los límites y las recomendaciones a usuarios finales.

14 FAVA - Formación en Ambientes Virtuales de Aprendizaje

SENA - Servicio Nacional de Aprendizaje