I'm struggling to persuade a query plan to behave as I think it should. The addition of a TOP clause when querying an indexed view is causing a sub-optimal plan, and I'm hoping for some help in sorting it.
- SQL Server 2019
- StackOverflow2013 database (50GB version), Compat Mode 150 (problem is not specific to this version)
Firstly, I've created a view to return everyone with a high reputation:
CREATE VIEW vwHighReputation WITH SCHEMABINDING AS SELECT [Id], [DisplayName], [Reputation] FROM [dbo].[Users] WHERE [Reputation] > 10000
Next, since I'll be searching by display name, I've created a couple of indexes on the view:
CREATE UNIQUE CLUSTERED INDEX IX_Users_Id ON [dbo].[vwHighReputation]([Id]) GO CREATE NONCLUSTERED INDEX IX_Users_DisplayName ON [dbo].[vwHighReputation]([DisplayName]) INCLUDE (Reputation) GO
If I query via the view, I can see my nonclustered index is being used:
SELECT * FROM [dbo].[vwHighReputation] WHERE [DisplayName] LIKE 'J%'
So far so good. I can even use my view as part of a more complex query with an OUTER APPLY, and I still get a seek with only 63 reads against my index (this is obviously a contrived example, but helps illustrate the problem that I'll come to):
SELECT [U].[Id], [A].[Reputation], [A].[DisplayName] FROM [dbo].[Users] AS [U] OUTER APPLY ( SELECT * FROM [dbo].[vwHighReputation] AS [v] WHERE [v].[Id] = [U].[Id] ) AS [A] WHERE [A].[DisplayName] LIKE 'J%';
However, if I add a TOP 1 to my OUTER APPLY:
SELECT [U].[Id], [A].[Reputation], [A].[DisplayName] FROM [dbo].[Users] AS [U] OUTER APPLY ( SELECT TOP 1 * FROM [dbo].[vwHighReputation] AS [v] WHERE [v].[Id] = [U].[Id] ) AS [A] WHERE [A].[DisplayName] LIKE 'J%';
Then the situation gets bad....very, very bad....
My logical read count against that view is now almost 5 million. I can see from the plan that SQL Server is now choosing to perform a seek on the clustered index with the User's ID as the predicate, but doing so around 2.5 million times. It is also scanning the whole of the Users table. It no longer seeks on the view's index.
Obviously the optimiser is deciding that this is the most efficient approach, but I can't understand why! I've think it's probably to do with the way the underlying tables are sorted, but I'm not sure.
Incidentally, re-writing it as a simple SUB QUERY rather than CROSS APPLY yeilds the same outcome.
Any help or advice would be great!