![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
Even though no one commented on my MSSQL pain, I thought it only friendly to post the solution. The precise "why" remains a mystery, though my money is still on an index corruption bug. I did find the "what"; indexed views!
They sounds innocuous enough. Oracle's name for the same mechanism, materialized views, gives more of a hint of what's going on. Basically, the "view" is actually a system maintained table that updates based on changes in the underlying tables.
There are a number of technical limitations for such things, but when they work they give a performance boost that's worth the hassle, mostly. Such a snapshot shouldn't impact the source data at all, but something about how MS implements this seems to.
Query optimizers are the black box voodoo of any RDBMS. Looks like SQL Server will select any index, even one that essentially has a circular logic dependency. I don't know this for sure, all I know is that my issues disappeared when I got rid of the indexed view that pointed at my problem table.
They sounds innocuous enough. Oracle's name for the same mechanism, materialized views, gives more of a hint of what's going on. Basically, the "view" is actually a system maintained table that updates based on changes in the underlying tables.
There are a number of technical limitations for such things, but when they work they give a performance boost that's worth the hassle, mostly. Such a snapshot shouldn't impact the source data at all, but something about how MS implements this seems to.
Query optimizers are the black box voodoo of any RDBMS. Looks like SQL Server will select any index, even one that essentially has a circular logic dependency. I don't know this for sure, all I know is that my issues disappeared when I got rid of the indexed view that pointed at my problem table.