SQL Server DTA tool recomendations

Due to the database size: How many users and what are your hardware specs?

Did you start with health checks? E.g. Brent Ozar's sp_blitz?
Did you look at the standard metrics? Memory usage, outstanding disk IO, CPU usage?
Did you look at the TOP IO/Performance queries in SSMS standard reports? Are they optimal?

Your database may have repeated indexes or indexes that are already covered by other existing indexes.
Can you provide the Query Execution Plan for one of your long running queries?

please find the attached execution plan with query.

Hardware spec: 256 GB RAM (2.80Ghz 6 processors).  CPU usage and IO usage is normal during the query execution.
SQL Server allocated memory (Min memory: 0 MB and Max memory: 185000 MB). MAXDOP value is 1 and we are executing index rebuild job every day to Defragment indexes.
We have hosted 2 DB's on this DB box (Microsoft Dynamics CRM DB)  and total two DB size is around 1.20 TB).
ExecutionPlan.sqlplan
Query.sql

MAXDOP 1 an vendor recommendation and I am using Ola Hallengren's scripts for DB maintenance.

Currently I am in the process of analyzing top expensive queries using suggested Brent Ozar's sp_Blitz.

Btw, I am herewith attaching table structure and current indexes of businessinteraction table. Do you have any suggestions on current indexes? do you want me to create new index with include columns (above your suggestion)? Row count of businessinteraction table: 111950721
Tbl_indexes.sql

I have done performance test step by step. The only below 4 columns in the attached query is taking more time (If I comment below columns in the attached query, I am getting output in 0 sec for 11 rows, If I have not comment below 4 columns in the query, the attached query is taking 25 min for 11 rows)

4 Columns:

businessin1_3_.isrequired                         AS isrequired186_1_, 
businessin1_4_.isjournaled                        AS isjourna2_187_1_, 
businessin1_5_.policy                             AS policy188_1_, 
businessin1_7_.policy                             AS policy190_1_

Select allOpen in new window

Tried to create Indexes for above 4 columns but still no luck

create nonclustered index ndx_BusinessInteractionCustomerSpec on 
BusinessInteractionCustomerSpec  (isrequired) /*isrequired datatype BIT - NUMBER OF ROWS IN THIS TABLE:5 */
go
create nonclustered index ndx_BusinessInteractionJournaledSpec on 
[BusinessInteractionJournaledSpec]  (isjournaled) /*isjournaled datatype BIT - NUMBER OF ROWS IN THIS TABLE:5*/
go
create nonclustered index ndx_BusinessInteractionPriceSpec on 
[BusinessInteractionPriceSpec]  (policy) /*policy datatype INT - NUMBER OF ROWS IN THIS TABLE:5*/
go
create nonclustered index ndx_BusinessInteractionReceiptSpec on 
[BusinessInteractionReceiptSpec]  (policy) /*policy datatype INT - NUMBER OF ROWS IN THIS TABLE:5*/

Select allOpen in new window

Can you please advise?
Query_modi.sql

In this case you should for locking or other wait issues during execution. Either execute sp_who2 or use the activity monitor in SSMS.

I would not have expected that those further indices can help. How many rows contain each of those four tables?
btw, what data type has your ID column in those tables and the base tables (businessinteraction, BusinessInteractionRequest)? I hope it's not UNIQUEIDENTIFIER.

p.s. why one earth are you storing passwords?

I have not found any locking or deadlock problems during the query execution. The data type of ID column is UNIQUEIDENTIFIER (in both tables - businessinteraction, BusinessInteractionRequest). All those 4 table having 5 rows in each table.

I have removed suggested indexes from table, still there is no change in query execution time. Currently this query only taking more than 25 min for 11 rows. the other queries are taking 1 min 33 sec for 100 rows. I am herewith attaching table structure of BusinessInteraction table and we need ID column with clustered.
BusinessInteractionTable.sql

I have removed suggested indexes from table, still there is no change in query execution time.

Hardly removing indexes will improve a query performance. It's only a cleansing procedure as well a way to reduce table size.

Currently this query only taking more than 25 min for 11 rows

But you're retrieving the 11 row from hundred of millions of records. It's a slow process, specially when your database design isn't the best.

i have redesigned BusinessInteraction table as per your suggestion. still query is taking same execution time. please find the attached new table structure with indexes and execution plan of the query. Do I need to redesign any other tables that are involved in attached query to reduce the query execution time?
Table.sql
execution-plan.sqlplan
Query_modi.sql

I have done changes as per your suggestion, still no luck :) Can you please review the attached table and indexes?
Table.sql

You need to check the other tables also. They should not use the referenced NEWID value as clustered index.

Any news of this?

A lot of good inputs provided in the above comments.