3

I have created the following indexed view and its clustered index:

This view was created on a subscriber database and all the tables used in the view are constantly being written to through the replication agent jobs (transaction replication).

USE Bocss2
GO

SP_HELP 'dbo.tblBOrder'
GO
sp_help 'dbo.tblBOrderPayment'
go

CREATE VIEW DBO.vwOrders
WITH SCHEMABINDING
AS 

/*-----------------------------------------------------------------------*\
   view for boccs2 reports
   29-sep-2015
\*-----------------------------------------------------------------------*/
SELECT 

o.strBxOrderNo,
op.lngPaymentID,
sintMarketId,
sdtmOrdCreated,
o.strCurrencyCode,
sintOrderStatusID,
op.sintPaymentTypeID,
optd.strPaymentTypeDescr

FROM dbo.tblBOrder o 

INNER JOIN dbo.tblBOrderPayment op 
        ON op.strBxOrderNo = o.strBxOrderNo

INNER JOIN dbo.tblBOrderPaymentTypeDescr optd 
        ON op.sintPaymentTypeID = optd.sintPaymentTypeID 
        AND optd.sintLanguageID = 1

WHERE 1=1
AND ( NOT o.sintOrderStatusId IN ( 9, 10, 11, 12, 13, 14 ))
AND OP.decPaymentAmount > 0 --Exclude the zero payment record...(sometimes created by the system to track the CC details) 

GO

--============================
-- THE ROLLBACK
--============================
--DROP VIEW DBO.vwOrders
GO


--===========================================================
-- the index creation
--http://dba.stackexchange.com/questions/116577/what-would-be-a-good-choice-of-clustered-index-and-other-indexes-for-this-situ
--===========================================================

set deadlock_priority high
CREATE UNIQUE CLUSTERED INDEX PK_VWoRDERS_ 
ON DBO.vwOrders (sintMarketId,sdtmOrdCreated,strBxOrderNo,lngPaymentID)
WITH(DROP_EXISTING=OFF)
GO
update statistics DBO.vwOrders
go
update statistics dbo.tblBOrderPayment
go
update statistics dbo.tblBOrder
go

We had great results on our queries that were using this indexed view, however,

I had to drop it because of too many deadlocks caused by the replication.

replication was deadlocking with itself when inserting into table dbo.tblBOrderPayment or table dbo.tblBOrder

how could I have avoid this rollback?

whilst I cannot re-create the view and put it in place in a busy production system, I have add below information from the log that shows me 2 replication procedures deadlocking each other.

enter image description here

OrderPayment and Order.

Looking at it today(day 1 after the rollback) , it seems so clear that this indexed view - with an inner join using these 2 big tables - is not a good idea.

Unless I had changed also the way data is written into these tables, alongside the replication.

  • 1
    Can you re-enable the view and capture a deadlock graph? – Max Vernon Oct 2 '15 at 0:54
  • What isolation level are you using for the query? Are you forcing RCSI at the database level? – Phil Grayson May 27 '16 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.