Please help! Suspended/Stalled Query

Hi,

I am having a problem with one package that attempts to do a join on two large tables. Within the data flow task control is the ole db source control which has the initial query that joins and filters the tables.

The total number of rows that should be returned are about 80-90million. Running this in debug mode shows the number of rows read/inserted into the ole db destination and it always hits the about the same number of records where it just stops/stalls/suspends around 75 million. I have read some possible solutions such as changing the max degrees of parrallelism but that has not worked.

I am currently using SQL Server 2005 64bit SP2 on a 64 bit platform. This is the only package that does anything like this and I am at my wit's end with this problem.

Please help!

Thanks,
Cheston

Can you share some more information about what's in your data flow? Sorts, aggregates, OLE DB Commands, etc...

Also, what happens when you run this without debugging?

|||

SELECT FCT_POST_MTHLY_FINANCE_REVENUE.POST_MTHLY_FINANCE_REVENUE_KEY, FCT_POST_MTHLY_FINANCE_REVENUE.SUBSCRIBER_KEY,
FCT_POST_MTHLY_FINANCE_REVENUE.MSF_REVENUE, FCT_POST_MTHLY_FINANCE_REVENUE.SAF_REVENUE,
FCT_POST_MTHLY_FINANCE_REVENUE.AIRTIME_REVENUE, FCT_POST_MTHLY_FINANCE_REVENUE.LD_INTL_REVENUE,
FCT_POST_MTHLY_FINANCE_REVENUE.LOAD_TS, FCT_POST_MTHLY_FINANCE_REVENUE.ROAMING_REVENUE,
FCT_POST_MTHLY_FINANCE_REVENUE.LD_CAN_REVENUE, FCT_POST_MTHLY_FINANCE_REVENUE.BILL_MONTH_KEY,
FCT_POST_MTHLY_FINANCE_REVENUE.DATA_ON_VOICE_REVENUE, FCT_POST_MTHLY_FINANCE_REVENUE.UPDATE_TS,
FCT_POST_MTHLY_FINANCE_REVENUE.OPTIONAL_REVENUE, FCT_POST_MTHLY_FINANCE_REVENUE.MISCELLANEOUS_REVENUE,
FCT_POST_MTHLY_FINANCE_REVENUE.SIF_REVENUE, FCT_POST_MTHLY_FINANCE_REVENUE.LD_US_REVENUE,
FCT_POST_MTHLY_FINANCE_REVENUE.CONSOLIDATED_REVENUE, FCT_POST_MTHLY_FINANCE_REVENUE.ARPU_TOTAL_REVENUE,
FCT_POST_MTHLY_FINANCE_REVENUE.UNCLASSIFIED_REVENUE
FROM FCT_POST_MTHLY_REVENUE_NEW RIGHT OUTER JOIN
FCT_POST_MTHLY_FINANCE_REVENUE ON
FCT_POST_MTHLY_REVENUE_NEW.BILL_MONTH_KEY = FCT_POST_MTHLY_FINANCE_REVENUE.BILL_MONTH_KEY AND
FCT_POST_MTHLY_REVENUE_NEW.SUBSCRIBER_KEY = FCT_POST_MTHLY_FINANCE_REVENUE.SUBSCRIBER_KEY
WHERE (FCT_POST_MTHLY_REVENUE_NEW.SUBSCRIBER_KEY IS NULL)

This is my query that I am trying to run. Actually all I am currently trying to do is a row count. It gets suspended there as well at the same number as when I was trying to direct the data into a table.

BTW, it stops exactly at 75, 627, 900

|||

So basically you have the OLE DB source and simply a row count transformation and it hangs? (Just the two components on the data flow?)

Have you made sure that your query isn't timing out, or network related? SQL Profiler can help here.

Also, I'm still curious to know what happens when you run this without debugging.

|||

Yes, there are only 2 components to the data flow.

I do not believe it the query timing out or network related but I will investigate into that. Also will try running it without debugging and seeing if it works.

|||

Audit Logout Microsoft SQL Server Management Studio 0 0 0 340000 3644 53 2007-03-10 17:55:21.153 2007-03-10 18:01:01.153
Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Microsoft SQL Server Management Studio Administrator 3644 53 2007-03-10 18:01:01.153

Hey Phil,

I did the trace on the package with just the row count. The above is what keeps repeating once I hit the 75, 627, 900 records. I am unfamiliar with what this is, can you explain?

|||quick bump|||From what you describe it could be a deadlock situation.

Look at the Activity Monitor and see WHY it is stalled.