Como administrador de bases de datos (DBA), una de tus principales responsabilidades es mantener los índices en SQL Server. El mantenimiento de los índices es crucial para un rendimiento óptimo de la base de datos. Si bien los planes de mantenimiento se utilizan comúnmente para este propósito, no siempre son confiables y pueden proporcionar información falsa sobre el éxito de la tarea. En este artículo, exploraremos un enfoque alternativo utilizando un script sencillo para reconstruir los índices de todas las bases de datos en SQL Server.

El Script

El script proporcionado a continuación te permite reconstruir los índices de todas las bases de datos y tablas dentro de una base de datos. Se puede personalizar aún más para manejar índices específicos basados en los niveles de fragmentación y realizar una reorganización de índices o una reconstrucción de índices.

DECLARE @Database NVARCHAR(255)   
DECLARE @Table NVARCHAR(255)  
DECLARE @cmd NVARCHAR(1000)  

DECLARE DatabaseCursor CURSOR READ_ONLY FOR  
SELECT name FROM master.sys.databases   
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')  -- bases de datos a excluir
--WHERE name IN ('DB1', 'DB2') -- utiliza esto para seleccionar bases de datos específicas y comenta la línea anterior
AND state = 0 -- la base de datos está en línea
AND is_in_standby = 0 -- la base de datos no es de solo lectura para el envío de registros
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +  
   table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''   

   -- crea el cursor de tabla  
   EXEC (@cmd)  
   OPEN TableCursor   

   FETCH NEXT FROM TableCursor INTO @Table   
   WHILE @@FETCH_STATUS = 0   
   BEGIN
      BEGIN TRY   
         SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD' 
         --PRINT @cmd -- descomenta si quieres ver los comandos
         EXEC (@cmd) 
      END TRY
      BEGIN CATCH
         PRINT '---'
         PRINT @cmd
         PRINT ERROR_MESSAGE() 
         PRINT '---'
      END CATCH

      FETCH NEXT FROM TableCursor INTO @Table   
   END   

   CLOSE TableCursor   
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor

Uso y Personalización

Para utilizar este script, simplemente ejecútalo en SQL Server Management Studio o cualquier otra herramienta de consulta de SQL Server. El script reconstruirá todos los índices de todas las bases de datos y tablas dentro de esas bases de datos.

Puedes personalizar el script para excluir bases de datos específicas modificando la cláusula WHERE en la declaración del cursor DatabaseCursor. De manera similar, puedes incluir solo bases de datos específicas comentando la línea de exclusión y descomentando la línea de inclusión.

Este script ha sido probado y comprobado que funciona con SQL Server 2005, SQL Server 2008, SQL Server 2008R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017 y SQL Server 2019.

Conclusión

Mantener los índices es una tarea crítica para los DBAs para garantizar un rendimiento óptimo en SQL Server. Si bien se utilizan comúnmente los planes de mantenimiento, no siempre proporcionan la confiabilidad deseada. Al utilizar un script sencillo como el proporcionado en este artículo, puedes reconstruir los índices de todas las bases de datos y tablas, lo que te brinda más control y flexibilidad en tus rutinas de mantenimiento de índices.

Recuerda tener precaución al realizar el mantenimiento de índices y probar a fondo cualquier script en un entorno no productivo antes de aplicarlo a tus bases de datos en vivo.

¡Gracias por leer!