jc85 (5/24/2016)
Added start_time as Non-clustered index for table ann_events_Tech_Details.
Make it clustered.
Otherwise - drop it, no use of it anyway.
Added report_id, report_item, call_flow_name, source as Non-clustered index for table ann_ReportItem
[Source] must come first.
You do not need report_item in index pages, it will be good enough just being INCLUDEd.
SQL Exec Plan -jc85_execplan
SSMS suggested adding Source to the index as well so I run another round with it.
SQL Exec Plan - jc85_execplan_wo_index
Without non-clustered index
SQL Exec Plan - jc85_execplan_wo_index.sqlplan
You have still DATEADD-DATEDIFF applied to [start_time] in WHERE clause.
If you do not intend to fix that - forget about indexes. They're not gonna help you.
_____________
Code for TallyGenerator