2 comments

@jyao 2018-04-12 15:49:26

To be more accurate, I would add another possibility is that your index rebuild has updated (not recreating) the statistics, and this caused recompilation of the execution plans (instead of using the cached execution plans).

With this theory (i.e. statistics updated -> execution plans recompiled), I'd say you may just need to do an statistics update on your table SERVICE_POINT_SESSIONS, like the following

update statistics SERVICE_POINT_SESSIONS with FULLSCAN, index;

It is common for DBAs to do a statistics update first before rebuilding index to check whether a query performance is improved. The reason is stats update is light-weight compared to index rebuild in overhead cost.

@Max Vernon 2018-04-12 15:34:08

You said you rebuilt the index and now both queries are performing index seeks, as desired.

This is most likely due to the index rebuild operation recreating the statistics for the affected columns. Now that that query optimizer has updated statistics, it knows it would be more efficient to seek the index.

You may want to ensure you have an index rebuild job that runs on a schedule - I'd recommend looking at Ola Hallengren's solution for that.

Related Questions

Sponsored Content

2 Answered Questions

2 Answered Questions

[SOLVED] why it is doing index seek

3 Answered Questions

[SOLVED] Why does this Index seek cause a lot more reads than index scan

1 Answered Questions

[SOLVED] Adding 2 Case statements

  • 2015-10-29 13:59:01
  • BlueLiner
  • 2085 View
  • 1 Score
  • 1 Answer
  • Tags:   sql-server case

1 Answered Questions

[SOLVED] Why SELECT COUNT() query execution plan includes left joined table?

1 Answered Questions

[SOLVED] How many LEFT JOINS make a VIEW unusable?

3 Answered Questions

[SOLVED] Change table scan in query plan to seek

1 Answered Questions

Sponsored Content