October 15, 2003 at 3:26 am
Hi,
Recently a new development team is busy with a new application and with that a new Db on my SQL*Server. From the moment that this application was on production I encountered a lot of more blockings on my server than before that. Investigating this blocking showed me that it was always in the tempdb on the object sysindexes.
My system runs every hour a check of all Database space to prevent the running out of space on a particular DB and it is almost every time when this check runs that blocking occurs.
This is my trace info about the blocking. It gives the blocked and blocking process + it provide me all locking info about those processes.
On the bottom you can also find the code of the involved stored procedure (it's not always the same one).
When looking at this code you can see that they are using a lot of mry table objects and sometimes also temporary tables (not in this one).
Can someone help me to find out the real cause of this problem? I think it has something to do with the Mry and/or temporary tables, but I don't know why.
I think there must be something with there way of programming but I need to be sure before I can go to them to complain about their programming technique.
spid hostname blk dbname cmd waittype
------ ------------ ---- ---------- ---------------- --------
186 BGC-IHZHSKIX 0 DbCbuGbs INSERT 0x0000
Blocking process 186
===========================
EventType Parameters EventInfo
--------- ---------- ------------------------------------
RPC Event 0 dbo.UspGetListAllOffresComparaison;1
spid db Obj IndId Type Resource Mode Status
------ ------------------------------ ------------------------------ ------ ---- ---------------- -------- ------
186 DbCbuGbs NULL 0 DB S GRANT
186 tempdb sysindexes 0 TAB IX GRANT
186 tempdb NULL 0 PAG 1:507612 X GRANT
186 tempdb NULL 0 PAG 1:507611 X GRANT
186 tempdb NULL 0 PAG 1:507610 X GRANT
186 DbCbuGbs spt_fallback_dev 0 TAB IS GRANT
186 DbCbuGbs spt_fallback_dev 0 RID 3:1451:50 S GRANT
186 tempdb ALLOCATION 0 RID 1:507611:1 X GRANT
186 DbCbuGbs NULL 0 PAG 3:25144 IS GRANT
186 tempdb NULL 0 TAB Sch-M GRANT
186 DbCbuGbs NULL 0 PAG 3:822 IS GRANT
186 DbCbuGbs NULL 0 RID 3:822:8 S GRANT
186 DbCbuGbs NULL 0 PAG 3:899 IS GRANT
186 DbCbuGbs NULL 0 PAG 3:897 IS GRANT
186 DbCbuGbs NULL 0 PAG 3:905 IS GRANT
186 DbCbuGbs NULL 0 PAG 3:911 IS GRANT
186 tempdb NULL 0 TAB Sch-M GRANT
186 tempdb NULL 0 TAB Sch-M GRANT
186 DbCbuGbs spt_fallback_db 0 PAG 3:46548 IS GRANT
186 DbCbuGbs spt_fallback_dev 0 PAG 3:1451 IS GRANT
186 tempdb NULL 0 TAB Sch-M GRANT
186 tempdb NULL 0 IDX IDX: 2:181957567 X GRANT
186 tempdb sysindexes 1 KEY (7900dcdac16c) X GRANT
186 DbCbuGbs NULL 0 PAG 3:26872 IS GRANT
186 DbCbuGbs spt_fallback_db 0 TAB IS GRANT
186 tempdb NULL 0 TAB Sch-M GRANT
186 DbCbuGbs NULL 0 PAG 3:19824 IS GRANT
186 tempdb NULL 0 PAG 1:223281 X GRANT
186 DbCbuGbs NULL 0 TAB IS GRANT
186 tempdb NULL 0 TAB Sch-M GRANT
open_tran
------------------- ---------
Blocked process 191
===========================
EventType Parameters EventInfo
-------------- ---------- -----------------
Language Event 0 exec sp_spaceused
spid db Obj IndId Type Resource Mode Status
------ ------------------------------ ------------------------------ ------ ---- ---------------- -------- ------
191 tempdb sysindexes 1 KEY (7900dcdac16c) S WAIT
open_tran
------------------- ---------
# open transactions 1
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/****************************************************************************************
** UspGetListAllOffresComparaison
****************************************************************************************
** Author : Marc Pleijte
** Description : Get list of all offers comparison
** Date Creation : 25/09/2002
**
** Input parameter :
**
** Output Record set :
**
** Usage : Export to XLS file
**
*****************************************************************************************/
ALTER PROCEDURE dbo.UspGetListAllOffresComparaison (@Destination varchar(2000) = NULL
, @Operator varchar(2000) = NULL
, @Buyer varchar(1000) = NULL
, @AreaManager varchar(1000) = NULL)
AS
BEGIN
SET ANSI_DEFAULTS ON
SET NOCOUNT ON
DECLARE @tblAreaManagers TABLE (PkAMID int NOT NULL)
DECLARE @tblBuyers TABLE (PkBuyerID int NOT NULL)
DECLARE @tblOperators TABLE (PkOpID int NOT NULL)
DECLARE @tblDestinations TABLE (PkDestID int NOT NULL)
DECLARE @tblUsedOffers TABLE (PkOpID int NOT NULL
, PkDestID int NOT NULL
, PkOfferDate datetime NOT NULL
, ApplicationDate datetime NOT NULL
, EndOfApplicationDate datetime NOT NULL
, EntryDate datetime NOT NULL
, Price float NOT NULL
, PRIMARY KEY (PkOpID, PkDestID, PkOfferDate))
DECLARE @tblMaxOffers TABLE (PkOffOpID int NOT NULL
, PkOffDestID int NOT NULL
, MaxOfOfferDate datetime NOT NULL
, PRIMARY KEY(PkOffOpID, PkOffDestID))
DECLARE @tblMaxTestResults TABLE (PkTrDestId int NOT NULL
, PkTrOpId int NOT NULL
, MaxTrRequestDate datetime NOT NULL
, PRIMARY KEY(PkTrDestId,PkTrOpId))
DECLARE @tblUsedTestResults TABLE (PkTrDestId int NOT NULL
, PkTrOpId int NOT NULL
, PkTrRequestDate datetime NOT NULL
, TrAutomatic bit NULL
, TrScore smallint NULL
, TrDiorId int NULL
, TrGo bit NULL
, PRIMARY KEY(PkTrDestID, PkTrOpID, PkTrRequestDate))
DECLARE @Found int
DECLARE @Err int
IF LEN(@Buyer) > 0 SET @Buyer = @Buyer + ','
IF LEN(@AreaManager) > 0 SET @AreaManager = @AreaManager + ','
IF LEN(@Destination) > 0 SET @Destination = @Destination + ','
IF LEN(@Operator) > 0 SET @Operator = @Operator + ','
--Filter area managers
IF LEN(@AreaManager) > 0
BEGIN
SET @Found = CHARINDEX(',', @AreaManager)
WHILE @Found > 0
BEGIN
SET @Found = CHARINDEX(',', @AreaManager)
INSERT @tblAreaManagers
SELECT SUBSTRING(@AreaManager, 1, (@Found-1))
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
SET @AreaManager = ISNULL(SUBSTRING(@AreaManager, (@Found+1), LEN(@AreaManager)),0)
SET @Found = CHARINDEX(',', @AreaManager)
END
END
ELSE
BEGIN
--Select All Area managers
INSERT @tblAreaManagers(PkAmID)
SELECT PkAMID
FROM tblAreaManagers
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
END
--Filter Buyers
IF LEN(@Buyer) > 0
BEGIN
SET @Found = CHARINDEX(',', @Buyer)
WHILE @Found > 0
BEGIN
SET @Found = CHARINDEX(',', @Buyer)
INSERT @tblBuyers
SELECT SUBSTRING(@Buyer, 1, (@Found-1))
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
SET @Buyer = ISNULL(SUBSTRING(@Buyer, (@Found+1), LEN(@Buyer)),0)
SET @Found = CHARINDEX(',', @Buyer)
END
END
ELSE
BEGIN
--Select All Buyers
INSERT @tblBuyers (PkBuyerID)
SELECT PkBuyerID
FROM tblBuyers
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
END
--Filter Destinations
IF LEN(@Destination) > 0
BEGIN
SET @Found = CHARINDEX(',', @Destination)
WHILE @Found > 0
BEGIN
SET @Found = CHARINDEX(',', @Destination)
INSERT @tblDestinations
SELECT SUBSTRING(@Destination, 1, (@Found-1))
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
SET @Destination = ISNULL(SUBSTRING(@Destination, (@Found+1), LEN(@Destination)),0)
SET @Found = CHARINDEX(',', @Destination)
END
END
ELSE
BEGIN
--Select All Destinations
INSERT @tblDestinations(PkDestID)
SELECT PkDestID
FROM tblDestinations
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
END
--Filter Operators
IF LEN(@Operator) > 0
BEGIN
SET @Found = CHARINDEX(',', @Operator)
WHILE @Found > 0
BEGIN
SET @Found = CHARINDEX(',', @Operator)
INSERT @tblOperators
SELECT SUBSTRING(@Operator, 1, (@Found-1))
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
SET @Operator = ISNULL(SUBSTRING(@Operator, (@Found+1), LEN(@Operator)),0)
SET @Found = CHARINDEX(',', @Operator)
END
END
ELSE
BEGIN
--Select All Destinations
INSERT @tblOperators(PkOpID)
SELECT PkOpID
FROM tblOperators
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
END
--Select maximum offer dates
INSERT @tblMaxOffers (PkOffOpID
, PkOffDestID
, MaxOfOfferDate)
SELECT PkOffOpId
, PkOffDestId
, Max(PkOffOfferDate) AS MaxOfOffer_date
FROM tbloffers
GROUP BY PkOffOpId, PkOffDestId
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
--Insert used offers
INSERT @tblUsedOffers(PkOpID
, PkDestID
, PkOfferDate
, ApplicationDate
, EndOfApplicationDate
, EntryDate
, Price)
SELECT DISTINCT tbloffers.PkOffOpId AS carrier_nb
, tbloffers.PkOffDestId AS Destination_id
, tbloffers.PkOffOfferDate AS OfferDate
, tbloffers.OffApplicationDate AS Application_date
, tbloffers.OffEndofApplicationDate AS EndofApplication_Date
, tbloffers.OffEntryDate AS Entry_Date
, tbloffers.OffPrice AS Price
FROM tbloffers INNER JOIN @tblMaxOffers AS q_offers_last_date ON tbloffers.PkOffOfferDate = q_offers_last_date.MaxOfOfferDate AND
tbloffers.PkOffOpId = q_offers_last_date.PkOffOpId AND
tbloffers.PkOffDestId = q_offers_last_date.PkOffDestId
INNER JOIN tblOperators ON tblOperators.PkOpId = tbloffers.PkOffOpId
INNER JOIN @tblOperators AS Op ON tblOperators.PkOpID = Op.PkOpID
INNER JOIN @tblBuyers AS Buyer ON tblOperators.OpBuyerID = Buyer.PkBuyerID
INNER JOIN tblDestinations ON tblOffers.PkOffDestID = tblDestinations.PkDestId
INNER JOIN @tblDestinations AS Dest ON tblDestinations.PkDestID = Dest.PkDestID
INNER JOIN @tblAreaManagers AS AM ON tblDestinations.DestAMID = AM.PkAMID
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
--Select maximum test results
INSERT @tblMaxTestResults(PkTrDestID
, PkTrOpID
, MaxTrRequestDate)
SELECT PkTrDestId
, PkTrOpId
, Max(PkTrRequestDate) AS MaxTrRequestDate
FROM tblTestResults
GROUP BY tblTestResults.PkTrDestId, tblTestResults.PkTrOpId
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
--Insert used test results
INSERT @tblUsedTestResults(PkTrDestId
, PkTrOpId
, PkTrRequestDate
, TrAutomatic
, TrScore
, TrDiorId
, TrGo)
SELECT DISTINCT tblTestResults.PkTrDestId
, tblTestResults.PkTrOpId
, tblTestResults.PkTrRequestDate
, tblTestResults.TrAutomatic
, tblTestResults.TrScore
, tblTestResults.TrDiorId
, tblTestResults.[TrGo?]
FROM tblTestResults INNER JOIN @tblMaxTestResults AS MaxTest ON tblTestResults.PkTrDestId = MaxTest.PkTrDestId AND
tblTestResults.PkTrOpId = MaxTest.PkTrOpId AND
tblTestResults.PkTrRequestDate = MaxTest.MaxTrRequestDate
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
--Combine selections
SELECT tblDestinations.DestName as Destination
, tblOperators.OpLongOperatorName as [Operator Name]
, Offer.Price
, Offer.ApplicationDate as [Offer StartDate]
, TestResults.PkTrRequestDate AS [Lastest TestDate]
, Case ISNULL(Trautomatic,'')
when 1 then 'yes'
when '' then null
else 'no'
end AS Auto
, TestResults.TrScore as Score
, case ISNULL(TrGo,'')
when 1 then 'yes'
when '' then null
else 'no'
end AS [Go]
, TestResults.TrDiorId as DiorID
FROM @tblUsedOffers AS Offer INNER JOIN tblOperators ON Offer.PkOpID = tblOperators.PkOpID
INNER JOIN tblDestinations ON Offer.PkDestID = tblDestinations.PkDestId
LEFT JOIN @tblUsedTestResults AS TestResults ON TestResults.PkTrOpId = Offer.PkOpID AND
TestResults.PkTrDestId = Offer.PkDestID
ORDER BY tblDestinations.DestName, Offer.Price
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
COMMIT
RETURN @Err
ERROR:
IF @@TRANCOUNT > 0 ROLLBACK
RETURN @Err
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
October 16, 2003 at 2:05 am
Ludo, I haven't analysed your comnplete work, but three things came to my mind which may be helpful:
1) Split your Transactions into smaller chunks, so you can commit multiple parts. Once committed, locks for that part are released .
2) If your statistics-job is "less" important than production work, use "SET DEADLOCK_PRIORITY low" to make your job loose possible deadlock conflickts. Otherwise your production jobs may fail.
3) If 1) doesn't help, consider index-to-data locking (reading-proc 1 grabs index first, then data, writing-proc 2 writes data first and than wants index). Dropping an index can help avoid deadlocks
Kay
October 23, 2003 at 2:34 am
Thnx, I will try your suggestions, but keep in mind that this isn't a deadlock but a blocking situation.
A deadlock can take care of it self, a blocking can't.
October 24, 2003 at 4:38 am
Ludo,
Is this a problem? The insert is blocking the sp_space_used procedure. I suppose it is no problem if this procedure has to wait. The other way round, that could be irritating.
Only if the query starts to block other program queries there would also be a problem.
October 24, 2003 at 5:05 am
It's indeed no problem here, but I generated this as example. The real problem is that other applications using temp. Tables or Mry tables can be blocked too.
October 24, 2003 at 6:12 am
I suppose now I must analyze your code. 😉
The lock is on a key. I believe this would only be a problem for a procedure that wants to access the structure of this particular index. It would be better is you send a case with two program procedures.
But if you have such problems:
The only reason I see for a lock on a key in sysindexes are the primary keys in the table variables you declare. There are two problems with this theory.
In the first place, why is there only one key locked and not all?
In the second place, why is the lock not freed after the declare?
Maybe it would help to put the declarations in a separate transaction.
You could run profiler and see which statements put locks on sysindexes and when the locks are freed.
October 24, 2003 at 6:59 am
With this I send you the code of a stored procedure + lock info.
As you can see this procedure is started in two different processes and yet they interference with each other again in the tempdb.
And again the appl development people are using a lot of temp tables and mry tables.
CREATE PROCEDURE dbo.UspGetListAllBCRRoutes(@OriginID varchar(10)) AS
/****************************************************************************************
** UspGetListAllBCRRoutes
****************************************************************************************
** Author : Marc Pleijte
** Description : Get list of all new routes
** Date Creation : 04/11/2002
**
** Input parameter :
**
** Output Record set :
**
** Usage : Export to XLS file
**
*****************************************************************************************/
BEGIN
SET ANSI_DEFAULTS ON
SET NOCOUNT ON
DECLARE @Err int
DECLARE @tblRedCost TABLE ( RouteID int
, RedCost float)
DECLARE @tblTrafficVolumes TABLE( DestinationID int
, Origin int
, MaxOfTrafficDate datetime)
DECLARE @tblBCRMinOrder TABLE ( RouteID int
, MinOrder int)
DECLARE @tblBCRMaxStartDate TABLE ( DestinationID int
, Origin int
, MaxOfStartDate datetime)
DECLARE @tblBCRMaxDetail TABLE ( PkBCRID int
, BCROrigin int
, BCRDestId int
, BCRStartDate datetime
, BCRMinimumPrice float
, BCRRerouting int
, BCRCliGuarantee bit
, BCRZeroTolerance bit
, BCRRemarks varchar(510))
--Last date of traffic volumes
INSERT @tblTrafficVolumes
SELECT PkTrvolDestId AS DestinationId
, PkTrvolOrigin AS Origin
, Max(PkTrvolDate) AS MaxOfTrafficDate
FROM tblTrafficVolumes
WHERE PkTrvolOrigin LIKE '%' + @OriginID
GROUP BY PkTrvolDestId, PkTrvolOrigin
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
--Minimum order
INSERT @tblBCRMinOrder
SELECT BcrdBcrId AS RouteID
, Min(BcrdOrder) AS MinOfOrder
FROM tblBestChoiceRoutingDetails
GROUP BY BcrdBcrId
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
--Maximum start date
INSERT @tblBCRMaxStartDate
SELECT BcrDestId AS DestinationID
, BcrOrigin AS Origin
, Max(BcrStartDate) AS MaxOfStartDate
FROM tblBestChoiceRouting
WHERE BcrOrigin LIKE '%' + @OriginID
GROUP BY BcrOrigin, BcrDestId
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
--BCR details
INSERT @tblBCRMaxDetail
SELECT tblBestChoiceRouting.PkBcrId
, tblBestChoiceRouting.BcrOrigin
, tblBestChoiceRouting.BcrDestId
, tblBestChoiceRouting.BcrStartDate
, tblBestChoiceRouting.BcrMinimumPrice
, tblBestChoiceRouting.BcrRerouting
, tblBestChoiceRouting.BcrCliGuarantee
, tblBestChoiceRouting.BcrZeroTolerance
, tblBestChoiceRouting.BcrRemarks
FROM @tblBCRMaxStartDate AS BCRMaxStartDate INNER JOIN tblBestChoiceRouting ON BCRMaxStartDate.Origin = tblBestChoiceRouting.bcrOrigin AND
BCRMaxStartDate.DestinationId = tblBestChoiceRouting.bcrDestId AND
BCRMaxStartDate.MaxOfStartDate = tblBestChoiceRouting.bcrStartDate
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
--Determine RedCost
-- get red cost for VT
INSERT @tblRedCost
SELECT BcrdBcrId
, MAX(BcrdBuy) AS RedCost
FROM tblBestChoiceRoutingDetails INNER JOIN @tblBCRMaxDetail AS BCRMaxDetail ON tblBestChoiceRoutingDetails.BcrdBcrId = BCRMaxDetail.PkBcrID
WHERE BCRMaxDetail.BCROrigin = 1
GROUP BY BcrdBcrId
-- get red cost for non VT
INSERT @tblRedCost
SELECT BcrdBcrId
, MAX(BcrdBuy) AS RedCost
FROM tblBestChoiceRoutingDetails INNER JOIN @tblBCRMaxDetail AS BCRMaxDetail ON tblBestChoiceRoutingDetails.BcrdBcrId = BCRMaxDetail.PkBcrID
WHERE NOT BCRMaxDetail.BCROrigin = 1 AND
BcrdWeight > 2 AND
BcrdOrder <> '0'
GROUP BY BcrdBcrId
--Create temporary table with latest traffic volumes
SELECT tblTrafficVolumes.PkTrvolDestId AS DestinationID
, tblTrafficVolumes.PkTrvolOrigin AS Origin
, tblTrafficVolumes.PkTrvolDate AS TrafficDate
, tblTrafficVolumes.TrvolVolume AS TrafficVolume
INTO ##TmpTrafficVolumes_latest
FROM @tblTrafficVolumes AS TVLastDate INNER JOIN tblTrafficVolumes ON TVLastDate.MaxOfTrafficDate = tblTrafficVolumes.PkTrvolDate AND
TVLastDate.Origin = tblTrafficVolumes.PkTrvolOrigin AND
TVLastDate.DestinationId = tblTrafficVolumes.PkTrvolDestId
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
--Create temporary table with all BCR
SELECT BCRMaxDetail.PkBcrId AS RouteId
, BCRMaxDetail.BcrOrigin AS Origin
, BCRMaxDetail.BcrDestId AS DestinationId
, BCRMaxDetail.BcrStartDate AS StartDate
, BCRMaxDetail.BcrMinimumPrice AS MinimumPrice
, BCRMaxDetail.BcrCliGuarantee AS CliGuarantee
, BCRMaxDetail.BcrZeroTolerance AS ZeroTolerance
, q_bcr_averages_with_overflow.WeighedAverageOfBuyWithOverflow AS Cost
, q_bcr_averages_with_overflow_1.WeighedAverageOfBuy AS [Old Cost]
, case
when q_bcr_averages_with_overflow.WeighedAverageOfBuy > q_bcr_averages_with_overflow_1.WeighedAverageOfBuy then 'increase'
else case
when q_bcr_averages_with_overflow.WeighedAverageOfBuy < q_bcr_averages_with_overflow_1.WeighedAverageOfBuy then 'decrease'
else 'no difference'
end
end AS Inc_Dec
INTO ##Tmpbcr_inc_dec
FROM
(SELECT q_bcr_averages.RouteID
, q_bcr_averages.MaxOfBuy
, q_bcr_averages.MinOfBuy
, q_bcr_averages.WeighedAverageOfBuy
, q_bcr_averages.NumberOfRoutes
, q_bcr_averages.SumOfWeight
, case BCRMinOrder.MinOrder
when '0' then (q_bcr_averages.WeighedAverageOfBuy*0.9) + (tblBestChoiceRoutingDetails.BcrdBuy*0.1)
else q_bcr_averages.WeighedAverageOfBuy
end AS WeighedAverageOfBuyWithOverflow
, BCRMinOrder.MinOrder
FROM tblBestChoiceRoutingDetails inner join @tblBCRMinOrder AS BCRMinOrder ON tblBestChoiceRoutingDetails.BcrdBcrId = BCRMinOrder.RouteID AND
tblBestChoiceRoutingDetails.BcrdOrder = BCRMinOrder.MinOrder
inner join
(SELECT BcrdBcrId AS RouteID
, Max(BcrdBuy) AS MaxOfBuy
, Min(BcrdBuy) AS MinOfBuy
, Sum((BcrdBuy*BcrdWeight)/100) AS WeighedAverageOfBuy
, Count(PkBcrdId) AS NumberOfRoutes
, Sum(BcrdWeight) AS SumOfWeight
FROM tblBestChoiceRoutingDetails
WHERE BcrdOrder <> '0'
GROUP BY BcrdBcrId
) as q_bcr_averages ON BCRMinOrder.RouteID = q_bcr_averages.RouteID
) as q_bcr_averages_with_overflow
inner join @tblBCRMaxDetail AS BCRMaxDetail ON q_bcr_averages_with_overflow.RouteId = BCRMaxDetail.PkBCRId
inner join
(SELECT tblBestChoiceRouting.BcrOrigin AS Origin
, tblBestChoiceRouting.BcrDestId AS DestinationID
, Max(tblBestChoiceRouting.BcrStartDate) AS MaxOfStartDate1
FROM @tblBCRMaxStartDate AS BCRMaxStartDate inner join tblBestChoiceRouting ON BCRMaxStartDate.DestinationID = tblBestChoiceRouting.BcrDestId AND
BCRMaxStartDate.Origin = tblBestChoiceRouting.BcrOrigin
WHERE tblBestChoiceRouting.BcrStartDate < BCRMaxStartDate.MaxOfStartDate
GROUP BY tblBestChoiceRouting.BcrOrigin, tblBestChoiceRouting.BcrDestId
) as q_bcr_latest_1 ON BCRMaxDetail.BCRDestId = q_bcr_latest_1.DestinationId AND
BCRMaxDetail.BCROrigin = q_bcr_latest_1.Origin
inner join tblBestChoiceRouting ON tblBestChoiceRouting.BcrStartDate = q_bcr_latest_1.MaxOfStartDate1 AND
tblBestChoiceRouting.BcrDestId = q_bcr_latest_1.DestinationId AND
tblBestChoiceRouting.BcrOrigin = q_bcr_latest_1.Origin
inner join
(SELECT q_bcr_averages.RouteID
, q_bcr_averages.MaxOfBuy
, q_bcr_averages.MinOfBuy
, q_bcr_averages.WeighedAverageOfBuy
, q_bcr_averages.NumberOfRoutes
, q_bcr_averages.SumOfWeight
, case BCRMinOrder.MinOrder
when '0' then (q_bcr_averages.WeighedAverageOfBuy*0.9) + (tblBestChoiceRoutingDetails.BcrdBuy*0.1)
else q_bcr_averages.WeighedAverageOfBuy
end AS WeighedAverageOfBuyWithOverflow
, BCRMinOrder.MinOrder
FROM tblBestChoiceRoutingDetails inner join @tblBCRMinOrder AS BCRMinOrder ON tblBestChoiceRoutingDetails.BcrdBcrId = BCRMinOrder.RouteID AND
tblBestChoiceRoutingDetails.BcrdOrder = BCRMinOrder.MinOrder
inner join
(SELECT BcrdBcrId AS RouteID
, Max(BcrdBuy) AS MaxOfBuy
, Min(BcrdBuy) AS MinOfBuy
, Sum((BcrdBuy*BcrdWeight)/100) AS WeighedAverageOfBuy
, Count(PkBcrdId) AS NumberOfRoutes
, Sum(BcrdWeight) AS SumOfWeight
FROM tblBestChoiceRoutingDetails
WHERE BcrdOrder <> '0'
GROUP BY BcrdBcrId
) as q_bcr_averages ON BCRMinOrder.RouteID = q_bcr_averages.RouteID
) AS q_bcr_averages_with_overflow_1 ON tblBestChoiceRouting.PkBcrId = q_bcr_averages_with_overflow_1.RouteId
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
--Create temporary table with averages
SELECT q_bcr_averages.RouteID
, q_bcr_averages.MaxOfBuy
, q_bcr_averages.MinOfBuy
, q_bcr_averages.WeighedAverageOfBuy
, q_bcr_averages.NumberOfRoutes
, q_bcr_averages.SumOfWeight
, case BCRMinOrder.MinOrder
when '0' then (q_bcr_averages.WeighedAverageOfBuy*0.9) + (tblBestChoiceRoutingDetails.BcrdBuy*0.1)
else q_bcr_averages.WeighedAverageOfBuy
end AS WeighedAverageOfBuyWithOverflow
, BCRMinOrder.MinOrder
INTO ##Tmpbcr_averages_with_overflow
FROM tblBestChoiceRoutingDetails INNER JOIN @tblBCRMinOrder AS BCRMinOrder ON tblBestChoiceRoutingDetails.BcrdBcrId = BCRMinOrder.RouteID AND
tblBestChoiceRoutingDetails.BcrdOrder = BCRMinOrder.MinOrder
INNER JOIN
(SELECT BcrdBcrId AS RouteID
, Max(BcrdBuy) AS MaxOfBuy
, Min(BcrdBuy) AS MinOfBuy
, Sum((BcrdBuy*BcrdWeight)/100) AS WeighedAverageOfBuy
, Count(PkBcrdId) AS NumberOfRoutes
, Sum(BcrdWeight) AS SumOfWeight
FROM tblBestChoiceRoutingDetails
WHERE BcrdOrder <> '0'
GROUP BY BcrdBcrId
) as q_bcr_averages ON BCRMinOrder.RouteID = q_bcr_averages.RouteID
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
--Create temporary table with all BCR
SELECT q_bcr_r1.PkBcrId as RouteId
, q_bcr_r1.BcrOrigin as Origin
, q_bcr_r1.BcrDestId as DestinationId
, q_bcr_r1.BcrStartDate as StartDate
, q_bcr_r1.BcrMinimumPrice as MinimumPrice
, q_bcr_r1.BcrRerouting as Rerouting
, q_bcr_r1.BcrCliGuarantee as CliGuarantee
, q_bcr_r1.BcrZeroTolerance as ZeroTolerance
, q_bcr_r1.BcrRemarks as Remarks
, q_bcr_r1.BcrdOperatorID AS Carrier_id1
, q_bcr_r1.OpLongOperatorName AS Carrier_name1
, q_bcr_r1.OpShortOperatorName AS Carrier_IBIS1
, q_bcr_r1.BcrdWeight AS Weight1
, q_bcr_r1.BcrdBuy AS Buy1
, q_bcr_r1.BcrdVolumeDeal AS VD1
, q_bcr_r2.BcrdOperatorID AS Carrier_id2
, q_bcr_r2.OpLongOperatorName AS Carrier_name2
, q_bcr_r2.OpShortOperatorName AS Carrier_IBIS2
, q_bcr_r2.BcrdWeight AS Weight2
, q_bcr_r2.BcrdBuy AS Buy2
, q_bcr_r2.BcrdVolumeDeal AS VD2
, q_bcr_r3.BcrdOperatorID AS Carrier_id3
, q_bcr_r3.OpLongOperatorName AS Carrier_name3
, q_bcr_r3.OpShortOperatorName AS Carrier_IBIS3
, q_bcr_r3.BcrdWeight AS Weight3
, q_bcr_r3.BcrdBuy AS Buy3
, q_bcr_r3.BcrdVolumeDeal AS VD3
, q_bcr_r4.BcrdOperatorID AS Carrier_id4
, q_bcr_r4.OpLongOperatorName AS Carrier_name4
, q_bcr_r4.OpShortOperatorName AS Carrier_IBIS4
, q_bcr_r4.BcrdWeight AS Weight4
, q_bcr_r4.BcrdBuy AS Buy4
, q_bcr_r4.BcrdVolumeDeal AS VD4
, q_bcr_r5.BcrdOperatorID AS Carrier_id5
, q_bcr_r5.OpLongOperatorName AS Carrier_name5
, q_bcr_r5.OpShortOperatorName AS Carrier_IBIS5
, q_bcr_r5.BcrdWeight AS Weight5
, q_bcr_r5.BcrdBuy AS Buy5
, q_bcr_r5.BcrdVolumeDeal AS VD5
, q_bcr_r0.BcrdOperatorID AS Carrier_id0
, q_bcr_r0.OpLongOperatorName AS Carrier_name0
, q_bcr_r0.OpShortOperatorName AS Carrier_IBIS0
, q_bcr_r0.BcrdWeight AS Weight0
, q_bcr_r0.BcrdBuy AS Buy0
, q_bcr_r0.BcrdVolumeDeal AS VD0
INTO ##TmpBCR_In_All
FROM
(SELECT BCRMaxDetail.PkBcrId
, BCRMaxDetail.BcrOrigin
, BCRMaxDetail.BcrDestId
, BCRMaxDetail.BcrStartDate
, BCRMaxDetail.BcrMinimumPrice
, BCRMaxDetail.BcrRerouting
, BCRMaxDetail.BcrCliGuarantee
, BCRMaxDetail.BcrZeroTolerance
, BCRMaxDetail.BcrRemarks
, tblOperators.OpLongOperatorName
, tblOperators.OpShortOperatorName
, tblBestchoiceRoutingDetails.PkBcrdId
, tblBestchoiceRoutingDetails.bcrdOrder
, tblBestchoiceRoutingDetails.BcrdOperatorId
, tblBestchoiceRoutingDetails.bcrdWeight
, tblBestchoiceRoutingDetails.bcrdBuy
, tblBestchoiceRoutingDetails.bcrdVolumeDeal
FROM tblOperators inner join tblBestchoiceRoutingDetails ON tblOperators.PkOpId = tblBestchoiceRoutingDetails.BcrdOperatorId
inner join @tblBCRMaxDetail AS BCRMaxDetail ON tblBestchoiceRoutingDetails.bcrdbcrId = BCRMaxDetail.pkbcrId
WHERE tblBestchoiceRoutingDetails.bcrdOrder = 1
) as q_bcr_r1
LEFT JOIN
(SELECT BCRMaxDetail.PkBcrId
, BCRMaxDetail.BcrOrigin
, BCRMaxDetail.BcrDestId
, BCRMaxDetail.BcrStartDate
, BCRMaxDetail.BcrMinimumPrice
, BCRMaxDetail.BcrRerouting
, BCRMaxDetail.BcrCliGuarantee
, BCRMaxDetail.BcrZeroTolerance
, BCRMaxDetail.BcrRemarks
, tblOperators.OpLongOperatorName
, tblOperators.OpShortOperatorName
, tblBestchoiceRoutingDetails.PkBcrdId
, tblBestchoiceRoutingDetails.bcrdOrder
, tblBestchoiceRoutingDetails.BcrdOperatorId
, tblBestchoiceRoutingDetails.bcrdWeight
, tblBestchoiceRoutingDetails.bcrdBuy
, tblBestchoiceRoutingDetails.bcrdVolumeDeal
FROM tblOperators inner join tblBestchoiceRoutingDetails ON tblOperators.PkOpId = tblBestchoiceRoutingDetails.BcrdOperatorId
inner join @tblBCRMaxDetail AS BCRMaxDetail ON tblBestchoiceRoutingDetails.bcrdbcrId = BCRMaxDetail.pkbcrId
WHERE tblBestchoiceRoutingDetails.bcrdOrder = 2
) as q_bcr_r2 ON q_bcr_r1.BcrStartDate = q_bcr_r2.BcrStartDate AND
q_bcr_r1.BcrDestId = q_bcr_r2.BcrDestID AND
q_bcr_r1.BcrOrigin = q_bcr_r2.BcrOrigin
LEFT JOIN
(SELECT BCRMaxDetail.PkBcrId
, BCRMaxDetail.BcrOrigin
, BCRMaxDetail.BcrDestId
, BCRMaxDetail.BcrStartDate
, BCRMaxDetail.BcrMinimumPrice
, BCRMaxDetail.BcrRerouting
, BCRMaxDetail.BcrCliGuarantee
, BCRMaxDetail.BcrZeroTolerance
, BCRMaxDetail.BcrRemarks
, tblOperators.OpLongOperatorName
, tblOperators.OpShortOperatorName
, tblBestchoiceRoutingDetails.PkBcrdId
, tblBestchoiceRoutingDetails.bcrdOrder
, tblBestchoiceRoutingDetails.BcrdOperatorId
, tblBestchoiceRoutingDetails.bcrdWeight
, tblBestchoiceRoutingDetails.bcrdBuy
, tblBestchoiceRoutingDetails.bcrdVolumeDeal
FROM tblOperators inner join tblBestchoiceRoutingDetails ON tblOperators.PkOpId = tblBestchoiceRoutingDetails.BcrdOperatorId
inner join @tblBCRMaxDetail AS BCRMaxDetail ON tblBestchoiceRoutingDetails.bcrdbcrId = BCRMaxDetail.pkbcrId
WHERE tblBestchoiceRoutingDetails.bcrdOrder = 3
) as q_bcr_r3 ON q_bcr_r1.BcrStartDate = q_bcr_r3.BcrStartDate AND
q_bcr_r1.BcrDestID = q_bcr_r3.BcrDestID AND
q_bcr_r1.BcrOrigin = q_bcr_r3.BcrOrigin
LEFT JOIN
(SELECT BCRMaxDetail.PkBcrId
, BCRMaxDetail.BcrOrigin
, BCRMaxDetail.BcrDestId
, BCRMaxDetail.BcrStartDate
, BCRMaxDetail.BcrMinimumPrice
, BCRMaxDetail.BcrRerouting
, BCRMaxDetail.BcrCliGuarantee
, BCRMaxDetail.BcrZeroTolerance
, BCRMaxDetail.BcrRemarks
, tblOperators.OpLongOperatorName
, tblOperators.OpShortOperatorName
, tblBestchoiceRoutingDetails.PkBcrdId
, tblBestchoiceRoutingDetails.bcrdOrder
, tblBestchoiceRoutingDetails.BcrdOperatorId
, tblBestchoiceRoutingDetails.bcrdWeight
, tblBestchoiceRoutingDetails.bcrdBuy
, tblBestchoiceRoutingDetails.bcrdVolumeDeal
FROM tblOperators inner join tblBestchoiceRoutingDetails ON tblOperators.PkOpId = tblBestchoiceRoutingDetails.BcrdOperatorId
inner join @tblBCRMaxDetail AS BCRMaxDetail ON tblBestchoiceRoutingDetails.bcrdbcrId = BCRMaxDetail.pkbcrId
WHERE tblBestchoiceRoutingDetails.bcrdOrder = 4
) as q_bcr_r4 ON q_bcr_r1.BcrStartDate = q_bcr_r4.BcrStartDate AND
q_bcr_r1.BcrDestID = q_bcr_r4.BcrDestID AND
q_bcr_r1.BcrOrigin = q_bcr_r4.BcrOrigin
LEFT JOIN
(SELECT BCRMaxDetail.PkBcrId
, BCRMaxDetail.BcrOrigin
, BCRMaxDetail.BcrDestId
, BCRMaxDetail.BcrStartDate
, BCRMaxDetail.BcrMinimumPrice
, BCRMaxDetail.BcrRerouting
, BCRMaxDetail.BcrCliGuarantee
, BCRMaxDetail.BcrZeroTolerance
, BCRMaxDetail.BcrRemarks
, tblOperators.OpLongOperatorName
, tblOperators.OpShortOperatorName
, tblBestchoiceRoutingDetails.PkBcrdId
, tblBestchoiceRoutingDetails.bcrdOrder
, tblBestchoiceRoutingDetails.BcrdOperatorId
, tblBestchoiceRoutingDetails.bcrdWeight
, tblBestchoiceRoutingDetails.bcrdBuy
, tblBestchoiceRoutingDetails.bcrdVolumeDeal
FROM tblOperators inner join tblBestchoiceRoutingDetails ON tblOperators.PkOpId = tblBestchoiceRoutingDetails.BcrdOperatorId
inner join @tblBCRMaxDetail AS BCRMaxDetail ON tblBestchoiceRoutingDetails.bcrdbcrId = BCRMaxDetail.pkbcrId
WHERE tblBestchoiceRoutingDetails.bcrdOrder = 5
) as q_bcr_r5 ON q_bcr_r1.BcrStartDate = q_bcr_r5.BcrStartDate AND
q_bcr_r1.BcrDestID = q_bcr_r5.BcrDestID AND
q_bcr_r1.BcrOrigin = q_bcr_r5.BcrOrigin
LEFT JOIN
(SELECT BCRMaxDetail.PkBcrId
, BCRMaxDetail.BcrOrigin
, BCRMaxDetail.BcrDestId
, BCRMaxDetail.BcrStartDate
, BCRMaxDetail.BcrMinimumPrice
, BCRMaxDetail.BcrRerouting
, BCRMaxDetail.BcrCliGuarantee
, BCRMaxDetail.BcrZeroTolerance
, BCRMaxDetail.BcrRemarks
, tblOperators.OpLongOperatorName
, tblOperators.OpShortOperatorName
, tblBestchoiceRoutingDetails.PkBcrdId
, tblBestchoiceRoutingDetails.bcrdOrder
, tblBestchoiceRoutingDetails.BcrdOperatorId
, tblBestchoiceRoutingDetails.bcrdWeight
, tblBestchoiceRoutingDetails.bcrdBuy
, tblBestchoiceRoutingDetails.bcrdVolumeDeal
FROM tblOperators inner join tblBestchoiceRoutingDetails ON tblOperators.PkOpId = tblBestchoiceRoutingDetails.BcrdOperatorId
inner join @tblBCRMaxDetail AS BCRMaxDetail ON tblBestchoiceRoutingDetails.bcrdbcrId = BCRMaxDetail.pkbcrId
WHERE tblBestchoiceRoutingDetails.bcrdOrder = 0
) as q_bcr_r0 ON q_bcr_r1.BcrStartDate = q_bcr_r0.BcrStartDate AND
q_bcr_r1.BcrDestID = q_bcr_r0.BcrDestID AND
q_bcr_r1.BcrOrigin = q_bcr_r0.BcrOrigin
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
--Selection for export
SELECT ' ' AS a
, ISNULL(tblDestinations.DestCF55, '') AS Destinationnb
, ISNULL(tblOrigins.OriginName, '') as Origin
, ISNULL(tblDestinations.DestName, '') as DestName
, ISNULL(q_bcr_all.Carrier_name1, '') as Carrier_name1
, ISNULL(q_bcr_all.Carrier_name2, '') as Carrier_name2
, ISNULL(q_bcr_all.Buy1,'') as Buy1
, ISNULL(q_bcr_all.Buy2,'') as Buy2
, ISNULL(q_bcr_all.Carrier_name3, '') as Carrier_name3
, ISNULL(q_bcr_all.Buy3,'') as Buy3
, ISNULL(q_bcr_all.Carrier_name4, '') as Carrier_name4
, ISNULL(q_bcr_all.Buy4,'') as Buy4
, ISNULL(q_bcr_all.Carrier_name5, '') as Carrier_name5
, ISNULL(q_bcr_all.Buy5,'') as Buy5
, ISNULL(q_bcr_averages_with_overflow.WeighedAverageOfBuy,'') as WeighedAverageOfBuy
, ISNULL(q_bcr_averages_with_overflow.WeighedAverageOfBuyWithOverflow,'') as WeighedAverageOfBuyWithOverflow
, ISNULL(q_bcr_inc_dec.Inc_Dec, '') as Inc_Dec
, ISNULL(q_bcr_inc_dec.[Old Cost],'') as [Old Cost]
, ISNULL(q_bcr_all.StartDate, '') as StartDate
, ISNULL(q_bcr_all.Weight1, '') AS [1 Route %]
, ISNULL(q_bcr_all.Weight2, '') AS [2 Route %]
, ISNULL(q_bcr_all.Weight3, '') AS [3 Route %]
, ISNULL(q_bcr_all.Weight4, '') AS [R Route %]
, ISNULL(q_bcr_all.Weight5, '') AS
, ISNULL(q_bcr_all.Carrier_name0, '') as Carrier_name0
, ISNULL(q_bcr_all.Buy0,'') as Buy0
, ISNULL(q_bcr_all.Remarks, '') as Remarks
, ISNULL(tblDestinations.DestName, '') AS standard
, ISNULL(q_TrafficVolumes_latest.TrafficVolume,'') as TrafficVolume
, case ISNULL(tblDestinationsOrigins.PkDestID,0)
when 0 then 'N'
else 'Y'
end AS [IBIS Available]
, ISNULL(tblRegions.RName, '') as Region
, ISNULL(tblAreaManagers.AmFirstName, '') as FirstName
, q_bcr_all.MinimumPrice as MinimumPrice
, BCRRedCost.RedCost AS RedCost
, case q_bcr_all.CliGuarantee
when 1 then 'Y'
else 'N'
end as CliGaurantee
, case q_bcr_all.ZeroTolerance
when 1 then 'Y'
else 'N'
end as ZeroTolerance
, tblDestinations.DestIBISCode as [IBIS Code]
FROM tblDestinations INNER JOIN tblRegions ON tblRegions.PkRId = tblDestinations.DestRId
INNER JOIN tblAreaManagers ON tblAreaManagers.PkAmId = tblDestinations.DestAmId
INNER JOIN ##TmpBCR_In_All as q_bcr_all ON tblDestinations.PkDestId = q_bcr_all.DestinationID
INNER JOIN @tblRedCost AS BCRRedCost ON q_bcr_all.RouteID = BCRRedCost.RouteID
LEFT JOIN tblOrigins ON q_bcr_all.Origin = tblOrigins.PKOriginID
LEFT JOIN tblDestinationsOrigins ON tblDestinations.PkDestId = tblDestinationsOrigins.PkDestID AND
q_bcr_all.Origin = tblDestinationsOrigins.PkOriginID
LEFT JOIN ##Tmpbcr_averages_with_overflow as q_bcr_averages_with_overflow ON q_bcr_all.RouteId = q_bcr_averages_with_overflow.RouteID
LEFT JOIN ##Tmpbcr_inc_dec as q_bcr_inc_dec ON q_bcr_all.RouteId = q_bcr_inc_dec.RouteID
LEFT JOIN ##TmpTrafficVolumes_latest as q_TrafficVolumes_latest ON q_bcr_all.DestinationID = q_TrafficVolumes_latest.DestinationID AND
q_bcr_all.Origin = q_TrafficVolumes_latest.Origin
ORDER BY q_bcr_all.Origin, tblDestinations.DestName
SET @Err = @@ERROR
IF @Err <> 0 GOTO ERROR
ERROR:
DROP TABLE ##TmpBCR_In_All
DROP TABLE ##Tmpbcr_averages_with_overflow
DROP TABLE ##Tmpbcr_inc_dec
DROP TABLE ##TmpTrafficVolumes_latest
RETURN @Err
END
GO
spid hostname blk dbname cmd waittype
------ ------------ ---- ---------- ---------------- --------
101 BGC-GLNFSROB 0 DbCbuGbs SELECT INTO 0x0000
Blocking process 101
===========================
EventType Parameters EventInfo
--------- ---------- ----------------------------
RPC Event 0 dbo.UspGetListAllBCRRoutes;1
spid db Obj IndId Type Resource Mode Status
------ ------------------------------ ------------------------------ ------ ---- ---------------- -------- ------
101 tempdb NULL 0 DB [BULK-OP-LOG] NULL GRANT
101 DbCbuGbs NULL 0 DB S GRANT
101 tempdb NULL 0 DB [BULK-OP-DB] NULL GRANT
101 tempdb sysobjects 0 TAB IX GRANT
101 tempdb syscolumns 0 TAB IX GRANT
101 tempdb sysindexes 0 TAB IX GRANT
101 tempdb sysindexes 1 KEY (2f0091cb7a37) X GRANT
101 tempdb NULL 0 TAB Sch-M GRANT
101 tempdb sysobjects 1 KEY (6800b41caa69) X GRANT
101 tempdb syscolumns 2 KEY (2403f25f8937) X GRANT
101 tempdb syscolumns 1 KEY (69002369165f) X GRANT
101 tempdb NULL 0 PAG 1:421 X GRANT
101 tempdb NULL 0 PAG 1:420 X GRANT
101 DbCbuGbs sp_msupg_dropcatalogcomputedco 0 RID 3:58:121 S GRANT
101 DbCbuGbs sp_msupg_dosystabcatalogupgrad 0 PAG 3:536 S GRANT
101 tempdb syscolumns 2 KEY (89017ecb6e7a) X GRANT
101 tempdb syscolumns 2 KEY (f801dddfe6bf) X GRANT
101 tempdb syscolumns 2 KEY (7302adfa7cd8) X GRANT
101 DbCbuGbs sp_msupg_dosystabcatalogupgrad 0 TAB IS GRANT
101 tempdb syscolumns 1 KEY (6b00a8a11ff5) X GRANT
101 tempdb sysobjects 1 KEY (2f0023991097) X GRANT
101 tempdb syscolumns 2 KEY (d101374d2e95) X GRANT
101 tempdb NULL 0 TAB Sch-M GRANT
101 tempdb syscolumns 1 KEY (6a00cdc6a34d) X GRANT
101 tempdb NULL 0 TAB Sch-M GRANT
101 tempdb syscolumns 2 KEY (560200a83ecb) X GRANT
101 tempdb syscolumns 2 KEY (5001dbd94b02) X GRANT
101 tempdb syscolumns 2 KEY (3903f7202aa0) X GRANT
101 tempdb NULL 0 PAG 1:510348 X GRANT
101 tempdb NULL 0 PAG 1:510349 X GRANT
101 tempdb NULL 0 PAG 1:510350 X GRANT
101 tempdb NULL 0 PAG 1:510351 X GRANT
101 tempdb NULL 0 PAG 1:510345 X GRANT
101 tempdb NULL 0 PAG 1:510346 X GRANT
101 tempdb NULL 0 PAG 1:510347 X GRANT
101 tempdb NULL 0 PAG 1:510356 X GRANT
101 tempdb NULL 0 PAG 1:510357 X GRANT
101 tempdb NULL 0 PAG 1:510358 X GRANT
101 tempdb NULL 0 EXT 1:510360 X GRANT
101 tempdb NULL 0 PAG 1:510359 X GRANT
101 tempdb NULL 0 PAG 1:510352 X GRANT
101 tempdb NULL 0 PAG 1:510353 X GRANT
101 tempdb NULL 0 PAG 1:510354 X GRANT
101 tempdb NULL 0 PAG 1:510355 X GRANT
101 tempdb NULL 0 PAG 1:510364 X GRANT
101 tempdb NULL 0 PAG 1:510365 X GRANT
101 tempdb NULL 0 EXT 1:510352 X GRANT
101 tempdb NULL 0 PAG 1:510366 X GRANT
101 tempdb NULL 0 PAG 1:510367 X GRANT
101 tempdb NULL 0 PAG 1:510360 X GRANT
101 tempdb NULL 0 PAG 1:510361 X GRANT
101 tempdb NULL 0 PAG 1:510362 X GRANT
101 tempdb NULL 0 PAG 1:510363 X GRANT
101 tempdb sysobjects 2 KEY (dc02ad5b4dd7) X GRANT
101 tempdb NULL 0 TAB Sch-M GRANT
101 tempdb NULL 0 IDX IDX: 2:149481886 X GRANT
101 tempdb syscolumns 2 KEY (d2021ea3cb76) X GRANT
101 tempdb syscolumns 1 KEY (6f00ff367d7a) X GRANT
101 DbCbuGbs sp_msupg_dropcatalogcomputedco 0 TAB S GRANT
101 tempdb sysobjects 3 KEY (2f00569976d3) X GRANT
101 tempdb syscolumns 2 KEY (ce023a59f92a) X GRANT
101 tempdb sysindexes 1 KEY (6800595ea894) X GRANT
101 tempdb sysobjects 2 KEY (1e05f57999ee) X GRANT
101 tempdb syscolumns 1 KEY (7100cc41a29a) X GRANT
101 tempdb syscolumns 1 KEY (6d0074fe74d0) X GRANT
101 tempdb NULL 0 TAB Sch-M GRANT
101 tempdb syscolumns 2 KEY (1e01442ee7fd) X GRANT
101 tempdb syscolumns 1 KEY (3200f7385976) X GRANT
101 tempdb syscolumns 1 KEY (6c001199c868) X GRANT
101 tempdb syscolumns 1 KEY (7000a9261e22) X GRANT
101 tempdb sysobjects 3 KEY (6800c11ccc2d) X GRANT
101 tempdb syscolumns 2 KEY (f6029f9b6d92) X GRANT
101 tempdb syscolumns 2 KEY (bb018f04e812) X GRANT
101 tempdb syscolumns 1 KEY (3100925fe5ce) X GRANT
101 tempdb syscolumns 1 KEY (30007cf050dc) X GRANT
101 tempdb syscolumns 1 KEY (33004e008eeb) X GRANT
101 tempdb syscolumns 1 KEY (720022ee1788) X GRANT
101 tempdb syscolumns 1 KEY (6e009a51c1c2) X GRANT
101 tempdb syscolumns 2 KEY (cb025405274d) X GRANT
Blocked process 104
===========================
EventType Parameters EventInfo
--------- ---------- ------------------------
RPC Event 0 UspGetListAllBCRRoutes;1
spid db Obj IndId Type Resource Mode Status
------ ------------------------------ ------------------------------ ------ ---- ---------------- -------- ------
104 DbCbuGbs NULL 0 DB S GRANT
104 tempdb sysobjects 0 TAB IX GRANT
104 tempdb syscolumns 0 TAB IX GRANT
104 tempdb sysindexes 0 TAB IX GRANT
104 tempdb NULL 0 TAB Sch-M GRANT
104 tempdb syscolumns 2 KEY (be02996b99ba) X GRANT
104 tempdb syscolumns 1 KEY (7d0094bc8011) X GRANT
104 tempdb sysindexes 1 KEY (7a001745f322) X GRANT
104 tempdb syscolumns 1 KEY (7c00f1db3ca9) X GRANT
104 tempdb syscolumns 1 KEY (7e002d84578c) X GRANT
104 tempdb NULL 0 TAB Sch-M GRANT
104 tempdb syscolumns 1 KEY (7b001f7489bb) X GRANT
104 tempdb sysobjects 2 KEY (1e05f57999ee) S WAIT
104 tempdb syscolumns 2 KEY (410373214091) X GRANT
104 tempdb sysobjects 1 KEY (7a00d1f44c87) X GRANT
104 tempdb syscolumns 2 KEY (9b01b7286154) X GRANT
104 DbCbuGbs xp_logevent 0 TAB IS GRANT
104 tempdb syscolumns 2 KEY (1603b8bf0a4e) X GRANT
open_tran
------------------- ---------
# open transactions 3
November 6, 2003 at 7:07 am
Ludo,
I think the problem may be centered around the select into statements, I have had similar problems in the past. Try creating the global tables and then using insert-- select. Also one thing I have found to help a lot of times for speed table variables is to assign a primary key to it. The most likely cause though is the select --into.
Mike
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply