SQLServerPedia

In the first part, we looked at the a very simple example with a single table SELECT using covering indexes. This one is a little more complex. We are still covering but we are joining two tables, ordering and grouping. Filtered indexes do not support joins so we have to create two indexes. In this test, the indexed view wins but filtered indexes come a respectable 2nd place compared to normal indexes.

 

 

The DDL(2.5M in tblitem and 25M in tblitemdetails)

CREATE TABLE [dbo].[tblItem](     [Itemid] [int] IDENTITY(1,1) NOT NULL,     [ItemName] [varchar](100) NULL,     [Datecreated] [datetime] NULL,     [Closed] [bit] NULL, PRIMARY KEY CLUSTERED  (     [Itemid] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[tblItemDetails](    [ItemDetailsID] [int] IDENTITY(1,1) NOT NULL,    [ItemID] [int] NULL,    [Color] [varchar](20) NULL,    [Size] [varchar](20) NULL,    [Flavor] [varchar](20) NULL,PRIMARY KEY CLUSTERED (    [ItemDetailsID] ASC) ON [PRIMARY]) ON [PRIMARY] GO SET ANSI_PADDING OFFGO ALTER TABLE [dbo].[tblItemDetails]  WITH CHECK ADD  CONSTRAINT [fk01] FOREIGN KEY([ItemID])REFERENCES [dbo].[tblItem] ([Itemid])GO ALTER TABLE [dbo].[tblItemDetails] CHECK CONSTRAINT [fk01]GO  --Create normal and filtered indexescreate index ix01 on tblitem(Datecreated, ItemName)   create index ix02 on tblitem(Datecreated, ItemName) where Datecreated > '2007-08-22' and itemid > 0 and itemid < 50  create index ix01 on tblitemdetails(itemid, color)   create index ix02 on tblitemdetails(itemid, color) where itemid > 0 and itemid < 50 --Create indexed viewsalter view ivw01 with schemabinding as  select  a.ItemID, b.Color, COUNT_big(*) as cntfrom dbo.tblItem a join dbo.tblItemDetails b on a.Itemid= b.ItemID where Datecreated > convert(datetime, '2007-08-22', 101) and a.itemid > 0 and a.itemid < 50group by a.ItemID, b.Color create unique clustered index ix01 on ivw01(itemid)create index ix02 on ivw01(itemid, color) 

Test Queries

set statistics io on --Using a normal covering index select  a.ItemID, b.Color, COUNT_big(*) as cntfrom dbo.tblItem a with (index=ix01) join dbo.tblItemDetails b with (index=ix01) on a.Itemid= b.ItemID where Datecreated > convert(datetime, '2007-08-22', 101) and a.itemid > 0 and a.itemid < 50group by a.ItemID, b.Colororder by cnt   --using a filtered index select  a.ItemID, b.Color, COUNT_big(*) as cntfrom dbo.tblItem a with (index=ix02) join dbo.tblItemDetails b with (index=ix02) on a.Itemid= b.ItemID where Datecreated > convert(datetime, '2007-08-22', 101) and a.itemid > 0 and a.itemid < 50group by a.ItemID, b.Colororder by cnt  --using an indexed view select * from ivw01order by cnt

IO numbers:

Normal covering indexes

(8 row(s) affected)
Table 'tblItemDetails'. Scan count 8, logical reads 24, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblItem'. Scan count 1, logical reads 991, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Filtered covering indexes

(8 row(s) affected)
Table 'tblItem'. Scan count 1, logical reads 99, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblItemDetails'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Indexed View

(8 row(s) affected)
Table 'ivw01'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Note: By increasing the result set 100 fold(itemid > 0 and itemid < 5000), we see very similar results except with merge joins.

 

Query Plans:

The query plans are different. Most notably being the scans for the filtered index and indexed view vs. the normal index. This is fine though. They as just a fraction of the size of the normal index.

 

Conclusion

The mere fact that the indexed view runs with 50 times less IO overhead does not make it the automatic choice. What if you are on standard edition? Filtered indexes work on standard edition. Also we filtered on a date column. What if you need a real time rolling total? You have to use a normal index. Know you tools and then test, test and test some more.