[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';

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

SEI

TableName	RowCounts	TotalSpaceMB
auditoria_protocolo	329881188	51544.15
seq_auditoria_protocolo	329881188	6140.91

select * into seq_auditoria_protocolo_aux from seq_auditoria_protocolo WITH(NOLOCK) where 1 = 2

SET IDENTITY_INSERT [dbo].[seq_auditoria_protocolo_aux] ON

INSERT INTO [dbo].[seq_auditoria_protocolo_aux]
           (id, campo)
     VALUES
           ((SELECT MAX(id_auditoria_protocolo) from auditoria_protocolo), null)
GO

SET IDENTITY_INSERT [dbo].[seq_auditoria_protocolo_aux] OFF

EXEC sp_rename 'seq_auditoria_protocolo', 'seq_auditoria_protocolo_old';

EXEC sp_rename 'seq_auditoria_protocolo_aux', 'seq_auditoria_protocolo';

/****** Object:  Table [dbo].[auditoria_protocolo]    Script Date: 01/02/2022 14:20:00 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[auditoria_protocolo_temp](
	[id_auditoria_protocolo] [bigint] NOT NULL,
	[id_protocolo] [bigint] NOT NULL,
	[id_usuario] [int] NOT NULL,
	[id_anexo] [int] NULL,
	[versao] [int] NULL,
	[dta_auditoria] [datetime] NOT NULL,
 CONSTRAINT [pk_auditoria_protocolo_temp] PRIMARY KEY CLUSTERED 
(
	[id_auditoria_protocolo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

EXEC sp_rename 'auditoria_protocolo', 'auditoria_protocolo_old';
EXEC sp_rename 'auditoria_protocolo_temp', 'auditoria_protocolo';