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:
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.
Paul's explanation might answer your question , you can take a look here
http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-why-would-checkdb-run-out-of-space/. I have copy pasted a snippet from his post related to your question:
There’s another reason that DBCC CHECKDB may stop with an out-of-space error. DBCC CHECKDB has to validate the contents of the whole database. As such it needs to store info about things it has seen at one point during database scanning so that it can match that info against things it sees at later points during database scanning. These bits of info are stored in an in-memory worktable. In many cases though, the amount of info being stored exceeds the available memory and the worktable needs to spill over to tempdb. (For example, running DBCC CHECKDB against a 1TB database on a machine with 16GB of memory – the amount of info that DBCC CHECKDB needs to store the intermediate pieces of info will likely exceed the memory available to SQL Server). So – if tempdb isn’t big enough to store the worktable, it has to grow. If it can’t grow, then DBCC CHECKDB will fail because it needs the worktable to operate.
Best Answer
You can run
DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS
directly on the indexed views. Checking indexed views can be problematic in certain circumstances, so be prepared to investigate any false positives that result. (Paul Randal also mentions in the comments to the referenced article that false negatives are also possible, but I have no direct experience of that.)There's no support for running the Service Broker or
FILESTREAM
checks separately, no.Not that I am aware of.
You might also consider running
DBCC CHECKCONSTRAINTS
. This check is not included inDBCC CHECKDB
, regardless of any options you may specify. You may also want to think about occasionally runningCHECKDB
, as and when circumstances permit.