[SPIKE][SEI] Otimizando a Base de Dados

Expurgo

 

Foi executado o seguinte comando

SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB

FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    RowCounts desc

TableName	RowCounts	TotalSpaceMB
seq_infra_auditoria	28823372	504.66
login	18479143	45781.11
seq_infra_log	140854	2.70
infra_log	140853	178.34

SIP

 

Tabela seq_infra_auditoria

select * into seq_infra_auditoria_aux from seq_infra_auditoria WITH(NOLOCK) where 1 = 2

SET IDENTITY_INSERT [dbo].[seq_infra_auditoria_aux] ON

INSERT INTO [dbo].[seq_infra_auditoria_aux]
           (id, campo)
     VALUES
           ((SELECT MAX(ID_INFRA_AUDITORIA) from infra_auditoria), null)
GO

SET IDENTITY_INSERT [dbo].[seq_infra_auditoria_aux] OFF

EXEC sp_rename 'seq_infra_auditoria', 'seq_infra_auditoria_old';

EXEC sp_rename 'seq_infra_auditoria_aux', 'seq_infra_auditoria';

 

Tabela seq_infra_log

select * into seq_infra_log_aux from seq_infra_log WITH(NOLOCK) where 1 = 2

SET IDENTITY_INSERT [dbo].[seq_infra_log_aux] ON

INSERT INTO [dbo].[seq_infra_log_aux]
           (id, campo)
     VALUES
           ((SELECT MAX(ID_INFRA_LOG) from infra_log), null)
GO

SET IDENTITY_INSERT [dbo].[seq_infra_log_aux] OFF

EXEC sp_rename 'seq_infra_log', 'seq_infra_log_old';

EXEC sp_rename 'seq_infra_log_aux', 'seq_infra_log';

 

Tabela LOGIN

Registra as sessões ativas dos usuários

select * into login_aux from login WITH(NOLOCK) where 1 = 2

EXEC sp_rename 'login', 'login_old';

EXEC sp_rename 'login_aux', 'login';

removido index

USE [sei]
GO

SET ANSI_PADDING ON
GO

/****** Object:  Index [i01_infra_sequencia]    Script Date: 01/02/2022 09:39:59 ******/
CREATE NONCLUSTERED INDEX [i01_infra_sequencia] ON [dbo].[infra_sequencia]
(
	[nome_tabela] ASC,
	[num_atual] ASC,
	[qtd_incremento] ASC,
	[num_maximo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO