One of my stored procedure has long execution time (average around 4 to 7 minutes). Now I trying to tweak it an make it run faster.
I am looking at execution plan and two things I see that using most of percentage. First is 68% of "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 is 26% of "Merge join" (left outer join), this is connection between already mentioned table and other with foreign key to one of two columns of primary key in first table.
I checked Index physical statistic and index for these tables are in good conditions.
What can I do to increase performance of this stored procedure? Do I need to make new index on this tables? Does
set transaction isolation level read uncommitted statement help to improve performance?