Philip Turtle (3/3/2011)
If you paste the SQL into a query window in SSMS and press CTL-L you will see the estimated execution plan. SSMS may display a Missing Index suggestion and right-clicking the suggestion and choosing Missing Index details will show the SQL needed to create the index. Alternatively you could use the Index tuning tool available from the query menu.
Gail,
I created the two indexes.
One on primary and year including all the columns in select query
Second on secondary or year including all the columns in select query.
But there is still a table scan.I have good amount of memory so all the database stays in cache but the scan makes the cpu go up to 90 percent every time this query runs.Any more suggestions please.