Help understanding SQL Server 2005 execution plan

Question

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 2000000 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 ?

Answer 1

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 2

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 3

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.

Answer 4

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