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:
- Why isn't the optimizer using my covering index? Is it because it thinks it's cheaper to scan the whole table?
- I noticed if i change the query to just fetch
a.ListingIdand 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?
- Why is the query optimizer doing an clustered index scan and not a seek?
- 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?