Avaliação de criação de Índices no banco de dados do SEI, seguindo as sugestões do Query Analyzer do SQL Server

O objetivo do estudo é melhorar o tempo de resposta e também reduzir o tempo de processamento do servidor de banco de dados do SEI (172.16.0.87), nas consultas feitas pela aplicação, quando essas consultas são demoradas e exibem boa parcela de processamento.

A ferramenta Traces que monitora o funcionamento do SQL Server foi ativada no período de 27/05/2022 às 9:00 de sexta feira até 30/05/2022 às 8:00 horas da segunda feira. Durante esse período identificou 302 registros com indicações de criação de índices, porém várias dessas indicações se repetiram por se tratar de repetições de uma mesma query de entrada. Os dados de cada query são avaliados abaixo:

 

Query 1

Quantidade registros: 96
Tempo gasto varia de: 1 a 25 minutos

use sei

SELECT DISTINCT

p.id_protocolo AS id_protocolo,

p.protocolo_formatado AS protocolo_formatado,

CONVERT(DATE,dta_geracao) AS protocolo_dta_geracao,

p.sta_protocolo AS protocolo_sta_protocolo,

(SELECT id_tipo_procedimento FROM procedimento WHERE id_procedimento = p.id_protocolo) AS protocolo_tipo_procedimento_id,

(SELECT id_serie FROM documento WHERE id_documento = p.id_protocolo) AS protocolo_id_serie,

p.sta_estado AS protocolo_sta_estado,

CASE WHEN us.sta_tipo IN (0, 1) THEN us.id_usuario

WHEN us.sta_tipo IN (2, 3) THEN NULL END AS id_usuario,

u.id_unidade AS id_unidade_geracao,

p.sta_nivel_acesso_local AS protocolo_sta_nivel_acesso_local,

p.sta_nivel_acesso_global AS protocolo_sta_nivel_acesso_global,

p.id_hipotese_legal,

a.nome AS arquivo,

a.tamanho AS tamanho

FROM

protocolo p

INNER JOIN unidade u ON

(u.id_unidade = p.id_unidade_geradora)

AND u.id_orgao <> 0

INNER JOIN usuario us ON

(us.id_usuario = p.id_usuario_gerador)

LEFT JOIN anexo a

ON a.id_protocolo = p.id_protocolo

/*
Detalhes de índice ausentes de ExecutionPlan1.sqlplan
O Query Processor estima que a implementação do índice a seguir pode melhorar o custo da consulta em 40,0142%.
*/

/*
USE [sei]
GO
CREATE NONCLUSTERED INDEX [<Ind_protocolo_w_1>]
ON [dbo].[protocolo] ([id_unidade_geradora])
INCLUDE ([id_protocolo],[id_usuario_gerador],[protocolo_formatado],[sta_protocolo],[dta_geracao],
         [sta_estado],[sta_nivel_acesso_local],[sta_nivel_acesso_global],[id_hipotese_legal])
GO
*/

Query sem sugestão de índice	Query com sugestão de índice (Gerado em 15 segundos)

 

Query 2

Quantidade registros: 86
Tempo gasto médio: 1 segundo

use sei
SELECT rel_protocolo_protocolo.id_protocolo_2 AS idprotocolo2 
FROM rel_protocolo_protocolo 
WHERE rel_protocolo_protocolo.sta_associacao=1 AND rel_protocolo_protocolo.id_protocolo_1 IN (521834)

/*
Detalhes de índice ausentes de ExecutionPlan1.sqlplan
O Query Processor estima que a implementação do índice a seguir pode melhorar o custo da consulta em 97,9918%.
*/

/*
USE [sei]
GO
CREATE NONCLUSTERED INDEX [<Ind_rel_protocolo_protocolo_w_1>]
ON [dbo].[rel_protocolo_protocolo] ([id_protocolo_1],[sta_associacao])

GO


*/

Query sem sugestão de índice	Query com sugestão de índice (Gerado em 9 segundos)

Query 3

Quantidade registros: 57
Tempo gasto varia de: 1 a 5 minutos.

use sei
SELECT a.id_protocolo                      AS id_protocolo_processo_movimentacao
     , pro.protocolo_formatado             AS protocolo_processo_movimentacao
     , b.id_unidade                        AS id_unidade_destino_movimentacao
     , b.sigla                             AS unidade_destino_movimentacao
     , c.id_unidade                        AS id_unidade_origem_movimentacao
     , c.sigla                             AS unidade_origem_movimentacao
     , d.nome                              AS usuario_envio_movimentacao
     , CONVERT(DATE, dth_abertura)         AS dt_envio_movimentacao
     , tproc.nome                          AS tipo_procedimento_movimentacao
     , SUBSTRING(tproc.nome, 1,(CHARINDEX(':',tproc.nome, 1))) AS tipologia_movimentacao
FROM atividade a
         INNER JOIN unidade b ON a.id_unidade = b.id_unidade
         INNER JOIN unidade c ON a.id_unidade_origem = c.id_unidade
         INNER JOIN usuario d ON a.id_usuario_origem = d.id_usuario
         INNER JOIN protocolo pro ON pro.id_protocolo = a.id_protocolo
         INNER JOIN procedimento proce ON pro.id_protocolo= proce.id_procedimento
        INNER JOIN tipo_procedimento tproc ON tproc.id_tipo_procedimento= proce.id_tipo_procedimento
WHERE id_tarefa = 32 AND a.id_unidade <> a.id_unidade_origem

/*
Detalhes de índice ausentes de ExecutionPlan1.sqlplan
O Query Processor estima que a implementação do índice a seguir pode melhorar o custo da consulta em 72,6506%.
*/

/*
USE [sei]
GO
CREATE NONCLUSTERED INDEX [<Ind_atividade_w_1>]
ON [dbo].[atividade] ([id_tarefa])
INCLUDE ([id_protocolo],[id_unidade],[dth_abertura],[id_unidade_origem],[id_usuario_origem])
GO
*/

 

Query sem sugestão de índice	Query com sugestão de índice (Gerado em 2m e 07 seg)

 

Query 4

Quantidade registros: 26
Tempo gasto varia de: 4 a 5 minutos.

use sei
SELECT
	p_pai.id_protocolo AS processo_pendanalise_id,
	p_pai.protocolo_formatado AS processo_pendanalise,
	pa.id_protocolo AS doc_pendanalise_processo_id,
	p.id_protocolo AS doc_pendanalise_documento_id,
	p.protocolo_formatado AS doc_pendanalise_documento,
	d.id_serie AS doc_pendanalise_serie_id,
	pa.sta_abertura AS doc_pendanalise_sta_abertura,
	u.sigla AS doc_pendanalise_unidade_sigla
FROM
	protocolo p
INNER JOIN
	rel_protocolo_protocolo rpp ON rpp.id_protocolo_2 = p.id_protocolo AND rpp.sta_associacao IN (1, 7)
-- INSERIDO O NUMERO DO PROCESSO DO DOCUMENTO
INNER JOIN protocolo p_pai ON rpp.id_protocolo_1 = p_pai.id_protocolo
-- FIM
INNER JOIN
   (SELECT
		distinct
        a.id_protocolo,
		a.id_unidade,
		a.dth_conclusao,
		(SELECT 'Aberto') AS sta_abertura
   FROM
		atividade a join unidade unid on a.id_unidade = unid.id_unidade
   WHERE
		id_tarefa IN (1, 29, 32, 64)
		-- IDs que Abre o Processo na Unidade
	    -- 1   Processo @NIVEL_ACESSO@@GRAU_SIGILO@ gerado@DATA_AUTUACAO@@HIPOTESE_LEGAL@
	    -- 29  Reabertura do processo na unidade
	    -- 32  Processo remetido pela unidade @UNIDADE@
 	    -- 64  Reabertura do processo

		AND id_atividade IN (SELECT
								MAX(ativ.id_atividade) AS id_atividade
							 FROM
								atividade ativ join unidade unid on ativ.id_unidade = unid.id_unidade
							 WHERE
								unid.sin_protocolo = 'N' and
								ativ.id_tarefa IN (1, 29, 32, 64, 28, 41, 63)
								-- IDs que Abre o Processo na Unidade
									  -- 1   Processo @NIVEL_ACESSO@@GRAU_SIGILO@ gerado@DATA_AUTUACAO@@HIPOTESE_LEGAL@
									  -- 29  Reabertura do processo na unidade
									  -- 32  Processo remetido pela unidade @UNIDADE@
									  -- 64  Reabertura do processo
								-- IDs que Conclui o Processo na Unidade
									  -- 28  Conclusão do processo na unidade
									  -- 41  Conclusão automática de processo na unidade
									  -- 63  Processo concluído
							 GROUP BY
								ativ.id_protocolo, ativ.id_unidade)

	UNION ALL
		SELECT
		   a.id_protocolo,
		   a.id_unidade,
		   a.dth_conclusao,
		   (SELECT 'Concluído') AS sta_abertura
		FROM
			atividade a
		WHERE
		    a.id_protocolo NOT IN (SELECT
						   DISTINCT id_protocolo
								FROM
									atividade
								WHERE
									id_tarefa IN (1, 29, 32, 64)
									AND id_atividade IN (
										SELECT
											MAX(ativ.id_atividade) AS id_atividade
										FROM
											atividade ativ join unidade unid on ativ.id_unidade = unid.id_unidade
										WHERE
											unid.sin_protocolo = 'N' and
											ativ.id_tarefa IN (1, 29, 32, 64, 28, 41, 63)
										GROUP BY
											ativ.id_protocolo, ativ.id_unidade)
									) AND

			id_tarefa IN (28, 41, 63)
		   -- IDs que Conclui o Processo na Unidade
						   -- 28  Conclusão do processo na unidade
						   -- 41  Conclusão automática do processo na unidade
						   -- 63  Processo concluído
		   AND id_atividade IN (SELECT
									MAX(ativ.id_atividade) AS id_atividade
								FROM
									atividade ativ join unidade unid on ativ.id_unidade = unid.id_unidade
								WHERE
									unid.sin_protocolo = 'N' AND
									ativ.id_tarefa IN (1, 29, 32, 64, 28, 41, 63)
								GROUP BY
									ativ.id_protocolo)

    ) pa ON rpp.id_protocolo_1 = pa.id_protocolo

INNER JOIN
	unidade u ON u.id_unidade = pa.id_unidade
INNER JOIN
	documento d ON d.id_documento = p.id_protocolo

WHERE
	p.sta_protocolo IN ('G', 'R')
	AND p.sta_estado = 0
	AND p.sta_nivel_acesso_local = 1
-- 	AND p.id_hipotese_legal = 3

/*
Detalhes de índice ausentes de ExecutionPlan1.sqlplan
O Query Processor estima que a implementação do índice a seguir pode melhorar o custo da consulta em 9,13525%.
*/

/*
USE [sei]
GO
CREATE NONCLUSTERED INDEX [<Ind_atividade_w_2>]
ON [dbo].[atividade] ([id_tarefa])
INCLUDE ([id_atividade],[id_protocolo])
GO
*/

Query sem sugestão de índice	Query com sugestão de índice (Gerado em 12 seg)

 

Query 5

Quantidade registros: 4
Tempo gasto varia de: 8 a 20 segundos.

use sei
SELECT DISTINCT
	protocolo.id_protocolo AS id_protocolo,
	protocolo.protocolo_formatado AS protocolo_formatado,
	atividade.id_unidade
FROM
	atividade
	INNER JOIN protocolo ON atividade.id_protocolo = protocolo.id_protocolo
	INNER JOIN unidade ON atividade.id_unidade = unidade.id_unidade
WHERE
	atividade.dth_conclusao IS NULL
	AND protocolo.sta_protocolo = 'P'
	AND protocolo.sta_estado IN (0,4)
	AND protocolo.sta_nivel_acesso_global <> 2
	AND unidade.id_orgao <> 0
	AND atividade.id_usuario_atribuicao IS NULL

/*
Detalhes de índice ausentes de ExecutionPlan1.sqlplan
O Query Processor estima que a implementação do índice a seguir poderia melhorar o custo da consulta em 63,7988%.
*/

/*
USE [sei]
GO
CREATE NONCLUSTERED INDEX [<Ind_protocolo_w_2>]
ON [dbo].[protocolo] ([sta_protocolo])
INCLUDE ([id_protocolo],[protocolo_formatado],[sta_estado],[sta_nivel_acesso_global])
GO
*/

Query sem sugestão de índice	Query com sugestão de índice (Gerado em 26 seg)