THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

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:

 

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(ChildIDParentIDAmount)
  
VALUES(1,1,1); 

 

From another tab, run a similar one:

 

BEGIN TRAN;
INSERT INTO dbo.ChildTable(ChildIDParentIDAmount)
  
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(AmountAS 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.ParentIDp.WideDatac.ChildIDc.Amount
FROM dbo.ParentTable AS 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(ParentIDWideData)
  
VALUES(1,'asdf');

SET NOCOUNT ON;
DECLARE @i INT;
SET @i=10000;
WHILE @i<20000 BEGIN
  INSERT INTO 
dbo.ChildTable(ChildIDParentIDAmount)
    
VALUES(@i,1,1);
  
SET @i=@i+1;
END
  

Let us select from this indexed view:

 

SELECT ParentIDWideData,  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 ParentIDWideData,  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 ParentIDWideData,  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 
ParentIDWideData,  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 
ParentIDWideData,  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.

 

Published Tuesday, June 02, 2009 5:10 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

Sounds like a Connect wish possibly?  Have the optimizer ignore (NOEXPAND) table hints instead of returning an error.  I have a growing system which uses indexed views for some real-time reporting purposes, but at the high end what you are talking about certainly rings true, and I will have to start chopping away.

June 2, 2009 7:43 PM
 

Alexander Kuznetsov said:

I found a similar suggestion on Connect:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=293508

Can you include it in your weekly digest?

June 2, 2009 7:55 PM
 

AaronBertrand said:

Cool, one for the digest.

June 2, 2009 8:58 PM
 

AaronBertrand said:

Somehow I missed it this week Alex, but I will make sure to get it in next week.

June 6, 2009 7:43 PM
 

Alexander Kuznetsov said:

That's OK, Aaron. Thank you for publishing this digest, it's very useful!

June 6, 2009 9:33 PM
 

Alexander Kuznetsov said:

In my previous post I described how correlation between columns may confuse the optimizer and cause it

June 9, 2009 10:41 PM
 

Amitesh said:

hi Alexander,

many thanks for this beautiful post, but as i know indexed view updated automatically bacause wheneve base table updated/inserted/deleted a autotrigger fires and updated Indexed view which finally cause the deadlock on base table and degrade the performance,

now if instead of using indexed view i simply use a trigger on every insertion/updation/deletion on base table and update my backupTable, it doesnt create any lock on base table and the another session can insert the value at the same time into baseTable.

now my confusion is if in indexed view scenario the same thing is happening (the firing of auto trigger for updating Indexed view ) then why it block the table.

please advice and let me know if still i am not able to explain my question properly

July 10, 2011 1:14 AM
 

Matt Karp said:

Is there a way to see how long it is taking to update/insert my indexed view when I update/insert one of the base tables upon which it is built?

September 21, 2011 5:16 PM
 

Alexander Kuznetsov said:

Matt,

Have you tried this:

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

GO

-- run your update here

September 21, 2011 5:20 PM
 

sam said:

I found very useful article on Indexed view

http://mssqltipsandtricks.blogspot.com/2012/01/index-on-views.html

January 27, 2012 4:10 AM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement