You have a two table query here with a join. One of the tables needs to be the outer table for the join and one of them needs to be the inner table. With appropriate indexes on both tables, as a general rule I would want the most selective table to be the outer table for a
TOP 100 query. As far as I can tell, SQL Server picked the correct table for the outer table,
AnalyticsSession. The problem appears to be that there isn't a selective enough index defined against that table.
Let's consider what would happen if the optimizer used the
AnalyticsUser table for the outer table. That filtered index looks to be the best possible index for the query. However, the index isn't selective. In the comments you indicated that 97% of the rows in the table match the filtering that you have on
[UserId]. So SQL server might end up scanning a lot of rows from the index until it found the first 100 that satisfied the filter conditions for both tables.
Compare that to using the
AnalyticsSession table as the outer table. The filter seems to be pretty selective (you had to process 113,000 rows just to get 100 matches), but the index used is not selective at all. A clustered index scan will just go in whatever order the data is logically or physically defined on disk. If you created an index similar to the following:
CREATE NONCLUSTERED INDEX [IX_AnalyticsSession__CreatedOn_Id] ON [dbo].
( CreatedOn, AnalyticsUserId)
INCLUDE (id, UserAgent, IP, Guid, CreatedOn, UpdatedOn, IsDeleted, AnalyticsUserId, SiteId)
I suspect that would help query performance quite a bit. SQL Server would be able to do a scan on that index to immediately jump to the first possibly relevant row from
AnalyticsSession. If 97% of the rows from
AnalyticsUser match the filter condition and the filters between the two tables are independent, then you should be able to get the first 100 rows with a scan of 110 rows or less from the new index on
If the filters are not independent then your query may scan more rows than expected. For example, suppose that users who registered near the beginning of the site are more likely to have an email or an id. With the index as defined above, SQL Server is likely to scan the oldest rows first by
CreatedOn. As you described in your comment you may get better performance by getting the scan to go in descending order rather than ascending order. The best way to do this is to add an ORDER BY at the end of your query:
(analyticsu1_.Email IS NULL
AND analyticsu1_.UserId IS NULL)
AND this_.CreatedOn < '2017-04-01'
ORDER BY this_.CreatedOn DESC
Otherwise you aren't guaranteed to get the scan in the order that you're expecting. Note that
TOP 100 without
ORDER BY is nondetermistic and may return different results from one execution to the next. Consider adding an
ORDER BY to your query if that makes sense to do so.
If you're interested in learning more about how SQL Server optimizes queries with
TOP in general I recommend Inside the Optimizer: Row Goals In Depth by Paul White.