We have a stored procedure that returns a result set of around 200 rows, it works pretty well most of the time, but for some reason the Engine is not updating the execution plan of this procedure or is changing it because it chooses to do full index scan instead of an index seek. Causing that query that executes in 30 ms takes around 5 minutes to complete.
The USP is being called from and ASP
The auto update statistics and the auto update statistics asynchronously are set to ON. The index fragmentation is always less than 5%.
Have you seen something like this before?