SQL SERVER – Interesting Observation – Index on Index View Used in Similar Query

Recently, I was working on an optimization project for one of the large organizations. While working on one of the queries, we came across a very interesting observation. We found that there was a query on the base table and when the query was run, it used the index, which did not exist in the base table. On careful examination, we found that the query was using the index that was on another view. This was very interesting as I have personally never experienced a scenario like this. In simple words, “Query on the base table can use the index created on the indexed view of the same base table.”

If you have skipped the first paragraph, I suggest you go over the first paragraph one more time and try to understand the intended meaning. According to me, this is a very interesting observation and we rarely come across such an instance. I have made very small attempt to recreate this particular behavior.

USE tempdb
GO
-- Create Table
CREATE TABLE IndexViewAggr (ID INT)
GO
-- Insert One Hundred Thousand Records
INSERT INTO IndexViewAggr (ID)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Create View on the table
CREATE VIEW ViewAggr WITH SCHEMABINDING
AS
SELECT
ID, COUNT_BIG(*) CountID
FROM dbo.IndexViewAggr
WHERE ID BETWEEN 1 AND 1000
GROUP BY ID
GO
-- Following query is very identical to View
-- Run the query on base table
-- Please note that in execution plan there is Table Scan
SELECT ID, COUNT_BIG(*) CountID
FROM dbo.IndexViewAggr
WHERE ID BETWEEN 1 AND 1000
GROUP BY ID
GO
-- Create Index on the View
-- Please note that this index is on View and not on table
CREATE UNIQUE CLUSTERED INDEX IndexView ON ViewAggr(ID)
GO
-- Run the query on base table again
-- Please note that in execution plan there is Index Scan
SELECT ID, COUNT_BIG(*) CountID
FROM dbo.IndexViewAggr
WHERE ID BETWEEN 1 AND 1000
GROUP BY ID
GO
-- Please note that there are difference in "Query Cost"
-- Clean up Database
DROP VIEW ViewAggr
GO
DROP TABLE IndexViewAggr
GO

Now let us quickly observe two performance comparison of two of the selects. One object is created before the creation of the index over the view, and the other object is created after the creation of the index over the view. Please note that we are talking about two different objects here. Let me explain this schematically.

This is a very interesting behavior as the index created on view affects the query that runs on the base table. Let me show you the execution plan of the query before and after index creation.

From this example, it is very clear that there is a very big difference between the query costs when the index is created on the table. In fact, there is a very simple explanation of this behavior in the white paper Improving Performance with SQL Server 2008 Indexed Views.

The reason for this behavior is that the query optimizer works when it checks for the index on view and finds that the execution plan of the query is similar to that of the view.

Let me know your thoughts about this article. I do understand that this concept is slightly complicated. Further, this type of behavior is rarely encountered in real world. I hope that this article will give you a better insight to such a situation.

Reference : Pinal Dave (http://blog.sqlauthority.com)

About these ads

29 thoughts on “SQL SERVER – Interesting Observation – Index on Index View Used in Similar Query

  1. Yeah good find ! I learnt this concept only 5 days ago.

    Indexed views adds complexity …
    Also they must stay in sync with the underlying tables as data changes.
    * Should be used only when speed of data retrieval outweighs overhead of data modification
    * these are good for highly static data
    * Are bad for high volume transactional data

    Like

  2. This is very helpful article,Thank you mr.Pinal Dave
    I have learned SQL server’s Indexes before but not clearly,after reading your article i understood well

    Like

  3. irrespective of how i run the before and after query, it shows the same results. (i am not running the query for the second time to think that it might be from cache).

    also, after the index on view has been created, the query we run still shows Missing index….

    executed on sql 2008 sp1.

    Like

  4. On editions lower than Enterprise, I believe you can force use of the view’s index with the NOEXPAND hint. But it won’t happen automatically.

    Like

  5. Tried to run the same query, but I’m still getting the same cost. Could you tell me why it is so?

    Working on SQL 2005 SP1

    Regards

    Dharamvir Singh

    Like

  6. It occurs to me that the schemabinding requirement for indexed views might allow the sql engine to consider bound objects in developing the execution plan. Just a thought.

    Like

  7. @Chris Z: that’s correct about the schemabinding, although schemabinding is really required just to allow the view to be indexed in the first place.

    @BartD: the NOEXPAND hint forces SQL Server to work with the view as a view, rather than expand it to its base definition during query normalization. The most common use of this parameter is for when the optimizer wants to service a query against an indexed view by hitting the base data instead of using the indexed view data. In this case, it’s the other way around – we want a query against a base table to use the indexed view.

    In general, remember that there isn’t any huge difference physically between indexes and (clustered) tables. If the optimizer understands the data connection between one or more indexes / tables, it will use them as it deems best. In Enterprise or Developer Edition, this can be done with indexed views, which are really just another automated form of indexing data from one or more tables.

    Like

  8. interesting as far as the inner workings of SQL Server, but nobody seems to be asking the obvious question – would you ever purposely design it this way? Isn’t it much cleaner and logical to create an index on the base table? One scenario I can think of is when you have a database created by a vendor that you do not want to contaminate with your own code, yet you need to improve its performance. Even then the indexed view needs to be in the same database for this to work, which is still not ideal.

    Like

  9. Hi Mr. Pianldave,

    I always read your post. This is a good post.

    I am navice in sql server.

    Would you please tell me is Index view stored in the database.

    Like

  10. Hi Pinal,

    I have two databases on same server(Development edition) ,One is little bit old in terms of data and another is just a replica taken few days before of production(Enterprise editon) server.

    But when i excute the same query on both databases in development edition, both gives me two different execution plan.

    One is using indexes created on view and another using indexes of table that has been reference by veiw.

    Schema and indexes are same on both databases in developer edition server.

    Can you let me know how can make use of index created on veiw other than table hint (noexpand) as same query when excuted in diffent database not use any table hint.

    Like

  11. Pingback: SQL SERVER – Indexed Views and Restrictions – Quiz – Puzzle – 7 of 31 « SQL Server Journey with SQL Authority

  12. Hi Pinal,

    I believe this will only happen if you run the query on an Enterprise (or Developer) Edition.

    However, for other editions .. using the NOEXPAND hint will ask the query optimizer to use the indexed view even if it is not a good idea.

    Thanks for the nice post.

    ~ Hossam

    Like

  13. Hi,
    It is great to know about this.

    I have a questions here, if the base table already has a clustered index, then if we add a clustered index on the view how does it work.

    Like

  14. Pingback: SQL SERVER – Weekly Series – Memory Lane – #048 | Journey to SQL Authority with Pinal Dave

  15. Thanks for article,, Really a nice one.

    Today I got this behaviour in one of my project while performing some optimization techniques.
    I wanted to check indexes used in base query (Has many tables with joins) but whenever i tried to look at this, every time it have shown me index scan for view only.
    I made few changes in select statements thinking that changes in base query (used in creating view) may show me the actual execution plan without any index or indexed view but no luck.

    Is there any way to look at the query execution plan or is it like in this case, the indexes on base query doesn’t matter anyhow but index on view does?

    Thanks in advanced!!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s