Ir para o conteúdo principal

[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

 

 

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

 

Voltar ao topo