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.

Solarwinds

SQL SERVER - Interesting Observation - Index on Index View Used in Similar Query queryindex

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.

SQL SERVER - Interesting Observation - Index on Index View Used in Similar Query indexview1

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 (https://blog.sqlauthority.com)

Solarwinds

, ,
Previous Post
SQL SERVER – Insert Values of Stored Procedure in Table – Use Table Valued Function
Next Post
SQL SERVER – Outer Join in Indexed View – Question to Readers

Related Posts

27 Comments. Leave new

  • Hi Pinal,

    Do you have any suggestion for creating full text catalog on data in 2 different languages.

    Reply
  • 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.

    Reply
  • Hossam Alfraih (@SaudiGeekNET)
    February 5, 2012 1:33 pm

    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

    Reply
  • i’m sorry to say this, but this is little misleading..the query cost (relative to batch) will always add to 100%. it depends on the number of queries we have per batch..please refer his link for more clear answers.

    Reply
  • Hi Pinal ,

    Here main table is using index from view its fine.

    but there is no change in query cost.

    Reply
  • 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.

    Reply
  • 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!!

    Reply

Leave a Reply

Menu