Why is an index I created not being used in a query execution?

Question

Background: this question stemmed from another question I asked, so you can get additional DDL/indexes from there, if need be.

I have a query inside an indexed view, like this:

SELECT      [a].[ListingId], 
            -- lots of other columns from [a], [b], [c], [d] etc
FROM        [dbo].[Listings][a]
INNER JOIN  [dbo].[ListingFeatures][b] ON [a].[ListingId] = [b].[ListingId] 
INNER JOIN [dbo].[ListingBuildingDetails][c] ON [a].[ListingId] = [c].[ListingId]
INNER JOIN [dbo].[ListingLandDetails][d] ON [a].[ListingId] = [d].[ListingId]
WHERE       [a].[IsVisible] = 1
AND         [a].[IsLive] = 1
AND         [a].[AgencyCompanyId] IS NOT NULL

Note the 3 predicates.

I created a covering index for the predicates, and included the ListingId column, hoping the query optimizer would use the index then do a KEY LOOKUP to fetch the other columns from that table, then do a clustered index seek to get the other table data.

Here's the index definition:

CREATE NONCLUSTERED INDEX [IX_Listings_ForListingSearchView] ON [dbo].[Listings]
(
    [IsVisible] ASC,
    [IsLive] ASC,
    [AgencyCompanyId] ASC
)
INCLUDE (   [ListingId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Quite the contrary, here's the estimated execution plan:

My questions:

1. Why isn't the optimizer using my covering index? Is it because it thinks it's cheaper to scan the whole table?
2. I noticed if i change the query to just fetch a.ListingId and not the rest of the columns from a, it uses my index (and the reads/cost seems much lower, due to avoiding the index scan). Should I use this in a subquery then?
3. Why is the query optimizer doing an clustered index scan and not a seek?
4. This query is the one existing in an index view. When the index view needs to be updated, is this the actual query that runs to update itself? My thinking was yes, which is why I'm trying to optimize it. If the index view update doesn't use this query, then I won't bother?

Answer 1

Thanks everyone for your help. I think i've/we've answered the questions.

Why isn't the optimizer using my covering index? Is it because it thinks it's cheaper to scan the whole table?

Because the optmizer has to read most rows in table, it's preferring the clustered index.

I noticed if i change the query to just fetch a.ListingId and not the rest of the columns from a, it uses my index (and the reads/cost seems much lower, due to avoiding the index scan). Should I use this in a subquery then?

No. Optimizer is doing the right thing. Forcing an index hint proves that, when looking at cost difference.

Why is the query optimizer doing an clustered index scan and not a seek?

See first answer

This query is the one existing in an index view. When the index view needs to be updated, is this the actual query that runs to update itself? My thinking was yes, which is why I'm trying to optimize it. If the index view update doesn't use this query, then I won't bother?

As requested, I split this out into a different question