Index of Materialized View Not Working

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

Hope you like it.
Posted by: MR. JOYDEEP DAS

Related Posts