One of my stored procedure has long execution time (average around 4 to 7 minutes). I want to make it run faster.
I am looking at the execution plan and two things are contributing most of the cost. First represents 68% of the cost with a "Clustered Index Scan" of one main tables for reporting. This table has primary key of two columns and 2.000.000 records inside.
Second most demanding task represents 26% of the cost with a "Merge join" (left outer join). This is joining table I mentioned above via a foreign key to one out of the two columns of primary key in above table.
I checked Index physical statistic and index for these tables are in good condition.
What can I do to increase performance of this stored procedure? Do I need to make new index on these tables? Would
set transaction isolation level read uncommitted statement help to improve performance?