Search results matching tag 'Indexed View'

Incorrect Results with Indexed Views

Paul White — Tue, 05 Feb 2013 22:18:11 GMT

Summary: If you use MERGE, indexed views and foreign keys, your queries can return incorrect results.

Microsoft have released a fix for incorrect results returned when querying an indexed view. The problem applies to:

SQL Server 2012
SQL Server 2008 R2
SQL Server 2008

The Knowledge Base article does not go into much detail, or provide a reproduction script, but this blog entry does :)

The KB does say that reproducing the bug requires these features:

An indexed view on two tables that have a foreign key relationship
An update performed against the base tables
A query executed against the indexed view using a NOEXPAND hint

There are two important details I would like to add right away:

The NOEXPAND hint is not required to reproduce the bug on Enterprise Edition
The update must be performed by a MERGE statement

The fix is available in the following cumulative update packages:

Cumulative Update 2 for SQL Server 2012 SP1 [build 11.0.3339]
Cumulative Update 5 for SQL Server 2012 RTM [build 11.0.2395]
Cumulative Update 4 for SQL Server 2008 R2 SP2 [build 10.50.4270]
Cumulative Update 10 for SQL Server 2008 R2 SP1 [build 10.50.2868]
Cumulative Update 8 for SQL Server 2008 SP3 [build 10.00.5828]

No service pack contains this fix, you must apply one of the hotfix packages above.

Steps to Reproduce

The first thing we will need is two tables:

     CREATE TABLE dbo.Parent 

    (

        parent_id integer IDENTITY NOT NULL,

        value varchar(20) NOT NULL,

        CONSTRAINT PK_Parent_id 

            PRIMARY KEY CLUSTERED (parent_id)

    );

    GO

    CREATE TABLE dbo.Child

    (

        child_id integer IDENTITY NOT NULL,

        parent_id integer NOT NULL,

        CONSTRAINT PK_Child_id 

            PRIMARY KEY CLUSTERED (child_id)

    );

And a few rows of data:

    INSERT dbo.Child (parent_id)

    SELECT New.parent_id 

    FROM 

        (

        INSERT Parent 

        OUTPUT inserted.parent_id 

        VALUES 

            ('Apple'), 

            ('Banana'), 

            ('Cherry')

        ) AS New;

The tables now look like this (parent first):

We can now add the required FOREIGN KEY relationship:

    ALTER TABLE dbo.Child

    ADD CONSTRAINT FK_Child_Parent

    FOREIGN KEY (parent_id)

    REFERENCES dbo.Parent (parent_id);

Next, a very simple indexed view that joins the two tables (the view could contain more complex features like aggregates):

    CREATE VIEW dbo.ParentsAndChildren

    WITH SCHEMABINDING 

    AS

    SELECT 

        p.parent_id, 

        p.value, 

        c.child_id

    FROM dbo.Parent AS p

    JOIN dbo.Child AS c ON 

        c.parent_id = p.parent_id;

    GO

    CREATE UNIQUE CLUSTERED INDEX cuq 

    ON dbo.ParentsAndChildren (child_id);

The final step is to use a MERGE statement to make some changes to the Parent table:

    DECLARE @ParentMerge AS TABLE

    (

        parent_id integer PRIMARY KEY, 

        value varchar(20) NOT NULL

    );

    INSERT @ParentMerge

        (parent_id, value)

    VALUES

        (1, 'Kiwi Fruit'),

        (4, 'Dragon Fruit');

    MERGE dbo.Parent AS p

    USING @ParentMerge AS s ON 

        s.parent_id = p.parent_id

    WHEN MATCHED THEN 

        UPDATE SET value = s.value

    WHEN NOT MATCHED THEN 

        INSERT (value) VALUES (s.value)

    OUTPUT 

        $action, 

        inserted.parent_id, 

        deleted.value AS old_value, 

        inserted.value AS new_value;

This MERGE performs two actions:

Updates the value column of parent row 1 from Apple to Kiwi Fruit
Adds a new parent row 4 for Dragon Fruit

The statement includes an OUTPUT clause to show the changes it makes (this is not required for the repro):

This confirms that the changes have been made as we requested: parent row 1 has changed; and row 4 has been added. The changes are reflected in the base tables:

    SELECT * FROM dbo.Parent AS p;

    SELECT * FROM dbo.Child AS c;

As highlighted, row 1 has changed from Apple to Kiwi Fruit and row 4 has been added.

We do not expect to see row 4 in the indexed view because there are no child records for that row, and the indexed view uses an inner join. Checking the indexed view using the NOEXPAND table hint (required in non-Enterprise SKUs to use indexes on a view):

    SELECT *

    FROM dbo.ParentsAndChildren 

    WITH (NOEXPAND);

The results are incorrect. They show the old value of the data for parent row 1.

Now we try using the EXPAND VIEWS query hint to force SQL Server to access the base tables rather than reading view indexes:

    SELECT *

    FROM dbo.ParentsAndChildren

    OPTION (EXPAND VIEWS);

This query produces correct results.

On SQL Server Enterprise Edition, the optimizer chooses whether to access the indexed view or the base tables. For following query, without any hints, the optimizer chooses not to expand the view. It reads the view index and produces incorrect results:

    -- Enterprise Edition ONLY

    SELECT * 

    FROM dbo.ParentsAndChildren;

Perhaps adding a child row to match the new parent row 4 will somehow fix things up?

    INSERT dbo.Child (parent_id) VALUES (4);

    GO

    SELECT * FROM dbo.ParentsAndChildren WITH (NOEXPAND);

    SELECT * FROM dbo.ParentsAndChildren OPTION (EXPAND VIEWS);

No. The query plan that accesses the view index still returns an incorrect value for row 1. It seems MERGE has corrupted our indexed view.

Analysis using DBCC CHECKTABLE

Checking the view with DBCC CHECKTABLE returns no errors:

    DBCC CHECKTABLE (ParentsAndChildren);

Unless we use the EXTENDED_LOGICAL_CHECKS option:

    DBCC CHECKTABLE (ParentsAndChildren) WITH EXTENDED_LOGICAL_CHECKS;

The damage is repairable:

    ALTER DATABASE Sandpit 

    SET SINGLE_USER 

    WITH ROLLBACK IMMEDIATE;

    GO

    DBCC CHECKTABLE (ParentsAndChildren, REPAIR_REBUILD) WITH EXTENDED_LOGICAL_CHECKS;

    GO

    DBCC CHECKTABLE (ParentsAndChildren) WITH EXTENDED_LOGICAL_CHECKS;

    GO

    ALTER DATABASE Sandpit SET MULTI_USER;

You probably do not want to set your database to SINGLE_USER mode and run a DBCC repair after every MERGE statement, however. We could also rebuild the indexed view’s clustered index manually, of course.

Cause

For the MERGE statement above, the query optimizer builds a plan that does not update the indexed view (click to enlarge):

In a version of SQL Server with the fix applied, the same MERGE statement produces a plan that does maintain the indexed view:

The plan operators used to keep the view index in step with the base tables are highlighted. Without these operators, the changes to the base table are not correctly written to any indexes defined on the view. The root cause is related to the same simplification that allows the optimizer to remove the reference to the Parent table in this query:

    SELECT 

        COUNT_BIG(*)

    FROM dbo.Parent AS p 

    JOIN dbo.Child AS c ON 

        c.parent_id = p.parent_id;

The FOREIGN KEY relationship and NOT NULL constraints on the referencing column together mean that the join to Parent cannot affect the result of the query, so the join is simplified away. In SQL Server 2012, we can see when this simplification is performed because the following message appears when undocumented trace flags 8619 and 3604 are enabled during compilation:

The same message is emitted when a MERGE statement contains a WHEN MATCHED THEN UPDATE clause and either a WHEN NOT MATCHED THEN INSERT or WHEN MATCHED … THEN DELETE clause. These conditions combine such that the optimizer incorrectly concludes that a table reference can be removed, when in fact it is needed later on when the update side of the plan is built.

Other details of the query and database can affect whether the simplification can be misapplied. For example, if the FOREIGN KEY constraint contains an ON DELETE CASCADE clause, and the MERGE contains a DELETE clause, the simplification is not performed, the TF 8619 message does not appear, and the bug does not manifest.

The key to determining whether a particular query is vulnerable to this bug (TF 8619 aside) is to check whether the query plan includes operators to maintain the indexed view. At a minimum, you should see a update operator for the view:

SQL Sentry Plan Explorer identifies the operator as applying to a view explicitly, in SSMS you need to click on the graphical operator and inspect the Properties window.

Summary

The updated conditions for incorrect results are:

An indexed view that joins tables
Two of the tables have a single-column FOREIGN KEY constraint
A MERGE statement contains an UPDATE action that affects one of the tables
The MERGE statement also contains an INSERT or DELETE action (or both)
The optimizer applies a simplification that removes a table reference based on the FK relationship and other metadata
As a result, the MERGE execution plan does not contain the operators necessary to correctly maintain the indexed view
A subsequent query plan accesses an index on the view, either explicitly or via indexed-view matching (Enterprise Edition)

Note:

The simplification is not applied in tempdb
The simplification is not applied to multi-column foreign key constraints

Under these conditions, the indexes on the view do not reflect the state of the base tables and incorrect results are returned. Once the hot fix is applied, the optimizer does not misapply the simplification so the correct indexed view maintenance features are built into execution plans.

Update: I am adding this based on Ian Yates’ great question in the comments: my expectation is that applying this hotfix will not remove any existing indexed view corruption. You would need to test the hotfix as usual, apply it, and then either rebuild all affected indexed views manually, or run DBCC CHECKDB with the EXTENDED_LOGICAL_CHECKS option (which could take a long time).

Twitter: @SQL_Kiwi
Email: SQLKiwi@gmail.com

Be ready to drop your indexed view.

Alexander Kuznetsov — Tue, 02 Jun 2009 21:10:00 GMT

In all-too-many cases an indexed view may solve your short term performance goals but at some later time become counterproductive. So if you choose to use an indexed view, you may need an exit strategy. Let me describe a few common problems with indexed views.

Indexed views may increase lock contention.

It is very easy to demonstrate. Create the following table:

CREATE TABLE dbo.ChildTable(ChildID INT NOT NULL CONSTRAINT PK_ChildTable PRIMARY KEY, ParentID INT NOT NULL, Amount INT NOT NULL); GO

From one tab in SSMS, run this script:

BEGIN TRAN; INSERT INTO dbo.ChildTable(ChildID, ParentID, Amount) VALUES(1,1,1);

From another tab, run a similar one:

BEGIN TRAN; INSERT INTO dbo.ChildTable(ChildID, ParentID, Amount) VALUES(2,1,1); ROLLBACK;

Note that both inserts complete, they do not block each other. Rollback in both tabs, and create an indexed view:

CREATE VIEW dbo.ChildTableTotals WITH SCHEMABINDING AS SELECT ParentID, COUNT_BIG(*) AS ChildRowsPerParent, SUM(Amount) AS SumAmount FROM dbo.ChildTable GROUP BY ParentID; GO CREATE UNIQUE CLUSTERED INDEX ChildTableTotals_CI ON dbo.ChildTableTotals(ParentID);

Rerun the two inserts. Note that the second one does not complete; it is blocked. The reason is very simple: the first insert modifies the corresponding entry in the indexed view, so the insert acquires and holds a lock on it.

It is just as easy to demonstrate that when you create an indexed view, deadlocks may become more likely too.

Note: this is not a problem with the way indexed views are implemented. If you roll out your own summary table, and develop triggers which directly modify it to keep it up-to-date, you will encounter the same problem. Only if you don't maintain your summary table all the time, you can get around this locking problem, but a more detailed discussion of this is beyond the scope of this post.

Also make sure you have read an excellent post by Tony Rogerson:

Indexed views on joins may become counterproductive.

Create the following table and another indexed view:

CREATE TABLE dbo.ParentTable(ParentID INT NOT NULL CONSTRAINT PK_ParentTable PRIMARY KEY, WideData CHAR(1000) NOT NULL); GO CREATE VIEW dbo.ParentTableWithAmounts WITH SCHEMABINDING AS SELECT p.ParentID, p.WideData, c.ChildID, c.Amount FROM dbo.ParentTable AS p JOIN dbo.ChildTable AS c ON p.ParentID = c.ParentID; GO CREATE UNIQUE CLUSTERED INDEX ParentTableWithAmounts_CI ON dbo.ParentTableWithAmounts(ChildID);

Suppose that originally you have an average one child row per parent on, and that selecting from this indexed view is faster than joining two tables - that's why you created it in the first place. However, if on average you have 10K child rows per parent one, your indexed view becomes counterproductive. Let's add 10K child rows:

INSERT INTO dbo.ParentTable(ParentID, WideData) VALUES(1,'asdf'); SET NOCOUNT ON; DECLARE @i INT; SET @i=10000; WHILE @i<20000 BEGIN INSERT INTO dbo.ChildTable(ChildID, ParentID, Amount) VALUES(@i,1,1); SET @i=@i+1; END

Let us select from this indexed view:

SELECT ParentID, WideData, ChildID, Amount FROM dbo.ParentTableWithAmounts;

The optimizer has chosen not to use the indexed view, and the execution costs are as follows:

Table 'ChildTable'. Scan count 1, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ParentTable'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

If you force the optimizer to use the indexed view, the execution costs are dramatically higher:

SELECT ParentID, WideData, ChildID, Amount FROM dbo.ParentTableWithAmounts WITH(NOEXPAND);

Table 'ParentTableWithAmounts'. Scan count 1, logical reads 1435, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

As you have seen, when the parent table is wide and there are many child rows per parent, the indexed view becomes counterproductive, and the optimizer is able to recognize the fact.

Also note that this indexed view increases lock contention just as the previous one did. You can try to update a parent row and one of its child rows and see for yourself.

Exit strategy for NOEXPAND hint

If your indexed view becomes counterproductive, you may consider dropping it altogether. However, dropping the index view will break all those queries with NOEXPAND hint:

DROP INDEX dbo.ParentTableWithAmounts.ParentTableWithAmounts_CI; GO SELECT ParentID, WideData, ChildID, Amount FROM dbo.ParentTableWithAmounts WITH(NOEXPAND);

Msg 8171, Level 16, State 2, Line 1
Hint 'noexpand' on object 'dbo.ParentTableWithAmounts' is invalid.

I would recommend to be prepared for such possibility, to wrap all your selects using NOEXPAND in stored procedures, for example:

CREATE PROCEDURE dbo.SelectParentTableWithAmounts AS SELECT ParentID, WideData, ChildID, Amount FROM dbo.ParentTableWithAmounts WITH(NOEXPAND);

and to provide a rollback script which alters these procedures, as follows:

DROP INDEX dbo.ParentTableWithAmounts.ParentTableWithAmounts_CI; GO

ALTER PROCEDURE dbo.SelectParentTableWithAmounts AS SELECT ParentID, WideData, ChildID, Amount FROM dbo.ParentTableWithAmounts;

You can add error handling to this script, so that either both changes deploy or none does. Make sure to test this script. If you have unit tests, include this scenario in your test harness. Of course there are other approaches, but my main point is that you need to be aware that your indexed view can be dropped, and have a working exit strategy.

Performance considerations for your exit strategy

We have just discussed how to make sure that your application does not break, but what about the performance? Clearly the performance of your selects may plunge, what can be done about it? In many cases, index covering gives you acceptable performance without too much lock contention. Also sometimes you want to roll out your own summary tables, but that sounds like a topic for another post.