viernes, 26 de agosto de 2016

Diferentes Status de una Sesión (Spid) en Sql Server // Scheduler Qué Es y Cuál es su Función?


Hola amigos…

Luego de unos meses en los cuales la actividad me impidió estar en contacto con Uds. quiero retomar el mismo, con la promesa de intentar no volver a interrumpirlo, a través de este artículo…

Gracias por los mails recibidos con preguntas, y todo tipo de cuestiones relacionadas con nuestra actividad. Como siempre, las voy respondiendo con toda la urgencia que puedo y con la privacidad requerida por uds.

Justamente quiero abordar un tema que suele ser muy frecuente en los correos que recibo. Tiene que ver con los distintos estados por los que una sesión – SPID - (proceso),   puede pasar hasta ser resuelta por el motor de base de datos. Para ello considero fundamental ahondar previamente en un concepto que pocos colegas parecen tener claro, hablo de qué es y qué función cumple el Scheduler en el motor de base de datos.

 Particularmente noto que el estado “Suspended” es el que más inquieta a los colegas.. Pero eso será objeto de un próximo post.

Hoy quiero concentrarme en una breve pero explícita explicación de los posibles estados de un SPID.
Un SPID es una sesión abierta para la ejecución de un proceso en nuestra base de datos.  Los SPID  1 al 49 están reservados para los procesos del sistema, el resto (del 50 en adelante), para los procesos de usuario.

Hay un concepto aquí que es fundamental entender y es el que tiene que ver con el SCHEDULER.
Qué es el scheduler? Básicamente es un proceso madre encargado de Organizar y Asignar a cada Spid un hilo de ejecución (Worker Thread).

Cada Scheduler puede estar corriendo una, y solo una,  tarea activa. El resto de las tareas son puestas por el scheduler en una cola de espera, que da origen, como verán luego, a algunos de los status por los que un proceso puede pasar.

Pero cuántos Scheduler hay en nuestro motor de base de datos?. Uno por cada procesador lógico (no físico).  Es decir que si vuestro equipo tiene tan sólo un procesador, pero tiene habilitado  hyperthreading, van a existir dos Schedulers.

 Una consulta para entender los schedulers presentes en nuestro sistema? Pues claro que si.. Microsoft nos asiste con la siguiente vista a saber:
SELECT * FROM sys.dm_os_schedulers WHERE STATUS = ‘VISIBLE ONLINE’

Entonces, y pasando en limpio, el Scheduler es quien se encarga de asignar a cada spid un hilo de ejecución del cpu. Es un intermediario entre los procesos y el cpu.

Pensemos en estos términos. Supongamos que vamos de compras a un mercado en el que solo 2 operarios están atendiendo (procesadores lógicos).  Pues bien, sería un caos si todos los clientes vamos a abordar a los pobres vendedores al mismo tiempo verdad?. Por suerte hay dos encargados que reparten números para que nosotros podamos ser atendidos, cada uno a su tiempo. Estos dos encargados en el contexto del sql server serían los Schedulers).

Recordemos que los procesos que se están ejecutando actualmente en nuestro SQL pueden ser consultados entre otras herramientas por medio del sp_who2 o la vista del sistema sys.sysprocesses. (por más detalles consultar en este mismo blog en post anteriores).

Vamos a la explicación de los estados entonces. Si bien hay mucha bibliografía al respecto noto por las consultas recibidas que muchos colegas necesitan de una explicación más amena que y simple que la de Microsoft da en su web.

Vamos con la explicación entonces al estilo Sql Server Para Todos. Es decir, sencilla y en castellano para todos los hermanos de Latinoamérica:

“Running”:  este estado es “música para nuestros oídos de DBA”,  puesto que implica que nuestro proceso está en plena ejecución, utilizando el thread asignado por el Scheduler y en camino de ser resuelto.

Runnable”: no confundir con “running”… Es un estado en el cual la sesión ha obtenido un worker thread (hilo de ejecución) por parte del Scheduler, mas está esperando la resolución de un proceso previo que libere al cpu y así poder utilizar el thread de trabajo que le fue asignado.  Pensemos en nuestro ejemplo el mercado… El cliente ya tiene el número, pero tienen que esperar que el vendedor se desocupe…

Pending”:  la sesión está esperando la asignación por parte del Scheduler de un Thread de Ejecución. El cliente está en el mercado esperando un nº para entonces si entrar en la cola y poder ser atendido por el vendedor…

Background”: generalmente un estado de los spids que corren “tras bambalinas” y que corresponden al sistema, no al usuario. Todos tienen un spid menor a 50 y no debiera de llamar nuestra atención de modo particular.

Sleeping”:  refiere a una sesión abierta cuyo proceso ya fue ejecutado y  no está desarrollando ninguna actividad en el server. Es típico ver este tipo de sesiones en programas mal diseñados o en usuarios que abren “new queries” constantemente en la consola del Sql Server sin cerrar las mismas una vez utilizadas. Ojo..toda sesión abierta, aún inactiva,  genera consumo de recursos, no debiera permitirse este comportamiento

Dormant” = el dormant es el estado en el cual una conexión hecha a través de un server linkeado permanece inactiva una vez que la misma deja de ser utilizada. El tiempo de permanencia de la sesión en este estado es de aproximadamente 4 a 5 minutos (antes de ser cerrada automáticamente por el SQL)

Rollback” =  indica que una transacción está en medio de un rollback. No recomiendo hacer ningún tipo de acción por más que el rollback se prolongue en el tiempo. Alguna vez he visto a un colega reiniciar el servicio de sql server pensando que eso iba a solucionar la demora.. Desde ya no es asi…

 “Suspended” = La session está ejecutándose (tal el caso de la sesión que muestra el valor running), mas está esperando por la liberación de recursos a nivel de I/O, memoria etc… Sin dudas es un estado que no debiera reflejar las constante de nuestro sistema y que merece un tratamiento aparte que, les prometo, he de abordarlo en mi próximo post.


Amigos espero que el post le haya sido de utilidad. Ya retomamos el contacto. Nos vemos próximamente con otro nuevo post. Sigan enviando sus mail que son muy bien recibidos (y por favor tengan paciencia y no olviden de apuntar la privacidad con la que quieren que sean contestados)..

viernes, 6 de noviembre de 2015

Extraer Un String De Longitud Variable Desde Dentro de Otro String o Cadena de Caracteres

Amigos,

   Todo DBA ha recibido alguna vez la pregunta de algún desarrollador, (con cara de pánico), del estilo "Che, vos que tenés experiencia en T-SQL.. Cómo hago para extraer un valor (supongamos dinero), que no siempre va a tener la misma longitud, desde dentro de un string?"

   Pues bien, ante todo lo importante es tranqulizar al desarrollador y explicarle las cosas con el siguiente ejemplo:

    1)  En un variable llamada @document,  tenemos un string dentro del cual debemos aislar  y extraer una cifra de longitud variable que se encuentra luego del string "fee=".
    Veamos...

    DECLARE @document varchar(64)
  SELECT @document = '123456;fee=260900;fee_SMT_Bycycle'
       
     La cifra a extraer es "260900" en este caso.

     La cosa no sería compleja si sabemos que siempre la cifra tendrá la misma longitud... (para el ejemplo 6 caractered).  Nos podríamos arreglar en ese caso con esta simple query que combina las funciones Substring y Charindex (cuya sintaxis excede el contenido de este artículo)

      SELECT SUBSTRING (@Document, (CHARINDEX('fee=', @document)+4), 6)

   Resultado  260900

    Pero dijimos que la cifra no siempre va a tener 6 caracteres, puede que tenga más o menos... Uyyyy pero esto es complicado!  No no amigos, a no marearse. La solución pasa por buscar un punto de referencia que nos indique la posición dentro de la cadena donde está el string (en este caso la cifra), que queremos extraer y otra referencia que nos marque la posición dentro de la cadena en donde finaliza dicho string (cifra).

       Ok .. manos a la obra...

       - Posición en donde empieza el string... Pues sabemos que empieza luego del "fee=". Con lo cual podemos obtener su ubicación con un simple charindex a saber:

   SELECT (CHARINDEX('fee=', @document)+4)  -- comienza en la posición 12


      - Posición donde termina el string... Aquí les propongo utilizar como referencia el ';' ubicado ni bien termina la cifra. 
          Y uds. me dirán... "Pero Gustavo, hay más de un punto y coma ';' en el string!!! Cómo haremos para saber la posición del ';' que da finalización a la cifra????" Y aquí es donde el tercer parámetro (opcional),  de la función CHARINDEX nos va dar una enorme mano... Cómo? Indicando que comience a buscar el ';' luego de la posición del string "'fee='. Veamos:

      SELECT CHARINDEX (';', @document, CHARINDEX('fee=', @document))  -- el punto y coma se encuentra en la posición 18

    - Ahora si... sacando la diferencia entre la posición de fin y la posición de comienzo del string a aislar (de la cifra para nuestro ejemplo), podremos extraerlo siemrpe más allá de su longitud. Cómo? Sencillo .. Utilizando la función SUBSTRING sobre la cadena. Marcándole como lugar de comienzo (CHARINDEX('fee=', @document)+4)y como longitud a abstraer aquella que nos da la la diferencia de los dos charindex utilizados es decir...  CHARINDEX (';', @document, CHARINDEX('fee=', @document))- (CHARINDEX('fee=', @document)+4)

     VAMOS YA MISMO LA SOLUCION DEL PROBLEMA PLANTEADO!! CON EL EJEMPLO COMPLETO:

-- String Inicial -- (desafío extraer la cifra de logitud variable luego del "fee="
DECLARE @document varchar(64)

SELECT @document = '123456;fee=260900;fee_SMT_Bycycle'


--Con el uso del Substring y los charindex explicados extraemos la cadena
select SUBSTRING (@document,  (CHARINDEX('fee=', @document)+4) ,  CHARINDEX (';', @document, CHARINDEX('fee=', @document))- (CHARINDEX('fee=', @document)+4))

RESULTADO 260900
      

  Amigos, les animo a que prueben la solución y me hagan las preguntas que crean pertinentes.

  Un saludo cordial desde Argentina.
         

miércoles, 29 de julio de 2015

Script Para Saber el Espacio Libre en Las Unidades de Disco de un Servidor

Gente,

    Quiero dejarles un script útil que les permitirá tener controlado el espacio disponible en disco siempre.

    Los valores que devuelve por unidad lógica son:   Gb Libres,  total de GB de la unidad y % de Gb libres restantes.

   Para ello deben primero configurar vuestro server para que les permita usar los “Ole Automation Procedures” que este sp utiliza.

   “Go down on business” entonces :



      -------------------------------------------
   -- Habilita - "Ole Automation Procedures --
   -------------------------------------------
    
       sp_configure 'show advanced options',
       GO  
       RECONFIGURE; 
       GO 
       sp_configure 'Ole Automation Procedures',
       GO  
       RECONFIGURE; 
       GO  
       sp_configure 'show advanced options',
       GO  
       RECONFIGURE;


     ------------------------------------------------
     -- Informa espacio libre en unidades de disco -
     -- Autor: GRH - Sql Server Para Todos         -
     ------------------------------------------------
    
      DECLARE
      @hr int,
      @fso int,
      @drive char(1),
      @odrive int,
      @TotalSize varchar(20),
      @MB bigint
     
      SET @MB = 1048576


      CREATE TABLE #drives (drive char(1) PRIMARY KEY,
      FreeSpace int NULL,
      TotalSize int NULL)

      INSERT #drives(drive,FreeSpace)

      EXEC master.dbo.xp_fixeddrives

      EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
      IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

      DECLARE dcur CURSOR LOCAL FAST_FORWARD
      FOR SELECT drive from #drives

      ORDER by drive
      OPEN dcur
      FETCH NEXT FROM dcur INTO @drive
      WHILE @@FETCH_STATUS=0
      BEGIN
         EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
         IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
         EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
         IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
         --
         UPDATE #drives
         SET TotalSize=@TotalSize/@MB
         WHERE drive=@drive
         FETCH NEXT FROM dcur INTO @drive
      END
      CLOSE dcur
      DEALLOCATE dcur
    
      EXEC @hr=sp_OADestroy @fso
      IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
             

          El resultado obtenido será el siguiente:
  


         ** TIP -  Pueden guardar estos datos en una tabla y medir de ese modo el % de crecimiento de utilización de vuestros discos duros proyectando el espacio necesario a futuro.
                        Pueden armar una alerta que, cuando el espacio disponible en disco caiga por debajo de x gb dispare un correo.


         Eso es todo amigos, amigable y sencillo.

          Saludos cordiales desde Argentina.


martes, 7 de julio de 2015

Pasando Datos Numéricos Largos Desde Sql Server Hacia Excel (evitar el truncamiento)

Amigos,

Me ha llegado una consulta desde la hermana República de Panamá. Don Omar Alfanno me cuenta de un inconveniente que tiene "al bajar datos desde el sql server al excel, cuando intento copiar y pegar la columna con mis números telefónicos, pues veo que los últimos tres dígitos se truncan y el Excel los cambia por tres números ceros. No encuentro la solución puede ud. ayudarme?"

Respuesta:

Ante todo don Omar quiero contarle que es un error con el que todos quienes trabajamos alguna vez con el SQL Server nos hemos topado y es de muy fácil resolución.

Simplemente debe (y en este orden, de lo contrario no funciona)

1) Abrir el excel y darle formato texto (text) a las columnas en las cuales ud. quiere pegar sus datos numéricos extensos.

2) Luego si copiar y pegar los datos desde el sql server en el excel.

Es muy importante que no invierta el orden, le repito. No pegue para luego cambiar el formato porque sus datos serán truncados.

Eso es todo, mi saludo desde Argentina.

lunes, 29 de junio de 2015

Bloqueos en Sql Server (Qué Son / Cómo Detectarlos y Monitorearlos / Cómo Minimizar su Ocurrencia)

Amigos,

     Como DBA’s de una compañía todos alguna vez hemos escuchado la famosa frase “el sistema está lento” o bien, de boca de un desarrollador, “por favor me puedes dar una mano, el sistema tira time out y tengo muchos usuarios furiosos, no entiendo que pasa”…

     Desde ya que uno supone que, cuando el reclamo ha llegado a nuestra área, se han descartado o bien se están descartando,  todo un abanico de cuestiones que exceden a nuestra función  tales como, problema de conexiones saturadas o lentas, problemas de hardware, etc. etc.

     Hablando de Base de Datos, no siempre la lentitud  está directamente relacionada con la existencia de constantes bloqueos, pero si es nuestra responsabilidad tratar disminuir al mínimo posible los mismos.

     Para ello, sin querer aburrirlos con teoría, les propongo:

-          Un poco de teoría (lo mínimo para entender realmente este importante tema)
-            ontSistematizar rar la forma de detectarlos (queries y algo más..)
-          Aprender tips para minimizar su ocurrencia

Lo prometido, un poco de teoría…

    Empecemos por definir qué es un Bloqueo?...

    Pues bien, en términos sencillos, un bloqueo es aquella demora que se produce en el acceso a un recurso de una base de datos (tablas, índices, etc),  cuando dos procesos “compiten” por tener la exclusividad sobre el mismo. En este caso se produce el famoso “bloqueo” que hace que uno de los procesos deba esperar a que el otro termine de utilizar el recurso, para poder hacer uso del mismo.

    Pero por qué un proceso querría tomar la exclusividad sobre un recurso? Pues bien… para entender ello debemos repasar los dos principales tipos de Locks del SQL Server … (hay otros derivados de ellos pero que no van a ser abordados en el presente art.)

    Muy brevemente ..

·         Shared Lock (S): es el loqueo que establece cualquier SELECT  sobre una tabla (lectura).

·         Exclusive Lock (X): es el nivel de lockeo necesario para cualquier instrucción de modificación de data (INSERT/UPDATE/DELETE).

    De la existencia de estos dos tipos de lockeos es fácil inferir que, si un usuario “A” está realizando un SELECT sobre una tabla,  y otro usuario “B”  quiere hacer una modificación sobre los datos de la misma (INSERT/UPDATE/DELETE),  “B”, tendrá que esperar a que “A” termine dicha operación de lectura, quedando momentáneamente BLOQUEADA la tabla.
    Del mismo modo… , si un usuario está modificando los datos de una tabla, los demás usuarios que deseen hacer desde una simple lectura hasta una modificación de  datos de esa tabla deberán esperar, puesto que, como hemos dicho.. todo INSERT/UPDATE/DELETE requiere de un lockeo exclusivo de la tabla…

    Pero por qué Microsoft  ha dispuesto la necesidad de “lockeos exclusivos” ? Tranquilos amigos, no se trata de mentes malvadas, se trata de que SQL SERVER es una base de datos relacional que toma este tipo de comportamiento para asegurarnos la integridad de la data. (es decir, que uno lea datos reales, no datos que ya no existen o han sido modificados al momento de la lectura)

   Se imaginan Uds en caso contrario, qué ocurriría si un usuario “A” está consultando el stock de un artículo cuyo stock  está siendo modificado  por otro usuario “B” al haber hecho una venta del mismo?. Pues el usuario “A” contaría con un estado falso del stock… ya que fue modificado por el usuario “B”. Eso es lo que se llama una “lectura sucia” y es lo que el sql server por default nos evita.

   Uds me dirán “Entonces es normal que existan bloqueos?”. Pues bien, todo sistema de base de datos tiene bloqueos, el problema es cuando los mismos son prolongados,  ya que esto produce  encolamientos y una degradación general todas las operaciones que afectan al sistema

    Y en este punto amigos es donde nosotros como DBA debemos intervenir…. Ya veremos cómo, no sin antes hablar de algo directamente relacionado con el tema que nos convoca… el “Isolation Level” (nivel de aislamiento en castellano).
   No es otra cosa más que un “seteo” que determina el grado de concurrencia simultánea con el que los datos pueden ser accedidos dentro de una sesión de Base de Datos.

   Por defecto, el SQL Server viene seteado con un “isolation leve”l llamado “READ COMMITED” . Este nivel de aislamiento no permite lecturas sucias.  Es decir, si “A” quiere leer o modificar un recurso que “B” está modificando, como explicamos anteriormente,  no puede hacerlo.

    Es posible sin embargo “setear”, por sesión o sólo para una determinada sentencia,  el Isolation Level para que permita lecturas sucias. Es decir,  lograr que por defecto ningún usuario al realizar una lectura establezca un lockeo, esto si vamos a la prática permitirá al proceso “A”  leer si “B” está modificando datos y, a la vez a “B” modificar datos si “A” está leyendo. Este tipo de seteo se llama “READ UNCOMITTED” y es el más laxo de los isolation levels, aptos para entornos donde la integridad de la data no sea un tema a tener en consideración.
-   Seteando una query de lectura para permitir lecturas sucias:

       select idarticulo from articulos with (nolock)

-           Seteando el isolation level de una sesión para permitir lecturas sucias:

      SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    Existe un tercer tipo de isolation level  que elimina un efecto llamado “REPETABLE READ”. Vamos a un ejemplo para una mejor comprensión. Supongamos que una transacción bancaria necesita leer el saldo inicial del cliente, para, a partir del mismo,  realizar cálculos y la adición de un nuevo registro con los intereses bancarios que modifica el saldo del cliente leído. Luego, como parte de la misma transacción, necesita volver a leer el saldo original dado que necesita insertar otro registro con x gasto bancario relacionado con el saldo original del cliente, (saldo que ya fue modificado como producto de la inserción previa de los intereses). Pues bien, la única forma de que esta transacción pueda contar con el saldo original es seteando la misma dentro un isolation level llamado “REPETABLE READ”, de lo contrario no podrá recuperar el saldo original. El saldo hasta finalizada la transacción siempre será el original si bien en la práctica ya  fue modificado por la adición de intereses.

-         -       Seteando el isolation level de una sesión:

    SET TRANSACTION ISOLATION LEVEL REPETEABLE READ

   Por último tenemos el más restrictivo de los Isolation Level, es llamado “SERIALIZABLE”. En este nivel de aislamiento el lockeo es sostenido durante toda la transacción, no pudiendo modificarse ni leerse dato alguno hasta que la misma no finalice.

-         -   Seteando el isolation level de una sesión:

   SET TRANSACTION ISOLATION LEVEL SERILIZABLE
 
    Pero como bonus, el SQLSrv nos entrega un Isolation Level que es muy interesante llamado “READ_COMITTED_SNAPSHOT”.  Este nivel de Isolation permite leer sin establecer lockeos, pero no nos entrega una lectura sucia (ahí lo interesante de este nivel). Bajo este isolation el sql server toma el último estado consistente (comitido) de un dato al comienzo de la lectura del mismo. Cómo lo hace? Leyendo desde la TempDb esta última versión consistente del dato….  Es decir que la integridad del dato está garantizada a nivel de sentencia sin establecer lockeos. Interesante no?. A tenerlo en cuenta ya que es muy útil…

-         - Seteando el isolation level de una sesión:
  
 SET TRANSACTION ISOLATION LEVEL READ_COMMITTED_SNAPSHOT


 Cómo Monitorear y Detectar Bloqueos ?

1) Utilizando la tabla del sistema sysprocesses


Ejemplo:


             Aquí la columna “blocked” nos dice qué proceso está causando el bloqueo. Para nuestro ejemplo el proceso 53 está siendo bloqueado por una requisitoria hecha por el proceso 52.


              Ahora pues podemos averiguar qué procesos están corriendo utilizando el comando DBCC INPUTBUFFFER, veamos:



   Si  quisiéramos entender qué tipo de lockeo está sosteniendo el proceso 52 …utilizamos SP_LOCK..


   Como podemos ver en la fila sombreada, el proceso 52 tiene un  intento de lockeo exclusivo permitido (IX), por lo tanto el proceso 53, que quiere hacer una lectura tiene que aguardar….


 2)      utilizando las vistas dinámicas del sistema

     A partir del Sql Server 2005 contamos con estas vistas que nos dan un nivel mayor de información que las viejas tablas del sistema.

     En este caso vamos a utilizar dos vistas a saber:

   sys.dm_exec_sessions: es una vista que, a nivel de server nos otorga información acerca de todas las conexiones de usuarios abiertas así como también las tareas internas del sistema (spid menor a 50).
   sys.dm_exec_requests: es una vista que nos devuelve información de cada solitud (request) que está pasando por el servidor.
   sys.dm_exec_sql_text: que actúa como una función que nos devuelve el texto del batch que se está ejecutando (identificado como sql_handle).

  Ahora que sabemos que función tiene cada vista, les cuento que las mismas pueden usarse combinadas con joins para encontrar en detalle:
·         El proceso bloqueado .
·         El proceso que está bloqueando.


Nada mejor que un buen ejemplo para poder entender su funcionamiento:
Vamos crear una tabla llamada “bloqueo” donde intentaremos insertar un dato y obteniendo pues el lockeo exclusivo de la tabla, el cual vamos a retardar con un waitfor delay de 2 minutos


-- creo la tabla y le insterto un registro, luego un wait..--
begin transaction
insert into bloqueo (id)
values (1)
waitfor delay  '00:02:00'

  Ahora abrimos otra session (otra pestaña de nuestro sql server) y tratemos de leer la tabla que está siendo bloqueada

-- intento leer la tabla lockeada en modo exlclusivo --
select * from bloqueo

Y llegó el momento de abrir una tercer pestaña y ejecutar nuestra query, la cual les recomiendo, guarden en su “botiquín de primeros auxilios”

 La query es la siguiente:

---------------------------------------------------------
--  Detalle: Muestra bloqueos en nuestra base de datos --
--  1) La sesion que está bloqueando                   --
--  2) La sesión bloqueada                             --
---------------------------------------------------------

SELECT
er.session_id ,
host_name,
program_name,
original_login_name,
er.status,
er.reads ,
er.writes,
er.cpu_time,
wait_type,
wait_time,
wait_resource,
blocking_session_id,
st.text,
er.transaction_isolation_level
FROM sys.dm_exec_sessions es
--
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = es.session_id
--
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
--
WHERE
er.blocking_session_id > 0
Union
SELECT
er.session_id ,
host_name,
program_name,
original_login_name,
er.status,
er.reads ,
er.writes,
er.cpu_time,
wait_type,
wait_time,
er.wait_resource,
er.blocking_session_id,
st.text,
er.transaction_isolation_level
FROM sys.dm_exec_sessions es
---
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = es.session_id
---
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
--
WHERE es.session_id IN ( SELECT blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0 )

Veremos pues como resultado de nuestra query en primer lugar al proceso que encuentra el bloqueo, el proceso nº 61, el cual intenta leer la tabla de nuestro ejemplo previamente lockeada en modo exclusivo por el proceso 67.

En “text” tenemos las sentencias que forman parte de los bloqueos…















     3)  ESTABLECIENDO UN “BASELINE” MEDIANTE EL PERFORMANCE MONITOR

   El “performance monitor” en si no es una herramienta que nos permita detectar la query que está bloqueando y la query que está esperando por la liberación de recursos. Mas su principal utilidad está en obtener un “baseline”, es decir un número promedio, de lo que serían los bloqueos promedio de nuestro sistema de base de datos.

   Para ello debemos utilizar los contadores “LOCK request/dec” – “Avg Wait Time (ms)” – “Lock Waits/sec” and “Number of Deadlocks”

Ejemplo:




 Cómo Minimizar La Ocurrencia de Bloqueos ?

 Amigos, he aquí unos consejos breves y concretos para tratar de minimizar la cantidad y duración de los bloqueos en nuestra base de datos:

  La premisa es que nuestras queries “lean y liberen lo más rápido posible”  Esto lo lograremos:

1)      Haciendo que nuestros códigos devuelvan la mínima cantidad de datos posibles

1.1) No a los Select * from ¡!!! Seleccionemos sólo los campos estrictamente necesarios.

1.2) No a las queries poco selectivas que devuelven muchos más datos de los necesarios,     filtremos bien en los where!!!

1.3) Revisemos los códigos que identificamos como conflictivos y rehagamos aquellos que sean necesario rehacer.

2)       Dotando a las tablas de los índices necesarios

2.1)Estudiemos los planes de ejecución a fondo y optimicemos las queries dotando las tablas              los índices necesarios para el funcionamiento óptimo de las queries.

3)      Implementando rutinas de mantenimiento de índices y estadísticas.

3.1) En entornos OLTP (de mucha modificación de datos), los índices tienden a degradarse rápidamente. Se fragmentan y pierden efectividad. Es necesario implementar rutinas de optimización y mantenimiento de los mismos (en este blog encontraran un script para ello)

3.2) Las estadísticas son importantísimas para que nuestros planes de ejecución elijan la mejor alternativa para ejecutar una query. Tengan en cuenta que si bien Microsoft nos da la chance de setear su actualización automáticamente dicha actualización no es efectiva. Utilicen una rutina para tal fin. La pueden encontrar también en este blog.

4)      Utilizando el Isolation Level apropiado para nuestro negocio.

4.1) Hemos estudiado los distintos tipos de Isolation Level existentes. La idea es utilizar el que se adecue a las exigencias de nuestro sistema. Si por ejemplo estamos en un sistema que permite utilizar lecturas sucias sería una buena idea abolir los lock utilizando el nivel Read Uncommitted a nivel de sesión o con el hint with (no lock) luego del from.

4.2) Tal vez no podemos realizar lecturas sucias pero si utilizar el Isolation Level “READ_COMMITED_SNAPSHOT” y asi evitar lockeo y los posteriores bloqueos.

5)      Particionando tablas cuando sea conveniente hacerlo.

5.1) El particionar tablas puede agilizar mucho nuestras consultas en tablas que necesitan mantener grandes cantidades de datos históricos on line y que por lo tanto tienen varios millones de registros.  Si en este tipo de tablas, existen consultas donde se puede llegar a necesitar datos históricos y actuales de un cliente (por ejemplo la consulta histórica de la cuenta corriente de una empresa), pues bien, sería una buena idea particionar por año y generar, distribuir datafiles en distintos discos y generar lecturas paralelas. OJO NO ABUSAR de las PARTCIONES, ya que su mantenimiento es complejo (más de todo este tema en este blog)


6)      Ubicando correctamente los datafiles de nuestras bases de datos.

6.1)  El gran punto fuerte del SQL Server es la posibilidad de realizar lectoescrituras paralelas Para ello un escenario ideal es el tener separado en unidades físicas diferentes:
 - El OS
 - El motor de Base de Datos
 - Los archivos de Datos
 - Los archivo de índices
-  Los archivos de log.

6.2)  Traten siempre de que sea posible de lograr este escenario. No olviden una vez hecho esto el analizar la carga de los discos (i/o) valiéndose del perfomance monitor y, llegado el caso, dettachar y reubicar los files que sean necesarios.

6.3) Para asegurarse de que el SqlSrv está seteado para utilizar todos los procesadores disponibles controlen el valor de “Server Properties”àAdvancedàParallelismàMaxDegree of Parallelism, el mismo debe ser de cero (0)-

7)      Manteniendo en línea sólo la data necesaria

El realizar el pase a files “off line” o bien a backups, es una tarea que si la reglas de negocio la permiten debería ser planificada y ejecutada con periodicidad.


8)      Parametrizando consultas

Si podemos utilizar una consulta y llegar a ella con parámetros, vamos a poder reutilizar los planes de ejecución cacheados en memoria. Lo opuesto sería el utilizar la misma consulta pero con valores hardcodeados cada vez que la utilicemos.

9)      Manteniendo los discos desfragmentados

Es muy importante que tengamos corriendo en períodos de baja o nula concurrencia alguna herramienta eficiente de desfragmentado de nuestros hard disk.

10) Monitoreando permanentemente el nivel de actividad de nuestro servidor

Es vital la uilización del performance monitor y el event viewer y el establecimiento de baselines. Si vemos por ejemplo que constantemente nuestro sistema pagina, puede ser una buena idea revisar la memoria ram asignada a nuestro sql server.