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