Cannot persuade SQL to bypass Bitmap Create

Question

Cannot persuade SQL to bypass Bitmap Create

SSChampion

Points: 12230

August 29, 2003 at 12:40 pm

I have a stumper here. Not quite sure why SQL Server is being so stubborn, so I'll post it out here for everyone to take a look at.

--

The situation is this: I have a stored procedure which has a fairly simple set of JOIN criteria and a 5K-10K resultset (Out of ~750K records):



SELECT

  WO.idsWorkOrderNo

, ISNULL(WO.strWorkType, '') AS strWorkType

, ISNULL(WS.strZone, '') AS strZone

, ISNULL(WO.strPO, '') AS strPO

FROM tblWorkOrder WO

  INNER JOIN tblWorksite WS

ON WO.strProj = WS.strProj

ANDWO.strWorksite = WS.strWorksiteCode

  INNER JOIN tblWorkOrderDetail WOD

ON WO.idsWorkOrderNo = WOD.intWorkOrder

WHERE

    WO.strProj = @Project

AND WOD.strPO IS NULL

AND WOD.dtmComplete IS NOT NULL

AND WO.dtmClosed IS NULL

GROUP BY

  WO.idsWorkOrderNo

, WO.strWorkType

, WS.strZone

, WO.strPO

ORDER BY

  WO.idsWorkOrderNo

When this procedure runs, it produces the following execution plan:



       |--Compute Scalar(DEFINE:([Expr1003]=isnull([WO].[strWorkType], '          '), [Expr1004]=isnull([WS].[strZone], '          '), [Expr1005]=isnull([WO].[strPO], '')))

            |--Parallelism(Gather Streams, ORDER BY:([WO].[idsWorkOrderNo] ASC))

                 |--Sort(ORDER BY:([WO].[idsWorkOrderNo] ASC))

                      |--Hash Match(Left Semi Join, HASH:([WO].[idsWorkOrderNo])=([WOD].[intWorkOrder]))

                           |--Bitmap(HASH:([WO].[idsWorkOrderNo]), DEFINE:([Bitmap1007]))

                           |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([WO].[idsWorkOrderNo]))

                           |         |--Hash Match(Inner Join, HASH:([WO].[strWorkSite])=([WS].[strWorkSiteCode]), RESIDUAL:([WO].[strWorkSite]=[WS].[strWorkSiteCode]))

                           |              |--Bitmap(HASH:([WO].[strWorkSite]), DEFINE:([Bitmap1006]))

                           |              |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([WO].[strWorkSite]))

                           |              |         |--Clustered Index Scan(OBJECT:([dbPDS].[dbo].[tblWorkOrder].[CIX_tblWorkOrder_CompleteDate] AS [WO]), WHERE:([WO].[dtmClosed]=NULL AND [WO].[strProj]=[@Project]))

                           |              |--Parallelism(Repartition Streams, PARTITION COLUMNS:([WS].[strWorkSiteCode]), WHERE:(PROBE([Bitmap1006])=TRUE))

                           |                   |--Table Scan(OBJECT:([dbPDS].[dbo].[tblWorkSite] AS [WS]), WHERE:([WS].[strProj]=[@Project]))

                           |--Parallelism(Repartition Streams, PARTITION COLUMNS:([WOD].[intWorkOrder]), WHERE:(PROBE([Bitmap1007])=TRUE))

                                |--Clustered Index Seek(OBJECT:([dbPDS].[dbo].[tblWorkOrderDetail].[CIX_tblWorkOrderDetail_CompletedDate] AS [WOD]), SEEK:([WOD].[dtmComplete] IsNotNull),  WHERE:([WOD].[strPO]=NULL) ORDERED FORWARD)

however, and this is the stumper part, when I execute the exact same script in QA for the exact same parameter, I get a COMPLETELY DIFFERENT execution plan:



  |--Compute Scalar(DEFINE:([Expr1003]=isnull([WO].[strWorkType], '          '), [Expr1004]=isnull([WS].[strZone], '          '), [Expr1005]=isnull([WO].[strPO], '')))

       |--Sort(ORDER BY:([WO].[idsWorkOrderNo] ASC))

            |--Nested Loops(Left Semi Join, OUTER REFERENCES:([WO].[idsWorkOrderNo]) WITH PREFETCH)

                 |--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([dbPDS].[dbo].[tblWorkSite] AS [WS]))

                 |    |--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[strWorkSite]) WITH PREFETCH)

                 |         |--Filter(WHERE:([WO].[dtmClosed]=NULL))

                 |         |    |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([dbPDS].[dbo].[tblWorkOrder] AS [WO]))

                 |         |         |--Index Seek(OBJECT:([dbPDS].[dbo].[tblWorkOrder].[IX_tblWorkOrder_Proj] AS [WO]), SEEK:([WO].[strProj]=[@Project]) ORDERED FORWARD)

                 |         |--Index Seek(OBJECT:([dbPDS].[dbo].[tblWorkSite].[PK_tblWorkSite] AS [WS]), SEEK:([WS].[strProj]=[@Project] AND [WS].[strWorkSiteCode]=[WO].[strWorkSite]) ORDERED FORWARD)

                 |--Filter(WHERE:([WOD].[strPO]=NULL))

                      |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([dbPDS].[dbo].[tblWorkOrderDetail] AS [WOD]))

                           |--Index Seek(OBJECT:([dbPDS].[dbo].[tblWorkOrderDetail].[PK_tblWorkOrderDetail] AS [WOD]), SEEK:([WOD].[intWorkOrder]=[WO].[idsWorkOrderNo]),  WHERE:([WOD].[dtmComplete]<>NULL) ORDERED FORWARD)

When run SIDE-by-SIDE, the non-procedure plan execute in 6% of the time that the procedure's execution plan does, for the same parameter and resultset.

--

I have recompiled the stored procedure manually, and SQL Server stubbornly refuses to give up on the HASH MATCH with BITMAP CREATE that it deems is most efficient for the script. I guess, my biggest question is: how is it that the exact same script, when run outside of a procedure (that has nothing else in it) produces a different query plan than the same script inside a stored procedure, given a mark for recompile?

--

I have never seen this situtation before. Every time I have done performance diagnostics on execution plans in the past, I have used the same techniques for assessing bottlenecks as I have done here. I strip out the SQL from the procedure, and line by line, rework the script until I am happy with the execution plan. Every time up until now, I have marked the stored procedure for recompile on altering it to reflect my changes, and was happy to find the new and improved execution plan on the next run of the proc. Somehow, something isn't working correctly for this (seemingly) benign script that is giving me headaches. Please help or comment if you can.

--

Jay

September 1, 2003 at 8:00 am

This was removed by the editor as SPAM

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

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

Try a MAXDOP 1 query hint. Or increase the cost threshold for parallelism. The query analyzer sees the SP execution time, but not that of the raw query, as exceeding the threshold. And, unfortunately, a parallel query plan is often slower than a single-threaded plan.

--Jonathan

jpipes SSChampion Points: 12230 More actions · Answer 2

Thanks for the reply. I'll try that out...

However, why would QA not effectively view the execution plan as the same as any other execution by the query optimizer? It's using the same compile and parse engine.

--

Thanks again,

Jay

jpipes SSChampion Points: 12230 More actions · Answer 3

OK,

I have to admit, I wasn't entirely convinced it was the SMP environment that was throwing things for a loop in my query testing, so I decided to run the same test on a single processor server, and the same kind of thing happened. I marked the procedure for recompile, cleared the buffers, and executed the stored procedure side by side with the script from the stored procedure, and it produced two different (6% vs 94% execution costs) plans for the same exact query.

Here is the tst script:



dbcc dropcleanbuffers

go

sp_recompile poa_GetWorkOrders

go

set noexec on

go

--

declare @Project CHAR(7)

set @Project = 'CC 0053'

--

SELECT

  WO.idsWorkOrderNo

, ISNULL(WO.strWorkType, '') AS strWorkType

, ISNULL(WS.strZone, '') AS strZone

, ISNULL(WO.strPO, '') AS strPO

FROM

  tblWorkOrder WO

  INNER JOIN tblWorksite WS

ON WO.strProj = WS.strProj

ANDWO.strWorksite = WS.strWorksiteCode

  INNER JOIN tblWorkOrderDetail WOD

ON WO.idsWorkOrderNo = WOD.intWorkOrder

WHERE

  WO.strProj = @Project

AND  WOD.strPO IS NULL

AND   WOD.dtmComplete IS NOT NULL

AND   WO.dtmClosed IS NULL

GROUP BY

  WO.idsWorkOrderNo

, WO.strWorkType

, WS.strZone

, WO.strPO

ORDER BY

  idsWorkOrderNo

--

EXEC poa_GetWorkOrders;2 @Project

And here are the DIFFERENT execution plans generated. The first one is the exact same script running inside of the procedure (run with the same parameter). Note that almost all the relevant LOOP and JOIN sections are different. Note that the CLUSTERED INDEX SCAN is what is making the biggest difference to the overall execution cost:



-- Script Plan outside of stored procedure:

  |--Compute Scalar(DEFINE:([Expr1003]=isnull([WO].[strWorkType], '          '), [Expr1004]=isnull([WS].[strZone], '          '), [Expr1005]=isnull([WO].[strPO], '')))

       |--Sort(DISTINCT ORDER BY:([WO].[idsWorkOrderNo] ASC, [WS].[strZone] ASC))

            |--Filter(WHERE:([WOD].[strPO]=NULL))

                 |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([dbPDS].[dbo].[tblWorkOrderDetail] AS [WOD]))

                      |--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[idsWorkOrderNo]))

                           |--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([dbPDS].[dbo].[tblWorkSite] AS [WS]))

                           |    |--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[strWorkSite]))

                           |         |--Filter(WHERE:([WO].[dtmClosed]=NULL))

                           |         |    |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([dbPDS].[dbo].[tblWorkOrder] AS [WO]))

                           |         |         |--Index Seek(OBJECT:([dbPDS].[dbo].[tblWorkOrder].[IX_WorkOrder_ProjWorksite] AS [WO]), SEEK:([WO].[strProj]=[@Project]) ORDERED FORWARD)

                           |         |--Index Seek(OBJECT:([dbPDS].[dbo].[tblWorkSite].[PK_tblWorkSite] AS [WS]), SEEK:([WS].[strProj]=[@Project] AND [WS].[strWorkSiteCode]=[WO].[strWorkSite]) ORDERED FORWARD)

                           |--Index Seek(OBJECT:([dbPDS].[dbo].[tblWorkOrderDetail].[PK_tblWorkOrderDetail] AS [WOD]), SEEK:([WOD].[intWorkOrder]=[WO].[idsWorkOrderNo]),  WHERE:([WOD].[dtmComplete]<>NULL) ORDERED FORWARD)

-- Stored Procedure Plan:

       |--Compute Scalar(DEFINE:([Expr1003]=isnull([WO].[strWorkType], '          '), [Expr1004]=isnull([WS].[strZone], '          '), [Expr1005]=isnull([WO].[strPO], '')))

            |--Sort(ORDER BY:([WO].[idsWorkOrderNo] ASC))

                 |--Nested Loops(Left Semi Join, OUTER REFERENCES:([WO].[idsWorkOrderNo]) WITH PREFETCH)

                      |--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([dbPDS].[dbo].[tblWorkSite] AS [WS]))

                      |    |--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[strWorkSite]) WITH PREFETCH)

                      |         |--Clustered Index Scan(OBJECT:([dbPDS].[dbo].[tblWorkOrder].[CIX_tblWorkOrder_CompleteDate] AS [WO]), WHERE:([WO].[dtmClosed]=NULL AND [WO].[strProj]=[@Project]))

                      |         |--Index Seek(OBJECT:([dbPDS].[dbo].[tblWorkSite].[PK_tblWorkSite] AS [WS]), SEEK:([WS].[strProj]=[@Project] AND [WS].[strWorkSiteCode]=[WO].[strWorkSite]) ORDERED FORWARD)

                      |--Filter(WHERE:([WOD].[strPO]=NULL))

                           |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([dbPDS].[dbo].[tblWorkOrderDetail] AS [WOD]))

                                |--Index Seek(OBJECT:([dbPDS].[dbo].[tblWorkOrderDetail].[PK_tblWorkOrderDetail] AS [WOD]), SEEK:([WOD].[intWorkOrder]=[WO].[idsWorkOrderNo]),  WHERE:([WOD].[dtmComplete]<>NULL) ORDERED FORWARD)

What's going on here???

--

Would anyone who has experienced a similar issue please chime in?

Thanks,

Jay

Edited by - jpipes on 09/03/2003 08:26:04 AM

Jonathan SSC-Insane Points: 20427 More actions · Answer 4

Yes, I've seen this sort of thing before. If you can't find some trick to make the SP find a different plan (just for fun, try WHERE WS.strProj = @Project instead of WHERE WO.strProj = @Project), then you may have to use a hint to get the thing to use the nonclustered index.

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hi Jay,

quote:

When this procedure runs, it produces the following execution plan:



       |--Compute Scalar(DEFINE:([Expr1003]=isnull([WO].[strWorkType], '          '), [Expr1004]=isnull([WS].[strZone], '          '), [Expr1005]=isnull([WO].[strPO], '')))

            |--Parallelism(Gather Streams, ORDER BY:([WO].[idsWorkOrderNo] ASC))

                 |--Sort(ORDER BY:([WO].[idsWorkOrderNo] ASC))

                      |--Hash Match(Left Semi Join, HASH:([WO].[idsWorkOrderNo])=([WOD].[intWorkOrder]))

                           |--Bitmap(HASH:([WO].[idsWorkOrderNo]), DEFINE:([Bitmap1007]))

                           |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([WO].[idsWorkOrderNo]))

                           |         |--Hash Match(Inner Join, HASH:([WO].[strWorkSite])=([WS].[strWorkSiteCode]), RESIDUAL:([WO].[strWorkSite]=[WS].[strWorkSiteCode]))

                           |              |--Bitmap(HASH:([WO].[strWorkSite]), DEFINE:([Bitmap1006]))

                           |              |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([WO].[strWorkSite]))

                           |              |         |--Clustered Index Scan(OBJECT:([dbPDS].[dbo].[tblWorkOrder].[CIX_tblWorkOrder_CompleteDate] AS [WO]), WHERE:([WO].[dtmClosed]=NULL AND [WO].[strProj]=[@Project]))

                           |              |--Parallelism(Repartition Streams, PARTITION COLUMNS:([WS].[strWorkSiteCode]), WHERE:(PROBE([Bitmap1006])=TRUE))

                           |                   |--Table Scan(OBJECT:([dbPDS].[dbo].[tblWorkSite] AS [WS]), WHERE:([WS].[strProj]=[@Project]))

                           |--Parallelism(Repartition Streams, PARTITION COLUMNS:([WOD].[intWorkOrder]), WHERE:(PROBE([Bitmap1007])=TRUE))

                                |--Clustered Index Seek(OBJECT:([dbPDS].[dbo].[tblWorkOrderDetail].[CIX_tblWorkOrderDetail_CompletedDate] AS [WOD]), SEEK:([WOD].[dtmComplete] IsNotNull),  WHERE:([WOD].[strPO]=NULL) ORDERED FORWARD)

however, and this is the stumper part, when I execute the exact same script in QA for the exact same parameter, I get a COMPLETELY DIFFERENT execution plan:



  |--Compute Scalar(DEFINE:([Expr1003]=isnull([WO].[strWorkType], '          '), [Expr1004]=isnull([WS].[strZone], '          '), [Expr1005]=isnull([WO].[strPO], '')))

       |--Sort(ORDER BY:([WO].[idsWorkOrderNo] ASC))

            |--Nested Loops(Left Semi Join, OUTER REFERENCES:([WO].[idsWorkOrderNo]) WITH PREFETCH)

                 |--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([dbPDS].[dbo].[tblWorkSite] AS [WS]))

                 |    |--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[strWorkSite]) WITH PREFETCH)

                 |         |--Filter(WHERE:([WO].[dtmClosed]=NULL))

                 |         |    |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([dbPDS].[dbo].[tblWorkOrder] AS [WO]))

                 |         |         |--Index Seek(OBJECT:([dbPDS].[dbo].[tblWorkOrder].[IX_tblWorkOrder_Proj] AS [WO]), SEEK:([WO].[strProj]=[@Project]) ORDERED FORWARD)

                 |         |--Index Seek(OBJECT:([dbPDS].[dbo].[tblWorkSite].[PK_tblWorkSite] AS [WS]), SEEK:([WS].[strProj]=[@Project] AND [WS].[strWorkSiteCode]=[WO].[strWorkSite]) ORDERED FORWARD)

                 |--Filter(WHERE:([WOD].[strPO]=NULL))

                      |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([dbPDS].[dbo].[tblWorkOrderDetail] AS [WOD]))

                           |--Index Seek(OBJECT:([dbPDS].[dbo].[tblWorkOrderDetail].[PK_tblWorkOrderDetail] AS [WOD]), SEEK:([WOD].[intWorkOrder]=[WO].[idsWorkOrderNo]),  WHERE:([WOD].[dtmComplete]<>NULL) ORDERED FORWARD)

no solution, but another question.

how did you manage to get the execution plan into this kind of ascii art ???

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jpipes SSChampion Points: 12230 More actions · Answer 6

quote:
..how did you manage to get the execution plan into this kind of ascii art ???..

Ahh, the magic of copy/paste...

quote:
If you can't find some trick to make the SP find a different plan (just for fun, try WHERE W...

jonathan,

thanks for the reply. I'm aware I can use query hints and will try reordering the where like you said, just to try something new. But my question is how is it possible that the execution plan of two identical scripts, one run inside a proc, one run outside a proc, are different. That's what my question is really directed at; that's what I've never seen before. You said you've run into this before. When, and could you reproduce it? Meaning, can you remember whether you tracked it down to a specific type of JOIN, or something particular about the query you remember?

Thanks again for your reply,

Jay

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

quote:
Ahh, the magic of copy/paste...

???

Share you knowledge with me, o great fountain of wisdom

or put it this way. I have no clue how you did this trick. Please explain slowly to a dummy.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jpipes SSChampion Points: 12230 More actions · Answer 8



SET SHOWPLAN_TEXT ON

GO

SET NOEXEC ON

GO

SELECT 2.Early FROM Morning 2

ORDER BY BrainPower DESC

GO

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

quote:



SET SHOWPLAN_TEXT ON

GO

SET NOEXEC ON

GO

SELECT 2.Early FROM Morning 2

ORDER BY BrainPower DESC

GO

thanks, but actually I'm disappointed!

I didn't expect this to be that easy.

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]