Query Not Returning Values and process is waiting on CXPACKET

Good Morning All,
I have a query from a user that I have been asked to take a look at. I
have narrowed down the issue to one of the parameters in the query.
When the query covers 2002 thru 2003 one execution plan is issued when
the query covers only a portion of 2003 a second execution plan is
issued. The issue is that the 2002 thru 2003 executes fine, the query
that executes a portion over a portion of 2003 data does not. If I
check the process it has an item that has a wait type of CXPACKET and it
just hangs never returning. I was wondering if anyone has seen this
kind of behavior before. The two different plans where created by only
changing the data range.
An advise would help.
Thanks
Show Plan text (Non Working)
|--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[item_ident], [dim_inv_info_tbl].[opco_num]))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[vendor_ident]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[opco_ident]))
| |
|--Filter(WHERE:([fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
| | | |--Bookmark
Lookup(BOOKMARK:([Bmk1001]), OBJECT:([pfg_dm].[dbo].[fact_sales] AS
[fact_sales]))
| | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([dim_inv_info_tbl].[opco_num]) WITH PREFETCH)
| | | |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
AS [dim_inv_info_tbl]), ORDERED FORWARD)
| | | |--Index
Seek(OBJECT:([pfg_dm].[dbo].[fact_sales].[idx_fact_sales_06] AS
[fact_sales]),
SEEK:([fact_sales].[opco_num]=[dim_inv_info_tbl].[opco_num] AND
[fact_sales].[calendar_date] >= 'Nov 5 2003 12:00AM' AND
[fact_sales].[calendar_date] < 'Nov 19 2003 12:00AM') ORDERED FORWARD)
| | |--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[PK_Dim_OPCO] AS [Dim_OPCO]),
SEEK:([Dim_OPCO].[opco_ident]=[fact_sales].[opco_ident]) ORDERED FORWARD)
|
|--Filter(WHERE:((((((((((((((((((((((((((((((((((((((((((((((((((((((((like([Dim_Vendor].[opco_vendor_name],
'All Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%',
NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)) AND
[Dim_Vendor].[opco_vendor_num]<>3136) AND
[Dim_Vendor].[opco_vendor_num]<>3571) AND
[Dim_Vendor].[opco_vendor_num]<>3572) AND
[Dim_Vendor].[opco_vendor_num]<>3573) AND
[Dim_Vendor].[opco_vendor_num]<>3673) AND
[Dim_Vendor].[opco_vendor_num]<>3860) AND
[Dim_Vendor].[opco_vendor_num]<>3861) AND
[Dim_Vendor].[opco_vendor_num]<>4290) AND
[Dim_Vendor].[opco_vendor_num]<>4530) AND
[Dim_Vendor].[opco_vendor_num]<>5373) AND
[Dim_Vendor].[opco_vendor_num]<>5760) AND
[Dim_Vendor].[opco_vendor_num]<>5797) AND
[Dim_Vendor].[opco_vendor_num]<>6064) AND
[Dim_Vendor].[opco_vendor_num]<>6065) AND
[Dim_Vendor].[opco_vendor_num]<>6066) AND
[Dim_Vendor].[opco_vendor_num]<>6073) AND
[Dim_Vendor].[opco_vendor_num]<>6329) AND
[Dim_Vendor].[opco_vendor_num]<>6525) AND
[Dim_Vendor].[opco_vendor_num]<>6607) AND
[Dim_Vendor].[opco_vendor_num]<>6684) AND
[Dim_Vendor].[opco_vendor_num]<>7240) AND
[Dim_Vendor].[opco_vendor_num]<>7241) AND
[Dim_Vendor].[opco_vendor_num]<>8325) AND
[Dim_Vendor].[opco_vendor_num]<>8326) AND
[Dim_Vendor].[opco_vendor_num]<>16229) AND
[Dim_Vendor].[opco_vendor_num]<>16230) AND
[Dim_Vendor].[opco_vendor_num]<>17756) AND
[Dim_Vendor].[opco_vendor_num]<>18121) AND
[Dim_Vendor].[opco_vendor_num]<>19552) AND
[Dim_Vendor].[opco_vendor_num]<>23852) AND
[Dim_Vendor].[opco_vendor_num]<>23853) AND
[Dim_Vendor].[opco_vendor_num]<>25307) AND
[Dim_Vendor].[opco_vendor_num]<>25558) AND
[Dim_Vendor].[opco_vendor_num]<>30003) AND
[Dim_Vendor].[opco_vendor_num]<>30004) AND
[Dim_Vendor].[opco_vendor_num]<>33068) AND
[Dim_Vendor].[opco_vendor_num]<>36230) AND
[Dim_Vendor].[opco_vendor_num]<>36236) AND
[Dim_Vendor].[opco_vendor_num]<>37756) AND
[Dim_Vendor].[opco_vendor_num]<>49365) AND
[Dim_Vendor].[opco_vendor_num]<>62380) AND
[Dim_Vendor].[opco_vendor_num]<>69485))
| |--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
[Dim_Vendor]),
SEEK:([Dim_Vendor].[vendor_ident]=[fact_sales].[vendor_ident]),
WHERE:((((((((((((((([Dim_Vendor].[opco_vendor_num]<>173 AND
[Dim_Vendor].[opco_vendor_num]<>372) AND
[Dim_Vendor].[opco_vendor_num]<>429) AND
[Dim_Vendor].[opco_vendor_num]<>448) AND
[Dim_Vendor].[opco_vendor_num]<>600) AND
[Dim_Vendor].[opco_vendor_num]<>617) AND
[Dim_Vendor].[opco_vendor_num]<>641) AND
[Dim_Vendor].[opco_vendor_num]<>713) AND
[Dim_Vendor].[opco_vendor_num]<>1325) AND
[Dim_Vendor].[opco_vendor_num]<>1672) AND
[Dim_Vendor].[opco_vendor_num]<>1850) AND
[Dim_Vendor].[opco_vendor_num]<>1940) AND
[Dim_Vendor].[opco_vendor_num]<>2210) AND
[Dim_Vendor].[opco_vendor_num]<>2215) AND
[Dim_Vendor].[opco_vendor_num]<>3130) AND
[Dim_Vendor].[opco_vendor_num]<>3131) ORDERED FORWARD)
|--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]),
SEEK:([Dim_item].[item_ident]=[fact_sales].[item_ident]),
WHERE:([Dim_item].[opco_num]=[dim_inv_info_tbl].[opco_num]) ORDERED FORWARD)
Show plan text (working)
|--Parallelism(Gather Streams, ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Hash Match(Inner Join,
HASH:([Dim_OPCO].[opco_ident])=([fact_sales].[opco_ident]),
RESIDUAL:([fact_sales].[opco_ident]=[Dim_OPCO].[opco_ident]))
|--Parallelism(Broadcast)
| |--Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[idx_dim_opco_01] AS [Dim_OPCO]))
|--Hash Match(Inner Join,
HASH:([fact_sales].[opco_num],
[fact_sales].[item_num])=([dim_inv_info_tbl].[opco_num],
[dim_inv_info_tbl].[item_num]),
RESIDUAL:([dim_inv_info_tbl].[opco_num]=[fact_sales].[opco_num] AND
[fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_num]))
| |--Hash Match(Inner Join,
HASH:([Dim_item].[opco_num],
[Dim_item].[item_ident])=([fact_sales].[opco_num],
[fact_sales].[item_ident]),
RESIDUAL:([Dim_item].[opco_num]=[fact_sales].[opco_num] AND
[Dim_item].[item_ident]=[fact_sales].[item_ident]))
| |--Bitmap(HASH:([Dim_item].[opco_num],
[Dim_item].[item_ident]), DEFINE:([Bitmap1006]))
| | |--Parallelism(Repartition
Streams, PARTITION COLUMNS:([Dim_item].[opco_num], [Dim_item].[item_ident]))
| | |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]))
| |--Parallelism(Repartition Streams,
PARTITION COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_ident]),
WHERE:(PROBE([Bitmap1006])=TRUE))
| |--Hash Match(Inner Join,
HASH:([Dim_Vendor].[vendor_ident])=([fact_sales].[vendor_ident]),
RESIDUAL:([fact_sales].[vendor_ident]=[Dim_Vendor].[vendor_ident]))
| |--Parallelism(Broadcast)
| | |--Nested Loops(Left
Anti Semi Join, WHERE:([Dim_Vendor].[opco_vendor_num]=[Expr1005]))
| |
|--Parallelism(Gather Streams)
| | |
|--Filter(WHERE:((((((((((((((like([Dim_Vendor].[opco_vendor_name], 'All
Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%', NULL))
OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)))
| | |
|--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
[Dim_Vendor]), WHERE:([Dim_Vendor].[opco_vendor_num]<>173))
| | |--Constant Scan
| |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[fact_sales].[PK_fact_sales] AS
[fact_sales]), WHERE:([fact_sales].[calendar_date]>='Nov 5 2002
12:00AM' AND [fact_sales].[calendar_date]<'Nov 19 2003 12:00AM'))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([dim_inv_info_tbl].[opco_num], [dim_inv_info_tbl].[item_num]))
|--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
AS [dim_inv_info_tbl]))This is a multi-part message in MIME format.
--=_NextPart_000_02C4_01C3B993.52F36600
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Sounds like parallelism is the problem. Try adding the following to the end
of your query:
OPTION (MAXDOP 1)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"sfibich" <sfibich@.pfgc.com> wrote in message
news:#ISwrybuDHA.2180@.TK2MSFTNGP09.phx.gbl...
Good Morning All,
I have a query from a user that I have been asked to take a look at. I
have narrowed down the issue to one of the parameters in the query.
When the query covers 2002 thru 2003 one execution plan is issued when
the query covers only a portion of 2003 a second execution plan is
issued. The issue is that the 2002 thru 2003 executes fine, the query
that executes a portion over a portion of 2003 data does not. If I
check the process it has an item that has a wait type of CXPACKET and it
just hangs never returning. I was wondering if anyone has seen this
kind of behavior before. The two different plans where created by only
changing the data range.
An advise would help.
Thanks
Show Plan text (Non Working)
|--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[item_ident], [dim_inv_info_tbl].[opco_num]))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[vendor_ident]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[opco_ident]))
| |
|--Filter(WHERE:([fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
| | | |--Bookmark
Lookup(BOOKMARK:([Bmk1001]), OBJECT:([pfg_dm].[dbo].[fact_sales] AS
[fact_sales]))
| | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([dim_inv_info_tbl].[opco_num]) WITH PREFETCH)
| | | |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
AS [dim_inv_info_tbl]), ORDERED FORWARD)
| | | |--Index
Seek(OBJECT:([pfg_dm].[dbo].[fact_sales].[idx_fact_sales_06] AS
[fact_sales]),
SEEK:([fact_sales].[opco_num]=[dim_inv_info_tbl].[opco_num] AND
[fact_sales].[calendar_date] >= 'Nov 5 2003 12:00AM' AND
[fact_sales].[calendar_date] < 'Nov 19 2003 12:00AM') ORDERED FORWARD)
| | |--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[PK_Dim_OPCO] AS [Dim_OPCO]),
SEEK:([Dim_OPCO].[opco_ident]=[fact_sales].[opco_ident]) ORDERED FORWARD)
|
|--Filter(WHERE:((((((((((((((((((((((((((((((((((((((((((((((((((((((((like
([Dim_Vendor].[opco_vendor_name],
'All Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%',
NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)) AND
[Dim_Vendor].[opco_vendor_num]<>3136) AND
[Dim_Vendor].[opco_vendor_num]<>3571) AND
[Dim_Vendor].[opco_vendor_num]<>3572) AND
[Dim_Vendor].[opco_vendor_num]<>3573) AND
[Dim_Vendor].[opco_vendor_num]<>3673) AND
[Dim_Vendor].[opco_vendor_num]<>3860) AND
[Dim_Vendor].[opco_vendor_num]<>3861) AND
[Dim_Vendor].[opco_vendor_num]<>4290) AND
[Dim_Vendor].[opco_vendor_num]<>4530) AND
[Dim_Vendor].[opco_vendor_num]<>5373) AND
[Dim_Vendor].[opco_vendor_num]<>5760) AND
[Dim_Vendor].[opco_vendor_num]<>5797) AND
[Dim_Vendor].[opco_vendor_num]<>6064) AND
[Dim_Vendor].[opco_vendor_num]<>6065) AND
[Dim_Vendor].[opco_vendor_num]<>6066) AND
[Dim_Vendor].[opco_vendor_num]<>6073) AND
[Dim_Vendor].[opco_vendor_num]<>6329) AND
[Dim_Vendor].[opco_vendor_num]<>6525) AND
[Dim_Vendor].[opco_vendor_num]<>6607) AND
[Dim_Vendor].[opco_vendor_num]<>6684) AND
[Dim_Vendor].[opco_vendor_num]<>7240) AND
[Dim_Vendor].[opco_vendor_num]<>7241) AND
[Dim_Vendor].[opco_vendor_num]<>8325) AND
[Dim_Vendor].[opco_vendor_num]<>8326) AND
[Dim_Vendor].[opco_vendor_num]<>16229) AND
[Dim_Vendor].[opco_vendor_num]<>16230) AND
[Dim_Vendor].[opco_vendor_num]<>17756) AND
[Dim_Vendor].[opco_vendor_num]<>18121) AND
[Dim_Vendor].[opco_vendor_num]<>19552) AND
[Dim_Vendor].[opco_vendor_num]<>23852) AND
[Dim_Vendor].[opco_vendor_num]<>23853) AND
[Dim_Vendor].[opco_vendor_num]<>25307) AND
[Dim_Vendor].[opco_vendor_num]<>25558) AND
[Dim_Vendor].[opco_vendor_num]<>30003) AND
[Dim_Vendor].[opco_vendor_num]<>30004) AND
[Dim_Vendor].[opco_vendor_num]<>33068) AND
[Dim_Vendor].[opco_vendor_num]<>36230) AND
[Dim_Vendor].[opco_vendor_num]<>36236) AND
[Dim_Vendor].[opco_vendor_num]<>37756) AND
[Dim_Vendor].[opco_vendor_num]<>49365) AND
[Dim_Vendor].[opco_vendor_num]<>62380) AND
[Dim_Vendor].[opco_vendor_num]<>69485))
| |--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
[Dim_Vendor]),
SEEK:([Dim_Vendor].[vendor_ident]=[fact_sales].[vendor_ident]),
WHERE:((((((((((((((([Dim_Vendor].[opco_vendor_num]<>173 AND
[Dim_Vendor].[opco_vendor_num]<>372) AND
[Dim_Vendor].[opco_vendor_num]<>429) AND
[Dim_Vendor].[opco_vendor_num]<>448) AND
[Dim_Vendor].[opco_vendor_num]<>600) AND
[Dim_Vendor].[opco_vendor_num]<>617) AND
[Dim_Vendor].[opco_vendor_num]<>641) AND
[Dim_Vendor].[opco_vendor_num]<>713) AND
[Dim_Vendor].[opco_vendor_num]<>1325) AND
[Dim_Vendor].[opco_vendor_num]<>1672) AND
[Dim_Vendor].[opco_vendor_num]<>1850) AND
[Dim_Vendor].[opco_vendor_num]<>1940) AND
[Dim_Vendor].[opco_vendor_num]<>2210) AND
[Dim_Vendor].[opco_vendor_num]<>2215) AND
[Dim_Vendor].[opco_vendor_num]<>3130) AND
[Dim_Vendor].[opco_vendor_num]<>3131) ORDERED FORWARD)
|--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]),
SEEK:([Dim_item].[item_ident]=[fact_sales].[item_ident]),
WHERE:([Dim_item].[opco_num]=[dim_inv_info_tbl].[opco_num]) ORDERED FORWARD)
Show plan text (working)
|--Parallelism(Gather Streams, ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Hash Match(Inner Join,
HASH:([Dim_OPCO].[opco_ident])=([fact_sales].[opco_ident]),
RESIDUAL:([fact_sales].[opco_ident]=[Dim_OPCO].[opco_ident]))
|--Parallelism(Broadcast)
| |--Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[idx_dim_opco_01] AS [Dim_OPCO]))
|--Hash Match(Inner Join,
HASH:([fact_sales].[opco_num],
[fact_sales].[item_num])=([dim_inv_info_tbl].[opco_num],
[dim_inv_info_tbl].[item_num]),
RESIDUAL:([dim_inv_info_tbl].[opco_num]=[fact_sales].[opco_num] AND
[fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_num]))
| |--Hash Match(Inner Join,
HASH:([Dim_item].[opco_num],
[Dim_item].[item_ident])=([fact_sales].[opco_num],
[fact_sales].[item_ident]),
RESIDUAL:([Dim_item].[opco_num]=[fact_sales].[opco_num] AND
[Dim_item].[item_ident]=[fact_sales].[item_ident]))
| |--Bitmap(HASH:([Dim_item].[opco_num],
[Dim_item].[item_ident]), DEFINE:([Bitmap1006]))
| | |--Parallelism(Repartition
Streams, PARTITION COLUMNS:([Dim_item].[opco_num], [Dim_item].[item_ident]))
| | |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]))
| |--Parallelism(Repartition Streams,
PARTITION COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_ident]),
WHERE:(PROBE([Bitmap1006])=TRUE))
| |--Hash Match(Inner Join,
HASH:([Dim_Vendor].[vendor_ident])=([fact_sales].[vendor_ident]),
RESIDUAL:([fact_sales].[vendor_ident]=[Dim_Vendor].[vendor_ident]))
| |--Parallelism(Broadcast)
| | |--Nested Loops(Left
Anti Semi Join, WHERE:([Dim_Vendor].[opco_vendor_num]=[Expr1005]))
| |
|--Parallelism(Gather Streams)
| | |
|--Filter(WHERE:((((((((((((((like([Dim_Vendor].[opco_vendor_name], 'All
Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%', NULL))
OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)))
| | |
|--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
[Dim_Vendor]), WHERE:([Dim_Vendor].[opco_vendor_num]<>173))
| | |--Constant Scan
| |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[fact_sales].[PK_fact_sales] AS
[fact_sales]), WHERE:([fact_sales].[calendar_date]>='Nov 5 2002
12:00AM' AND [fact_sales].[calendar_date]<'Nov 19 2003 12:00AM'))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([dim_inv_info_tbl].[opco_num], [dim_inv_info_tbl].[item_num]))
|--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
AS [dim_inv_info_tbl]))
--=_NextPart_000_02C4_01C3B993.52F36600
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Sounds like parallelism is the =problem. Try adding the following to the end of your query:
OPTION (MAXDOP 1)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"sfibich" wrote in message news:#ISwrybuDHA.2180=@.TK2MSFTNGP09.phx.gbl...Good Morning All,I have a query from a user that I have been asked to =take a look at. I have narrowed down the issue to one of the parameters in =the query. When the query covers 2002 thru 2003 one execution plan is =issued when the query covers only a portion of 2003 a second execution plan =is issued. The issue is that the 2002 thru 2003 executes fine, =the query that executes a portion over a portion of 2003 data does not. =If I check the process it has an item that has a wait type of CXPACKET =and it just hangs never returning. I was wondering if anyone has seen =this kind of behavior before. The two different plans where created =by only changing the data range.An advise would help.ThanksShow Plan text (Non =Working) |--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC, =[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC, =[dim_inv_info_tbl].[case_qty_on_hand] ASC)) =|--Parallelism(Gather Streams) &=nbsp; |--Nested Loops(Inner Join, OUTER =REFERENCES:([fact_sales].[item_ident], [dim_inv_info_tbl].[opco_num])) &n=bsp; |--Nested Loops(Inner Join, OUTER REFERENCES:([fact_sales].[vendor_ident])) = &=nbsp; | |--Nested Loops(Inner Join, OUTER REFERENCES:([fact_sales].[opco_ident])) &n=bsp; &nb=sp; | | |--Filter(WHERE:([fact_sales].[item_num]=3D[dim_inv_info_tbl].[item_n=um])) &nbs=p; | | | |--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([pfg_dm].[dbo].[fact_sales] AS [fact_sales])) &nb=sp; | |
20
| |--Nested Loops(Inner =Join, OUTER REFERENCES:([dim_inv_info_tbl].[opco_num]) WITH PREFETCH) = | | | = |--Clustered Index Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_=01] AS [dim_inv_info_tbl]), ORDERED FORWARD) &=nbsp; | | | = |--Index =Seek(OBJECT:([pfg_dm].[dbo].[fact_sales].[idx_fact_sales_06] AS [fact_sales]), SEEK:([fact_sales].[opco_num]=3D[dim_inv_info_tbl].[opco_num] AND [fact_sales].[calendar_date] >=3D 'Nov 5 2003 12:00AM' AND [fact_sales].[calendar_date] < 'Nov 19 2003 12:00AM') ORDERED FORWARD) &=nbsp; | | |--Clustered Index Seek(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[PK_Dim_OPCO] AS [Dim_OPCO]), =SEEK:([Dim_OPCO].[opco_ident]=3D[fact_sales].[opco_ident]) ORDERED FORWARD) &=nbsp; | |--Filter(WHERE:(((((((((((((((((((((((((((((((((((((((((((((((((((((=(((like([Dim_Vendor].[opco_vendor_name], 'All Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], ='Awrey%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)) AND [Dim_Vendor].[opco_vendor_num]3136) AND [Dim_Vendor].[opco_vendor_num]3571) AND [Dim_Vendor].[opco_vendor_num]3572) AND [Dim_Vendor].[opco_vendor_num]3573) AND [Dim_Vendor].[opco_vendor_num]3673) AND [Dim_Vendor].[opco_vendor_num]3860) AND [Dim_Vendor].[opco_vendor_num]3861) AND [Dim_Vendor].[opco_vendor_num]4290) AND [Dim_Vendor].[opco_vendor_num]4530) AND [Dim_Vendor].[opco_vendor_num]5373) AND [Dim_Vendor].[opco_vendor_num]5760) AND [Dim_Vendor].[opco_vendor_num]5797) AND [Dim_Vendor].[opco_vendor_num]6064) AND [Dim_Vendor].[opco_vendor_num]6065) AND [Dim_Vendor].[opco_vendor_num]6066) AND [Dim_Vendor].[opco_vendor_num]6073) AND [Dim_Vendor].[opco_vendor_num]6329) AND [Dim_Vendor].[opco_vendor_num]6525) AND [Dim_Vendor].[opco_vendor_num]6607) AND [Dim_Vendor].[opco_vendor_num]6684) AND [Dim_Vendor].[opco_vendor_num]7240) AND [Dim_Vendor].[opco_vendor_num]7241) AND [Dim_Vendor].[opco_vendor_num]8325) AND [Dim_Vendor].[opco_vendor_num]8326) AND [Dim_Vendor].[opco_vendor_num]16229) AND [Dim_Vendor].[opco_vendor_num]16230) AND [Dim_Vendor].[opco_vendor_num]17756) AND [Dim_Vendor].[opco_vendor_num]18121) AND [Dim_Vendor].[opco_vendor_num]19552) AND [Dim_Vendor].[opco_vendor_num]23852) AND [Dim_Vendor].[opco_vendor_num]23853) AND [Dim_Vendor].[opco_vendor_num]25307) AND [Dim_Vendor].[opco_vendor_num]25558) AND [Dim_Vendor].[opco_vendor_num]30003) AND [Dim_Vendor].[opco_vendor_num]30004) AND [Dim_Vendor].[opco_vendor_num]33068) AND [Dim_Vendor].[opco_vendor_num]36230) AND [Dim_Vendor].[opco_vendor_num]36236) AND [Dim_Vendor].[opco_vendor_num]37756) AND [Dim_Vendor].[opco_vendor_num]49365) AND [Dim_Vendor].[opco_vendor_num]62380) AND [Dim_Vendor].[opco_vendor_num]69485)) &n=bsp; &nb=sp; | |--Clustered Index Seek(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS [Dim_Vendor]), SEEK:([Dim_Vendor].[vendor_ident]=3D[fact_sales].[vendor_ident]), WHERE:((((((((((((((([Dim_Vendor].[opco_vendor_num]173 AND [Dim_Vendor].[opco_vendor_num]372) AND [Dim_Vendor].[opco_vendor_num]429) AND [Dim_Vendor].[opco_vendor_num]448) AND [Dim_Vendor].[opco_vendor_num]600) AND [Dim_Vendor].[opco_vendor_num]617) AND [Dim_Vendor].[opco_vendor_num]641) AND [Dim_Vendor].[opco_vendor_num]713) AND [Dim_Vendor].[opco_vendor_num]1325) AND [Dim_Vendor].[opco_vendor_num]1672) AND [Dim_Vendor].[opco_vendor_num]1850) AND [Dim_Vendor].[opco_vendor_num]1940) AND [Dim_Vendor].[opco_vendor_num]2210) AND [Dim_Vendor].[opco_vendor_num]2215) AND [Dim_Vendor].[opco_vendor_num]3130) AND [Dim_Vendor].[opco_vendor_num]3131) ORDERED FORWARD) &=nbsp; |--Clustered Index =Seek(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]), =SEEK:([Dim_item].[item_ident]=3D[fact_sales].[item_ident]), WHERE:([Dim_item].[opco_num]=3D[dim_inv_info_tbl].[opco_num]) =ORDERED FORWARD)Show plan text (working) |--Parallelism(Gather Streams, ORDER BY:([Dim_OPCO].[opco_num] ASC, [Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC, =[dim_inv_info_tbl].[case_qty_on_hand] ASC)) |--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC, [Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC, =[dim_inv_info_tbl].[case_qty_on_hand] ASC)) &nbs=p; |--Hash Match(Inner Join, HASH:([Dim_OPCO].[opco_ident])=3D([fact_sales].[opco_ident]), RESIDUAL:([fact_sales].[opco_ident]=3D[Dim_OPCO].[opco_ident]))&n=bsp; &nb=sp; |--Parallelism(Broadcast) &n=bsp; | |--Index Scan(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[idx_dim_opco_01] AS [Dim_OPCO])) &nb=sp; |--Hash Match(Inner Join, HASH:([fact_sales].[opco_num], [fact_sales].[item_num])=3D([dim_inv_info_tbl].[opco_num], [dim_inv_info_tbl].[item_num]), RESIDUAL:([dim_inv_info_tbl].[opco_num]=3D[fact_sales].[opco_num] =AND [fact_sales].[item_num]=3D[dim_inv_info_tbl].[item_num])) &n=bsp; &nb=sp; |--Parallelism(Repartition Streams, PARTITION COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_num])) &n=bsp; &nb=sp; | |--Hash Match(Inner Join, =HASH:([Dim_item].[opco_num], [Dim_item].[item_ident])=3D([fact_sales].[opco_num], [fact_sales].[item_ident]), RESIDUAL:([Dim_item].[opco_num]=3D[fact_sales].[opco_num] AND [Dim_item].[item_ident]=3D[fact_sales].[item_ident])) = &=nbsp; | |--Bitmap(HASH:([Dim_item].[opco_num], [Dim_item].[item_ident]), DEFINE:([Bitmap1006]))  =; = | | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([Dim_item].[opco_num], [Dim_item].[item_ident])) &n=bsp; &nb=sp; | | |--Clustered Index Scan(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item])) &nb=sp; &nbs=p; | =|--Parallelism(Repartition Streams, PARTITION COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_ident]), WHERE:(PROBE([Bitmap1006])=3DTRUE)) = &=nbsp; | = |--Hash Match(Inner Join, HASH:([Dim_Vendor].[vendor_ident])=3D([fact_sales].[vendor_ident]), RESIDUAL:([fact_sales].[vendor_ident]=3D[Dim_Vendor].[vendor_ident]))= &nb=sp; | = |--Parallelism(Broadcast) &n=bsp; &nb=sp; | = | |--Nested Loops(Left Anti Semi Join, WHERE:([Dim_Vendor].[opco_vendor_num]=3D[Expr1005]))  =; = | = | |--Parallelism(Gather Streams) &=nbsp; | = | | |--Filter(WHERE:((((((((((((((like([Dim_Vendor].[opco_vendor_name], ='All Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%', =NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL))) &n=bsp; | = | | |--Clustered =Index Scan(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS [Dim_Vendor]), WHERE:([Dim_Vendor].[opco_vendor_num]173)) &=nbsp; &n=bsp; | = | |--Constant Scan  =; | = |--Clustered Index =Scan(OBJECT:([pfg_dm].[dbo].[fact_sales].[PK_fact_sales] AS [fact_sales]), =WHERE:([fact_sales].[calendar_date]>=3D'Nov 5 2002 12:00AM' AND [fact_sales].[calendar_date]<'Nov 19 2003 12:00AM'))  =; = |--Parallelism(Repartition Streams, PARTITION COLUMNS:([dim_inv_info_tbl].[opco_num], [dim_inv_info_tbl].[item_num])) &n=bsp; &nb=sp; |--Clustered Index Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_=01] AS [dim_inv_info_tbl]))

---

--=_NextPart_000_02C4_01C3B993.52F36600--|||1. I would update stats to make sure that's not an issue...
2. CXPacket indicates a waittime assoicated with parrallel query plans. Try
setting the MAXDOP to 1 as a test. The slow plan is probably parallel and
the fast plan may be serial.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"sfibich" <sfibich@.pfgc.com> wrote in message
news:%23ISwrybuDHA.2180@.TK2MSFTNGP09.phx.gbl...
> Good Morning All,
> I have a query from a user that I have been asked to take a look at. I
> have narrowed down the issue to one of the parameters in the query.
> When the query covers 2002 thru 2003 one execution plan is issued when
> the query covers only a portion of 2003 a second execution plan is
> issued. The issue is that the 2002 thru 2003 executes fine, the query
> that executes a portion over a portion of 2003 data does not. If I
> check the process it has an item that has a wait type of CXPACKET and it
> just hangs never returning. I was wondering if anyone has seen this
> kind of behavior before. The two different plans where created by only
> changing the data range.
> An advise would help.
> Thanks
>
> Show Plan text (Non Working)
> |--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
> [Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
> [dim_inv_info_tbl].[case_qty_on_hand] ASC))
> |--Parallelism(Gather Streams)
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([fact_sales].[item_ident], [dim_inv_info_tbl].[opco_num]))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([fact_sales].[vendor_ident]))
> | |--Nested Loops(Inner Join, OUTER
> REFERENCES:([fact_sales].[opco_ident]))
> | |
> |--Filter(WHERE:([fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
> | | | |--Bookmark
> Lookup(BOOKMARK:([Bmk1001]), OBJECT:([pfg_dm].[dbo].[fact_sales] AS
> [fact_sales]))
> | | | |--Nested Loops(Inner Join, OUTER
> REFERENCES:([dim_inv_info_tbl].[opco_num]) WITH PREFETCH)
> | | | |--Clustered Index
> Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
> AS [dim_inv_info_tbl]), ORDERED FORWARD)
> | | | |--Index
> Seek(OBJECT:([pfg_dm].[dbo].[fact_sales].[idx_fact_sales_06] AS
> [fact_sales]),
> SEEK:([fact_sales].[opco_num]=[dim_inv_info_tbl].[opco_num] AND
> [fact_sales].[calendar_date] >= 'Nov 5 2003 12:00AM' AND
> [fact_sales].[calendar_date] < 'Nov 19 2003 12:00AM') ORDERED FORWARD)
> | | |--Clustered Index
> Seek(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[PK_Dim_OPCO] AS [Dim_OPCO]),
> SEEK:([Dim_OPCO].[opco_ident]=[fact_sales].[opco_ident]) ORDERED FORWARD)
> |
>
|--Filter(WHERE:((((((((((((((((((((((((((((((((((((((((((((((((((((((((like
([Dim_Vendor].[opco_vendor_name],
> 'All Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%',
> NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)) AND
> [Dim_Vendor].[opco_vendor_num]<>3136) AND
> [Dim_Vendor].[opco_vendor_num]<>3571) AND
> [Dim_Vendor].[opco_vendor_num]<>3572) AND
> [Dim_Vendor].[opco_vendor_num]<>3573) AND
> [Dim_Vendor].[opco_vendor_num]<>3673) AND
> [Dim_Vendor].[opco_vendor_num]<>3860) AND
> [Dim_Vendor].[opco_vendor_num]<>3861) AND
> [Dim_Vendor].[opco_vendor_num]<>4290) AND
> [Dim_Vendor].[opco_vendor_num]<>4530) AND
> [Dim_Vendor].[opco_vendor_num]<>5373) AND
> [Dim_Vendor].[opco_vendor_num]<>5760) AND
> [Dim_Vendor].[opco_vendor_num]<>5797) AND
> [Dim_Vendor].[opco_vendor_num]<>6064) AND
> [Dim_Vendor].[opco_vendor_num]<>6065) AND
> [Dim_Vendor].[opco_vendor_num]<>6066) AND
> [Dim_Vendor].[opco_vendor_num]<>6073) AND
> [Dim_Vendor].[opco_vendor_num]<>6329) AND
> [Dim_Vendor].[opco_vendor_num]<>6525) AND
> [Dim_Vendor].[opco_vendor_num]<>6607) AND
> [Dim_Vendor].[opco_vendor_num]<>6684) AND
> [Dim_Vendor].[opco_vendor_num]<>7240) AND
> [Dim_Vendor].[opco_vendor_num]<>7241) AND
> [Dim_Vendor].[opco_vendor_num]<>8325) AND
> [Dim_Vendor].[opco_vendor_num]<>8326) AND
> [Dim_Vendor].[opco_vendor_num]<>16229) AND
> [Dim_Vendor].[opco_vendor_num]<>16230) AND
> [Dim_Vendor].[opco_vendor_num]<>17756) AND
> [Dim_Vendor].[opco_vendor_num]<>18121) AND
> [Dim_Vendor].[opco_vendor_num]<>19552) AND
> [Dim_Vendor].[opco_vendor_num]<>23852) AND
> [Dim_Vendor].[opco_vendor_num]<>23853) AND
> [Dim_Vendor].[opco_vendor_num]<>25307) AND
> [Dim_Vendor].[opco_vendor_num]<>25558) AND
> [Dim_Vendor].[opco_vendor_num]<>30003) AND
> [Dim_Vendor].[opco_vendor_num]<>30004) AND
> [Dim_Vendor].[opco_vendor_num]<>33068) AND
> [Dim_Vendor].[opco_vendor_num]<>36230) AND
> [Dim_Vendor].[opco_vendor_num]<>36236) AND
> [Dim_Vendor].[opco_vendor_num]<>37756) AND
> [Dim_Vendor].[opco_vendor_num]<>49365) AND
> [Dim_Vendor].[opco_vendor_num]<>62380) AND
> [Dim_Vendor].[opco_vendor_num]<>69485))
> | |--Clustered Index
> Seek(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
> [Dim_Vendor]),
> SEEK:([Dim_Vendor].[vendor_ident]=[fact_sales].[vendor_ident]),
> WHERE:((((((((((((((([Dim_Vendor].[opco_vendor_num]<>173 AND
> [Dim_Vendor].[opco_vendor_num]<>372) AND
> [Dim_Vendor].[opco_vendor_num]<>429) AND
> [Dim_Vendor].[opco_vendor_num]<>448) AND
> [Dim_Vendor].[opco_vendor_num]<>600) AND
> [Dim_Vendor].[opco_vendor_num]<>617) AND
> [Dim_Vendor].[opco_vendor_num]<>641) AND
> [Dim_Vendor].[opco_vendor_num]<>713) AND
> [Dim_Vendor].[opco_vendor_num]<>1325) AND
> [Dim_Vendor].[opco_vendor_num]<>1672) AND
> [Dim_Vendor].[opco_vendor_num]<>1850) AND
> [Dim_Vendor].[opco_vendor_num]<>1940) AND
> [Dim_Vendor].[opco_vendor_num]<>2210) AND
> [Dim_Vendor].[opco_vendor_num]<>2215) AND
> [Dim_Vendor].[opco_vendor_num]<>3130) AND
> [Dim_Vendor].[opco_vendor_num]<>3131) ORDERED FORWARD)
> |--Clustered Index
> Seek(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]),
> SEEK:([Dim_item].[item_ident]=[fact_sales].[item_ident]),
> WHERE:([Dim_item].[opco_num]=[dim_inv_info_tbl].[opco_num]) ORDERED
FORWARD)
>
> Show plan text (working)
> |--Parallelism(Gather Streams, ORDER BY:([Dim_OPCO].[opco_num] ASC,
> [Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
> [dim_inv_info_tbl].[case_qty_on_hand] ASC))
> |--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
> [Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
> [dim_inv_info_tbl].[case_qty_on_hand] ASC))
> |--Hash Match(Inner Join,
> HASH:([Dim_OPCO].[opco_ident])=([fact_sales].[opco_ident]),
> RESIDUAL:([fact_sales].[opco_ident]=[Dim_OPCO].[opco_ident]))
> |--Parallelism(Broadcast)
> | |--Index
> Scan(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[idx_dim_opco_01] AS [Dim_OPCO]))
> |--Hash Match(Inner Join,
> HASH:([fact_sales].[opco_num],
> [fact_sales].[item_num])=([dim_inv_info_tbl].[opco_num],
> [dim_inv_info_tbl].[item_num]),
> RESIDUAL:([dim_inv_info_tbl].[opco_num]=[fact_sales].[opco_num] AND
> [fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
> |--Parallelism(Repartition Streams, PARTITION
> COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_num]))
> | |--Hash Match(Inner Join,
> HASH:([Dim_item].[opco_num],
> [Dim_item].[item_ident])=([fact_sales].[opco_num],
> [fact_sales].[item_ident]),
> RESIDUAL:([Dim_item].[opco_num]=[fact_sales].[opco_num] AND
> [Dim_item].[item_ident]=[fact_sales].[item_ident]))
> | |--Bitmap(HASH:([Dim_item].[opco_num],
> [Dim_item].[item_ident]), DEFINE:([Bitmap1006]))
> | | |--Parallelism(Repartition
> Streams, PARTITION COLUMNS:([Dim_item].[opco_num],
[Dim_item].[item_ident]))
> | | |--Clustered Index
> Scan(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]))
> | |--Parallelism(Repartition Streams,
> PARTITION COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_ident]),
> WHERE:(PROBE([Bitmap1006])=TRUE))
> | |--Hash Match(Inner Join,
> HASH:([Dim_Vendor].[vendor_ident])=([fact_sales].[vendor_ident]),
> RESIDUAL:([fact_sales].[vendor_ident]=[Dim_Vendor].[vendor_ident]))
> | |--Parallelism(Broadcast)
> | | |--Nested Loops(Left
> Anti Semi Join, WHERE:([Dim_Vendor].[opco_vendor_num]=[Expr1005]))
> | |
> |--Parallelism(Gather Streams)
> | | |
> |--Filter(WHERE:((((((((((((((like([Dim_Vendor].[opco_vendor_name], 'All
> Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%', NULL))
> OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)))
> | | |
> |--Clustered Index
> Scan(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
> [Dim_Vendor]), WHERE:([Dim_Vendor].[opco_vendor_num]<>173))
> | | |--Constant Scan
> | |--Clustered Index
> Scan(OBJECT:([pfg_dm].[dbo].[fact_sales].[PK_fact_sales] AS
> [fact_sales]), WHERE:([fact_sales].[calendar_date]>='Nov 5 2002
> 12:00AM' AND [fact_sales].[calendar_date]<'Nov 19 2003 12:00AM'))
> |--Parallelism(Repartition Streams, PARTITION
> COLUMNS:([dim_inv_info_tbl].[opco_num], [dim_inv_info_tbl].[item_num]))
> |--Clustered Index
> Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
> AS [dim_inv_info_tbl]))
>|||Brian Moran wrote:
> 1. I would update stats to make sure that's not an issue...
> 2. CXPacket indicates a waittime assoicated with parrallel query plans. Try
> setting the MAXDOP to 1 as a test. The slow plan is probably parallel and
> the fast plan may be serial.
>
Thanks,
I guess I should have specified that MAXDOP is not an option, this
query is being run through a tool that does not allow direct
modification of the query. I will check the stats, thanks