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?

  • For clarification: 1.) Are you saying that recompiling the procedure and then running again from the client DID fix the problem for now? Or are you saying that worked for the linked question but not you? I'm confused :-) 2.) Are you supplying different parameters to the proc locally vs what the app was using? 3.) Does the proc have any dynamic SQL in it? Can you post the stored procedure create code in your question if not overly complex?
    – Mike Walsh
    Feb 10, 2012 at 16:36
  • @MikeWalsh has updated to address your queries; in short: yes, yes and yes :) Feb 10, 2012 at 16:56
  • Have you considered creating a name-value pair user defined table and passing that in to your SP as the list of Ids rather than a string of Ids? It would eliminate the need for your dynamic sql.
    – Ben
    Feb 10, 2012 at 23:42
  • Could this be the answer? Mar 21, 2012 at 23:16

2 Answers 2


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.


SQL Performance... its been a while.

Have you considered using a function for splitting delimited values rather than dynamic SQL?

Are you using the regular ADO.Net SqlClient objects to execute the SP?

And a random question from a previous problem that bit me on the arse.... are you running the query in SSMS on the same machine the client app is being tested? I have seen an issue with large data sets over flakey networks. As a dev, I had a different route to the server than the clients did.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.