Some time ago, I was on a project in which we implemented a report model. The data was located in several different databases, none of which were very conducive to reporting. The consequence was that the views upon which we based our entities for the model required lots of joins and performed poorly. We decided to implement indexed views in order to boost the read performance and improve the experience for the users. With indexed views, the data returned by the view is persisted in a clustered index, just like a clustered table, which can have great benefits in read performance.
NOTE: You should research and understand the requirements and cost of using Indexed Views before implementing them in your environment. Indexed views incur a cost in terms of write performance, so you will want to test extensively to make sure the gain on reads is worth that write cost.
You can read details about Creating Indexed Views here.
One thing I noticed is that when I first queried the indexed views, the performance didn’t really change. The reason is that the query optimizer typically defaults to expanding the view definition to access the underlying objects directly. I will demonstrate this in a moment. I did find, however, if my index on the view covered all the columns necessary to satisfy my query, then the optimizer did in fact go ahead and use the index on the view instead of expanding.
You can read details about Resolving Indexed Views here.
Let’s demonstrate this behavior I mentioned above where the indexed view does not cover the query.
CREATE TABLE dbo.Musketeer
(
MusketeerID int NOT NULL
, MusketeerName varchar(20) NOT NULL
)
CREATE TABLE dbo.Lackey
(
LackeyID int NOT NULL
, MusketeerID int NOT NULL
, LackeyName varchar(20) NOT NULL
)
CREATE TABLE dbo.Sword
(
SwordID int NOT NULL
, MusketeerID int NULL
, SwordName varchar(20) NOT NULL
)
INSERT INTO dbo.Musketeer VALUES(1,'Athos')
INSERT INTO dbo.Musketeer VALUES(2,'Porthos')
INSERT INTO dbo.Musketeer VALUES(3,'Aramis')
INSERT INTO dbo.Lackey VALUES(1,1,'Grimaud')
INSERT INTO dbo.Lackey VALUES(2,2,'Mousqueton')
INSERT INTO dbo.Lackey VALUES(3,3,'Bazin')
INSERT INTO dbo.Sword VALUES(1,1,'Cutlass')
INSERT INTO dbo.Sword VALUES(2,2,'Rapier')
INSERT INTO dbo.Sword VALUES(3,3,'Epee')
In the code above, we create our tables and insert some data. I chose The Three Musketeers today. NOTE: I made up the data regarding the Sword preferences.
Let’s create our view.
CREATE VIEW dbo.vMusketeerInfo WITH SCHEMABINDING
AS
SELECT
m.MusketeerID
, m.MusketeerName
, l.LackeyName
, s.SwordName
FROM dbo.Musketeer m
INNER JOIN dbo.Lackey l
ON l.MusketeerID = m.MusketeerID
INNER JOIN dbo.Sword s
ON s.MusketeerID = m.MusketeerID
Let’s query our view, returning the Actual Execution Plan:
SELECT
MusketeerID
, MusketeerName
, LackeyName
, SwordName
FROM dbo.vMusketeerInfo
/* RESULTS
MusketeerID MusketeerName LackeyName SwordName
----------- -------------- ----------- --------------------
1 Athos Grimaud Cutlass
2 Porthos Mousqueton Rapier
3 Aramis Bazin Epee
(3 row(s) affected)
*/
The plan returned is the following:
Notice that the Optimizer is doing a Table Scan on each table referenced.
Now let’s create an index on our view:
CREATE UNIQUE CLUSTERED INDEX ix_vMusketeerInfo_MusketeerID
ON dbo.vMusketeerInfo (MusketeerID)
GO
Great. Now that we have an index on our view, we should be all set, right? Let’s query the view again.
SELECT
MusketeerID
, MusketeerName
, LackeyName
, SwordName
FROM dbo.vMusketeerInfo
/* RESULTS
MusketeerID MusketeerName LackeyName SwordName
----------- -------------- ----------- --------------------
1 Athos Grimaud Cutlass
2 Porthos Mousqueton Rapier
3 Aramis Bazin Epee
(3 row(s) affected)
*/
OK. We get the same results, as one would expect. How about the plan?
The plan is the same. The Optimizer is still performing Table Scans on each table. SQL Server has expanded the view and accessed the underlying objects directly. If you want to make sure that you are going to use the unexpended view, you can add the WITH (NOEXPAND) hint.
SELECT
MusketeerID
, MusketeerName
, LackeyName
, SwordName
FROM dbo.vMusketeerInfo WITH (NOEXPAND)
/* RESULTS
MusketeerID MusketeerName LackeyName SwordName
----------- -------------- ----------- --------------------
1 Athos Grimaud Cutlass
2 Porthos Mousqueton Rapier
3 Aramis Bazin Epee
(3 row(s) affected)
*/
Again, the results are the same. However, we have a new plan.
The WITH (NOEXPAND) hint dictates to SQL Server that the view is not to be expanded to the underlying objects. Thus, in our implementation, we achieved a tremendous gain in READ performance. We did incur a cost on write performance, but in our implementation, the benefits to reads greatly outweighed that write cost.
Mark Vaillancourt 