What factors go into they query optimizer's selection of an indexed view's index?
For me, indexed views seem to defy what I understand about how the Optimizer picks indexes. I've seen this asked before, but the OP wasn't too well received. I'm really looking for guideposts, but I'll concoct a pseudo example, then post real example with with a lot of DDL, output, examples.
Assume I'm using Enterprise 2008+, understand
Take this pseudo example: I create a view with 22 joins, 17 filters, and a circus pony that crosses a bunch of 10 million row tables. This view is Expensive (yep, with a capital E) to materialize. I'll SCHEMABIND and Index the view. Then a
SELECT a,b FROM AnIndexedView WHERE theClusterKeyField < 84. In Optimizer logic that eludes me the underlying joins are performed.
- No Hint: 4825 reads for 720 rows, 47 cpu over 76ms, and an estimated sub tree cost of 0.30523.
- With Hint: 17 reads, 720 rows, 15 cpu over 4ms, and an estimated subtree cost of 0.007253
So what's going on here? I've tried it in Enterprise 2008, 2008-R2 and 2012. By every metric I can think of using the view's index is vastly more efficient. I don't have parameter sniffing issue or skewed data, since this is ad hock.
A Real (Long) Example
Unless you are a touch masochistic you probably don't need or want to read this part.
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)
CREATE VIEW dbo.TimelineMaterialized WITH SCHEMABINDING AS SELECT TM.TimelineID, TM.TimelineTypeID, TM.EmployeeID, TM.CreateUTC, CUL.CultureCode, CASE WHEN TM.CustomerMessageID > 0 THEN TM.CustomerMessageID WHEN TM.CustomerSessionID > 0 THEN TM.CustomerSessionID WHEN TM.NewItemTagID > 0 THEN TM.NewItemTagID WHEN TM.OutfitID > 0 THEN TM.OutfitID WHEN TM.ProductTransactionID > 0 THEN TM.ProductTransactionID ELSE 0 END As HrefId, CASE WHEN TM.CustomerMessageID > 0 THEN IsNull(C.Name, 'N/A') WHEN TM.CustomerSessionID > 0 THEN IsNull(C.Name, 'N/A') WHEN TM.NewItemTagID > 0 THEN IsNull(NI.Title, 'N/A') WHEN TM.OutfitID > 0 THEN IsNull(O.Name, 'N/A') WHEN TM.ProductTransactionID > 0 THEN IsNull(PT_PL.NameLocalized, 'N/A') END as HrefText FROM dbo.Timeline TM INNER JOIN dbo.CustomerSession CS ON TM.CustomerSessionID = CS.CustomerSessionID INNER JOIN dbo.CustomerMessage CM ON TM.CustomerMessageID = CM.CustomerMessageID INNER JOIN dbo.Outfit O ON PO.OutfitID = O.OutfitID INNER JOIN dbo.ProductTransaction PT ON TM.ProductTransactionID = PT.ProductTransactionID INNER JOIN dbo.Product PT_P ON PT.ProductID = PT_P.ProductID INNER JOIN dbo.ProductLang PT_PL ON PT_P.ProductID = PT_PL.ProductID INNER JOIN dbo.Culture CUL ON PT_PL.CultureID = CUL.CultureID INNER JOIN dbo.NewsItemTag NIT ON TM.NewsItemTagID = NIT.NewsItemTagID INNER JOIN dbo.NewsItem NI ON NIT.NewsItemID = NI.NewsItemID INNER JOIN dbo.Customer C ON C.CustomerID = CASE WHEN TM.TimelineTypeID = 1 THEN CM.CustomerID WHEN TM.TimelineTypeID = 5 THEN CS.CustomerID ELSE 0 END WHERE CUL.IsActive = 1
CREATE UNIQUE CLUSTERED INDEX PK_TimelineMaterialized ON TimelineMaterialized (EmployeeID, CreateUTC, CultureCode, TimelineID)
-- NO HINT - - - - - - - - - - - - - - - SELECT * --yes yes, star is bad ...just a test example FROM TimelineMaterialized TM WHERE TM.EmployeeID = 2 AND TM.CultureCode = 'en-US' AND TM.CreateUTC > '9/10/2012' AND TM.CreateUTC < '9/11/2012' -- WITH HINT - - - - - - - - - - - - - - - SELECT * FROM TimelineMaterialized TM with(noexpand) WHERE TM.EmployeeID = 2 AND TM.CultureCode = 'en-US' AND TM.CreateUTC > '9/10/2012' AND TM.CreateUTC < '9/11/2012'
Result = 11 Rows of Output
The top 4 lines are without a hint. The bottom 4 lines are using the hint.
GitHub Gist for both Execution Plans in SQLPlan format
No Hint Execution plan -- why not use the clustered index I gave you Mr. SQL? It's clusterd on the 3 filter fields. Try it, you might like it.
Simple plan when using a hint.