Unexpected Blocking during the Indexed View Creation - StraightforwardSQL

[utterances-bot]

Unexpected Blocking during the Indexed View Creation - StraightforwardSQL

Here are the chronicles of my struggles to add a large Clustered index to a View without causing an outage.

https://straightforwardsql.com/posts/unexpected-blocking-during-the-indexed-view-creation/

[ben-thul]

What I find particularly unfortunate was that the indexed view was being considered before it was, in fact, an indexed view. That is, the optimizer is doing something that's equivalent to reading an uncommitted state of the database (i.e. the create index statement has been issued but not finished) and as a result the workload that I'm trying to help by creating this indexed view is hindered.

[zikato]

Agreed, it's a bug and I've tried reporting it here: https://feedback.azure.com/d365community/idea/68a73199-0a30-ed11-a81b-000d3a04ded5

[mjbarlow1985]

Really interesting article, thanks.

I think I have the same issue and suspect adding "OPTION (EXPAND VIEWS)" to my SELECT would resolve. However, my SELECT is within a view that is dynamically created and this option is not supported on the SELECT when within a view :(

"Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'option'."

Any ideas on how to workaround this?

[mjbarlow1985]

....Either that or I am experiencing the bug you reference above....

[ben-thul]

@mjbarolow1985 - are you able to put the OPTION (EXPAND VIEWS) hint on the query you're actually trying to run?

[mjbarlow1985]

Yes when run as an independent query. But when I try and make that query a view, its not supported.

[ben-thul]

That's a no go. From the docs for create view:

The SELECT clauses in a view definition cannot include the following: ... The OPTION clause

[mjbarlow1985]

Yes exactly, so not sure what my options are as a workaround here? Do you know if this is actually logged as a bug at all? Thanks for your time :)