Is there an option to have Optimizer suggest the "best alternative" based on criteria other then elapse time?
You can select the run time statistic to use as the criteria to select the SQL alternative that provides the best performance. This is under Options | Optimize SQL | Batch Run | Best Alternative Criteria, there are 5 options:
- Elapsed Time
- First Row Time
- Physical Reads
- Session Logical Reads
- CPU Used by this Session
A similar setting can be found under Batch Optimize when using the Batch Optimizer window (instead of the Optimize SQL window).