Índice de fragmentación actual

 

Query:

 

-- Query to check index fragmentation

SELECT

    OBJECT_NAME(ips.OBJECT_ID) AS TableName,

    i.name AS IndexName,

    ips.index_id,

    ips.avg_fragmentation_in_percent AS FragmentationPercent,

    ips.page_count AS PageCount

FROM

    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips

JOIN

    sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id

WHERE

    ips.index_id > 0 -- Ignore heap tables

ORDER BY

    FragmentationPercent DESC;

Rendimiento actual del sistema

Abrimos el task manager, nos dirigimos a la pestaña Performance y cambiamos la vista para “CPU” para ver el rendimiento por núcleo:

Lo primero que haremos será poner en marcha el SQL Server Agent:

Nos quedará en icono en verde indicando que ya está en marcha.

Luego crearemos un nuevo Job donde le indicaremos:

En la pestaña General:

-          Name: “Mantenimiento BBDD MES”

-          Category: “Database Maintenance”

En la pestaña Steps, creamos un nuevo:

-          Step name: Backup Full

-          Type: Transact-SQL T-SQL

-          Database: Seleccionamos la BBDD de MES

-          Command:

BACKUP DATABASE [MESDB_FRIT]

TO DISK = N'C:\Backups\MESDB_FRIT_Full.bak'

WITH INIT, NAME = N'Full Backup of MESDB_FRIT',

STATS = 10; -- This will show progress in the output window

-          Advanced: “Quit the job reporting success”

-          Step name: Backup Log MESDB

-          Type: Transact-SQL T-SQL

-          Database: Seleccionamos la BBDD de MES

-          Command:

-- Specify the backup directory path and backup file name.

DECLARE @BackupPath NVARCHAR(255)

SET @BackupPath = 'C:\Backup\MESDB_TLog_' + CONVERT(VARCHAR, GETDATE(), 112) + '_' +

                  REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '') + '.trn'

 

-- Perform the transaction log backup.

BACKUP LOG [MESDB]

TO DISK = @BackupPath

WITH

    FORMAT, -- Overwrites any existing file

    INIT, -- Initializes the backup

    NAME = 'MESDB Transaction Log Backup', -- Descriptive name for the backup

    STATS = 10; -- Show progress every 10%

 

-- Output the backup location

PRINT 'Transaction log backup completed. Backup saved to: ' + @BackupPath;

-          Advanced: “Quit the job reporting success”

Programar tarea de Archive Purge del BBDD MES desde la herramienta APR integrada en la aplicación MES Client para guardar información del MES (archivos propios) y luego realizar una purga.

Ya que con el backup del paso anterior también guardamos esta información, si no se precisa necesario, podemos prescindir del Archive y realizar solamente una tarea de Purge.

Filtros a aplicar:

                                     i.      Tables -> Tablas a purgar (todas las tablas).

                                    ii.      Work Order State Filter -> WO a purgar (Complete y Closed)

                                   iii.      Date filter -> Profundidad de purga (*11 mes, es decir purgamos todo lo anterior a 1 mes de datos)

Los pasos a realizar tanto si vamos a realizar un Archive + Purge como solamente un Purge, son los siguientes: (ejemplo con Archive + Purge)

Realizaremos el Archive Purge con la herramienta APR desde el MES Client.

Vamos a System Management > Database Maintenance , botón derecho y creamos un nuevo Job con el nombre que le indiquemos:

Con el Job seleccionado, en la barra del lateral derecho realizamos las siguientes configuraciones:

Name: ArchiveSemanal

Type: Archive Purge

Tables: Seleccionamos todas (por defecto)

Work Order State Filter: Seleccionamos Completed y Closed

Date Filter: Relative 30 Days (por defecto). Realizaremos un purgado de los datos de MES anteriores a los 30 días a partir de la fecha actual.

Ejemplo, si fijamos 30 days y estamos a 04 de noviembre del 2024, entonces se eliminarán todos los datos anteriores al 04 de octubre del 2024. Se realizará un archive de estos mismos datos antes de ser eliminados.

 

Guardamos los cambios presionando el botón de “Save” situado en la esquina superior derecha.

Para hacer la programación semanal nos vamos al “Task Scheduler” de Windows y creamos una nueva tarea:

Asegurar-se de que el usuario seleccionado tiene permisos para ejecutar dicha tarea.

Vamos a la pestaña de Triggers y configuramos una ejecución semanal de la tarea:

Vamos a la pestaña de “Actions”, creamos una nueva acción donde le indicaremos el programa a ejecutar mediante el browse:

Para indicarle a la tarea de Windows que Job debe ejecutar le pondremos en Arguments exactamente el mismo nombre del Job que previamente creamos en el MES Client:

Guardamos la acción.

Revisamos que en la pestaña “Conditions” todo esté acorde a la configuración pertinente a la infraestructura del cliente. Es importante asegurar que la tarea se ejecuta sin problemas (revisar modo de energía).

a)       Abrimos el scrpit “CreateCopyandPurgeJob” y lo ejecutamos sobre la base de datos del BI.

 

Esto nos creará unas stored procedures que serán las encargadas de realizar los archives y los purge que son: usp_copy y usp_purge

Para mantener la base de datos del BI, se deben utilizar los procedimientos almacenados usp_Copy y usp_Purge. Se recomienda ejecutarlos diariamente o semanalmente. Pero en el caso de que se copien millones de registros en la primera ejecución, se recomienda ejecutarlos por lotes de semanas o de un mes.

En los jobs del SQL Server Agent se nos habrá creado dos jobs para programar:
CopyIntelligenceData y el PurgeExpiredIntelligenceData

b)      Configurar y programar las tareas de archive:

En este ejemplo le indicamos que nos realice una purga de la base de datos Intelligence a la base de datos Intelligence_Offline que se encuentran el mismo servidor, de los datos de los últimos 30 días desde la fecha actual.

Desde la pestaña de Schedules programamos la periodicidad de las copias.

c)       Configurar y programar las tareas de archive:

En este ejemplo estaremos realizando una purga de todos los datos anteriores a últimos 30 días desde la fecha actual. Es decir, si tenemos datos de todo el año 2024 y ejecutamos este script a día 30/12/2024, solo preservaremos la información del mes de diciembre del 2024. Todo lo anterior a este mes será purgado.

Desde la pestaña de Schedules programamos la periodicidad de las purgas.

Para el mantenimiento de los índices y mejorar la performance de nuestra BBDD de MES ejecutaremos los scripts adjuntos a esta guía de mantenimiento que de forma automática realizarán un:

 

·         Reorganizar cuando el índice si su fragmentación: < 30%

·         Rebuilt cuando el índice de fragmentación: > 30%

 

Ø  Ejecutar el script defrag_CreateTables.sql. Este script crea las siguientes tablas para realizar un seguimiento del proceso de desfragmentación:

o   Temp_Frag_log

o   Temp_Frag_Data

Ø  Ejecutar el script ww_spDefrag.sql. Este script crea el procedimiento almacenado “stored procedure” ww_spDefrag que realizará la desfragmentación.

Ø  Programar un job con SQL Server Agent para desfragmentar la base de datos.

Vamos a SQL Server Agent  y creamos un nuevo Job al que podemos llamar “Mantenimiento Índices y Logs”.

 

-          Step name: Defragmentacion indices MES DDBB

-          Type: Transact-SQL T-SQL

-          Database: Seleccionamos la BBDD de MES

-          Command:

EXEC ww_spDefrag @p_Schedule=NULL,@p_DayNo=NULL,@p_RunHour=NULL

-          Advanced: “Go to next step”

Y lo programamos para el domingo a las 4:00 AM (dando 2 horas de margen para que se realice la tarea de purga).

Nota Importante: Esta Nota Técnica se entrega “as is”, es decir, como complemento a la documentación del producto, pero no incluido dentro del ámbito del Soporte Técnico. Por tanto, cualquier mal funcionamiento derivado del contenido de esta nota técnica no es responsabilidad de Becolve Digital.