I am investigating using the READPAST hint to reduce resource locking in our application's financial subsystem. It seemed like a good way to go because financial transaction records are only ever added, never updated or deleted. The only rows that would ever be skipped are brand new rows inserted inside of a transaction; they effectively don't exist to the outside world until the transaction is commited. However, I noticed worse performance on queries that utilize indexed views that I had put the READPAST hint on. Comparing the query plans, it looks like with the hint, the query optimizer chooses to not use the indexed view and instead falls back to treating it like a regular view. I'm not sure why that would be; I imagine indexed views to be like any other index in that keys can be locked during operations and adding READPAST would work similarly.

SELECT TOP 1 isa.InvoiceId
FROM Financial_InvoiceSummaryAmounts isa WITH (READPAST)
WHERE isa.TotalOwedAmount = 0.0

SELECT TOP 1 isa.InvoiceId
FROM Financial_InvoiceSummaryAmounts isa
WHERE isa.TotalOwedAmount = 0.0

I am investigating using the READPAST hint to reduce resource locking in our application's financial subsystem. 

It seemed like a good way to go because financial transaction records are only ever added, never updated or deleted. The only rows that would ever be skipped are brand new rows inserted inside of a transaction; they effectively don't exist to the outside world until the transaction is committed. 

However, I noticed worse performance on queries that utilize indexed views that I had put the READPAST hint on. Comparing the query plans, it looks like with the hint, the query optimizer chooses to not use the indexed view and instead falls back to treating it like a regular view. 

I'm not sure why that would be; I imagine indexed views to be like any other index in that keys can be locked during operations and adding READPAST would work similarly.

SELECT TOP 1 isa.InvoiceId
FROM Financial_InvoiceSummaryAmounts isa WITH (READPAST)
WHERE isa.TotalOwedAmount = 0.0

SELECT TOP 1 isa.InvoiceId
FROM Financial_InvoiceSummaryAmounts isa
WHERE isa.TotalOwedAmount = 0.0

Adding a NOEXPAND hint as well does seem to work, but I am interested in learning more about possibly why READPAST caused the query optimizer to make that choice in the first place (as part of a full answer).