Help improving SQL Server 2005 query execution time

Question

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?

Answer 1

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

Answer 2

Your approach is correct: A clustered index scan basically loops through the whole table, which is something you want to avoid. Look at the details of the clustered index scan: There should be a WHERE predicate telling you which field is searched by the scan. Make sure that there is an index on this field (or these fields, if there is more than one), then try again.

Answer 3

That first query that causes 68% of the time spent - what does it look like? What does your table and its primary key look like?

If you find a way to eliminate that clustered index scan, things should be a lot better - but in order to help you find the problem, we need some more info! Please edit your original question and add those things to them - thanks!

Answer 4

You are doing a clustered index scan which basically means read in the whole table. How wide is your clustered index? Based on the logic of the query you are trying to perform, do you think you should be reading the whole table? This might be true if you want to group / sum on all of history in the table, but it is much more likely that your intention is to only look at a portion of the table. If the latter is true, please tell us more about the table, the clustered index columns, and the query you are trying to satisfy.