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….
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.
