sql server 2008 - How to replace clustered index scan with a non-clustered index seek or clustered index seek? -

below create table script:-

create table [dbo].[patientcharts]( [patientchartid] [uniqueidentifier] rowguidcol  not null, [facilityid] [uniqueidentifier] not null, [visitnumber] [varchar](200) not null, [mrnnumber] [varchar](100) null, [timein] [time](7) null, [timeout] [time](7) null, [dateofservice] [date] null, [dateout] [date] null), 

i have 1 clustered index on patientchartid , 2 non-clustered index on visitnumber , mrnnumber. table has millions of records.

the following query doing clustered index scan:-

select  *    dbo.patientcharts inner join ( select facilityid                    facilities                   remoteclientdb in (                         select  siteid                            remoteclient ( nolock )                           code = 'in-esxi-edisc14'                                 )                ) filter on dbo.patientcharts.facilityid = filter.facilityid    

this clustered index scan taking lot of time in production because of data volume.

the execution plan :-

i have tried adding non-clusted index on facilityid , including patientchartid still same execution plan.

i doing dbcc freeproccache everytime instruct sql server use new plan every time.

is there else should prevent clusteredindex scan ?

the clustered scan occur since there no index support query. if index facilityid , patientchartid still potentially asking sufficient amounts of data scan due going past tipping point (google kimberly tripp tipping point)

there no easy way next part, system millions of records such trivial query causing problem, going have lot more aware indexing in general , how sql plan engine behaves. recommend kalen delany's sql internals , if search on here book recommendations, there questions number of solid recommendations.