We've had an issue reported where a stored procedure was behaving badly when called from some .Net code. On the face of it, that sounds similar to this case, which turned out to be due to EDMX, but in this case, we're just executing a stored procedure.
In the case today, when connected directly to the server holding the database, via SSMS, the query took 2 seconds; when running the query from the client application it was about 5 minutes, and when running the query from a linked server, it also took around 5 minutes. The degradation in performance "suddenly happens" without any changes having been committed.
From a comment on a previous answer, the suggestion wasn't due to query plans, even though recompiling the stored procedure did "fix" the performance for now. i.e. Recompiling the stored procedure makes it equally performant from both SSMS in direct vs linked scenario, and also to the .Net client. I am passing through exactly the same parameters through for comparison.
The stored procedure does a small amount of dynamic SQL (to unwrap a comma-separated list of IDs into a temp table, of the form INSERT INTO #Temp SELECT id FROM dbo.MyTable WHERE Id IN ( 1, 2 ,3 )) before going on to to the main query which joins a whole bunch (circa 20) of tables for the final result.
Ideally I meant to better understand the cause, such that this can be avoided in the future, so what should I be doing to stop this from happening?
Best Answer
The difference between SSMS and .Net client performance could well be down to how plans are cached. Part of the cache key is the session options - for me, at least, SSMS sets ARITHABORT, whereas .Net SqlClient does not, by default. You sound like you're ending up in the situation where a bad plan is cached vs. one cache key, and a good plan vs. another.