En ocasiones nos encontramos con clientes que tienen problemas serios para ejecutar ciertas operaciones DDL cuando la tabla afectada tiene cientos/miles de millones de filas o bien tiene un tamaño total de varios cientos de gigas.

Un caso típico es el de querer modificar el esquema de alguna de estas tablas de gran tamaño. Por ejemplo, se desea añadir una columna nueva o modificar el tipo de datos de una ya existente. También nos encontramos con problemas importantes cuando se quiere crear un índice cluster en una tabla de estas características que previamente estaba organizada como un heap y además cuenta ya con varios índices non-clustered. En este último escenario si lo pensamos bien tenemos que realizar una labor muy pesada de entrada/salida debido al «reapuntado» de todas las estructuras, pasando de utilizar un puntero RID a la nueva clave del índice cluster.

Tanto en un caso como en otro el problema que tenemos es que tenemos una operación que se debe ejecutar de forma atómica y que involucra el realizar una alta cantidad de entrada/salida. La entrada/salida en la mayoría de entornos es el cuello de botella. Por tanto, cuando le pedimos «un poco más» de lo habitual en alguna operación masiva es fácil que saturemos dicho subsistema.

Para complicar las cosas estas tablas pueden estar involucradas en algún sistema 24×7 por lo que no disponemos de una ventana de mantenimiento en la que podamos «cerrar» el acceso a las aplicación y usuarios. En ocasiones, aunque dispongamos de una ventana, ésta no es suficientemente amplia para poder abordar este tipo de operaciones que pueden llevarnos varias horas.

Ante estas situaciones una posible solución es la de disponer de una especie de «proxy/interfaz» que nos permita seguir operando habitualmente, realizando nuestras SELECT, nuestros INSERT, UPDATES y DELETES mientras se ejecuta la operación. Vamos a ver con un ejemplo cómo aplicaríamos esta técnica. Imaginemos que tenemos la tabla A cuya definición es la siguiente:

-- Tabla existente
CREATE TABLE [dbo].[A](
	[ID] [INT] IDENTITY(1,1) NOT NULL,
	[data] [NVARCHAR](200) NOT NULL,
	[fecha] [DATE] NOT NULL
 CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)
)
GO
INSERT INTO dbo.A (data,fecha) VALUES ('data',GETDATE())
INSERT INTO dbo.A (data,fecha) VALUES ('data1',GETDATE())
INSERT INTO dbo.A (data,fecha) VALUES ('data2',GETDATE())

SELECT * FROM dbo.A

batch_1

Podemos ver como el plan de ejecución de la consulta únicamente hace un scan de la tabla A:

batch_1_plan

El siguiente paso será preparar la nueva tabla que ya tenga la nueva columna (newcol) definida:

-- Nueva tabla
CREATE TABLE [dbo].[C](
	[ID] [INT] IDENTITY(1,1) NOT NULL,
	[data] [NVARCHAR](200) NOT NULL,
	[fecha] [DATE] NOT NULL,
	[newcol] [INT] NOT NULL
 CONSTRAINT [PK_C] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)
)

Llegados a este punto tendremos que realizar varias acciones para crear el «proxy» para la tabla A. Lo que vamos a hacer, de forma transaccional, es lo siguiente:

  1. Renombrar la tabla A a tabla B
  2. Asignar el seed de la columna identity de la tabla A (renombrada a B) para que continúe la secuencia sobre la tabla C (la nueva)
  3. Crear una vista A que incluya los datos de la tabla B y la tabla C. En esa vista adaptaremos la query sobre B para que devuelva la columna «newcol» con un valor constante.
  4. Añadir un trigger de tipo INSTEAD OF sobre la vista A. Este trigger manejará las operaciones de inserción, borrado y actualización que se realicen mientras dura el proceso de trasvase de datos

El siguiente script incluye los pasos del 1 al 4 dentro de una transacción:

BEGIN TRAN
-- Renombramos
EXEC SP_RENAME 'A','B'
GO
-- Fijar el IDentity en el valor actual + 1
DECLARE @IDentity INT = IDENT_CURRENT('dbo.B') +1
DBCC CHECKIDENT (C, RESEED,@IDentity)
GO
-- Creamos la vista
CREATE VIEW  [dbo].[A]
AS
SELECT *,0 newol FROM [B]
UNION ALL
SELECT * FROM [C]
GO

-- Creamos el trigger INSTEAD OF para las 3 operaciones
CREATE TRIGGER tr_temp ON dbo.A
INSTEAD OF INSERT,UPDATE,DELETE
AS
BEGIN
SET NOCOUNT ON
DECLARE @OP char(1) -- 'I(NSERT)','U(PDATE)','D(ELETE)'

SET @OP = 'I'; 
IF EXISTS(SELECT 1 FROM DELETED) -- Es un update o un delete
	IF  EXISTS(SELECT 1 FROM INSERTED) 
		SET @OP='U'
	ELSE 
		SET @OP='D'

IF @OP = 'I'
BEGIN
	-- Insertamos solo en la nueva tabla
	INSERT INTO dbo.C (data,fecha,newcol)
	SELECT data,fecha,0 FROM inserted
RETURN
END

IF @OP = 'U'
BEGIN
	-- Actualizamos en las dos, no sabemos en cual esta el registro
	UPDATE B 
	SET fecha=inserted.fecha, data=inserted.data
	FROM dbo.B 
	INNER JOIN inserted ON inserted.ID=B.ID
	
	UPDATE C 
	SET fecha=inserted.fecha, data=inserted.data
	FROM dbo.C 
	INNER JOIN inserted ON inserted.ID=C.ID
	
RETURN
END

IF @OP = 'D'
BEGIN
	-- Borramos en las dos, no sabemos en cual esta el registro a borrar
	DELETE B
	FROM dbo.B 
	INNER JOIN deleted ON deleted.ID=B.ID
	
	DELETE C
	FROM dbo.C
	INNER JOIN deleted ON deleted.ID=C.ID

RETURN
END

END

COMMIT

Una vez tenemos ya creado el «proxy», el siguiente paso es probar que las operaciones de consulta, inserción, modificación y borrado funcionan correctamente. Mostraremos la operación, el resultado y el plan de ejecución. Obviamos la parte del plan de ejecución que corresponde con la ejecución del trigger por simplicidad:

-- Consultamos
SELECT * FROM dbo.A

batch_2

batch_6_plan

Podemos ver como ahora las consultas necesitarán acceder a ambas tablas para resolverse ya que el dato puede estar en la «tabla vieja» o en la «tabla nueva».

-- Insertamos
INSERT INTO A (data,fecha) VALUES ('data3','20150101')

batch_3

batch_2_plan

La inserción trabaja con la vista como si de una tabla más se tratara y, en el trigger, se insertará únicamente en la tabla nueva. Ya que vamos a mover todos los datos de la antigua tabla A (renombrada a B) a la tabla nueva C no tiene sentido añadir más datos a la tabla a vaciar.

-- Modificamos
UPDATE A SET data=data+'_2'

batch_4batch_3_planComo no sabemos en cuál de las dos tablas tenemos los registros a modificar podemos ver como se accede a las dos tablas para buscar los registros. De nuevo el trigger se encargará de modificar en la tabla que corresponda en cada caso. Como cruzaremos por ID, únicamente modificaremos los registros que estén en cada tabla.

 

-- Borramos 
DELETE A WHERE fecha < '20151001'
DELETE A WHERE ID=1

batch_5

batch_4_plan batch_5_plan

El caso del borrado es similar y se procesa eliminando los datos por ID. En el caso que utilicemos el ID para el borrado se podrá hacer un index seek para localizar los datos y si tenemos un predicado que no está indexado tendremos que realizar un scan de cada tabla para localizar los IDs a borrar.

Una vez tenemos esta vista funcionando como proxy, el siguiente paso es mover los datos de la tabla B a la tabla C. Para ello una posible solución es utilizar una operación DELETE y junto a la cláusula OUTPUT insertar en la tabla C los registros borrados de la tabla B de forma atómica. En nuestro ejemplo como además estamos utilizando valores identity necesitaremos forzar la propiedad INSERT_IDENTITY en la tabla destino para poder realizar la inserción. Para procesar en un mismo batch varias operaciones incluir este delete en un bucle para procesar de N en N filas:

SET IDENTITY_INSERT dbo.C ON
SELECT 1
WHILE (@@ROWCOUNT > 0)
BEGIN
DELETE TOP (1000) FROM dbo.B
OUTPUT deleted.id,deleted.data,deleted.fecha,0 INTO dbo.C (id,data,fecha,newcol)
END

Si la tabla es una tabla grande es probable que no sea suficiente con esta aproximación. Puede que necesitemos aumentar el número de filas a procesar en cada iteración (con lo que podríamos necesitar ajustar el escalado de bloqueos de las tablas) o podríamos necesitar lanzar este proceso concurrentemente desde varias sesiones (con lo que nos podríamos beneficiar de utilizar el hint READPAST). También podríamos necesitar añadir algún tipo de delay entre iteraciones para no saturar el sistema de entrada/salida.

Sabemos que nos estamos dejando en el tintero otras acciones que podrían ser necesarias, como podrían ser la gestión de las foreign key, de los permisos, de los trigger de la tabla original, etc. Es decir, normalmente el proceso no es tan sencillo como el ejemplo expuesto y hay que realizar un análisis de dependencias para ver la forma en la que se puede abordar el cambio de forma que sea lo menos intrusivo posible para la actividad 24×7. No está de más tener en cuenta que, si las operaciones de acceso a la tabla se realizaran ya actualmente mediante procedimientos almacenados (sin acceso directo a la tabla al menos para las modificaciones, borrados e inserciones), podríamos ahorrarnos el trigger y, en algunos casos, la vista. Utilizar procedimientos almacenados desde el punto de vista del DBA es una herramienta que nos facilita poder resolver este tipo de problemáticas ya que mientras mantengamos «el interfaz» de cara a la aplicación todo será transparente.

En resumen, en SQL Server existe a día de hoy una limitación importante para la realización de ciertas operaciones DDL en tablas grandes en un sistema 24×7. Esta problemática la tendríamos aunque hubiéramos particionado la tabla, algo habitual en tablas de este tamaño. Cualquier operación que adquiera un bloqueo exclusivo de esquema en una tabla durante un periodo de tiempo elevado es muy probable que nos dé problemas de bloqueos. En estos casos especiales debemos crear una solución adhoc como la que hemos expuesto en este post, testearla muy bien y planificar bien su ejecución en el entorno final de producción.

Rubén Garrigós