Query question

Hi,
I am logging process data every 5 min, every record has a datetime field in
addition to the process data one of the requierements of the application is
to avg the data every 3 hours. is there any simple way to accomplish this
with a query or will it be better create a job that runs every 3 hrs and
average the last 3 hrs and insert the results into a new table.
ThanksJulio
If you need to agregate the data every three hours , so yes , create a job
"Julio Delgado" <jdelgado89@.hotmail.com> wrote in message
news:OWLI7ip4GHA.3400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am logging process data every 5 min, every record has a datetime field
> in addition to the process data one of the requierements of the
> application is to avg the data every 3 hours. is there any simple way to
> accomplish this with a query or will it be better create a job that runs
> every 3 hrs and average the last 3 hrs and insert the results into a new
> table.
> Thanks
>

Query question

Hi,
I am logging process data every 5 min, every record has a datetime field in
addition to the process data one of the requierements of the application is
to avg the data every 3 hours. is there any simple way to accomplish this
with a query or will it be better create a job that runs every 3 hrs and
average the last 3 hrs and insert the results into a new table.
ThanksJulio
If you need to agregate the data every three hours , so yes , create a job
"Julio Delgado" <jdelgado89@.hotmail.com> wrote in message
news:OWLI7ip4GHA.3400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am logging process data every 5 min, every record has a datetime field
> in addition to the process data one of the requierements of the
> application is to avg the data every 3 hours. is there any simple way to
> accomplish this with a query or will it be better create a job that runs
> every 3 hrs and average the last 3 hrs and insert the results into a new
> table.
> Thanks
>

Query process time

I have a weird issue that I just can't figure out. I have a query that runs on tables that are all located in db x. If I run the query under a different db (like use database y) then it runs in like 11 seconds. If I run the query under the db that all of the tables reside under (use database x) then it takes almost 4 minutes.

Why would it take so much longer to run under the db where all of the data/tables exists? Both dbs are on the same SQL 2005 server.

I just don't get it?

Any ideas?

S

We can't possibly give you a meaningful response without having more information. What is the query, what is the nature of the data, what is different between the two databases, etc.

Perhaps, using your description about, database X has data in it, and database Y does not?

|||If you think that execution plan will be same then why SQl statement take different time on different server. But have you checked the CPU Cost and I/O cost.

Query problem in Microsoft_time_series

hello,all:

In the process of one of my data mining projects using Microsoft SQL Server2005,i met a problem. I have created a mining model with microsoft_time_series algorithm and have processed it successfully.when i predict the pridictabe column [traff] with the following DMX:

select predict([traff],-50,0) from my_model_name

and:

select predict([traff],40) from my_model_name

(in this mining model,the values of microsoft_time_series algorithm parameters are : historic_model_gap=10,historic_model_count=10,and the other parameters i use the default values)

The problem is:the prediction results will contain some null values most of the time.And when i changed one of or some of the 9 algorithm parameters(minimum_supportcomplexity_penalty/historic_model_gap/ historic_model_count/......and so on),the position of null values will be different from before,even sometimes the prediction results will contain no null values. but i can not find any rules or ralations between the null values' existence and the 9 algorithm parameters.

This problem upsets me completely.Could somebody help me?

Time Series algorithm returns null when predictions become unstable. Where these nulls start depends not only on algorithm parameters, but also on input data the model was trained with. To understand how values of different algorithm parameters affect the model created (and predictions the model returns) you can look at the documentation. To understand these dependancies it is also helpful to understand how the algorithm works. Good starting point is following MSDN article:http://msdn2.microsoft.com/en-us/library/ms174923.aspx

Predictions usually are more stable when you increase values of COMPLEXITY_PENALTY and MINIMUM_SUPPORT parameters. These parameters inhibit tree growth which, provides more stable predictions.

Let us know if you have specific questions and we will try help you.

Tatyana

|||

thank you very much for Tatyana's kind help.i will try again and check the result.

sql

Query performance

Hello,
I wrote the query below, but I was wondering whether there was a better way
of doing so. I am basically trying to build a recursive process.
Any suggestion would be greatly appreciated.
Thanks.
Mike
****************************************
************************************
*****************
****************************************
************************************
*****************
CREATE PROCEDURE dbo.CustomersFiltered
(
@.filter varchar(50)
)
AS
create table #t
(
dummyId bigint IDENTITY(1,1) PRIMARY KEY,
customerId bigint,
parentId bigint,
levelId int
)
INSERT #t (customerId, parentId, levelId)
SELECT customerAutoId, parentCustomerAutoId, 0
FROM customers
WHERE [name] like @.filter
declare @.dummyId bigint
select @.dummyId = 1
declare @.customerId bigint
declare @.parentId bigint
declare @.levelId bigint
WHILE (SELECT COUNT(*) FROM #t WHERE dummyId = @.dummyId) > 0
BEGIN
select @.customerId = customerId,
@.parentId = parentId,
@.levelId = levelId
FROM #t
WHERE dummyId = @.dummyId
insert into #t (customerId, parentId, levelId)
select CustomerAutoId,
parentCustomerAutoId,
@.levelId - 1
from customers
where parentCustomerAutoId = @.customerId
group by CustomerAutoId,
parentCustomerAutoId
if (@.parentId is not null)
insert into #t (customerId, parentId, levelId)
select c.CustomerAutoId,
c.parentCustomerAutoId,
@.levelId + 1
from customers c
where c.customerAutoId = @.parentId
and not exists (select * from #t where c.customerAutoId = customerId)
group by CustomerAutoId,
parentCustomerAutoId
select @.dummyId = @.dummyId + 1
END
select t.levelId as LevelId,
t.customerId as CustomerAutoId,
c.[name] as [Name],
c.sic as SIC,
c.ParentCustomerAutoId as ParentCustomerAutoId
from #t t
inner join customers c on c.customerAutoId = t.customerId
group by t.levelId,
t.customerId,
c.[name],
c.sic,
c.ParentCustomerAutoId
order by levelId desc,
c.ParentCustomerAutoId,
c.[name]
drop table #t
GOIf you're using SQL Server 2005, you should consider using Common Table
Expressions (CTE) instead. Temporary tables suffer from writing data to
the tempdb database, whereas CTEs are in memory only.
A good walktrough is available at the MSDN site:
http://msdn2.microsoft.com/en-us/library/ms186243.aspx
Hope this helps
"Mike" <none@.none.com> wrote in
news:uMSJXrFrGHA.4112@.TK2MSFTNGP02.phx.gbl:

> I wrote the query below, but I was wondering whether there was a
> better way of doing so. I am basically trying to build a recursive
> process.
> Any suggestion would be greatly appreciated.
> Thanks.
> Mike
>
> ****************************************
******************************
*
> **********************
> ****************************************
******************************
*
> **********************
> CREATE PROCEDURE dbo.CustomersFiltered
> (
> @.filter varchar(50)
> )
> AS
>
> create table #t
> (
> dummyId bigint IDENTITY(1,1) PRIMARY KEY,
> customerId bigint,
> parentId bigint,
> levelId int
> )
>
> INSERT #t (customerId, parentId, levelId)
> SELECT customerAutoId, parentCustomerAutoId, 0
> FROM customers
> WHERE [name] like @.filter
>
> declare @.dummyId bigint
> select @.dummyId = 1
>
> declare @.customerId bigint
> declare @.parentId bigint
> declare @.levelId bigint
> WHILE (SELECT COUNT(*) FROM #t WHERE dummyId = @.dummyId) > 0
> BEGIN
> select @.customerId = customerId,
> @.parentId = parentId,
> @.levelId = levelId
> FROM #t
> WHERE dummyId = @.dummyId
> insert into #t (customerId, parentId, levelId)
> select CustomerAutoId,
> parentCustomerAutoId,
> @.levelId - 1
> from customers
> where parentCustomerAutoId = @.customerId
> group by CustomerAutoId,
> parentCustomerAutoId
> if (@.parentId is not null)
> insert into #t (customerId, parentId, levelId)
> select c.CustomerAutoId,
> c.parentCustomerAutoId,
> @.levelId + 1
> from customers c
> where c.customerAutoId = @.parentId
> and not exists (select * from #t where c.customerAutoId =
> customerId)
> group by CustomerAutoId,
> parentCustomerAutoId
>
> select @.dummyId = @.dummyId + 1
> END
>
> select t.levelId as LevelId,
> t.customerId as CustomerAutoId,
> c.[name] as [Name],
> c.sic as SIC,
> c.ParentCustomerAutoId as ParentCustomerAutoId
> from #t t
> inner join customers c on c.customerAutoId = t.customerId
> group by t.levelId,
> t.customerId,
> c.[name],
> c.sic,
> c.ParentCustomerAutoId
> order by levelId desc,
> c.ParentCustomerAutoId,
> c.[name]
>
> drop table #t
> GO
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||I am using SQL Server 2000 SP4.
Is there anything that I could improve in my query? I checked all indexes,
even in the temp table, but I ran out of ideas.
Thanks.
Mike
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns98075D16535A0olekristianbangaas@.
207.46.248.16...
> If you're using SQL Server 2005, you should consider using Common Table
> Expressions (CTE) instead. Temporary tables suffer from writing data to
> the tempdb database, whereas CTEs are in memory only.
> A good walktrough is available at the MSDN site:
> http://msdn2.microsoft.com/en-us/library/ms186243.aspx
> Hope this helps
>
> "Mike" <none@.none.com> wrote in
> news:uMSJXrFrGHA.4112@.TK2MSFTNGP02.phx.gbl:
>
> *
> *
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP

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

Query never ends execution

Hello

I have a .Net application that calls an stored procedure. When it does, the execution goes and never ends (I have to kill the windows process). When I call the sp from within the Management Studio, it also never ends executing and I have to cancel the query. But, when I call it immediately after, it takes 45 seconds to complete.

Now, the sp has several parts and I have made that it prints a message at the end of each part so that I can read where it stops. Strange enough, it completes all parts except the last one, which has the form INSERT INTO myLocalTable SELECT * FROM MyRemoteTable. But if I execute the Select independetly, I discover that it brings no rows! Now, many of the @.@.rowcount printed after the execution of the other parts shows zero rows involved or just a few. I am not using cursors, each part is an UPDATE statement or an INSERT.

TestMachine1 runs SQL2005 SP2 and has as linked server myRemoteServer (SQL2000) server. The stored procedure in TestMachine1 inserts rows to a table in myRemoteServer and brings back some rows.

What could be wrong?What could be wrong?
I'll guess it is one of these issues:
A) Gerbils nibbling on your network cable.
B) Global warming affecting your server environment.
C) Bears. Big nasty ones.
D) Some problem with the code you did not bother posting.|||I vote for bears. they are always on the threatdown causing trouble.|||Here is the general structure of the problematic stored procedure (Consider that there are 2 remote tables and 2 local tables instead of just one, and that the operations are made for both of them in a similar fashion):

declare @.LastUpdate datetime
declare @.Workstation varchar(250)

set @.Workstation=host_name()

SET NOCOUNT ON

execute spGetLastUpdate @.LastUpdate output

insert into Synonym_MyRemoteTable1Temp
select
@.Workstation,
ID,
Value1,
Value2,
Value3
from myLocalTable1
where UpdateTimeStamp>@.LastUpdate

execute Synonym_spMyRemoteProcedure @.Workstation -- Explained below
/*
This remote procedure makes an update and an insert as follows:

update MyRemoteTable1
set
Value1=MyRemoteTable1Temp.Value1,
Value2=MyRemoteTable1Temp.Value2,
Value3=MyRemoteTable1Temp.Value3
from MyRemoteTable1
inner join MyRemoteTable1Temp on MyRemoteTable1Temp.ID=MyRemoteTable1.ID and WorkStation=@.WorkStation

insert into MyRemoteTable1
(
ID,
Value1,
Value2,
Value3,
UpdateTimeStamp
)
select
ID,
Value1,
Value2,
Value3,
GetDate()
from MyRemoteTable1Temp
where
WorkStation=@.WorkStation and ID not in (select ID from MyRemoteTable1)
*/

update MyLocalTable1
set
Value1=T.Value1,
Value2=T.Value2,
Value3=T.Value3,
UpdateTimeStamp=GetDate()
from MyLocalTable1
inner join
(
select
ID,
Value1,
Value2,
Value3
from Synonym_MyRemoteTable1
where UpdateTimeStamp>@.LastUpdate and
WorkStation<>@.Workstation

) as T on T.ID=MyLocalTable1.ID

insert into MyLocalTable1
(
ID,
Value1,
Value2,
Value3,
UpdateTimeStamp
)
select
ID,
Value1,
Value2,
Value3,
GetDate()
from Synonym_MyRemoteTable1
where
UpdateTimeStamp>@.LastUpdate and
Workstation<>@.WorkStation and
ID not in (select ID from MyLocalTable1)