Index to filter a view before joining the underlying tables Using SQL Server 2008 R2

Suppose you have a query like the following

select top 1 * 
from view_cms_user
where UserId = 1234 or UserPasswordRequestHash = 'anxazno121o3n'


view_cms_user is basically:

select *
from cms_user
left outer join cms_usersettings
    on cms_user.UserId = cms_usersettings.UserSettingsUserId


Now, the query execution plan looks like the follow: 

Now, this query takes a long time because the filter happens after the join between the two tables in the view, but cms_usersettings actually contains both of the fields necessary to answer this question, since UserSettingsUserId is a foreign key field referencing cms_user.UserId.

I tried creating a non-clustered index with both fields in it (UserSettingsUserId, UserPasswordRequestHash), but SQL Server ignored it. Currently it seems to being using an Index Scan of IX_CMS_UserSettings and doing nested loops with the key lookup operator to return all the fields on the table (because of the select *).

IX_CMS_UserSettingUserId contains the field: UserSettingUserId asc

PK_CMS_UserSetting is clustered key with UserSettingId (note: not the UserSettingUserId).

I cannot change the query, only the indexes.

If I change the query to an 'and' instead of an OR it's instant.

edit: In response to some of the comments below, there are already some indexes on the table in question, each in the image includes the column of the same name only:


Also including all the columns would be undesirable since there are about 50.
The table has 1,239,541 rows.

Also, UserPasswordRequestHash is 'Null' 95% of the time.
Could I through indexing alone influence SQL server to run this much faster.


Possibly.

There are all sorts of things you could try with indexing, including creating a filtered index to exclude the 95% of UserPasswordRequestHash entries that are null, expanding existing indexes to include more columns, or adjusting indexes so the chances of finding the first match is likely to occur earlier.

The query optimizer does not currently have the ability to explore separating the OR predicates into a UNION ALL, pushing each predicate below a local TOP (1) as a human query writer might naturally do.

It also has to make assumptions about the distribution of data to assess the chances of finding the first match, as mentioned above. In addition, there are all sorts of ways that the optimizer's calculations could be 'off', leading to it selecting the 'wrong' index strategy. Not being able to push index hints through a view also limits the opportunities for using a query hint via a plan guide, for example.

So, the best answer I can give from the information given is "possibly".

If you want a more concrete answer to your question, please supply complete DDL for the tables and indexes, and scripts for the existing statistics. Also, let us know how many logical processors, and how much memory, the instance is configured to use. I realize that is a lot of detail to provide, but a specific answer likely does depend on one or more of these details.