September 7, 2012 at 4:29 am
Here's an odd one. It just started showing up two months ago. We have a daily-running package that loads an ODS (operational data store database). In one specific data flow task, we've had a problem pop up. It's an OLE DB Source to OLE DB Destination (no transformations between) where the source is a stored procedure and fast loads a table on the same server, same database.
This package is several years old. The daily run works fine, no issues. But the past two month ends, when the row counts spike, it's been freezing on this particular transformation. Freezing as in the Source is blocking the Destination (sp_who2 shows both sets of threads suspended and the Destination having a BlkBy SPID that equals the Source).
According to my research, SQL is treating the OLE DB Destination as a BULK INSERT. That makes sense to me. But what confuses me is why (or how) the Source is blocking its own Destination.
The stored procedure does a left outer join to the destination table to see what records currently exist so it only loads the non-existent records.
SELECT s.I2 AS SCEID, dci.CIKey, dt2.TimeKey AS EDate,
dt1.TimeKey AS CycleMonth,
ISNULL(CONVERT(MONEY,s.C1),0) AS AE1,
ISNULL(CONVERT(MONEY,s.C3),0) AS AE2,
ISNULL(CONVERT(MONEY,s.C4),0) AS AE3,
ISNULL(CONVERT(MONEY,s.C5),0) AS AE4,
ISNULL(CONVERT(MONEY,s.C6),0) AS AE5,
ISNULL(CONVERT(MONEY,s.C7),0) AS AE6,
ISNULL(CONVERT(MONEY,s.C8),0) AS AE7,
ISNULL(CONVERT(MONEY,s.C9),0) AS AE8,
ISNULL(CONVERT(MONEY,s.C10),0) AS AE9,
ISNULL(CONVERT(MONEY,s.C2),0) AS AE10,
ISNULL(CONVERT(MONEY,s.C11),0) AS PE1,
ISNULL(CONVERT(MONEY,s.C12),0) AS PE2,
ISNULL(CONVERT(MONEY,s.C13),0) AS PE3,
ISNULL(CONVERT(MONEY,s.C14),0) AS PE4,
ISNULL(CONVERT(MONEY,s.C15),0) AS TotalAE,
ISNULL(CONVERT(MONEY,s.C16),0) AS TotalPE,
dt.TimeKey AS ModifiedOn, @TimeKey AS TimeKey
FROM dbo.Staging_SC s
INNER JOIN dbo.DimCI dci
ON s.I1 = dci.SCConID
LEFT OUTER JOIN dbo.DimDestinationTable dceh /*This is what is getting blocked */
ON s.I2 = dceh.SCEID
LEFT OUTER JOIN dbo.DimTime dt
ON CONVERT(SMALLDATETIME,s.LastUpdate) = dt.FullDate
LEFT OUTER JOIN dbo.DimTime dt1
ON CONVERT(VARCHAR,CONVERT(SMALLDATETIME,s.D1),101) = dt1.FullDate --CycleMonth
LEFT OUTER JOIN dbo.DimTime dt2
ON CONVERT(SMALLDATETIME,s.D2) = dt2.FullDate --EDate
WHERE s.EntityType = 'CEHist' and s.I1 IS NOT NULL and s.I2 IS NOT NULL
and dceh.DestinationKey IS NULL;
This is not something that has happened on the daily feed and it used to work fine on month end until the last two month ends. We're trying to figure out what changed. We're currently running Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2).
Trying to google the issue only gives me tutorials on how to create sources and destinations. Does anyone have any other thoughts?
September 10, 2012 at 8:24 am
I think I have found my issue. Details in this post: http://www.sqlservercentral.com/Forums/Topic1356750-391-1.aspx?Update=1
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply