SQL Probe Bitmap 1008 high CPU

  • We have a very simple query and appropriate index has been created on both tables. But both tables are doing index scan. 
    Second one is doing Index scan with PROBE([Bitmap1011].[dbo].[CAVOLS_Keys].[Requis] as [CK].[Requis],.[dbo].[CAVOLS_Keys].[TestCod] as [CK].[TestCod])

    Is that due to change in column length? How do i overcome this situation as this is one of the CPU intesive query and running very frequently?

    SELECT Min(C.id)   lowestID, 
           Count(C.id) repeatedRows, 
           C.requis, 
           C.testcod 
    INTO   #tempc 
    FROM   cavols C 
    WHERE  NOT EXISTS(SELECT 1 
                      FROM   cavols_keys CK 
                      WHERE  C.requis = CK.requis 
                             AND C.testcod = CK.testcod) 
    GROUP  BY requis, 
              testcod 

    CREATE NONCLUSTERED INDEX [AI_CAVLOS_REQUIS] 
      ON [dbo].[CAVOLS] ( [id] ASC, [requis] ASC, [testcod] ASC ) 

    CREATE NONCLUSTERED INDEX [AI_CAVLOS_Keys_REQUIS] 
      ON [dbo].[CAVOLS_Keys] ( [requis] ASC, [testcod] ASC ) 

    Column datatype:
    [CAVOLS].Requis :Varchar(64) null,             
    [CAVOLS].[TestCod] : varchar(26) null
    CAVOLS_Keys].Requis :Varchar(64) not null,
    [CAVOLS_Keys].[TestCod] : varchar(5) not null

  • How many rows are actually in each table?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Tuesday, January 30, 2018 6:52 AM

    How many rows are actually in each table?

    32530570 for the Cavlos and 176607102 for the second one.

  • Actual execution plan is always preferred over estimated plan - more information.
    What proportion of CAVOLS_Keys is duplicated on REQUIS, TESTCOd?

    Try forcing the join type:

    -- QO chooses hash join

    OPTION (LOOP JOIN)

    OPTION (HASH JOIN)

    OPTION (MERGE JOIN)

    You will have to change this index, like so:

    CREATE

    NONCLUSTERED INDEX [AI_CAVLOS_REQUIS]


    ON [dbo].[CAVOLS] ([requis] ASC, [testcod] ASC ) INCLUDE ([id])

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work - Tuesday, January 30, 2018 9:17 AM

    Actual execution plan is always preferred over estimated plan - more information.
    What proportion of CAVOLS_Keys is duplicated on REQUIS, TESTCOd?

    Try forcing the join type:

    -- QO chooses hash join

    OPTION (LOOP JOIN)

    OPTION (HASH JOIN)

    OPTION (MERGE JOIN)

    You will have to change this index, like so:

    CREATE

    NONCLUSTERED INDEX [AI_CAVLOS_REQUIS]


    ON [dbo].[CAVOLS] ([requis] ASC, [testcod] ASC ) INCLUDE ([id])

    I have created index and tried with all option, but doesnt make any difference.

  • EasyBoy - Tuesday, January 30, 2018 11:52 AM

    ChrisM@Work - Tuesday, January 30, 2018 9:17 AM

    Actual execution plan is always preferred over estimated plan - more information.
    What proportion of CAVOLS_Keys is duplicated on REQUIS, TESTCOd?

    Try forcing the join type:

    -- QO chooses hash join

    OPTION (LOOP JOIN)

    OPTION (HASH JOIN)

    OPTION (MERGE JOIN)

    You will have to change this index, like so:

    CREATE

    NONCLUSTERED INDEX [AI_CAVLOS_REQUIS]


    ON [dbo].[CAVOLS] ([requis] ASC, [testcod] ASC ) INCLUDE ([id])

    I have created index and tried with all option, but doesnt make any difference.

    Can you post an "actual" as opposed to "estimated" execution plan, please - with the proposed new index in place (and no query hints)? Cheers.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work - Wednesday, January 31, 2018 2:28 AM

    EasyBoy - Tuesday, January 30, 2018 11:52 AM

    ChrisM@Work - Tuesday, January 30, 2018 9:17 AM

    Actual execution plan is always preferred over estimated plan - more information.
    What proportion of CAVOLS_Keys is duplicated on REQUIS, TESTCOd?

    Try forcing the join type:

    -- QO chooses hash join

    OPTION (LOOP JOIN)

    OPTION (HASH JOIN)

    OPTION (MERGE JOIN)

    You will have to change this index, like so:

    CREATE

    NONCLUSTERED INDEX [AI_CAVLOS_REQUIS]


    ON [dbo].[CAVOLS] ([requis] ASC, [testcod] ASC ) INCLUDE ([id])

    I have created index and tried with all option, but doesnt make any difference.

    Can you post an "actual" as opposed to "estimated" execution plan, please - with the proposed new index in place (and no query hints)? Cheers.

    Please find attached actual execution plan.

  • Thank you. Can you run this please, and report back with the number of rows returned?

    ;WITH Agg AS (

    SELECT

    MIN(C.id) lowestID,

    COUNT(C.id) repeatedRows,

    C.requis,

    C.testcod

    FROM cavols C

    GROUP BY requis, testcod

    )

    SELECT *

    FROM Agg a

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work - Wednesday, January 31, 2018 7:21 AM

    Thank you. Can you run this please, and report back with the number of rows returned?

    ;WITH Agg AS (

    SELECT

    MIN(C.id) lowestID,

    COUNT(C.id) repeatedRows,

    C.requis,

    C.testcod

    FROM cavols C

    GROUP BY requis, testcod

    )

    SELECT *

    FROM Agg a

    32523961 rows.

  • Thanks.
    Playing with a similar data set here. Merge join appears to be slowest and hash join fastest, but that might be memory-dependant in your case. Here's an alternative query form which might be faster with your data:

    WITH InterestingData AS (

    SELECT requis, testcod FROM cavols

    EXCEPT

    SELECT requis, testcod FROM cavols_keys

    )

    SELECT

    MIN(C.id) lowestID,

    COUNT(C.id) repeatedRows,

    C.requis,

    C.testcod

    FROM cavols C

    INNER JOIN InterestingData i

    ON i.requis = c.testcod AND i.requis = c.testcod

    GROUP BY requis, testcod


    Your last query plan is serial, which is unexpected because of the table sizes. What resources do you have on your server?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply