Force using an Index in Inner Join SELECT I have the following query: 

select  field1, field2 from table1 t1 with (index(IndSearch))
inner join table2 t2 ON t2.Id = t1.Id 
where t1.date between '04/01/2019' AND '05/01/2019' 
AND t2.field3 IN(....) AND t2.field4 IN(...)



Table1 has clustered index in t1.Id 
IndSearch is a nonclustered index with (t1.Id, t1.date and other fields) 
Table2 has a nonclustered index with (t2.id, t2.field3, field4)
Table1 contains more than 5M rows 
Table2 contains more than 11M rows 


The problem is the query analyzer always chooses the index in Table2. That option is like 7 seconds slower because there are more rows in that table. 

I would like to hint the query analyzer to use the index IndSearch, searching with the date field. I have done some tests and that search is faster. 

I did try using with (index(IndSearch)), however that did not work. 

I did try using left join instead inner join. That changed the execution plan.  In fact, that modification improved the response time, now the query analyzer first do an index scan over IndSearch. 

I think that did solve my problem, however I am still curious if someone has any comments or suggestions. I will appreciate if someone can explain how to achieve a similar result using query or table hints.

You can take a look in the execution plans: 

Inner join (7-8 seconds duration): 
https://www.brentozar.com/pastetheplan/?id=BJl-Lg5jE

Left Join (almost instantaneous): 
https://www.brentozar.com/pastetheplan/?id=ryya8gqoN
The goto solution to improve queries should not be query or table hints.

You should try other solutions like changing the query, adding or changing indexes first.

If the optimizer is still making mistakes after trying other workarounds then you might try adding a query / table hint.

Table variable estimates

The table variables have low estimates (one , two rows) while 850 rows or even 16.284 rows can be returned. 
This is impacting the rest of the query.

Estimated plan on the inner join query:



Actual query plan on the inner join query:


Consider changing the table variables to temporary tables:

CREATE TABLE #lTemas(TemaId int);
INSERT INTO #lTemas (TemaId)
SELECT t.TemaId FROM Temas t
INNER JOIN @lClientes lc ON lc.n = t.ClienteId
AND nEstado &gt; 0;


or adding OPTION(RECOMPILE) to the main query.



Indexing

Next to these awful estimates, the query gets filtered from 127.689 rows to 4.071 rows at this point:



This could be due to the predicate on dNota



** possible fix**

Consider adding an index on dNota first

CREATE INDEX  IX_dNota_ArtId
ON dbo.NotasHist(dNota,ArtId)
INCLUDE(bTipo,idDominio,iFuente);


and removing the with (index(Buscador)) hint to see if the optimizer will use the index with the better estimates.



The main difference with the left join

The filtering is happening much sooner on the left join:



The estimates are higher



And due to the higher estimates the query is going parallel



Whereas on the inner join you are not using parallelism.

This and other considerations make the difference in execution time happen, fixing the estimates by adding option recompile or changing the table variables to temp tables would be the first thing to look into.