or
sql-server
mars imported from SE
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

[![enter image description here][1]][1]

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

[![enter image description here][2]][2]

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).

  [1]: https://i.stack.imgur.com/aR9zV.png
  [2]: https://i.stack.imgur.com/l0UeJ.png
Top Answer
Paul White
Reusing the example table and indexed view from my article [Another Reason to Use `NOEXPAND` hints in Enterprise Edition][1]:

    CREATE TABLE dbo.T
    (
        col1 integer NOT NULL
    );
    GO
    INSERT dbo.T WITH (TABLOCKX)
        (col1)
    SELECT 
        SV.number
    FROM master.dbo.spt_values AS SV
    WHERE 
        SV.type = N'P';
    GO
    CREATE VIEW dbo.VT
    WITH SCHEMABINDING
    AS
    SELECT T.col1 
    FROM dbo.T AS T;

## Repro

This query matches the indexed view (albeit with a redundant aggregate):

    SELECT DISTINCT
        VT.col1 
    FROM dbo.VT AS VT;

[![Indexed view matched][2]][2]

Adding a `READPAST` hint results in accessing the base table:

    SELECT DISTINCT
        VT.col1 
    FROM dbo.VT AS VT 
        WITH (READPAST);

[![Indexed view not matched][3]][3]

## Explanation

The `READPAST` hint is semantic-affecting. The optimizer resists rewriting queries such that the results change. To illustrate:

The following query executes without problems:

    SELECT DISTINCT
        VT.col1 
    FROM dbo.VT AS VT 
        WITH (READPAST);

However:

    SELECT DISTINCT
        VT.col1 
    FROM dbo.VT AS VT 
        WITH (READPAST)
    OPTION 
        (TABLE HINT (VT, FORCESCAN));

Produces the error:

``` none
Msg 8722, Level 16, State 1, Line 42
Cannot execute query.
Semantic affecting hint 'readpast' appears in the 'WITH' clause of object 'VT'
but not in the corresponding 'TABLE HINT' clause.
Change the OPTION (TABLE HINTS...) clause so the semantic affecting hints
match the WITH clause.
```

When you reference the indexed view without the `NOEXPAND` hint, the view is expanded (before compilation and optimization begins) to reference the underlying objects instead. Later in the process, the optimizer may consider matching the query tree back to an indexed view, in whole or in part.

When `READPAST` is used without `NOEXPAND`, the hint [propagates][4] to the base table, preventing view matching (different semantics).

With `NOEXPAND`, the hint applies to the view directly, so there is no problem.


  [1]: https://sqlperformance.com/2015/12/sql-performance/noexpand-hints
  [2]: https://i.stack.imgur.com/N3XZM.png
  [3]: https://i.stack.imgur.com/z26Zv.png
  [4]: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table#remarks
Why does the READPAST hint cause indexed views to be ignored?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.