Home Dashboard Directory Help
Search

Speed up CHECKDB EXTENDED_LOGICAL_CHECKS by 2x by xor88


Status: 

Active


1
0
Sign in
to vote
Type: Suggestion
ID: 795698
Opened: 8/1/2013 9:01:16 AM
Access Restriction: Public
0
Workaround(s)
view

Description

As of SQL12, CHECKDB WITH EXTENDED_LOGICAL_CHECKS performs a query of the following form to ascertain that the indexed view to be checked contains accurate data:


SELECT [Date], [IP], [TotalRequestCount], 0 as 'SOURCE'
FROM [dbo].[ViewName] tOuter WITH (NOEXPAND)
WHERE NOT EXISTS
(
    SELECT 1
    FROM [dbo].[ViewName] tInner
    WHERE
    (
        (([tInner].[Date] = [tOuter].[Date]) OR ([tInner].[Date] IS NULL AND [tOuter].[Date] IS NULL))
            AND (([tInner].[IP] = [tOuter].[IP]) OR ([tInner].[IP] IS NULL AND [tOuter].[IP] IS NULL))
            AND (([tInner].[TotalRequestCount] = [tOuter].[TotalRequestCount]) OR ([tInner].[TotalRequestCount] IS NULL AND [tOuter].[TotalRequestCount] IS NULL))
    )
)
UNION ALL
SELECT[Date], [IP], [TotalRequestCount], 1 as 'SOURCE'
FROM [dbo].[ViewName] tOuter
WHERE NOT EXISTS
(
    SELECT 1
    FROM [dbo].[ViewName] tInner WITH (NOEXPAND)
    WHERE
    (
        (([tInner].[Date] = [tOuter].[Date]) OR ([tInner].[Date] IS NULL AND [tOuter].[Date] IS NULL))
            AND (([tInner].[IP] = [tOuter].[IP]) OR ([tInner].[IP] IS NULL AND [tOuter].[IP] IS NULL))
            AND (([tInner].[TotalRequestCount] = [tOuter].[TotalRequestCount]) OR ([tInner].[TotalRequestCount] IS NULL AND [tOuter].[TotalRequestCount] IS NULL))
     )
)
OPTION (EXPAND VIEWS)


The way this query is executed by the query processor forces all base tables and the indexed view to be scanned twice, once for each "branch" of the query.

By rewriting the query to a FULL OUTER JOIN between the expanded view and the indexed view only one scan each will be necessary. This amounts to a speedup of close to 2x (or 50% of the execution time) in practice.

The full outer join can be processed using hashing or merging. Also, the QP can decide to use two nested loop joins if it wants to.
Details
Sign in to post a comment.
Sign in to post a workaround.