Index of Materialized View Not Working

Introduction

I got a mail from one of my closed friends related to "Materialized view Index not working". I am little bit confused after getting this mail. When I am getting closer look at the problem that I understand what he mean by "Materialized view Index not working" This article is related to Why in Materialized view Index is not working.

Point to focus

1.    Understanding the case scenario

2.    Finding the rood of the problem

3.    How we fix it.

4.    Related Tropics

In this article I am not going to describe related to Materialize View as we all know that how important it is in case of performance boost up. If have you any confusion visit my previous article. You can find it in related tropic options of this article.

Understanding the case scenario

Here in this article, I cannot provide the exact scenario due to some restrictions but I can create a scenario related to it. So let's see what the scenario is.

Step-1 [ Creating the Base Table ]

IF OBJECT_ID('my_BaseTable') IS NOT NULL

   BEGIN

        DROP TABLE my_BaseTable

   END

GO

CREATE TABLE my_BaseTable

       (ID      INT         NOT NULL IDENTITY(1,1) PRIMARY KEY,

        EMPNAME VARCHAR(50) NOT NULL,

        GRADE   VARCHAR(1)  NOT NULL)

GO

Step-2 [ Now Insert Some records on our Base Table ]

INSERT INTO my_BaseTable

       (EMPNAME, GRADE)  

VALUES ('Mr. Sudip Das', 'A'),

       ('Mr. Joydeep Das', 'B'),

       ('Mr. Sukamal Jana', 'B'),           

       ('Mr. Tuhin Kr. Shinah', 'C'),

       ('Mr. Sangram Jit', 'C'),

       ('Mr. Sourav Sarkar', 'D');

GO    

Step-3 [ Display the Index Information ]

sp_helpindex my_BaseTable

index_name	index_description	index_keys
PK__my_BaseT__3214EC2766979D65	clustered, unique, primary key located on PRIMARY	ID

Here the UNIQUE CLUSTERED index is created.

Step-4 [ Optional -- Display the Records of the Base Table ]

ID    EMPNAME                 GRADE

1     Mr. Sudip Das           A

2     Mr. Joydeep Das         B

3     Mr. Sukamal Jana        B

4     Mr. Tuhin Kr. Shinah    C

5     Mr. Sangram Jit         C

6     Mr. Sourav Sarkar       D

Step-5 [ Create the Materialize View ]

IF OBJECT_ID('View_my_BaseTable') IS NOT NULL

   BEGIN

        DROP VIEW View_my_BaseTable

   END

  

GO

CREATE VIEW View_my_BaseTable

WITH SCHEMABINDING

AS

SELECT ID, EMPNAME, GRADE

FROM   dbo.my_BaseTable;

Step-6 [ Create the Clustered index on the View ]

CREATE UNIQUE CLUSTERED INDEX IX_View_my_BaseTable

ON View_my_BaseTable (ID);

Step-7 [ Display the Index Information ]

sp_helpindex View_my_BaseTable

index_name	index_description	index_keys
IX_View_my_BaseTable	clustered, unique located on PRIMARY	ID

Here the UNIQUE CLUSTERED index is created.

Step-7 [ Display the Actual Execution Plan ]

In this execution plan if we see it carefully we find that the Index named "PK__my_BaseT__3214EC2766979D65" is used which is the Base Table Index (The Index of the Primary Key). The Index that is created on the View is not used.

"And this is the Problem. Why the Index of the View is not used by the MS SQL Query optimizer? "

Finding the rood of the problem

The cost of using the indexed view may exceed the cost of getting the data from the base tables, or the query is so simple that a query against the base tables is fast and easy to find. This often happens when the indexed view is defined on small tables.

How we fix it

We can use the NOEXPAND hint if we want to force the query processor to use the indexed view.

SELECT * FROM View_my_BaseTable(NOEXPAND)

Related Tropics

1.    Materialize view and the performance issue

http://sqlknowledgebank.blogspot.in/2012/03/materialize-view-and-performance-issue.html

Hope you like it.

Posted by: MR. JOYDEEP DAS