0

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?

enter image description here

enter image description here

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.

3

From the documentation:

Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors up to 64 processors.

Found here:

https://msdn.microsoft.com/en-us/library/ms189094.aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.