SQL Probe Bitmap 1008 high CPU

Question

Post reply

SQL Probe Bitmap 1008 high CPU

EasyBoy

SSCertifiable

Points: 5301
More actions
January 30, 2018 at 6:34 am

#343693

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

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

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

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)

EasyBoy SSCertifiable Points: 5301 More actions · Answer 2

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

EasyBoy SSCertifiable Points: 5301 More actions · Answer 4

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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

EasyBoy SSCertifiable Points: 5301 More actions · Answer 6

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

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

EasyBoy SSCertifiable Points: 5301 More actions · Answer 8

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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