Hello All,

I have run into a small issue with the Data Pump Task (or Data
transformation Task). The issue is the data pump appears to be creating
a deadlock.

I am running SQL Server 2000 developer edition Service pack 3a on a
dual 3.ghz 4 gig byte server. At the time of running this task there
was no other processes running on the development server.

The Deadlock occurs when the data pump task calls a stored procedure in
the ODS database that checks the destination fact table in the DSS
database, for records that have already been loaded. This is achieved
by using a left outer join on an Audit key I have created.

When I look at the processes running on the server I notice the ODS
Connection is still running the SELECT process while the DSS connection
tries to began the BULK INSERT. Both processes hang at this point.
With the DSS SPID showing that it is blocked by the ODS SPID

Both databases are on the same SQL Server. I am using the SQL Server
OLE DB provider for both SQL server connections.

The following sample code shows what the stored procedure is doing.

SELECT

ODS.Col0001
, ODS.Col0002
, ODS.Col0003
, ODS.Col0004
, ODS.DWAuditKey
, ODS.DWAUDITSource

FROM DWODS.dbo.SourceTable AS ODS

LEFT OUTER JOIN DWDSS.dbo.DSSFact01 AS DSS
ON ODS.DWAuditKey = DSS. DWAuditKey
AND ODS.DWAUDITSource= DSS.DWAUDITSource
AND DSS.DWCurrentFlag ='Y'
WHERE
DSS. DWAuditKey IS NULL
AND DSS.DWAUDITSource IS NULL


The above select statement code does not have any of the dimension key
lookups from the DSS database. But I am using the same technique for
retrieving dimension keys. The store procedure is way more complex as
it works with 23 source tables to build this fact table.

The stored procedure runs successfully outside of DTS, and parses
correctly within the Data pump task itself.

I have been using DTS for a number of years building data warehouses
and data marts. I have not had this problem before. I have always
written my code to check if the record exists in the destination table,
to stop double loading and manage event failure.

I am not using DTS transactions although I have run this package with
this option set and still have this issue.

I am currently running profiler to see if I can find any more useful
information.

I am also considering moving to Store procedures for this task if I
cannot find a resolution.

Has anyone come across this before?


Thanks

Myles

On which connection is the deadlock occuring?

Is the proc the source statement for the datapump i.e. it is not in a
lookup?

If it is then have you tried to specify NOLOCK on the source select
statement.

Also have a look in profiler or sp_lock at what is trying to happen.

I would expect to see your locking requests conflicting.

Have a look here

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acdata.chm::/ac_8_con_7a_8um1.htm


What about the ExecuteSQL task?


Thanks

Allan





"Myles" <Myles.Matheson (AT) gmail (DOT) com> wrote

Hello Alan,

I am really glad that you have responded. Yes the Stored proc is in
the SQL Statement object in the Data pump.

The blocking is occurring on the bulk Insert. This would be when the
SELECT is still running.

I would think that this is normal SQL server behaviour except for the
SELECT is not completing.

The lock is on the destination table. I will try the NO LOCK hint on
the destination table in the SP.

Is there a chance of dirty reads?

Have you seen this before?


Myles

Yes I have seen Deadlocks before. What BULK INSERT? With NOLOCK yes
you have the chance of dirty reads

Allan

"Myles" <Myles.Matheson (AT) gmail (DOT) com> wrote

Hello Allan,

Sorry the process shown from sp_who2 is Bulk Insert. When solving this
issue did you use the NO LOCK or did you create a sp to do the insert?

Also is this a bug or just a problem with the SELECT taking a long time
to run?

I am going to try the data pump with NO LOCK. I am away until Wednesday
so I will let you know how I got on then.

Any techniques for minimizing the dirty reads? I am thinking of using a
row count from the ODS Source tables DTS package log to check the same
number of records where inserted into the Fact table.

Myles

OK So you are using a DataPump task with the use fast load option
checked. Is the Lock table option also checked?

Sp_who2 will not tell you about locks.

Allan

"Myles" <Myles.Matheson (AT) gmail (DOT) com> wrote

Hello Allan,

Yes SP_WHO2 will not tell you what resources are being block, but it
does tell the processes. The lock appears on the destination table from
SP_LOCK.

Yes I am using the Fast load Option with LOCK TABLE.

Myles

Can you remove those options and tell me what you get?

Also the DataPump is the only thing accessing that table t that time right?
No lookups etc?

Allan

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Myles" <Myles.Matheson (AT) gmail (DOT) com> wrote

Hello Alan,

Unchecking the fast load option appears to be working. I am going to
try the following:

1. Try Fast load option without Table Lock
2. Try fast load option with table lock and NO LOCK for the
destination table.

Any thoughts on why this occurs?

Myles

You should be able to see the statements that occur and if the table lock
"locks the table" you are likely to get blocking.

Is there only one thing happening to that table (no lookups etc)
Is your source a simple query?

Allan

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Myles" <Myles.Matheson (AT) gmail (DOT) com> wrote