Performance Tips

Char vs Varchar
Because of the fixed field lengths, data is pulled straight from the column without doing any data manipulation and index lookups against varchar are slower than that of char fields. CHAR is better than VARCHAR performance wise, however, it takes unnecessary memory space when the data does not have a fixed-length. So in cases where disk size is not an issue, it is recommended to use CHAR.

Bottom line is to use the data type that fits our need. You can use SQL varchar when the sizes of the column vary considerably, use varchar(max) when there are chances that string length might exceed 8000 bytes, use char when the sizes of the column are fixed and use nvarchar if there is a requirement to store Unicode or multilingual data.



-------------------------------------------------------------------
Adding Extra Join conditions some time helps as it gives maximum chances for Optimizer to Build better Query Plan 

select * from
table fth
join  table2 dp

on dp.[col1] =fth.[col1]
and dp.indicator=1
and dp.source_system=fth.source_system

Instead do if possible

select * from
table fth
join  table2 dp

on dp.[col1] =fth.[col1]
and dp.col2 =fth.col2
and dp.col3=fth.col3
and dp.indicator=1
and dp.source_system=fth.source_system

---------------------------------------------------
To get rid of Sort in query Plan try using left outer join Instead of Inner Join
------------------------------------------------

SQL server uses Nested loop instead of Hash Match when it believes that Inner Table has very few records that you can see from estimated no of rows on inner table try updating the DB Stats if it doesn't work then force Left Hash Join instead of Left Join

--------------------------------------------------

To get rid of Nested Loop in query Plan

If Query plan is using Nested Loop for some joins - Look if you have used IN clause on an attribute with is NULLABLE i.e.  where Customer_ID in (select Cust_ID from customerProdcutTable).

Now if CustomerID is nullable in customerProdcutTable then SQL will use Nested Loop instead of hash Join 

If the attribute is nullable the SQL server will chose to use Nested loop as It needs to cater for NULL values. 

You could also try to use another clause in Join condition with IN clause i.e. where Cust_ID is NOT NULL this may help SQL Server to use Merge join.

Or 

you could instead use

where exists (select 1 from customerProdcutTable )

this will enable SQL server to use Merge Join instead of Nested Loop.