I am working with sql server 2000 and 2005,could any one suggest me how to
optimize a select query which is also using aggregate function on some of its columns.
I have tried the following alternatives,
1. I have used index on the table which is using the column order same as in where clause.but in this case RID/KEY lookup is taking primary key index and consuming most of the cost of the QUERY EXECTION.
Execution Plan Of this case is Shown in Image-1
2. To avoid RID/KEY lookup,I have used INCLUDE Index,So that all the non primary keys are also included in index.In this case Sort Operator is consuming too much time and again i am at the same place,no improvement in reducing Execution time of Query.
Execution Plan for this case is shown in image-2 below where sort opertator is taking 70% of CPU Cost.
Please help me out of this issue.