December 2, 2010 at 7:36 am
1) I recommend you force each index to be used with a hint and see the actual plan costs and especially the actual IO used. This is a good way to learn that using an index seek/bookmark lookup is often BAD for performance. It is only efficient for VERY few rows as a percentage of the total table size. Likewise it can reveal scenarios where the optimizer gets it wrong due to bad estimates from stale statistics or bad data distribution skew. BEWARE leaving these hints in place in production code!! It is much better to address the root cause of the poor plan choice!
2) As Grant said it could be statistics related. And after the first execution it could be parameter sniffing, where subsequent executions use the previously-generated plan. This can be bad where you have non-even data distribution. Try statement-level OPTION (RECOMIPLE) or other methods to avoid this issue.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply