[SPIKE][SEI] - Otimização da 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

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

Expurgo das SEQUENCES

	SELECT (SCHEMA_NAME(A.schema_id) + '.' + A.Name) AS TableName  , SUM(B.rows) AS RecordCount  
		FROM sys.objects A  
		INNER JOIN sys.partitions B ON A.object_id = B.object_id  
		WHERE A.type = 'U'
		and (SCHEMA_NAME(A.schema_id) + '.' + A.Name) like 'dbo.seq%'
		GROUP BY A.schema_id, A.Name 
		order by RecordCount desc

TableName	RecordCount
dbo.seq_atributo_andamento	217491282
dbo.seq_atividade	152413718
dbo.seq_versao_secao_documento	98070754
dbo.seq_secao_documento	75635256
dbo.seq_protocolo	26082648
dbo.seq_upload	25887694
dbo.seq_rel_protocolo_protocolo	24027167
dbo.seq_documento	23740437
dbo.seq_participante	23130564
dbo.seq_infra_navegador	18926572
dbo.seq_assinatura	18778626
dbo.seq_acesso	12424778
dbo.seq_anexo	8924230
dbo.seq_andamento_marcador	8091696
dbo.seq_infra_auditoria	3968120
dbo.seq_infra_log	3004899
dbo.seq_auditoria_protocolo	2795134
dbo.seq_anotacao	1994726
dbo.seq_acompanhamento	1361733
dbo.seq_bloco	781595
dbo.seq_observacao	736625
dbo.seq_protocolo_modelo	479738
dbo.seq_estatisticas	475008
dbo.seq_contato	457457
dbo.seq_acesso_externo	414732
dbo.seq_publicacao	289994
dbo.seq_monitoramento_servico	182033
dbo.seq_grupo_acompanhamento	106467
dbo.seq_retorno_programado	98258
dbo.seq_email_utilizado	75218
dbo.seq_marcador	69985
dbo.seq_grupo_protocolo_modelo	64542
dbo.seq_email_grupo_email	62368
dbo.seq_numeracao	56757
dbo.seq_texto_padrao_interno	30672
dbo.seq_conjunto_estilos_item	10080
dbo.seq_unidade_publicacao	8807
dbo.seq_serie_publicacao	7495
dbo.seq_email_unidade	3570
dbo.seq_grupo_email	2687
dbo.seq_grupo_unidade	2121
dbo.seq_controle_unidade	1932
dbo.seq_rel_unidade_tipo_contato	1589
dbo.seq_cargo	1384
dbo.seq_secao_modelo	1222
dbo.seq_nivel_acesso_permitido	1032
dbo.seq_grupo_contato	746
dbo.seq_serie_restricao	444
dbo.seq_tipo_proced_restricao	344
dbo.seq_serie	287
dbo.seq_base_conhecimento	285
dbo.seq_modelo	258
dbo.seq_assinante	232
dbo.seq_tipo_procedimento	209
dbo.seq_tipo_contato	179
dbo.seq_operacao_servico	133
dbo.seq_conjunto_estilos	125
dbo.seq_estilo	79
dbo.seq_novidade	38
dbo.seq_tarja_assinatura	17
dbo.seq_servico	15
dbo.seq_feriado	13
dbo.seq_hipotese_legal	12
dbo.seq_vocativo	8
dbo.seq_controle_interno	7
dbo.seq_andamento_situacao	7
dbo.seq_situacao	4
dbo.seq_veiculo_publicacao	3
dbo.seq_tipo_formulario	2
dbo.seq_assunto	2
dbo.seq_assunto_proxy	2
dbo.seq_atributo	2
dbo.seq_dominio	2
dbo.seq_cidade	1
dbo.seq_email_sistema	1
dbo.seq_arquivo_extensao	1
dbo.seq_tabela_assuntos	1
dbo.seq_tarefa	1
dbo.seq_secao_imprensa_nacional	1
dbo.seq_pais	1
dbo.seq_imagem_formato	1
dbo.seq_grupo_serie	1
dbo.seq_tipo_localizador	1
dbo.seq_tipo_conferencia	1
dbo.seq_veiculo_imprensa_nacional	1
dbo.seq_tipo_suporte	1
dbo.seq_tratamento	1
dbo.seq_uf	1
dbo.seq_notificacao	0
dbo.seq_md_wssei_notificacao_ativ	0
dbo.seq_localizador	0
dbo.seq_lugar_localizador	0
dbo.seq_ordenador_despesa	0
dbo.seq_atributo_andamento_situaca	0
dbo.seq_feed	0
TOTAL	751.176.872

    DELETE FROM seq_infra_auditoria where id < 
        (SELECT MAX(id) FROM seq_infra_auditoria WITH(NOLOCK));
    DELETE FROM seq_infra_log where id < 
        (SELECT MAX(id) FROM seq_infra_log WITH(NOLOCK));
    DELETE FROM seq_infra_navegador where id < 
        (SELECT MAX(id) FROM seq_infra_navegador WITH(NOLOCK));
    DELETE FROM seq_localizador where id < 
        (SELECT MAX(id) FROM seq_localizador WITH(NOLOCK));
    DELETE FROM seq_lugar_localizador where id < 
        (SELECT MAX(id) FROM seq_lugar_localizador WITH(NOLOCK));
    DELETE FROM seq_marcador where id < 
        (SELECT MAX(id) FROM seq_marcador WITH(NOLOCK));
    DELETE FROM seq_md_wssei_notificacao_ativ where id < 
        (SELECT MAX(id) FROM seq_md_wssei_notificacao_ativ WITH(NOLOCK));
    DELETE FROM seq_modelo where id < 
        (SELECT MAX(id) FROM seq_modelo WITH(NOLOCK));
    DELETE FROM seq_monitoramento_servico where id < 
        (SELECT MAX(id) FROM seq_monitoramento_servico WITH(NOLOCK));
    DELETE FROM seq_nivel_acesso_permitido where id < 
        (SELECT MAX(id) FROM seq_nivel_acesso_permitido WITH(NOLOCK));
    DELETE FROM seq_notificacao where id < 
        (SELECT MAX(id) FROM seq_notificacao WITH(NOLOCK));
    DELETE FROM seq_novidade where id < 
        (SELECT MAX(id) FROM seq_novidade WITH(NOLOCK));
    DELETE FROM seq_numeracao where id < 
        (SELECT MAX(id) FROM seq_numeracao WITH(NOLOCK));
    DELETE FROM seq_observacao where id < 
        (SELECT MAX(id) FROM seq_observacao WITH(NOLOCK));
    DELETE FROM seq_operacao_servico where id < 
        (SELECT MAX(id) FROM seq_operacao_servico WITH(NOLOCK));
    DELETE FROM seq_ordenador_despesa where id < 
        (SELECT MAX(id) FROM seq_ordenador_despesa WITH(NOLOCK));
    DELETE FROM seq_pais where id < 
        (SELECT MAX(id) FROM seq_pais WITH(NOLOCK));
    DELETE FROM seq_participante where id < 
        (SELECT MAX(id) FROM seq_participante WITH(NOLOCK));
    DELETE FROM seq_protocolo where id < 
        (SELECT MAX(id) FROM seq_protocolo WITH(NOLOCK));
    DELETE FROM seq_protocolo_modelo where id < 
        (SELECT MAX(id) FROM seq_protocolo_modelo WITH(NOLOCK));
    DELETE FROM seq_publicacao where id < 
        (SELECT MAX(id) FROM seq_publicacao WITH(NOLOCK));
    DELETE FROM seq_rel_protocolo_protocolo where id < 
        (SELECT MAX(id) FROM seq_rel_protocolo_protocolo WITH(NOLOCK));
    DELETE FROM seq_rel_unidade_tipo_contato where id < 
        (SELECT MAX(id) FROM seq_rel_unidade_tipo_contato WITH(NOLOCK));
    DELETE FROM seq_retorno_programado where id < 
        (SELECT MAX(id) FROM seq_retorno_programado WITH(NOLOCK));
    DELETE FROM seq_secao_documento where id < 
        (SELECT MAX(id) FROM seq_secao_documento WITH(NOLOCK));
    DELETE FROM seq_secao_imprensa_nacional where id < 
        (SELECT MAX(id) FROM seq_secao_imprensa_nacional WITH(NOLOCK));
    DELETE FROM seq_secao_modelo where id < 
        (SELECT MAX(id) FROM seq_secao_modelo WITH(NOLOCK));
    DELETE FROM seq_serie where id < 
        (SELECT MAX(id) FROM seq_serie WITH(NOLOCK));
    DELETE FROM seq_serie_publicacao where id < 
        (SELECT MAX(id) FROM seq_serie_publicacao WITH(NOLOCK));
    DELETE FROM seq_serie_restricao where id < 
        (SELECT MAX(id) FROM seq_serie_restricao WITH(NOLOCK));
    DELETE FROM seq_servico where id < 
        (SELECT MAX(id) FROM seq_servico WITH(NOLOCK));
    DELETE FROM seq_situacao where id < 
        (SELECT MAX(id) FROM seq_situacao WITH(NOLOCK));
    DELETE FROM seq_tabela_assuntos where id < 
        (SELECT MAX(id) FROM seq_tabela_assuntos WITH(NOLOCK));
    DELETE FROM seq_tarefa where id < 
        (SELECT MAX(id) FROM seq_tarefa WITH(NOLOCK));
    DELETE FROM seq_tarja_assinatura where id < 
        (SELECT MAX(id) FROM seq_tarja_assinatura WITH(NOLOCK));
    DELETE FROM seq_texto_padrao_interno where id < 
        (SELECT MAX(id) FROM seq_texto_padrao_interno WITH(NOLOCK));
    DELETE FROM seq_tipo_conferencia where id < 
        (SELECT MAX(id) FROM seq_tipo_conferencia WITH(NOLOCK));
    DELETE FROM seq_tipo_contato where id < 
        (SELECT MAX(id) FROM seq_tipo_contato WITH(NOLOCK));
    DELETE FROM seq_tipo_formulario where id < 
        (SELECT MAX(id) FROM seq_tipo_formulario WITH(NOLOCK));
    DELETE FROM seq_tipo_localizador where id < 
        (SELECT MAX(id) FROM seq_tipo_localizador WITH(NOLOCK));
    DELETE FROM seq_tipo_proced_restricao where id < 
        (SELECT MAX(id) FROM seq_tipo_proced_restricao WITH(NOLOCK));
    DELETE FROM seq_tipo_procedimento where id < 
        (SELECT MAX(id) FROM seq_tipo_procedimento WITH(NOLOCK));
    DELETE FROM seq_tipo_suporte where id < 
        (SELECT MAX(id) FROM seq_tipo_suporte WITH(NOLOCK));
    DELETE FROM seq_tratamento where id < 
        (SELECT MAX(id) FROM seq_tratamento WITH(NOLOCK));
    DELETE FROM seq_uf where id < 
        (SELECT MAX(id) FROM seq_uf WITH(NOLOCK));
    DELETE FROM seq_unidade_publicacao where id < 
        (SELECT MAX(id) FROM seq_unidade_publicacao WITH(NOLOCK));
    DELETE FROM seq_upload where id < 
        (SELECT MAX(id) FROM seq_upload WITH(NOLOCK));
    DELETE FROM seq_veiculo_imprensa_nacional where id < 
        (SELECT MAX(id) FROM seq_veiculo_imprensa_nacional WITH(NOLOCK));
    DELETE FROM seq_veiculo_publicacao where id < 
        (SELECT MAX(id) FROM seq_veiculo_publicacao WITH(NOLOCK));
    DELETE FROM seq_versao_secao_documento where id < 
        (SELECT MAX(id) FROM seq_versao_secao_documento WITH(NOLOCK));
    DELETE FROM seq_vocativo where id < 
        (SELECT MAX(id) FROM seq_vocativo WITH(NOLOCK));