¿Quien bloquea? y ¿que hace? II. Agendando la detección

A partir de la consulta que detecta quien bloquea y que hace podemos construir una forma algo más atrevida de monitorización de bloqueos. La idea es la siguiente, con el query que detecta los bloqueos, guardamos el resultado en una tabla temporal. y esperamos un tiempo prudencial -y configurable- y volvemos a ejecutar la misma sentencia. Si los mismos procesos siguen bloqueados por los mismos actores, es tiempo de reportar ese bloqueo. Es decir, es normal que en un momento del tiempo haya un bloqueo, es cosa de la concurrencia, pero no es normal que ese bloqueo dure mucho (el cuanto es ese mucho depende de la aplicación).
En el caso de que el bloqueo persista, se notifica, en nuestro caso a través de mail. Si has entendido todos estos conceptos y solo quieres el código…

Si poor el contrario quieres entenderlo un poquito mejor y ver como funciona y como se agendaría, aquí tienes el video de demostración.

TSQL- ¿Quien bloquea y que hace?

Son muchas las ocasiones en las que ante un problema de bloqueos podemos sentirnos impotentes, la falta de información de que está pasando puede atenazarnos e impedirnos hacer nuestra labor, descubrir quien bloquea y que está haciendo.
Es cierto que a través de procedimientos del sistema, como sp_who o sp_who2 y algunas versiones que circulan por la red podemos tener cierta información de este tipo de cosas. La verdad es que yo nunca fuí muy fan de esos métodos, me parece que podemos personalizar más y mejor la información que nos da el sistema y podemos encontrar exactamente lo que estamos buscando.

Voy a poner aquí el resultado final, es decir el código terminado que determina quien bloquea y que hace, pero en las próximas lineas contaré el porqué de cada una de las decisiones.

lo primero que puede notar un observador detenido es que mientras que las consultas bloqueadas se obtienen de la DMV sys_dm_exec_requests, los bloqueadores se obtienen sin embargo de exec_conections, el motivo es que el bloqueador puede haber terminado su ejecución, al no estar bloqueado y por tanto el comando desaparece de las peticiones actuales, por lo que no podríamos obtener mas datos de la consulta que nos bloquea, mientras que usando el campo most_recent_sql_handle de la dmv connections podemos obtener esa información.

Se podría obtener tambien el plan de ejecución del bloqueador, pero en este momento no me parece relevante.

En los próximos días escribiré algunas mejoras a este procedimiento.

En el video de demostración, verás, no solamente como usar este comando sino también como obtener el plan de ejecución y visualizarlo en SQL Server Management Studio.

Os dejo el video de demostración

 

Hasta el próximo!

Niveles de aislamiento basados en instantáneas. Vídeo comparativa

Como prometiamos, ha llegado el momento de ver una demostración sobre los niveles de aislamiento y sus repercusiones. En la demostración vamos a demostrar cuando se genera un bloqueo, vamos a ver el tema de lecturas repetibles y que significa y vamos a comprobar la diferencia entre read_commited_snapshot y snapshot isolation level

El siguiente script es el script de iniciación de los datos, si quieres probar en tu propio sistema esta demostración, sería bueno que lo copiaras 🙂

En la demostración, podreis comprobar como se comportan los updates, los bloqueos en función del nivel de aislamiento que estemos usando en nuestra conexión. A pesar de no ser muy larga la demostración es bastante intensa, sobre todo para aquellos que seáis noveles en estos aspectos.

Quedo a la espera de vuestros comentarios! Gracias y hasta el próximo vídeo demostración.

 

Niveles de aislamiento basados en instantaneas

Hemos hablado de nolock y ahora vamos a hacer un pequeño viaje por los niveles de aislamiento basados en instantáneas. Primero un poco de contexto.
En el mundo de las bases de datos y los estándares se habla de las propiedades que deben cumplir los motores de bases de datos en cuanto al ACID , atomicidad, consistendia, aislamiento y durabilidad. No voy a entrar en esas explicaciones, porque son largas y quizá no vengan al caso en este artículo. Sin embargo si que necesitamos saber algo de la parte del aislamiento.
Los problemas que se enumeran sobre los niveles de aislamiento son
* Lecturas sucias
* Lecturas no repetibles
* Lecturas fantasma
Aquí podéis encontrar informacion acerca del acid
https://es.wikipedia.org/wiki/Aislamiento_(ACID)

Dicho esto, al final, los motores de bases de datos han de implementar estos atributos estandar, y cada uno lo hace según su propio criterio. En general, y podríamos decir que hay dos metodos, basarnos en bloqueos y basarnos en instantáneas. Intento explicar las diferencias:

Supongamos que tenemos dos conexiones con una transaccion abierta, la transaccion a modifica un registro y la transaccion b
* Si estamos basados en bloqueos , la transacción B tendrá que esperar a que la transacción A acabe, puesto que el registro está modificado, pero no confirmado. Si usamos el hint nolock, veremos el datos sin confirmar (lecturas sucias)

*Si estamos basados en instantáneas  entonces lo que sucede es que cuando se modifica el registro, la ultima versión confirmada de la transacción se guarda para que si otro usuario la reclama pueda obtenerla, esta vez sin bloqueos puesto que hay un registro que tiene un valor ya confirmado (aunque potencialmente vaya a cambiar)

 

Si las instantáneas se destruyen toda vez que la transacción que modifica el registro (transaccion A) acaba, y dentro de la misma transaccion B volvemos a ejecutar la misma sentencia de consulta, el resultado será que dentro de la transacción B hemos visto dos valores distintos para el registro en cuestion, produciendose así lecturas no repetibles.

Las lecturas fantasma sin embargo se producen cuando esa selección se hace sobre todos los registros y pueden aparecer o desaparecer registros del final.

En SQL Server tradicionalmente y por defecto el modelo de implementación está basado en bloqueos, sin embargo desde hace ya bastantes versiones, se puede habilitar para estar basado en instantaneas. La forma de hacerlo sería así

Este comando habilita a la base de datos demo2 para implementación basada en instantáneas, lo que hace que automaticamente se eliminen muchos de los bloqueos que podamos tener, pero OJO no es recomendable cambiar este valor sin probar la aplicacion que use SQL bien antes, ya que puede que los desarrolladores se hayan basado en que hay bloqueos para ciertas implementaciones, y eliminar esos bloqueos podría hacer que la aplicación no funcionara bien. Sin embargo, si eres desarrollador y estas creando una aplicacion nueva y en el pasado los bloqueos te han mareado mucho… no lo dudes, desde el minuto 0 hablita este nivel de aislamiento.

Otros gestores, como Oracle, por defecto se comportan con la implementación basada en instantáneas.

Además del nivel de aislamiento por defecto, se puede habilitar un nivel mas de instantaneas, aunque para hacerlo hay primero que habilitarlo en la base de datos y además usar un comando SET TRANSACTION ISOLATION LEVEL

la forma de habilitarlo es la siguiente

En los proximos dias, publicaré una demostración sobre estos niveles de aislamiento y como se comportan así en unos pocos minutos podremos tener una formación más completa sobre la materia

With NoLock

¿Cuantas veces ante un bloqueo hemos usado with nolock? En muchas ocasiones incluso es posible que lo hayamos recomendado. Lo que dice el sentido común es que es posible que leamos transacciones que no han sido validadas, y esto, no parece que sea muy importante.

Es decir, si por ejemplo usamos una sentencia select sum(ImporteFacturado) from facturas  with(nolock) ¿que puede pasar?

En realidad lo que te van a comentar generalmente es que nada demasiado grave, que si una factura está a medio grabarse en una transacción sin confirmar pues veras ese importe, y si luego hacen rollback desaparecerá. Y tu sensación será.. tampoco es para tanto.. ¿verdad?

En realidad, internamente pasan muchas mas cosas, no solamente se leen transacciones sucias de usuario, sino también transacciones sucias de sistema, esto es, todo lo que el motor necesite para organizar páginas estará bajo lecturas sucias.

Y la cuestión es ¿y eso que implicaciones tiene?

Las implicaciones son a veces imprevisibles,  lo normal es que no pase gran cosa, pero simplemente decir eso es una temeridad puesto que aunque solo variara un registro las implicaciones de negocio son imprevisibles. En el video que pueden ver a continuación tienen una demostración que creo que ilustra bastante bien lo que puede pasar.

Esta demostración está inspirada en una del gran Itzik Ben-Gan https://twitter.com/ItzikBenGan , sirva de agradecimiento a todo lo que hemos aprendido  y seguimos aprendiendo con él.

 

Una solución a esto, sin entrar demasiado en detalle es utilizar niveles de aislamiento basados en instantáneas,  veamos un ejemplo

 

En conclusión. No quiero demonizar el uso de nolock, pero al menos, cuando lo usemos, que seamos conscientes de las alternativas y de sus peligros, de todos ellos.

Construir procesos ETL con SSIS.

Dentro de los básicos del mundo de los datos, la capacidad de construir y desplegar procesos ETL (extracción transformacion y carga) es una de principales características de los sistemas modernos de datos.
Los procesos ETL llevan vigentes muchos años, aunque ahora hay tendencias a usar ELT es decir, construir las estructuras de destino antes de transformar la información, sobre todo con el auge del famoso BIG DATA. Lo hagas antes o despues, en un orden o en otro al final siempre hay un proceso de transformación. Por si alguien está interesado en esto, básicamente el paradigma ELT pretende dar una respuesta al tiempo real, o como decía un cliente al tiempo razonable. Incluso hay iniciativas que simplemente son un envoltorio sobre los datos que no se mueven y por tanto están siempre disponibles en tiempo real, a costa claro, de que la base de datos de origen «sufra» las consultas.

Más allá de todas estas teorías y del reto que puedan suponer, el artículo va orientado a lo que dice su título, Construir, desplegar y agendar procesos ETL con SQL Server Integration Services.

  1. Las herramientas que necesitaremos

Ha pasado por varios nombres, pero en el día de hoy se usan las SQL Server Data Tools, te sugiero que lo busques en tu buscador favorito, puesto que el enlance que te voy a poner a continuación seguramente quede obsoleto sin que yo me de cuenta Descarga la version actual de las SQL server Data Tools

Ojo, porque hay comos dos versiones, una de las data tools es para el mundo relacional y se encarga de la generación y mantenimiento de bases de datos. Es una interesante herramienta pero no es de la que hablamos en este artículo. Síguele la pista sobre todo si quieres automatizar los despliegues de aplicaciones en lo que en el momento de la escritura de este artículo se llama DevOps.

Una vez que las descargues, en tu versión de Visual Studio te aparecerá un nuevo menú de proyectos, en el que tendrás un apartado llamado Business Intelligence (la verdad es que no se que pone si el Visual Studio está en español), con los proyectos tipo Analisys Services, ReportingServices y el que nos interesa a nosotros, Integration Services.

2. Crea tu primer proyecto SSIS

La intención de este artículo no es hacer un tutorial detallado del uso de SSIS, digo esto porque no quiero que el artículo pese 100 páginas y sea medio libro, por eso vamos a mostrar un paquete ya creado con una somera explicación de cada una de las partes .Todo esto con el objeto de dar una visión general del proceso completo, así pues una vez creado nuestro paquete podremos compilarlo y desplegarlo.

Los paquetes de SSIS tienen dos tipos de objetos, los flujos de control y los flujos de datos. Los flujos de control son lo que en una aplicación en cualquiera de tus lenguajes favoritos son los bucles y estructuras de control del flujo del programa. En ellos decides que trozos se ejecutan y cuales no, decides que trozo se paraleliza y que trozo se hace en secuencia, si hay que iterar por tablas etc etc. Para todo esto tiene componentes SSIS y además no se necesitan conocimientos de desarrollo profundos para usarlos, simplemente conocer que hace cada componente y como se va a comportar.

Un ejemplo terminado de flujo de control que yo uso de forma habitual es el siguiente. (verás que es bastante completo). Sería muy costoso mantener ese nivel de calidad, pero en mi caso los genero automáticamente con el Framework que tenemos en SolidQ para desarrollar estas aplicaciones me puedo permitir el que sean así de terminados.

El primero de los objetos «Check load Active» es una consulta SQL a una tabla de meta-datos, en esa tabla guardo un montón de información sobre la ejecución del proceso, uno de los valores que guardo es si está o no activa para ejecutarse. De esta forma si una vez desplegado el paquete quiero que no se ejecute, me basta con cambiar ese valor y no tengo que desplegar de nuevo una solución. De ahí que la flecha verde que une el flujo tenga un fx, ese valor es el que comprueba si ha de seguir ejecutándose o no.

El segundo componente es una llamada a un procedimiento almacenado que marca el inicio de la carga, ahí guardo información como el nombre del paquete la hora de comienzo, el tipo de proceso y más información relevante para hacer después análisis de ejecuciones. Esta información se completará al final con el resultado de la ejecución (satisfactorio o erróneo)

Después lanzo script de pre-ejecución. Estos scripts los uso en ocasiones para hacer tareas previas al movimiento de datos en sí, reconstrucción de estadísticas, creación de falsas tablas temporales, actualización de valores relevantes, resúmenes de información, o cualquier otra tarea que necesite antes de ejecutar mi proceso de sincronizar. En esta ocasión solo hay un comando, pero en procesos más complejos puedo tener varios.

A continuación busco el ultimo valor leído. Esto es porque el proceso, en este caso, es incremental, imaginemos que es por una fecha. En una tabla de configuración tengo almacenada la última fecha que leí, ahora en este componente leo esa fecha y la asigno a una variable. Después usaré esa variable para filtrar la información de origen exclusivamente a la que sea mayor o igual que la última fecha que leí. (si estás pensando que eso no detecta borrados… es correcto.. para eso hay otros procesos pero no es el caso de detallar todos y cada uno de los pormenores en este artículo)

Después se puede observar que entramos en el proceso de carga de datos. Lo primero que se hará es limpiar la tabla de cambios. Esto significa que en mi forma habitual de proceder, para cada tabla que voy a sincronizar creo una tabla paralela para guardar las operaciones que han cambiado, de esta forma lo que hago es siempre insertar cambios en esta tabla, para despues actualizar los datos de la tabla original con los datos de la tabla de cambios. ¿Porqué? El motivo es que los componentes oleDB command de SSIS en un flujo de datos se ejecutan por cada fila, si se actualizaran muchas filas el proceso sería estúpidamente largo en tiempos prefiero hacer un solo comando update, o delete para actualizar todas las filas. Decir también que cuando ha sido necesario he creado proceso que hacen estas actualizaciones por bloques para evitar que sufra el log de transacciones.

Lo más relevante tras esta operación es el flujo de datos, en él también voy a dar una explicación somera de lo que se hace puesto que no pretendo aquí crear un tutorial de creación de flujos de datos en ssis.

En mi caso lo primero que hago es usar un componente OleDB Source (llamado HLP) para leer datos del origen, ese componente es el que filtrará el query de origen para traerme solo los datos que han cambiado.  Una vez que tenemos esos datos, es posible, que como parte de nuestra transformación, algunos tipos de datos cambien, particularmente si nuestro origen es de un sistema gestor de bases de datos distinto al de  destino. Como puede observarse en el camino de error (el rojo) si no se puede hacer esa transformación prevista, simplemente llevaré los registros a una tabla de error, que me permita saber que algo no está funcionando bien en mi proceso. A continuación genero un Hash con todas las columnas de la tabla, excepto las que forman parte de la clave primaria. Uso el componente Multiple hash que os recomiendo encarecidamente. Con eso me ahorro de escribir complejos codigos de comprobacion teniendo en cuenta posibles nulos etc. Simplemente para cada clave primaria que coindice, veo si coincide o no el hash que ya había calculado vs el hash que acabo de calcular.

Una vez hecho ese cálculo uso el componente lookup, para encontrar los registros que ya tengo y poder distinguir entre  insertados y modificados (y no cambiados en absoluto).

Además de esto contaré las filas que pasan por cada flujo y marcaré la operación real (actualizacion o inserción) que sufre el registro, dejando el resultado en la tabla de cambios.

Como podréis imaginar después hay un post-proceso en el que las filas modificadas se modifican realmente y las insertadas se llevan a su lugar y un proceso adicional -cuando es necesario- de detección de eliminados, completando el proceso de sincronización.

 

 

 

 

 

Desplegar y agendar paquetes SSIS en el Catalogo

Es una tarea bastante sencilla pero siempre genera dudas, así que vamos a intentar aclarar como se hace el despliegue de un paquete y como se agenda, y algunos de los problemas que nos podemos encontrar.

Una vez creado un paquete creado en Visual Studio con SSIS necesitamos un catalogo al que subirlo. Como nota al pie os diré que antes los paquetes se podían subir a MSDB o al sistema de archivos, igual se puede hacer aún hoy, pero es un atraso y una complicación innecesaria. El catalog (-SSIS catalog-) es con mucha diferencia mucha mejor solución que sus predecesoras.

Entre otras ventajas se pueden configurar ambientes, cada ambiente puede asignar valores a cadenas de conexion y variables, además esos valores pueden ir cifrados si es necesario. Además genera versiones de los paquetes, de forma que ante un error en un despliegue siempre puedes volver atrás, y algunas otras más que no podemos desarrollar simplemente en el ámbito de un articulo como este.

Veamos primero donde se configura. Por si aún no lo habías notado, SQL Server Management Studio es el anillo único cuando se trata de administrar SQL-Server. así que configurar implica univocamente SQL Server Management Studio. Ahí vamos al Integration Services Catalogs y con botón derecho sobre el menú nos aparece crear catálogo. Yo ya lo tengo creado por lo que me resulta complejo mostraros la información, pero básicamente se configura un password para encriptar la información sensible y se habilita el uso del CLR para la ejecución de paquetes de SSIS a través de procedimientos almacenados.

Una vez configurado el catálogo ya podemos subir paquetes de varias formas distintas.

  • Generar un .ispac desde visual studio y desplegarlo con el SSIS Deployement Wizard
  • Desde el propio Visual Studio
  • Desplegando solo un paquete y no toda la solución

Generar un fichero .ispac

Para hacer esto en nuestra solución de SSIS vamos podemos pulsar el botón build (compilar), Build Solution. Podemos hacerlo a nivel de solución o a nivel de proyecto, dependiendo de si tenemos más de un proyecto o no. En cualquier caso en el directorio bin\xx donde xx es el entorno donde estemos trabajando (normalmente development ) encontraremos un fichero de extension .ispac. Ese fichero, para los mas curiosos, no es mas que un fichero comprimido en formato zip que contiene todos los paquetes de SSIS y algunos metadatos para su despliegue.

Normalmente la extensión .ispac ya se encuentra asociada al SSIS Deployment Wizard, pero siempre puedes buscarlo en las aplicaciones y abrir el fichero.  En resumen, podemos enviar ese fichero .ispac por nuestro mecanismo favorito a quien tenga que hacer el despliegue (incluso si somos nosotros mismos) y quien lo recibe simplemenete haciendo doble click recibirá un wizard en el que pide todos los valores de despliegue.

En otro artículo intentaré contar la parte de ambientes y su configuración, de momento aquí vamos a hacer el proceso más simple, subir los paquetes, y ejecutarlos.

Desplegar paquete

En esta primera pantalla vamos a ver que se puede seleccionar el fichero y si el origen es un catalogo (para mover paquetes de un catálogo a otro) o un proyecto (como en nuestro caso) que no es otra cosa que el fichero .ispac del que venimos hablando.

También podemos ver como nos permite seleccionar entre Project Deployment y Package Deployment. Bien , la diferencia es la que esperas, que se despliegue todo el proyecto o solamente un paquete de los que incluye, en cuyo caso hay que decir que paquetes. Yo no suelo usar esta opción, pero no es por nada en especial, solamente por la forma en la que suelo trabajar con esta herramienta.

Cuando se pulsa siguiente, se valida los paquetes, que todo esté correcto y pide el destino.

En el path podemos usar el botón browse para crear nuevas carpetas y de esa forma organizar nuestros proyectos y despliegues en carpetas del catalogo.  Despues de esto, poca cosa hay que hacer puesto que simplemente muestra un resumen de la operación que se va a hacer y posteriormente hace el desplieuge mostrando el avance como puede verse en la siguiente figura

Al acabar simplemente podemos acercarnos a nuestro menú catalog en SQL Server Management studio y comprobar que nuestro proyecto está desplegado

y ahí simplemente botón derecho sobre cualquier paquete y ejecutar.

Agendar un job

También podemos agendar en un job el paquete para que se ejecute. Para ello simplemente en el step del job decimos que es de tipo SQL Server Integration Services Package, indicamos en que servidor está el catálogo y que paquete es el que hay que ejecutar y generamos el agendado como cualquier otro paquete.

El menú que aparecerá será el que puede verse en la siguiente figura.

Si nos fijamos en la figura el job se ejecutará bajo las credenciales del SQL Server Agent Service Account. Esto es el primer reto que se puede encontrar toda persona «nueva» en el mundo del agendado. El paquete puede funcionar en Visual Studio, y cuando vas a agendarlo no funciona ¿porqué? pues porque cuando lo ejecutas en Visual Studio, corre con tus credenciales, mientras que cuando lo ejecuta el agente corre con las credenciales que tenga el propio SQL Server Agent (las de la cuenta que levanta el servicio) y puede que no sean suficientes.

¿y cual es la solución? Pues hay 2 soluciones

  • Asignar permisos suficientes al agente.
  • Crear un proxy con un usuario que si tenga esos permisos y asignar la ejecución a ese proxy.

Para la primera parte no creo que se necesite mucha ayuda, básicamente, averigua cual es la cuenta con la que se ejecuta el agente (puedes mirarlo en los servicios) y después asigna los permisos a esa cuenta, ya sea en directorios de Windows, rutas compartidas o bases de datos.

Para crear el proxy sin embargo hay un poquito mas de trabajo

  1. Crea unas credenciales  que tenga los permisos suficientes (puede ser un usuario de windows) como puedes ver en la figura siguiente – se hace desde el módulo de seguridad, menú Credentials, crear nueva credencial. Puedes encontrar el menú en SQL Server Management Studio.
  2. Crea el proxy. para eso, dentro del SQL Server Agent, Proxies, SSIS package Execution, new proxy y asigna las credenciales creadas como puedes ver en la siguiente figura
  3. En el paso del job, selecciona la cuentra proxy adecuada para la ejecución del paquete como puedes ver en la siguiente figura.

Conclusiones., Aunque hemos visto solamente los pasos más básicos sin entrar en detalles de configuración podemos empezar a desplegar nuestros paquetes de SSIS y conseguir que se ejecuten sin problemas de permisos. En breve publicaremos un artículo, también introductorio sobre como se crean estos paquetes.

Saludos

 

 

 

Alcance de los niveles de aislamiento

Cuando se ejecuta el comando set transaction isolation level para fijar el nivel de aislamiento a un valor ¿hasta donde queda fijado ese valor? es para la conexión hasta el final de esta?

Lo cierto es que en mi opinión se comporta como debe comportarse, es decir, manteniendo ese nivel de aislamiento en el ámbito en el que se haya ejecutado.

Imaginemos la siguiente circunstancia. Tenemos un procedimiento almacenado que establece el nivel de aislamiento a un valor, al ejecutarlo y terminar ¿como queda?

Lo mejor siempre es probar. La primera de las pruebas sería ejecutar el procedimiento y comprobar cual es el nivel de aislamiento dentro del procedimiento y al salir de él

Para eso la tabla sys.dm_db_exec_sessions tiene la información necesaria.

 

El query devuelve cual es él nivel de aislamiento de la conexión actual y de esta forma podremos comprobar como se ha comportado.

En la ejecución inicial devuelve el nivel de aislamiento por defecto que es Read Committed.

para comprobar el comportamiento vamos a crear un procedimiento almacenado que cambie el nivel de aislamiento y vamos a ver que devuelve.

Una vez instalado vamos a obtener cual es el nivel de aislamiento actual con el primero de los queries, ejecutar el procedimiento almacenado y volver a obtener el nivel de aislamiento actual

 

Podemos comprobar como dentro del procedimiento el nivel de aislamiento ha cambiado mientras que fuera se mantiene.

La otra duda es que pasa si el procedimiento almacenado a llamase a un segundo (que estaría por tanto embebido en la ejecución del primero. 

para eso simplemente creamos un procedimiento almacenado que solamente tiene la instrucción que nos devuelve el nivel de aislamiento actual.

el pseudocódigo sería

  • Obtener aislamiento actual
  • Procedimiento almacenado que lo cambia y llama a otro
  • Segundo procedimiento comprueba nivel de aislamiento actual
  • Terminan procedimientos
  • Obtener aislamiento actual

Y el resultado es

 

Conclusión

En una conexión si lo primero que se hace es cambiar el nivel de aislamiento, éste permanecerá invariante dentro de toda la conexión, Sin embargo si es un trozo de código tipo procedimiento almacenado el que hace ese cambio, su ámbito se reduce a la vida del procedimiento almacenado.