Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Msg 8907, Level 16, State 1, Line 1 The spatial index, XML index or
indexed view 'ViewName' (object ID 784109934) contains
rows that were not produced by the view definition. This does not
necessarily represent an integrity issue with the data in this
database. (...)

I do understand that this message indicates that the materialized data of the indexed view 'ViewName' is not identical with what the underlying query produces. However, manually verifying the data does not turn up any discrepancies:

NOEXPAND was used to force use of the (only) index on ViewName. FORCESCAN was used to prevent indexed view matching from happening. The execution plan confirms both measures to be working.

No rows are being returned here, meaning that the two tables are identical. (There are only integer and guid columns, collations do not come into play).

The error cannot be fixed by recreating the index on the view or by running DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS. Repeating the fixes also did not help. Why does DBCC CHECKDB report this error? How to get rid of it?

(Even if rebuilding fixed it my question would still stand - why is an error reported although my data checking queries run successfully?)

Are you saying you dropped and re-created the index on the view and DBCC CHECKDB still reports the same error? What about dropping the view and creating it from scratch?
–
Aaron Bertrand♦Jul 30 '13 at 17:21

From BOL : Troubleshooting DBCC Errors on Indexed ViewsIf the indexed view does not contain an aggregate over values of type float or real and you receive errors 8907 or 8708, drop the index on the view and re-create it. Do not use ALTER INDEX REBUILD to try to remove the differences between the stored and the computed view, because ALTER INDEX REBUILD does not recalculate the view before rebuilding the index. Then run DBCC CHECKTABLE on the View to verify no differences remain.
–
KinJul 30 '13 at 17:23

@Kin I edited your comment. The [1] notation does not work in comment mark-down.
–
Aaron Bertrand♦Jul 30 '13 at 17:28

I recreated everything. I also let DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS run. It said it rebuilt the view, but then it reported the same error.
–
usrJul 30 '13 at 17:31

Can you show the view definition (if too long here then in a pastebin)?
–
Aaron Bertrand♦Jul 30 '13 at 17:41

2 Answers
2

The query processor can produce an invalid execution plan for the (correct) query generated by DBCC to check that the view index produces the same rows as the underlying view query.

The plan produced by the query processor incorrectly handles NULLs for the ImageObjectID column. It incorrectly reasons that the view query rejects NULLs for this column, when it does not. Thinking that NULLs are excluded, it is able to match the filtered nonclustered index on the Users table that filters on ImageObjectID IS NOT NULL.

By producing a plan that uses this filtered index, it ensures that rows with NULL in ImageObjectID are not encountered. These rows are returned (correctly) from the view index, so it appears there is a corruption when there is not.

This is generic code that compares values in a NULL-aware fashion. It is certainly verbose, but the logic is fine.

The bug in the query processor's reasoning means that a query plan that incorrectly uses the filtered index may be produced, as in the example plan fragment below:

The DBCC query takes a different code path through the query processor from user queries. This code path contains the bug. When a plan using the filtered index is generated, it cannot be used with the USE PLAN hint to force that plan shape with the same query text submitted from a user database connection.

The main optimizer code path (for user queries) does not contain this bug, so it is specific to internal queries like those generated by DBCC.

I can see the faulty plan in the SQL Profiler Showplan XML event. I'll mark this as the answer.; Why does DBCC build the query in a different way than the normal query processor?; I'll add a link to this answer to the connect item.
–
usrJul 31 '13 at 16:06

2

@usr DBCC does all sorts of things that wouldn't be possible from a user connection. I imagine it works this way because it has to, but you'd have to ask someone like Paul Randal to get the real detail on that. He might not be at liberty to say, of course. I do know that there are lots of things outside DBCC that do even weirder things; some even construct an execution plan without going through the optimizer at all!
–
Paul WhiteJul 31 '13 at 16:32

A lot of (production) data was needed to reproduce the bug (which is further evidence that a plan change might be the cause). I'm not comfortable releasing the data although I was able to delete all but two columns from each table. The issue you linked to requires causing a corruption in the view. I recreated the view so no corruption due to DML can be the cause.; Are you aware of anything that might cause a different plan if the query is being run under DBCC CHECKDB instead of in a normal query window?
–
usrJul 31 '13 at 11:15

An anonymized database has just been uploaded. Here is a script that rebuilds all indexes and recreates the view: pastebin.com/jPEALeEw (useful to reset everything and make sure the physical structure is ok). Other helpful scripts: pastebin.com/KxNSwm2J The scripts should just run and the issue should repro immediately.
–
usrJul 31 '13 at 11:42

On 11.0.3349 with -T272,4199,3604. 4199 enabled query processor fixes. I just removed that TF.; Maybe we need to induce the right query plan. I now have set 1GB RAM and restarted the instance (was 8GB). That changed one of the two merge joins I was seeing to NLJ. Still repros.; To try some plan variations I added and removed rows: pastebin.com/y972Sx4d The bug seems to trigger iff I get a merge join or a hash in the "left anti semi join" part of the query. Try this: add 100k rows to Users. This reliably gives a (parallel) hash join for me.
–
usrJul 31 '13 at 13:05

I just uploaded "plans.zip" to the connect item which contains different executions plans for the DBCC CHECKDB query. With different row counts in Universities I can produce at least three different plans. Only with the loop joins plan the issue does not occur. With merge and hash joins the bug is reproducible.
–
usrJul 31 '13 at 14:01