Be ready to drop your indexed view.

Be ready to drop your indexed view.

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:





  Amount INT NOT NULL);



From one tab in SSMS, run this script:



INSERT INTO dbo.ChildTable(ChildIDParentIDAmount)



From another tab, run a similar one:



INSERT INTO dbo.ChildTable(ChildIDParentIDAmount)





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





  COUNT_BIG(*) AS ChildRowsPerParent, 

  SUM(Amount) AS SumAmount

FROM dbo.ChildTable




  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:



  WideData CHAR(1000) NOT NULL);




SELECT p.ParentID, p.WideData, c.ChildID, c.Amount

FROM dbo.ParentTable AS JOIN dbo.ChildTable AS c

  ON p.ParentID = c.ParentID;



  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)




SET @i=10000;

WHILE @i<20000 BEGIN

  INSERT INTO dbo.ChildTable(ChildID, ParentID, Amount)


  SET @i=@i+1;


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;


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


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;


ALTER PROCEDURE dbo.SelectParentTableWithAmounts


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.