Ir para o conteúdo principal

[SPIKE] melhores rotinas de manutenção para o sei (avaliar fragmentação e periodicidade)

O objetivo do estudo é criar rotinas de manutenção dos índices e estatísticas com periodicidade de suas ativações no banco de dados do SEI, buscando manter boa performance nas consultas executadas nessa instância do SQL Server, bem como monitorar a estrutura de índices antes da execução da tarefa de desfragmentação e consequentemente após o término da desta tarefa, assim como manter um histórico para posterior consulta sobre quais índices fragmentam mais rápido.

Um breve resumo das tecnologias utilizadas para atingir o objetivo proposto:

  • Traces → Ferramenta gratuíta de monitoramento de bancos de dados SQL Server(https://github.com/soupowertuning/Script_SQLServer_Alerts), que é composta de um banco de dados chamado traces, tabelas de histórico da instância, bem como procedures que implementam as funcionalidades que são acionadas por um conjunto de jobs. Boa parte das funcionalidades usadas na ferramenta é baseada na procedure sp_whoisactive (https://www.brentozar.com/) muito conhecida pela nos fóruns sobre SQL Server. Funcionalidade da ferramenta usada no estudo:

    stpLoad_Index_Fragmentation → É um procedimento armazenado da ferramenta Traces responsável em armazenar em tabela um “retrato” de como está toda a estrutura de fragmentação dos índices do banco de dados num determinado momento. Configuração de execução:

    --Configuração
    USE Traces
    EXECUTE dbo.stpLoad_Index_Fragmentation

  • SQL Server Maintenance Solution → Ferramenta gratuíta desenvolvida pela uma comunidade de DBAs chamada Hallengren(https://ola.hallengren.com/), muito referenciado nos fóruns, que é especializada em administração de bancos de dados SQL Server. Funcionalidades usadas durante o estudo:


    IndexOptimize → É o procedimento armazenado do SQL Server Maintenance Solution para reconstruir e reorganizar índices e atualizar estatísticas.

    A configuração 1, faz analise individualizada dos índices do banco SEI segundo critérios padronizados microsoft, podendo assim efetuar rebuild ou reorganize ou nada fazer no índice de acordo com cada caso:


    --Configuração 1

    USE master

    EXECUTE dbo.IndexOptimize

    @Databases = 'SEI',

    @FragmentationLow = NULL,

    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

    @FragmentationLevel1 = 5,

    @FragmentationLevel2 = 30



    Na configuração 2, a procedure IndexOptimize faz analise individualizada dos índices do bancos de Sistema segundo critérios padronizados microsoft, podendo assim efetuar rebuild ou reorganize ou nada fazer no índice de acordo com cada caso:


    --Configuração 2

    USE master

    EXECUTE dbo.IndexOptimize

    @Databases = 'SYSTEM_DATABASES',

    @FragmentationLow = NULL,

    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

    @FragmentationLevel1 = 5,

    @FragmentationLevel2 = 30



    Na configuração 3, a procedure IndexOptimize faz a atualização completa de todas as estatísticas do banco SEI:


    --Configuração 3

    USE master

    EXECUTE dbo.IndexOptimize

    @Databases = 'SEI',

    @FragmentationLow = NULL,

    @FragmentationMedium = NULL,

    @FragmentationHigh = NULL,

    @UpdateStatistics = 'ALL'



    Na configuração 4, a procedure IndexOptimize faz somente a atualização das estatísticas no banco SEI, onde alguma modificação de registros de dados:


    --Configuração 4

    USE master

    EXECUTE dbo.IndexOptimize

    @Databases = 'SEI',

    @FragmentationLow = NULL,

    @FragmentationMedium = NULL,

    @FragmentationHigh = NULL,

    @UpdateStatistics = 'ALL',

    @OnlyModifiedStatistics = 'Y'

Caso de uso prático dos recursos abordados aplicados ao banco de dados do SEI de Produção (172.16.0.87) para efeitos de demonstração

Tirando uma “Retrato” da estrutura de fragmentação dos índices:

--Execução e posterior ordenação por índice mais fragmentado, tempo necessário aproximado entre 30 segundos a 2 minutos, dependendo do horário de execução, tamanho do banco e nível de fragmentação.
USE Traces
EXECUTE dbo.stpLoad_Index_Fragmentation
Dt_Log	    Id_Table	Nm_Index	            Avg_Fragmentation_In_Percent	Page_Count
2022-06-11	4	fk_andamento_marcador_proced	88.74	3933
2022-06-11	11	ie1_bloco	87.49	1127
2022-06-11	19	pk_infra_dado_usuario	87.01	1955
2022-06-11	4	i01_andamento_marcador	85.73	5832
2022-06-11	4	i02_andamento_marcador	85.60	4965
2022-06-11	32	if3_rel_bloco_unidade	85.48	1460
2022-06-11	4	fk_andamento_marcador_marcador	84.08	3160
2022-06-11	4	Ind__andamento_marcador_wag1	82.01	8321
2022-06-11	27	i16_protocolo	81.59	171858
2022-06-11	28	fk_protocolo_modelo_usuario	79.37	1139
2022-06-11	4	fk_andamento_marcador_usuario	78.94	3082
2022-06-11	32	if4_rel_bloco_unidade	76.68	1205
2022-06-11	3	fk_acompanhamento_grupo_acompa	76.62	3952
2022-06-11	28	Ind__protocolo_modelo_tia3	76.52	1448
2022-06-11	4	Ind__andamento_marcador_tia	75.83	5051
2022-06-11	6	fk_anotacao_usuario	75.53	3710
2022-06-11	28	fk_prot_mod_grupo_prot_mod	73.77	1159
2022-06-11	6	fk_anotacao_protocolo	72.99	4836
2022-06-11	32	fk_rel_bloco_unidade_unidade	72.81	1085
2022-06-11	6	_dta_index_anotacao_8_637245325__K3_1	72.80	4824
2022-06-11	32	fk_rel_bloco_unidade_bloco	72.64	1199
2022-06-11	3	idx_acompanhamento_id	72.27	2798
2022-06-11	11	i02_bloco	71.97	2255
2022-06-11	3	fk_acompanhamento_usuario	71.72	3720
2022-06-11	32	fk_rel_blo_uni_usu_prioridade	71.39	1349
2022-06-11	32	fk_rel_blo_uni_usu_revisao	71.19	1357
2022-06-11	32	fk_rel_blo_uni_grupo_bloco	71.11	1350
2022-06-11	32	fk_rel_blo_uni_usu_atribuicao	71.11	1350
2022-06-11	32	fk_rel_blo_uni_usu_comentario	70.96	1350
2022-06-11	28	fk_protocolo_modelo_unidade	70.84	1070
2022-06-11	11	Ind__bloco_tia3	70.71	2277
2022-06-11	3	fk_acompanhamento_protocolo	70.10	4883
2022-06-11	11	Ind__bloco_tia	69.39	2277
2022-06-11	32	i06_rel_bloco_unidade	68.07	1444
2022-06-11	32	i07_rel_bloco_unidade	66.65	1715
2022-06-11	4	fk_andamento_marcador_unidade	66.30	2733
2022-06-11	1	fk_acesso_protocolo	64.89	36049
2022-06-11	1	i02_acesso	63.94	37758
2022-06-11	12	i11_contato	63.17	2199
2022-06-11	32	i05_rel_bloco_unidade	62.54	1423
2022-06-11	6	i01_anotacao	61.99	6703
2022-06-11	39	i01_usuario	61.98	1139
2022-06-11	28	_dta_index_protocolo_modelo_8_907866301__K5_1	59.52	1213
2022-06-11	6	fk_anotacao_unidade	58.15	3240
2022-06-11	12	i01_contato	57.93	2294
2022-06-11	1	i03_acesso	57.92	48608
2022-06-11	32	pk_rel_bloco_unidade	57.92	4432
2022-06-11	28	fk_protocolo_modelo_protocolo	56.68	1212
2022-06-11	3	fk_acompanhamento_unidade	56.17	3210
2022-06-11	26	pk_procedimento	51.47	10575
2022-06-11	1	i02_acesso_detic	50.85	39758
2022-06-11	1	i01_acesso	47.92	45384
2022-06-11	34	pk_rel_protocolo_protocolo	46.77	246055
2022-06-11	5	fk_anexo_usuario	46.54	22949
2022-06-11	29	i03_publicacao	45.86	1147
2022-06-11	2	fk_acesso_externo_participante	45.25	1136
2022-06-11	6	_dta_index_anotacao_8_637245325__K2_K3_K1_K4_K6_5_7_8	44.25	18273
2022-06-11	34	fk_protocolo_protocolo_1	43.86	91389
2022-06-11	15	fk_documento_procedimento	43.29	90036
2022-06-11	4	pk_andamento_marcador	42.94	9238
2022-06-11	6	Ind__anotacao_tia	40.77	16097
2022-06-11	11	pk_bloco	39.40	3330
2022-06-11	31	Ind_rel_bloco_protocolo_tia	38.31	24130
2022-06-11	27	i03_protocolo	37.40	91069
2022-06-11	34	<Ind_rel_protocolo_protocolo_w_1>	36.18	87437
2022-06-11	7	fk_assinatura_usuario_autentic	33.97	43447
2022-06-11	1	fk_acesso_unidade	33.83	21063
2022-06-11	15	i04_documento	29.81	72135
2022-06-11	31	pk_rel_bloco_protocolo	27.68	45705
2022-06-11	8	i06_atividade	27.25	651663
2022-06-11	8	i07_atividade	27.22	653824
2022-06-11	24	fk_observacao_unidade	27.07	1533
2022-06-11	8	i09_atividade	27.01	651815
2022-06-11	27	i10_protocolo	26.40	114525
2022-06-11	8	IX_Atividade	25.74	744162
2022-06-11	34	fk_rel_proc_doc_usuario	25.57	66278
2022-06-11	15	idx_documento_num	25.51	55844
2022-06-11	3	pk_acompanhamento	24.99	31611
2022-06-11	8	i16_atividade	24.55	930200
2022-06-11	27	fk_protocolo_usuario	23.96	71585
2022-06-11	5	fk_anexo_unidade	23.88	19312
2022-06-11	8	Ind__atividade_tia2	23.67	835914
2022-06-11	8	fk_atividade_usuario_conclusao	20.24	340711
2022-06-11	39	Ind__usuario_tia9	20.21	1118
2022-06-11	39	Ind__usuario_tia7	20.04	1118
2022-06-11	8	_dta_index_atividade_8_356196319__K2_K3_K5_K1_K24_K20_7_18_19_23	19.61	1235666
2022-06-11	6	pk_anotacao	19.02	15766
2022-06-11	27	Ind__protocolo_wag2	18.99	198883
2022-06-11	27	Ind__protocolo_wag4	18.82	198885
2022-06-11	39	Ind__usuario_tia	18.78	1118
2022-06-11	27	i02_protocolo	15.46	100113
2022-06-11	7	fk_assinatura_unidade	14.87	38164
2022-06-11	25	i01_participante	14.29	59092
2022-06-11	1	idx_acesso_tipo	14.25	14447
2022-06-11	7	fk_assinatura_documento	14.23	48251
2022-06-11	25	IX_Participante	14.18	59072
2022-06-11	7	IX_assinatura_detic	13.99	50794
2022-06-11	8	_dta_index_atividade_8_356196319__K2_K1_K13_K7_K3_K25_K20_K15_4_5_18_22_24	13.58	1646498
2022-06-11	8	i01_atividade	13.52	891630
2022-06-11	7	ak1_assinatura	13.17	58448
2022-06-11	1	fk_acesso_controle_interno	12.72	18101
2022-06-11	8	Ind__atividade_tia9	12.72	1631601
2022-06-11	27	<Ind_protocolo_w_2>	12.19	109862
2022-06-11	40	fk_versao_secao_doc_usuario	11.94	244804
2022-06-11	25	fk_participante_unidade	11.70	43506
2022-06-11	8	fk_atividade_usuario_visualiza	11.55	317799
2022-06-11	15	fk_documento_unidade_responsav	10.45	58686
2022-06-11	34	fk_rel_protocolo_protocolo_uni	10.44	59403
2022-06-11	8	fk_atividade_usuario_origem	10.43	315306
2022-06-11	9	i02_atributo_andamento	10.16	976108
2022-06-11	36	fk_secao_doc_secao_mod	9.99	140425
2022-06-11	34	fk_protocolo_protocolo_2	9.65	71357
2022-06-11	22	i02_monitoramento_servico	9.65	1057
2022-06-11	1	fk_acesso_usuario	9.20	17898
2022-06-11	2	pk_acesso_externo	8.91	6165
2022-06-11	15	pk_documento	8.70	211903
2022-06-11	33	fk_rel_protocolo_assunto_uni	8.59	148017
2022-06-11	40	IX_VersaoSecaoDocumento	8.52	290761
2022-06-11	40	i01_versao_secao_documento	8.50	290710
2022-06-11	2	fk_acesso_externo_documento	8.15	1104
2022-06-11	39	pk_usuario	7.92	1920
2022-06-11	15	if5_documento	7.86	81908
2022-06-11	5	i01_anexo	7.79	23292
2022-06-11	15	i06_documento	7.78	47864
2022-06-11	34	i01_rel_protocolo_protocolo	7.67	99153
2022-06-11	27	Ind__protocolo_wag3	7.61	49251
2022-06-11	27	i06_protocolo	7.48	79910
2022-06-11	27	ie1_protocolo	7.44	97674
2022-06-11	27	fk_protocolo_protocolo_fed	7.32	49220
2022-06-11	28	pk_protocolo_modelo	7.22	6455
2022-06-11	2	i04_acesso_externo	7.07	1641
2022-06-11	27	<Ind_protocolo_w_1>	7.06	182488
2022-06-11	15	i03_documento	7.02	72669
2022-06-11	40	fk_versao_secao_doc_unidade	6.82	235607
2022-06-11	27	i15_protocolo	6.72	95957
2022-06-11	5	fk_anexo_protocolo	6.29	21909
2022-06-11	36	i01_secao_documento	6.19	235894
2022-06-11	8	_dta_index_atividade_8_356196319__K20_K3_K5_K18_K1_K2_K24_7_19_23_9910	6.12	1129208
2022-06-11	15	i02_documento	6.11	69129
2022-06-11	15	ak_documento	6.08	69152
2022-06-11	8	_dta_index_atividade_8_356196319__K20_K3_K5_K18_K1_K2_K24_7_19_23	6.06	1129123
2022-06-11	27	ak2_protocolo	5.83	96096
2022-06-11	27	if4_protocolo	5.69	76129
2022-06-11	12	IX_Contato	5.58	1970
2022-06-11	33	fk_rel_prot_assunto_assunto	5.52	118321
2022-06-11	8	i03_atividade	5.50	498056
2022-06-11	15	fk_documento_serie	5.47	56034
2022-06-11	8	i04_atividade	5.26	905969
2022-06-11	24	fk_observacao_protocolo	5.18	1678
2022-06-11	33	pk_rel_protocolo_assunto	5.01	190925
2022-06-11	1	pk_acesso	4.92	41860
2022-06-11	12	Ind__contato_tia3	4.83	2566
2022-06-11	5	fk_anexo_base_conhecimento	4.81	16975
2022-06-11	33	fk_rel_prot_assunto_protocolo	4.75	118951
2022-06-11	27	i13_protocolo	4.56	106389
2022-06-11	27	fk_protocolo_hipotese_legal	4.38	61879
2022-06-11	5	i02_anexo	4.26	21886
2022-06-11	8	Ind__atividade_tia7	3.94	1113892
2022-06-11	27	i07_protocolo	3.92	92213
2022-06-11	15	fk_documento_conjunto_estilos	3.57	55646
2022-06-11	8	_dta_index_atividade_8_356196319__K5_K2_K3_K1_K24_8066	3.54	734222
2022-06-11	34	IX_RelProtocolo	3.52	146672
2022-06-11	16	pk_documento_conteudo	3.45	1377360
2022-06-11	7	i01_assinatura	3.19	25487
2022-06-11	25	Ind__participante_tia1	2.75	66560
2022-06-11	8	i10_atividade	2.70	475265
2022-06-11	7	fk_assinatura_atividade	2.61	35338
2022-06-11	38	pk_texto_padrao_interno	2.60	5431
2022-06-11	36	IX_SecaoDocumento	2.57	211910
2022-06-11	36	_dta_index_secao_documento_8_1179867270__K3_K1_K2	2.56	211897
2022-06-11	29	IX_DETIC_DataDeAgendamentoDaAplicacaoDiof	2.49	1324
2022-06-11	7	fk_assinatura_tarja_assinatura	2.48	35321
2022-06-11	8	<Ind_protocolo_w_3>	2.39	725256
2022-06-11	36	Ind__secao_documento_tia5	2.20	259817
2022-06-11	36	Ind__secao_documento_tia8	2.20	259818
2022-06-11	25	pk_participante	1.87	97940
2022-06-11	36	_dta_index_secao_documento_8_1179867270__K3_K4_K1_K5_2_6_7_8_9_10_11	1.77	354797
2022-06-11	36	Ind__secao_documento_tia9	1.75	354794
2022-06-11	15	fk_documento_tipo_conferencia	1.68	55372
2022-06-11	12	Ind__contato_tia2	1.55	3031
2022-06-11	12	Ind__contato_tia	1.54	2527
2022-06-11	8	pk_atividade	1.15	1761316
2022-06-11	8	<Ind_atividade_w_2>	0.98	462538
2022-06-11	15	fk_documento_tipo_formulario	0.87	55375
2022-06-11	21	i01_infra_navegador	0.84	103666
2022-06-11	8	<Ind_atividade_w_1>	0.77	884833
2022-06-11	12	pk_contato	0.75	7043
2022-06-11	166	SK01_Log_Whoisactive	0.71	4639
2022-06-11	22	pk_monitoramento_servico	0.66	3314
2022-06-11	9	i04_atributo_andamento	0.60	427795
2022-06-11	27	pk_protocolo	0.55	1391296
2022-06-11	9	i01_atributo_andamento	0.52	1049597
2022-06-11	29	pk_publicacao	0.42	4536
2022-06-11	35	fk_re_secmod_cj_est_i_cj_est_i	0.32	1862
2022-06-11	35	fk_rel_secmod_cj_est_it_secmod	0.32	1861
2022-06-11	35	pk_rel_secao_mod_cj_estilos_it	0.29	2393
2022-06-11	9	IX_DETIC_Indice_Atributo_Andamento	0.29	1617787
2022-06-11	8	i02_atividade	0.22	635679
2022-06-11	5	pk_anexo	0.21	137361
2022-06-11	24	pk_observacao	0.19	13244
2022-06-11	7	pk_assinatura	0.13	227781
2022-06-11	36	pk_secao_documento	0.10	3701923
2022-06-11	40	pk_versao_secao_documento	0.08	16222353
2022-06-11	9	pk_atributo_andamento	0.03	1707531
2022-06-11	21	pk_infra_navegador	0.02	457443
2022-06-11	23	pk_novidade	0.00	1414
2022-06-11	16	pk_documento_conteudo	0.00	349921674
2022-06-11	40	pk_versao_secao_documento	0.00	537391472
2022-06-11	166	SK01_Log_Whoisactive	0.00	94635
2022-06-11	36	pk_secao_documento	0.00	757821
2022-06-11	38	pk_texto_padrao_interno	0.00	36041



Executando a configuração 1, para efetuar rebuild ou reorganize ou nada fazer no índice de acordo com cada caso:

--Configuração 1, novamente o tempo necessário para conclusão depende do horário de execução, nível de fragmentação e do tamanho do banco de dados, nesse caso de uso na estrutura do retrato acima, e execução num sábado por volta das 9 horas demorou:4h e 50 minutos
USE master
EXECUTE dbo.IndexOptimize
@Databases = 'SEI',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30
Dt_Log	    Id_Table Nm_Index	Avg_Fragmentation_In_Percent	Page_Count
2022-06-11	1	pk_acesso	4.92	41862
2022-06-11	12	Ind__contato_tia3	4.83	2566
2022-06-11	5	fk_anexo_base_conhecimento	4.81	16975
2022-06-11	33	fk_rel_prot_assunto_protocolo	4.75	118958
2022-06-11	27	i13_protocolo	4.56	106392
2022-06-11	27	fk_protocolo_hipotese_legal	4.39	61882
2022-06-11	5	i02_anexo	4.26	21887
2022-06-11	8	Ind__atividade_tia7	3.94	1113911
2022-06-11	27	i07_protocolo	3.92	92217
2022-06-11	15	fk_documento_conjunto_estilos	3.57	55648
2022-06-11	8	_dta_index_atividade_8_356196319__K5_K2_K3_K1_K24_8066	3.54	734237
2022-06-11	34	IX_RelProtocolo	3.52	146677
2022-06-11	16	pk_documento_conteudo	3.45	1377408
2022-06-11	4	fk_andamento_marcador_proced	3.42	3971
2022-06-11	7	i01_assinatura	3.19	25489
2022-06-11	32	if4_rel_bloco_unidade	2.97	1347
2022-06-11	25	Ind__participante_tia1	2.76	66562
2022-06-11	8	i10_atividade	2.70	475276
2022-06-11	4	fk_andamento_marcador_marcador	2.67	3481
2022-06-11	28	fk_protocolo_modelo_unidade	2.66	1279
2022-06-11	38	pk_texto_padrao_interno	2.63	5432
2022-06-11	7	fk_assinatura_atividade	2.61	35340
2022-06-11	28	fk_prot_mod_grupo_prot_mod	2.58	1280
2022-06-11	36	IX_SecaoDocumento	2.57	211919
2022-06-11	36	_dta_index_secao_documento_8_1179867270__K3_K1_K2	2.56	211906
2022-06-11	28	Ind__protocolo_modelo_tia3	2.54	1459
2022-06-11	32	if3_rel_bloco_unidade	2.53	1344
2022-06-11	4	i01_andamento_marcador	2.50	5044
2022-06-11	29	IX_DETIC_DataDeAgendamentoDaAplicacaoDiof	2.49	1324
2022-06-11	7	fk_assinatura_tarja_assinatura	2.48	35323
2022-06-11	11	i02_bloco	2.45	1716
2022-06-11	8	<Ind_protocolo_w_3>	2.39	725279
2022-06-11	4	i02_andamento_marcador	2.35	4547
2022-06-11	4	fk_andamento_marcador_usuario	2.34	3468
2022-06-11	28	fk_protocolo_modelo_protocolo	2.34	1455
2022-06-11	19	pk_infra_dado_usuario	2.33	1246
2022-06-11	32	fk_rel_bloco_unidade_unidade	2.24	1293
2022-06-11	36	Ind__secao_documento_tia5	2.20	259826
2022-06-11	36	Ind__secao_documento_tia8	2.20	259827
2022-06-11	28	fk_protocolo_modelo_usuario	2.19	1278
2022-06-11	4	fk_andamento_marcador_unidade	2.11	3465
2022-06-11	2	fk_acesso_externo_participante	2.06	1698
2022-06-11	28	_dta_index_protocolo_modelo_8_907866301__K5_1	2.06	1457
2022-06-11	11	Ind__bloco_tia	2.04	1715
2022-06-11	11	Ind__bloco_tia3	1.98	1715
2022-06-11	32	fk_rel_bloco_unidade_bloco	1.93	1294
2022-06-11	25	pk_participante	1.87	97942
2022-06-11	29	i03_publicacao	1.82	1426
2022-06-11	12	i01_contato	1.81	1877
2022-06-11	32	i07_rel_bloco_unidade	1.80	1607
2022-06-11	36	_dta_index_secao_documento_8_1179867270__K3_K4_K1_K5_2_6_7_8_9_10_11	1.77	354808
2022-06-11	36	Ind__secao_documento_tia9	1.76	354805
2022-06-11	32	i06_rel_bloco_unidade	1.75	1425
2022-06-11	3	fk_acompanhamento_usuario	1.74	4481
2022-06-11	3	fk_acompanhamento_grupo_acompa	1.70	4483
2022-06-11	15	fk_documento_tipo_conferencia	1.68	55374
2022-06-11	32	i05_rel_bloco_unidade	1.63	1470
2022-06-11	12	i11_contato	1.56	1735
2022-06-11	12	Ind__contato_tia2	1.55	3031
2022-06-11	4	Ind__andamento_marcador_wag1	1.55	6715
2022-06-11	3	fk_acompanhamento_unidade	1.54	4478
2022-06-11	12	Ind__contato_tia	1.54	2527
2022-06-11	32	pk_rel_bloco_unidade	1.38	3563
2022-06-11	6	fk_anotacao_usuario	1.35	4376
2022-06-11	3	fk_acompanhamento_protocolo	1.30	5094
2022-06-11	3	idx_acompanhamento_id	1.17	3857
2022-06-11	8	pk_atividade	1.15	1761346
2022-06-11	11	pk_bloco	1.14	2896
2022-06-11	6	fk_anotacao_unidade	1.08	4372
2022-06-11	8	<Ind_atividade_w_2>	0.98	462547
2022-06-11	4	Ind__andamento_marcador_tia	0.97	4522
2022-06-11	18	pk_infra_auditoria	0.95	1795
2022-06-11	6	_dta_index_anotacao_8_637245325__K3_1	0.90	4978
2022-06-11	6	fk_anotacao_protocolo	0.88	4974
2022-06-11	7	fk_assinatura_unidade	0.88	35637
2022-06-11	15	fk_documento_tipo_formulario	0.86	55377
2022-06-11	21	i01_infra_navegador	0.84	103672
2022-06-11	7	fk_assinatura_usuario_autentic	0.84	71294
2022-06-11	6	i01_anotacao	0.83	6481
2022-06-11	8	<Ind_atividade_w_1>	0.77	884850
2022-06-11	24	fk_observacao_unidade	0.76	1308
2022-06-11	12	pk_contato	0.75	7043
2022-06-11	32	fk_rel_blo_uni_grupo_bloco	0.75	1468
2022-06-11	32	fk_rel_blo_uni_usu_revisao	0.75	1468
2022-06-11	166	SK01_Log_Whoisactive	0.71	4781
2022-06-11	7	fk_assinatura_documento	0.68	45320
2022-06-11	22	pk_monitoramento_servico	0.66	3321
2022-06-11	7	IX_assinatura_detic	0.64	47722
2022-06-11	8	_dta_index_atividade_8_356196319__K2_K3_K5_K1_K24_K20_7_18_19_23	0.64	1123609
2022-06-11	1	fk_acesso_unidade	0.63	34578
2022-06-11	7	ak1_assinatura	0.61	55121
2022-06-11	32	fk_rel_blo_uni_usu_comentario	0.61	1468
2022-06-11	8	_dta_index_atividade_8_356196319__K2_K1_K13_K7_K3_K25_K20_K15_4_5_18_22_24	0.60	1550728
2022-06-11	9	i04_atributo_andamento	0.60	427809
2022-06-11	8	i16_atividade	0.59	821847
2022-06-11	8	i07_atividade	0.58	568828
2022-06-11	1	i01_acesso	0.58	50226
2022-06-11	5	fk_anexo_unidade	0.58	16963
2022-06-11	26	pk_procedimento	0.58	11706
2022-06-11	27	Ind__protocolo_wag4	0.57	178004
2022-06-11	8	IX_Atividade	0.57	653125
2022-06-11	27	pk_protocolo	0.55	1391329
2022-06-11	27	Ind__protocolo_wag2	0.55	177999
2022-06-11	5	fk_anexo_usuario	0.55	33745
2022-06-11	8	i01_atividade	0.55	827171
2022-06-11	32	fk_rel_blo_uni_usu_prioridade	0.55	1467
2022-06-11	32	fk_rel_blo_uni_usu_atribuicao	0.55	1467
2022-06-11	8	Ind__atividade_tia2	0.54	740244
2022-06-11	9	i01_atributo_andamento	0.53	1049627
2022-06-11	15	i04_documento	0.53	60541
2022-06-11	27	i02_protocolo	0.52	92675
2022-06-11	8	i09_atividade	0.52	565086
2022-06-11	4	pk_andamento_marcador	0.51	8707
2022-06-11	15	idx_documento_num	0.51	48568
2022-06-11	6	_dta_index_anotacao_8_637245325__K2_K3_K1_K4_K6_5_7_8	0.49	15847
2022-06-11	1	i02_acesso_detic	0.49	45286
2022-06-11	27	fk_protocolo_usuario	0.48	62680
2022-06-11	22	i02_monitoramento_servico	0.47	1054
2022-06-11	8	i06_atividade	0.46	564925
2022-06-11	1	i02_acesso	0.45	40499
2022-06-11	34	fk_rel_proc_doc_usuario	0.45	57401
2022-06-11	8	fk_atividade_usuario_conclusao	0.44	304152
2022-06-11	27	i16_protocolo	0.44	142035
2022-06-11	6	Ind__anotacao_tia	0.43	14627
2022-06-11	24	fk_observacao_protocolo	0.42	1650
2022-06-11	15	fk_documento_procedimento	0.42	111903
2022-06-11	29	pk_publicacao	0.42	4536
2022-06-11	27	i10_protocolo	0.41	98641
2022-06-11	15	fk_documento_unidade_responsav	0.39	56078
2022-06-11	1	fk_acesso_protocolo	0.39	39343
2022-06-11	34	fk_protocolo_protocolo_1	0.39	113268
2022-06-11	8	_dta_index_atividade_8_356196319__K20_K3_K5_K18_K1_K2_K24_7_19_23	0.38	1106222
2022-06-11	8	_dta_index_atividade_8_356196319__K20_K3_K5_K18_K1_K2_K24_7_19_23_9910	0.38	1106271
2022-06-11	34	fk_rel_protocolo_protocolo_uni	0.37	56757
2022-06-11	34	i01_rel_protocolo_protocolo	0.36	96586
2022-06-11	31	pk_rel_bloco_protocolo	0.36	39047
2022-06-11	8	fk_atividade_usuario_origem	0.33	303423
2022-06-11	25	fk_participante_unidade	0.32	41283
2022-06-11	33	fk_rel_protocolo_assunto_uni	0.32	143442
2022-06-11	35	fk_re_secmod_cj_est_i_cj_est_i	0.32	1862
2022-06-11	35	fk_rel_secmod_cj_est_it_secmod	0.32	1861
2022-06-11	27	ie1_protocolo	0.31	95178
2022-06-11	9	i02_atributo_andamento	0.31	936079
2022-06-11	8	i03_atividade	0.31	489300
2022-06-11	2	i04_acesso_externo	0.31	1597
2022-06-11	2	pk_acesso_externo	0.30	5947
2022-06-11	8	Ind__atividade_tia9	0.30	1548826
2022-06-11	25	i01_participante	0.30	55609
2022-06-11	34	fk_protocolo_protocolo_2	0.30	68963
2022-06-11	31	Ind_rel_bloco_protocolo_tia	0.29	22613
2022-06-11	35	pk_rel_secao_mod_cj_estilos_it	0.29	2393
2022-06-11	40	fk_versao_secao_doc_usuario	0.29	233750
2022-06-11	25	IX_Participante	0.29	55607
2022-06-11	9	IX_DETIC_Indice_Atributo_Andamento	0.29	1617821
2022-06-11	27	<Ind_protocolo_w_1>	0.27	178770
2022-06-11	8	fk_atividade_usuario_visualiza	0.27	302306
2022-06-11	6	pk_anotacao	0.27	14130
2022-06-11	1	fk_acesso_usuario	0.26	17276
2022-06-11	1	i03_acesso	0.26	48917
2022-06-11	1	fk_acesso_controle_interno	0.24	17311
2022-06-11	27	i03_protocolo	0.24	120187
2022-06-11	40	fk_versao_secao_doc_unidade	0.24	230871
2022-06-11	28	pk_protocolo_modelo	0.23	6209
2022-06-11	15	fk_documento_serie	0.23	55308
2022-06-11	1	idx_acesso_tipo	0.23	13732
2022-06-11	8	i04_atividade	0.23	891714
2022-06-11	8	i02_atividade	0.22	635692
2022-06-11	5	i01_anexo	0.22	22512
2022-06-11	15	if5_documento	0.22	79763
2022-06-11	15	i03_documento	0.22	70618
2022-06-11	27	i06_protocolo	0.22	78233
2022-06-11	34	<Ind_rel_protocolo_protocolo_w_1>	0.22	115923
2022-06-11	27	<Ind_protocolo_w_2>	0.21	104625
2022-06-11	5	pk_anexo	0.21	137364
2022-06-11	27	i15_protocolo	0.20	92794
2022-06-11	15	i02_documento	0.20	67549
2022-06-11	15	ak_documento	0.19	67534
2022-06-11	24	pk_observacao	0.19	13244
2022-06-11	5	fk_anexo_protocolo	0.19	21313
2022-06-11	2	fk_acesso_externo_documento	0.19	1070
2022-06-11	3	pk_acompanhamento	0.19	27413
2022-06-11	40	i01_versao_secao_documento	0.19	280519
2022-06-11	40	IX_VersaoSecaoDocumento	0.19	280518
2022-06-11	33	fk_rel_prot_assunto_assunto	0.19	116913
2022-06-11	33	pk_rel_protocolo_assunto	0.18	187396
2022-06-11	36	fk_secao_doc_secao_mod	0.18	135401
2022-06-11	27	if4_protocolo	0.18	74563
2022-06-11	15	pk_documento	0.18	203101
2022-06-11	15	i06_documento	0.17	46282
2022-06-11	27	Ind__protocolo_wag3	0.17	47832
2022-06-11	27	ak2_protocolo	0.16	93979
2022-06-11	39	pk_usuario	0.16	1860
2022-06-11	36	i01_secao_documento	0.15	228280
2022-06-11	27	fk_protocolo_protocolo_fed	0.15	47844
2022-06-11	7	pk_assinatura	0.13	227788
2022-06-11	36	pk_secao_documento	0.11	3702019
2022-06-11	40	pk_versao_secao_documento	0.08	16222719
2022-06-11	12	IX_Contato	0.05	1929
2022-06-11	9	pk_atributo_andamento	0.03	1707566
2022-06-11	34	pk_rel_protocolo_protocolo	0.03	214575
2022-06-11	21	pk_infra_navegador	0.02	457465
2022-06-11	23	pk_novidade	0.00	1414
2022-06-11	16	pk_documento_conteudo	0.00	349930304
2022-06-11	40	pk_versao_secao_documento	0.00	537406732
2022-06-11	36	pk_secao_documento	0.00	757844
2022-06-11	38	pk_texto_padrao_interno	0.00	36041
2022-06-11	166	SK01_Log_Whoisactive	0.00	95541


Executando a configuração 2, para efetuar rebuild ou reorganize ou nada fazer no índice nas bases de sistema de acordo com cada caso:

--Configuração 2, demorou apenas: 2 segundos
USE master
EXECUTE dbo.IndexOptimize
@Databases = 'SYSTEM_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30


Executando a configuração 3, para atualização completa de todas as estatísticas do banco SEI:

--Configuração 3 – Demorou 2 horas e 30, mais por conta do término da configuração 1, e pelo longo tempo sem uma atualização completa de estatísticas.
USE master
EXECUTE dbo.IndexOptimize
@Databases = 'SEI',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL'


Executando a configuração 4, para atualização somente das estatísticas modificadas do banco SEI:

--Configuração 4 – Demorou 1 minuto e 40 segundos para finalizar.
USE master
EXECUTE dbo.IndexOptimize
@Databases = 'SEI',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'


Implementação da política de manutenção do histórico fragmentação de índices, manutenção de estatísticas e manutenção de índices no banco de dados do SEI de Produção (172.16.0.87)

JOBs implementados baseados nas técnicas abordadas:

  • MANUTENCAO - SEI Rebuild ou Reorganize de indexes fragmentados e Atualização completa de Statistics do SEI → Agendado para ser executado toda sexta feita as 15 horas. Composto de 3 passos, cito: 1 - Tira “Retrato” da estrutura dos índices, 2 - Rebuild or reorganize e 3 – Update full de estatisticas;

  • MANUTENCAO - SEI Update Somente Statistics Modificadas → Agendado para ser executado todo dia as 21 horas. Composto apenas de 1 passo, onde faz a atualização das estatísticas onde houve modificação de registros;

  • MANUTENCAO - SYSTEM Rebuild ou Reorganize e Update full Statistics → Agendado para ser executado toda quinta feita às 16 horas. Composto de 2 passos, cito: 1 - Rebuild or reorganize e 2 – Update full de estatisticas;

  • DBA - Load Index Fragmentation → Agendado para ser executado de segunda a sexta as 02 horas da madrugada. Tira um “retrato” de toda a estrutura de fragmentação dos índices, armazena na tabela: Traces.dbo.Index_Fragmentation_History para posterior acompanhamento da percentagem de fragmentação de cada índice.


Conclusão

As ferramentas abordadas no estudo e implementação são muito poderosas e completas quando se fala de monitoramento e manutenção de instâncias de bancos de dados SQL Server. Os recursos usados durante o estudo foi uma pequena parte do que está contido nestas. Existem uma variedade de configurações e recursos que podem ser abordados de acordo com cada cenário existente. Assim sendo, ainda existe muita coisa a ser feita valendo dos recursos que a ferramenta oferece.


Referencias:

https://www.brentozar.com/
https://ola.hallengren.com/
https://github.com/soupowertuning/Script_SQLServer_Alerts