Guía de mantenimiento de base de datos SQL Server

by Leonardo Lizano V.2017.05.12

La importancia de las base de datos en la actualidad

La recopilación de datos es fundamental para que una empresa o institución mantenga sus relaciones. Por este motivo se le brinda una gran importancia al mantenimiento de la base de datos y también al constante crecimiento de la misma..

El mantenimiento para una base de datos siempre es muy importante ya que nos permite mantener nuestra Base de Datos en un nivel óptimo, estas operaciones de mantenimiento las podemos ejecutar mensualmente. Sin embargo, podemos variar esta frecuencia de acuerdo con el entorno y con la actividad que recibe cada base de datos.

Vamos a ver principalmente algunos comandos que están orientados a este tipo de procedimientos:

Paso 1: Calcular peso de Base de Datos 

SELECT DB_NAME(database_id) as [DatabaseName], Name as [Logical_Name],  
Physical_Name as [Physical_Name], (size*8)/1024 as [SizeMB], ((size*8)/1024)/1024 as [SizeGB]
FROM sys.master_files
WHERE DB_NAME(database_id) = 'NOMBREBASE'
GO

Paso 2: Calcular total de la base de datos sumados (MDF y LDF)

SELECT (sum(size)/1024)/1024*8 as [SizeGB] 
FROM sys.master_files 
WHERE DB_NAME(database_id)= 'NOMBREBASE' 
GO

Para tener un mejor detalle del tamaño de la base de datos, observar las propiedades de los archivos propiamente en la raíz donde estén almacenados. 

Paso 3: Primera compactación de la base de datos 

DBCC SHRINKDATABASE ('NOMBREBASE')

Para compactar el Log  
USE 'NOMBREBASE';
GO

ALTER DATABASE 'NOMBREBASE' SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE ('NOMBREBASE'_LOG, 1);
GO

Paso 4:  Desfragmentación al rescate, para evitar el deterioro del rendimiento en nuestro servidor, deberemos mantener nuestros índices en un estado de fragmentación óptimo. Lo podremos lograr sencillamente siguiendo estos pasos. Primer paso: detectar fragmentación en los índices de tu base de datos. Para ello, nos basaremos en la vista de sistema sys.dm_db_index_physical_stats, que encapsularemos en el siguiente Query 

SELECT DB_NAME(database_id) AS DatabaseName, database_id, OBJECT_NAME(ips.object_id) AS TableName, 
ips.object_id,i.name AS IndexName, i.index_id, p.rows,ips.partition_number, index_type_desc, 
alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent, fragment_count, 
avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent, record_count, 
ghost_record_count, version_ghost_record_count, min_record_size_in_bytes, max_record_size_in_bytes, 
avg_record_size_in_bytes, forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') ips INNER JOIN 
sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id INNER JOIN
sys.partitions p ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE avg_fragmentation_in_percent > 10.0 AND ips.index_id > 0 AND page_count > 1000
ORDER BY avg_fragmentation_in_percent DESC

Paso 5: Ejecutar un script para desfragmentar los índices con problemas. El script determina si hay que hacer un Reorganize o un Rebuild para cada índice

Asegurarse de ejecutar la sentencia USE antes.  
USE 'NOMBREBASE'

SET NOCOUNT ON;

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);

-- Conditionally SELECT tables and indexes FROM the sys.dm_db_index_physical_stats function and convert object and index IDs to names.

SELECT object_id AS [objected], index_id AS [indexid], partition_number AS [partitionnum], avg_fragmentation_in_percent AS [frag]
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND page_count > 1000;

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Close and deallocate the cursor.

CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.

DROP TABLE #work_to_do;
GO

Diferencias entre reconstruir (REBUILD) y reorganizar (REORGANIZE) un índice básicamente, existen dos maneras de volver a ordenar un índice. La más eficaz consiste en eliminar el índice y volver a crearlo, desde cero. Ésta garantiza un resultado óptimo, dejando la fragmentación en 0%. Como contrapartida, es una operación más costosa y genera bloqueos sobre la tabla cuyo índice se está actualizando. Usaremos esta opción (REBUILD) cuando la fragmentación del índice supere el 30%.

Para índices ligeramente fragmentados (entre el 10% y el 30%) existe la opción de reorganizar (REORGANIZE) el índice. Esta opción simplemente reordena los datos del índice dentro de las páginas que ocupa, devolviendo un orden adecuado al mismo. Se trata de una operación mucho más ligera, que no bloquea las tablas y vistas subyacentes.

Adicionalmente, habrá que tener en cuenta que solamente tiene sentido efectuar la operación de desfragmentación sobre índices con un tamaño superior a 1000 páginas. Ya que, por debajo de este valor, SQL Server, considera que es más eficiente escribir los datos de forma desordenada que mantener un índice poco efectivo, al tratarse de una tabla pequeña. 

Paso 6: Mantenimiento a una base de datos

Este comando examina la asignación y la integridad estructural de todos los objetos que se encuentran en la base de datos especificada, cuando acabe de ejecutarse nos mostrará un mensaje similar al siguiente cuando se ejecuta correctamente. 

DBCC CHECKDB ('NOMBREBASE')

Si el chequeo de la base de datos presenta algún error que no se pudo corregir (se denota en letras rojas), se deben ejecutar los siguientes querys: 

1- Poner la base de datos para acceso de un único usuario: 

ALTER DATABASE 'NOMBREBASE' SET SINGLE_USER;

2- Luego ejecutar el siguiente Query de chequeo de la base de datos: 

DBCC CHECKDB ('NOMBREBASE', REPAIR_REBUILD);

3- Por último, poner nuevamente la base de datos para acceso multiusuario: 

ALTER DATABASE 'NOMBREBASE' SET MULTI_USER;

Paso 7: Reconstrucción de índices manualmente eligiendo las tablas de la base de datos

7.1- Se despliegan las tablas y se eligen a las que se les va a reconstruir los índices manualmente

7.2- En la ventana que muestra los índices y su porcentaje de fragmentación, se presiona [OK].

Paso 8: Compactación de la base de datos final 

DBCC SHRINKDATABASE ('NOMBREBASE')

Para compactar el Log  
USE 'NOMBREBASE';
GO

ALTER DATABASE 'NOMBREBASE' SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE ('NOMBREBASE'_LOG, 1);
GO

ALTER DATABASE 'NOMBREBASE' SET RECOVERY FULL;
GO