Bloqueos problematicos

[cancha]

Hola amigos,

tengo un cliente que su DB usada por un aproximado de 60 clientes(pc), antes que yo llegara me comentaron que tenían muchos problemas con lentitud y bloqueos

el sistema que usan esta hecho en vb6 con sql server 2008

lo primero que hice fue identificar las consultas que mas demoraban y usando el analizador de planes de ejecución me recomendaba crear indices para mejorar las consultas los fui creando conforme identificada las consultas problemáticas y veía su plan de ejecución y las recomendaciones que sugería y posteriormente casi 2 semanas no hubo mucha incidencia de bloqueos, pensé que los indices ayudaron mucho..

pero luego volvió a parecer el problema y el tiempo de espera total llegaba hasta en algunos días a 5 horas, adjunto un gráfico con los tiempos de espera total por día



aquí algunas imágenes de días específicos la barra de color plomizo representa los bloqueos






en el tiempo que se presentaron los problemas y bloqueos agregue unos cuantos indices mas, los problemas empezaron al día siguiente que agregue algunos indices poco a poco fue aumentando y haciéndose mas larga los tiempos de espera por bloqueo, creen que pueda ser los indices la causa de esto?

use esta consulta para identificar los indices que podrían ayudar a mejorar las consultas

Código SQL:

SELECT
db_name(d.database_id) AS DB,
object_name(d.object_id, d.database_id) tabla,
s.avg_user_impact, s.user_seeks,
d.equality_columns, d.inequality_columns, d.included_columns,
p.ROW_COUNT, l.num_esperas, l.ms_esperas, s.last_user_seek,
create_index = REPLACE('create nonclustered index IX_' +
object_name(d.object_id, d.database_id) +'_A# on ' +
object_name(d.object_id, d.database_id) + ' (' +
isnull(d.equality_columns + ',', '') + isnull(d.inequality_columns, '') + ') ' +
isnull('include (' + d.included_columns + ')', '') +
' with(online = on)', ',)', ')')
FROM
sys.dm_db_missing_index_details d LEFT JOIN
sys.dm_db_missing_index_groups g ON d.index_handle =g.index_handle LEFT JOIN
sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle LEFT
JOIN
sys.dm_db_partition_stats p ON d.object_id = p.object_id AND p.index_id < 2 LEFT JOIN
(SELECT
database_id,
object_id,
ROW_NUMBER() OVER (partition BY database_id
ORDER BY SUM(page_io_latch_wait_in_ms) DESC) AS ROW_NUMBER,
SUM(page_io_latch_wait_count) AS num_esperas,
SUM(page_io_latch_wait_in_ms) AS ms_esperas,
SUM(range_scan_count) AS range_scans,
SUM(singleton_lookup_count) AS index_lookups
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)
WHERE page_io_latch_wait_count > 0
GROUP BY database_id, object_id) l
ON d.object_id = l.object_id AND d.database_id = l.database_id
WHERE
d.database_id = db_id()
AND s.last_user_seek > dateadd(dd, -7, getdate())
ORDER BY --floor(s.avg_user_impact) desc,
s.user_seeks DESC
 

y también me ayude del analizador de plan de ejecución que muchos indices que sugería el analizador estaban en el resultado de esa consulta

también realice cambios en los planes de mantenimiento de diariamente reorganizar los indices y 2 veces por semana regeneración de indices

aquí tengo los archivos de los estados de fragmentacion de los indices antes que se ejecutaran los planes

http://dl.dropbox.com/u/5247806/indicesFragmentados.xls

y tengo esta información que saque con la vista dm_db_index_operational_stats
según tengo entendido la columna page_lock_wait_in_ms representa el tiempo de bloqueo y tengo una que llega a los 48 minutos esta en la primera fila...

http://dl.dropbox.com/u/5247806/estadoIndices.xlsx

quizás sea el indice el que causo ese cambio en en los tiempos de espera no tengo seguro eso si alguien podría confirmarme o decirme si hay algo anomalo en la información que comparto en esos documentos

y por ultimo la otra alternativa que estoy planteando es usar versionamiento de filas(READ_COMMITED_SPANSHOP en ON) y evitar esos bloqueos la mayoría son bloqueos compartidos creo que esto se da mas por las consultas select que crean bloqueos compartidos

no eh tenido experiencia con esto si alguien ah tenido por fa que me comparta su experiencia si esto podría mejorar notablemente la performance y el tema de los bloqueos

si es necesario mas información pídanme para postearlo por favor

gracias saludos

[iislas]

Lo que debes hacer, es RESOLVER EL PROBLEMA DE RAIZ, ¿como?, identificando los procesos que te generan interbloqueos y solucionarlo.

Los bloqueos (o interbloqueos) son por mala programacion en las transacciones

http://msdn.microsoft.com/es-es/library/ms188246.aspx

[matanga]

Los bloqueos (o interbloqueos) son por mala programacion en las transacciones

100% de acuerdo, los interbloqueos son por errores en el diseño de las transacciones y se producen cuando hay dos o más operaciones de escritura simultáneas, en estos casos, habilitar Row Versioning no resuelve ni reduce el problema ya que es una opción para bloqueos entre escritura-lectura o lectura-escritura.

Saludos

[cancha]

Iniciado por matanga

100% de acuerdo, los interbloqueos son por errores en el diseño de las transacciones y se producen cuando hay dos o más operaciones de escritura simultáneas, en estos casos, habilitar Row Versioning no resuelve ni reduce el problema ya que es una opción para bloqueos entre escritura-lectura o lectura-escritura.

Saludos

Entiendo, pero no son muchos los problemas de interbloqueo, como ven el gráfico
me reporta tiempos de espera por bloqueos compartidos (LCK_M_S) osea el tiempo que se quedan esperando las otras consultas para que libere el recurso y se pueda usar, es este el mayor problema, las columnas pintadas de color medio plomo representan los tiempos de espera por LCK_M_S, y bueno el aplicativo es grande y la cantidad de código que se tendría que modificar también llevaría tiempo, a eso le sumamos que no se cuenta con un equipo de desarrollo, que den soporte a esto, por ello considere la opción de activar el versionamiento de fila

para evitar esos tiempos de espera por bloqueo compartido, los bloqueos compartidos no son interbloqueos o me equivoco ? lo que me dices del versionado de filas es que evita el bloqueo de lectura a escritura y viceversa, y no evita el caso de lectura a lectura? lo que pasa que son los select los que bloquean a otros select para este caso en particular

tengo el nivel de aislamiento read_commited en las transacciones

y lo otro que eh visto es que hay selects dentro de transacciones cuando solo las transacciones deberían de usarse solo para operaciones que modifiquen datos correcto? no entiendo porque habría que ponerle selects dentro de transacciones? esto es lo que creo que causa esos bloqueos al estar los select dentro de Begin trans, bloquean la tabla hasta que el select termine, cuando esto no es necesario y casos como esto hay por varios sitios del aplicativo, como no hay developers y para no demorar mas tiempo (los usuarios quieren soluciones rapidas) considero activar el versionado de filas, pero si solo evita el bloqueo de lectura a escritura y viceversa, mas no de lectura a lectura no solucionaría el problema?

Saludos

[iislas]

Hasta un simple SELECT, bloquea.....

[cancha]

Iniciado por iislas

Hasta un simple SELECT, bloquea.....

entonces seria conveniente activar el versionado de filas (reducir bloqueos compartidos)