Limpieza de primavera, consideraciones con datos LOB

mar

2017

Limpieza de primavera, consideraciones con datos LOB

La primavera ya está aquí y probablemente te entren ganas de hacer una limpia en tus bases de datos, y eso está muy bien.

En publicaciones anteriores, he explicado cómo tener grupos de archivos dedicados para datos de usuarios puede mejorar nuestro RTO, en caso de desastre, recuperando primero los datos críticos y luego el resto.

El problema es que cuando manejamos bases de datos que no fueron creadas de esta manera, necesitamos mover los datos de un grupo de archivos a otro antes de poder aplicar este tipo de técnicas.

Aquí es donde esta publicación puede mostrar uno de los problemas que se pueden encontrar durante este proceso.

Antecedentes

De acuerdo con Books Online

Puede usar la cláusula DROP_EXISTING para reconstruir el índice, agregar o quitar columnas, modificar opciones, modificar el orden de clasificación de las columnas o cambiar el esquema de partición o el grupo de archivos

Pero supongamos que tiene bases de datos antiguas que se crearon en su día sin planificar el crecimiento futuro con solo un grupo de archivos y un archivo de datos y que ahora le está causando algunos problemas en forma de disputa o un tiempo de recuperación muy largo.

También digamos que algunas de sus tablas usan tipos de datos LOB como MAX o incluso los BLOBS antiguos (TEXT, NTEXT, IMAGE).

Para imaginar esto mejor, vamos a crear una base de datos simple de la vieja escuela y una tabla

USE master

CREATE DATABASE LOB_allocations
GO

USE LOB_allocations
GO

-- Create table with NVARCHAR(MAX) column (LOB)
CREATE TABLE dbo.blobs (
Id	INT IDENTITY NOT NULL CONSTRAINT PK_blobs PRIMARY KEY
, Column1 VARCHAR(50) DEFAULT 'a'
, blob_column NVARCHAR(MAX) DEFAULT REPLICATE(CONVERT(NVARCHAR(MAX), N'b'), 10000)
)
GO

-- populate the table
INSERT INTO dbo.blobs ( Column1, blob_column )
DEFAULT VALUES
GO 20

Esto nos pone el escenario para comenzar, ahora dejadme mostraros como se distribuyen nuestros datos por tipo de unidad de asignación.

-- Check the different allocation units
SELECT OBJECT_NAME(ix.object_id)
		, ix.name
		, FILEGROUP_NAME(au.data_space_id) AS [Filegroup_name]
		, au.*
		, p.rows
	FROM sys.indexes AS ix
		INNER JOIN sys.partitions AS p
			ON p.object_id = ix.object_id 
				AND p.index_id = ix.index_id
		INNER JOIN sys.allocation_units AS au
			ON au.container_id = p.hobt_id	
WHERE ix.object_id IN (OBJECT_ID('dbo.blobs'))

Aquí vemos los diferentes tipos de asignación y el grupo de archivos al que pertenecen.

Y porque estamos en el modo limpieza de primavera, queremos mover nuestra table a un nuevo grupo de archivos y asi dejar PRIMARY solo para tablas de sistema. Primero hay que crear un nuevo grupo de archivos.

-- Add new filegroup and file 
ALTER DATABASE LOB_allocations ADD FILEGROUP [USER_DATA]
GO
ALTER DATABASE LOB_allocations 
	ADD FILE (NAME='LOB_allocations_user_data'
			, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQL2016\MSSQL\DATA\LOB_allocations_user_data.ndf'
			, SIZE= 16MB
			, FILEGROWTH=16MB) 
	TO FILEGROUP [USER_DATA]

Y ahora podemos seguir las instrucciones y mover nuestra tabla de un grupo de archivos a otro si reconstruimos nuestro indice cluster con WITH DROP_EXISTING.

-- Move the clustered index to the new filegroup
CREATE UNIQUE CLUSTERED INDEX PK_blobs ON dbo.blobs (Id) WITH (DROP_EXISTING = ON) ON [USER_DATA]

Ahora nuestra tabla está almacenada en un grupo de archivos diferente. Vamos a verlo

-- check again the allocation units.
SELECT OBJECT_NAME(ix.object_id)
		, ix.name
		, FILEGROUP_NAME(au.data_space_id) AS [Filegroup_name]
		, au.*
		, p.rows
	FROM sys.indexes AS ix
		INNER JOIN sys.partitions AS p
			ON p.object_id = ix.object_id 
				AND p.index_id = ix.index_id
		INNER JOIN sys.allocation_units AS au
			ON au.container_id = p.hobt_id	
WHERE ix.object_id IN (OBJECT_ID('dbo.blobs'))

Ejem, eso no mola nada… Parece que reconstruir el índice cluster no afecta a las unidaded de asignación LOB, así que para mover nuestros datos del todo, tenemos que crear una nueva tabla en el nuevo grupo de archivos y volcar los datos, con el consiguiente trastorno.

Conclusión

Obviamente es una buena idea el separar de manera lógica y física nuestros datos usando multiples grupos de archivos, pero siempre es mejor si planeamos esto con la debida antelacion. De esta forma nos ahorraremos situaciones incómodas en fases mas avanzadas.

Y una vez mas, podemos ver la importancia de tener un poco de conocimiento sobre el funcionamiento internos y cómo los datos son distribuidos por las diferentes unidades de asignación que se usan en SQL Server.

Espero que esta entrada haya side de ayuda y por favor usad los comentarios para cualquier pregunta que pueda surgir.

Gracias!