Take the 2-minute tour ×
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.

TL;DR: I've got an unfixable corruption in an indexed view. Here the details:


Running

DBCC CHECKDB([DbName]) WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY, NO_INFOMSGS, ALL_ERRORMSGS

on one of my databases produces the following error:

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

CHECKDB found 0 allocation errors and 1 consistency errors in table 'ViewName'.

repair_rebuild is the minimum repair level (...).

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:

SELECT * FROM ViewName WITH (NOEXPAND)
EXCEPT
SELECT ...
from T1 WITH (FORCESCAN)
join T2 on ...

SELECT ...
from T1 WITH (FORCESCAN)
join T2 on ...
EXCEPT
SELECT * FROM ViewName WITH (NOEXPAND)

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

share|improve this question
1  
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 Views If 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. –  Kin Jul 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. –  usr Jul 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
show 7 more comments

2 Answers 2

up vote 10 down vote accepted

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.

The view definition is:

SELECT
    dbo.Universities.ID AS Universities_ID, 
    dbo.Users.ImageObjectID AS Users_ImageObjectID
FROM dbo.Universities
JOIN dbo.Users
    ON dbo.Universities.AdminUserID = dbo.Users.ID

The ON clause equality comparison between AdminUserID and ID rejects NULLs in those columns, but not from the ImageObjectID column.

Part of the DBCC generated query is:

SELECT [Universities_ID], [Users_ImageObjectID], 0 as 'SOURCE'
FROM [dbo].[mv_Universities_Users_ID] tOuter WITH (NOEXPAND) 
WHERE NOT EXISTS
( 
    SELECT 1 
    FROM   [dbo].[mv_Universities_Users_ID] tInner
    WHERE 
    (
        (
            (
                [tInner].[Universities_ID] = [tOuter].[Universities_ID]
            ) 
            OR 
            (
                [tInner].[Universities_ID] IS NULL
                AND [tOuter].[Universities_ID] IS NULL
            )
        )
        AND
        (
            (
                [tInner].[Users_ImageObjectID] = [tOuter].[Users_ImageObjectID]
            ) 
            OR 
            (
                [tInner].[Users_ImageObjectID] IS NULL 
                AND [tOuter].[Users_ImageObjectID] IS NULL
            )
        )
    )
)
OPTION (EXPAND VIEWS);

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:

Erroneous plan

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.

share|improve this answer
    
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. –  usr Jul 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 White Jul 31 '13 at 16:32
add comment

Further investigation shows that this is a bug in DBCC CHECKDB. A Microsoft Connect bug has been opened: Unfixable DBCC CHECKDB error (that is also a false positive and otherwise strange). Fortunately, I was able to produce a repro so that the bug can be found and fixed.

The bug can be hidden by playing with the database schema. Deleting an unrelated filtered index, or removing the filter, hides the bug. For details please see the connect item.

The connect item also contains the internal query that DBCC CHECKDB uses to validate the view contents. It returns no results, showing that this is a bug.

share|improve this answer
    
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? –  usr Jul 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. –  usr Jul 31 '13 at 11:42
    
Mirror of the .bak: mega.co.nz/… –  usr Jul 31 '13 at 11:48
    
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. –  usr Jul 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. –  usr Jul 31 '13 at 14:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.