row-locks en operaciones SQL-Insert/Update/Delete

Este es un tip que me paso un cuate llamado Christian que es DBA en SQL Server. Para no poner de rodillas a su servidor de base de datos SQL Server

Un link de referencia adicional es:
http://support.microsoft.com/kb/323630

"A veces por cuestiones de performance o necesidad de trabajar con un conjunto de registros grandes, es conveniente hacerlo parcialmente, es decir, en "pedazos". Esto puede suceder porque al trabajar con millones de registros se generen demasiados row-locks y consuman toda la memoria y el query falle en su ejecución."

Aquí un query de ejemplo:


SET NOCOUNT ON --QUITAMOS LOS MENSAJES DE CONTEO DE REGISTROS AFECTADOS YA QUE LO PERSONALIZAMOS

DECLARE @Afectados bigint --DECLARAMOS UNA VARIABLE PARA ACUMULAR LOS REGISTROS AFECTADOS

DECLARE @Afec bigint --DECLARAMOS UNA VARIABLE PARA SABER CUANTOS ACUMULO EN CADA PASO

SET @Afectados = 0 --INICIALIZAMOS EL ACUMULADO

Borra: --ETIQUETA PARA SALTO

SET Rowcount 1000 --ESTA SENTENCIA ES LA MAS IMPORTANTE: LE DECIMOS AL SQL QUE LA SIGUIENTE SENTENCIA SOLO AFECTARÁ A 1000 REGISTROS (AUNQUE EXISTAN MÁS)!!!!!!.

DELETE Facturas

WHERE Fecha_factura < '2007/01/01' --EJECUTAMOS NUESTRA SENTENCIA, ESTA ES SOLO UN EJEMPLO Y SUPONDREMOS QUE LA SENTENCIA POR SÍ SOLA AFECTARÍA MILLONES DE REGISTROS.

SET @Afec = @@Rowcount --OBTENEMOS CUANTOS REGISTROS AFECTO (SOLAMENTE AL FINAL QUE YA NO HAYA REGISTROS POR AFECTAR DEVOLVERÁ CERO, DE LO CONTRARIO DEVOLVERÁ LOS 1000 QUE CONFIGURAMOS)

SET @Afectados = @Afectados + @Afec

print 'Afectados: ' + CONVERT(varchar,@Afectados) --AQUÍ VAMOS IMPRIMIENDO EL ACUMULADO DE LOS REGISTROS QUE VA AFECTANDO PARA DARNOS UNA IDEA DE CUANTO LEVA. (Ver pantalla resultado al final)

if @Afec > 0 goto borra --SI EL NUMERO DE REGISTROS QUE AFECTO EL DELETE ES MAYOR A CERO (ES DECIR QUE AUN HAY MÁS REGISTROS POR BORRAR) REGRESA A EJECUTAR EL DELETE.

set rowcount 0 --RESETEAMOS EL ROWNCOUNT PARA QUE YA AFECTE NORMAL


El resultado se vería como la siguiente imágen: