Optimize query with join and null predicate [SQL Server] I have the following query which I am having problems optimizing: 

SELECT TOP (100) this_.Id AS Id80_1_,
   this_.UserAgent AS UserAgent80_1_,
   this_.IP AS IP80_1_,
   this_.Guid AS Guid80_1_,
   this_.CreatedOn AS CreatedOn80_1_,
   this_.UpdatedOn AS UpdatedOn80_1_,
   this_.IsDeleted AS IsDeleted80_1_,
   this_.AnalyticsUserId AS Analytic8_80_1_,
   this_.SiteId AS SiteId80_1_,
   analyticsu1_.Id AS Id15_0_,
   analyticsu1_.Email AS Email15_0_,
   analyticsu1_.DateLastChecked AS DateLast3_15_0_,
   analyticsu1_.Guid AS Guid15_0_,
   analyticsu1_.CreatedOn AS CreatedOn15_0_,
   analyticsu1_.UpdatedOn AS UpdatedOn15_0_,
   analyticsu1_.IsDeleted AS IsDeleted15_0_,
   analyticsu1_.UserId AS UserId15_0_
FROM [AnalyticsSession] this_
INNER JOIN [AnalyticsUser] analyticsu1_ ON this_.AnalyticsUserId=analyticsu1_.Id
WHERE (analyticsu1_.Email IS NULL
       AND analyticsu1_.UserId IS NULL)
  AND this_.CreatedOn &lt; '2017-04-01'


I have tried adding an index in multiple ways including:

CREATE NONCLUSTERED INDEX [IX_AnalyticsUser_UserEmailIsNull1] ON [dbo].
[AnalyticsUser]
(   [Email],    [UserId])
INCLUDE (Id, DateLastChecked, Guid, CreatedOn, UpdatedOn, IsDeleted)
WHERE (Email IS NULL and UserId IS NULL)
WITH (online=on)
GO


However my query plan shows sql server scanning and joining 113,000 rows to get the 100 rows returned: (both ActualRows="112806" ActualRowsRead="112806" on the scan operator)



This database is on azure in compatibility mode 130.

What is the best index to add to optimize this query? There are 2355366 rows in the AnalyticsUser table.
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 [Email] and [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].
[AnalyticsSession]
(   CreatedOn, AnalyticsUserId)
INCLUDE (id, UserAgent, IP, Guid, CreatedOn, UpdatedOn, IsDeleted, AnalyticsUserId, SiteId)
WITH (online=on);


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 AnalyticsSession.

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: 

WHERE
(analyticsu1_.Email IS NULL
AND analyticsu1_.UserId IS NULL)
AND this_.CreatedOn &lt; '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.