Our MVP buddy Erland Sommarskog doesn't post articles that often but when he does, you should read them. His latest post is here:
http://www.sommarskog.se/query-plan-mysteries.html
It talks about why a query might be slow when sent from an application but fast when you execute it in SSMS. But it covers way more than that. There is a great deal of good info on how queries are executed and query plans generated.
Highly recommended!
Wow, what a great explanation. Thanks for linking that!
Thank you, that was awesome. Just saved my bacon.
One of the best articles on SQL Server I have ever read.
Well researched and clearly explained.