I'm having some headaches with a procedure.
Long story short, I'm using now a hint to force an index ( now, the query runs in 0.1 secs, after this, it was running in 2min )
but the question is, why is it using Parallelism, if it is set to 0?
Funny part is, when the query uses a different index, it does not use parallelism. Whe it uses this index ( IX_tbReps_cod_perito_primeiro_cod_status ), it uses parallelism.
For more information, the query:
alter PROCEDURE [dbo].[SP_GDLIC_REP_PARTS_SEARCH](
@P_NUMBER VARCHAR(23),
@P_YEAR INT = NULL,
@P_UNIT_COD INT = NULL,
@P_GENERIC_PARAM VARCHAR(100) = NULL,
@P_STATUS_COD INT = NULL,
@P_EXPERT_ID INT = NULL,
@P_PAGE_SIZE INT = 10,
@P_PAGE_INDEX INT = 1
)AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @P_NUMBER = '0' OR @P_NUMBER = '' SET @P_NUMBER = NULL
IF @P_YEAR = 0 SET @P_YEAR = NULL
IF @P_EXPERT_ID = 0 SET @P_EXPERT_ID = NULL
IF @P_STATUS_COD = -1 SET @P_STATUS_COD = NULL
IF @P_UNIT_COD = 0 SET @P_UNIT_COD = NULL
IF (@P_GENERIC_PARAM = '0' OR @P_GENERIC_PARAM = '') SET @P_GENERIC_PARAM = NULL
IF (@P_PAGE_INDEX IS NULL) OR (@P_PAGE_INDEX = 0) SET @P_PAGE_INDEX = 1;
SELECT
ROW_NUMBER() OVER(ORDER BY TBP.COD_REP DESC) AS ITEM,
TBP.COD_PECA,
TBP.COD_SEQ,
TBP.COD_REP,
TBP.NUMERO,
TBP.ANO,
TBP.COD_TIPOPECA,
TBTP.TIPOPECA,
TBP.IDENTIFICACAO,
ISNULL(TBP.OBSERVACAO, '') as OBSERVACAO,
TBP.QUANTIDADE_OLD,
TBP.DATAAPREENSAO,
TBP.LACREENTRADA,
TBP.DATALIBERACAO,
TBP.LACRESAIDA,
TBP.CONSUMIDA,
TBP.EXCLUIDA,
TBP.TIPO,
TBP.DESCRICAO,
TBP.QUANTIDADE,
TBP.DESCRICAO_QTD,
TBP.ANALISE_CG,
ISNULL(TBP.PLACA, '') as PLACA,
ISNULL(TBP.CHASSI, '')as CHASSI,
TBP.N_SERIE,
TBP.NANALISES,
TBP.COD_ETPCT,
TBP.RESULTADO,
TBP.QUANT_ANALISADA,
TBP.PESO,
TBP.MODELO,
TBP.COR,
TBP.MARCA,
TBP.ESTADOCONSERVACAO,
TBP.CHASSISTATUS,
TBP.PLACASTATUS,
TBP.cod_morfologia,
TBP.restante_material,
TBP.descricao_resultado,
ISNULL(TBM.nome, '') as DIMENSAO,
TBP.objeto,
M.descricao as des_droga_aparente,
TBED.tipoent as des_droga_definitiva
INTO #TBTEMPPARTS
--FROM TBREPS REP WITH(NOLOCK)
--Alteração feita por: DBA
--Data: 12-feb-2016
FROM TBREPS REP WITH(nolock,index([IX_tbReps_cod_perito_primeiro_cod_status]))
--
JOIN TBPECAS TBP WITH(NOLOCK) ON REP.COD_REP = TBP.COD_REP
LEFT OUTER JOIN LSTTIPOSPECA TBTP ON TBTP.COD_TIPOPECA = TBP.COD_TIPOPECA
LEFT OUTER JOIN LSTMEDIDAS TBM ON TBP.DESCRICAO_QTD = TBM.cod_medida
LEFT JOIN lstMorfologias M ON TBP.cod_morfologia = M.cod_morfologia
LEFT JOIN lstTipoEnt TBED ON TBP.cod_etpct = TBED.cod_tipoent
WHERE
1=1
AND (rep.numero = @P_NUMBER OR @P_NUMBER IS NULL)
AND (rep.ano = @P_YEAR OR @P_YEAR IS NULL)
AND (rep.COD_PERITO_PRIMEIRO = @P_EXPERT_ID OR @P_EXPERT_ID IS NULL)
AND (rep.cod_unidade = @P_UNIT_COD OR @P_UNIT_COD IS NULL)
AND (rep.cod_status IN ( SELECT item FROM dbo.fnSplitInt(@P_GENERIC_PARAM, ',') ) OR @P_GENERIC_PARAM IS NULL)
AND (rep.cod_status = @P_STATUS_COD OR @P_STATUS_COD IS NULL)
ORDER BY TBP.COD_REP DESC
SELECT
(SELECT COUNT(*) FROM #TBTEMPPARTS) AS QTDE_REG,
ROW_NUMBER() OVER(Partition By cod_rep Order By cod_rep) AS ROWNUM,
#TBTEMPPARTS.*
FROM #TBTEMPPARTS
WHERE ITEM BETWEEN (@P_PAGE_INDEX - 1) * @P_PAGE_SIZE AND @P_PAGE_INDEX * @P_PAGE_SIZE;
SET NOCOUNT OFF;
END
And the Index:
CREATE NONCLUSTERED INDEX [IX_tbReps_cod_perito_primeiro_cod_status] ON [dbo].[tbReps]
(
[cod_perito_primeiro] ASC,
[cod_status] ASC
)
INCLUDE ( [data]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
There is this key lookup I can't fix but I will think about it later.
