Help improving SQL Server 2005 query execution time

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?

If you post schema and query etc, that would be useful, in addition to the other 3 answers.

Some links anyway, from Simple-Talk. Good stuff.

• Execution Plan Basics

• Graphical Execution Plans for Simple SQL Queries

• Understanding More Complex Query Plans

StackExchange Database Administrators Q#12465, answer score: 5