0

I have a very weird scenario occur at work today in our production system. Wondering if anyone has seen anything like this and have a good explanation for me.

We have a stored procedure in Sql Server 2014 and it was not returning any data when our .NET system called it.

We captured the call using Sql Profiler and replayed it in Sql Management Studio using the same Sql Authentication credentials and it returned results as expected.

No matter how many times we tried each interchangeably, they were consistent in that when the client was the .NET Client, it gave no results and when it was SSMS it worked fine. Keep in mind, its the exact same sp, params, etc.

We were able to resolve the issue by doing an SP Recompile but that feels like a temporary solution and not know the original cause means that it can recur without warning. Furthermore, I was under the impression that sp recompile only affects performance issues not differing results.

Has anyone seen this before? Can you explain why an sp recompile fixed it?

Many Thanks!

DancingZorba
  • 13
  • 7

1 Answers1

1

Usually what you see is that the query will execute just fine with SSMS, but with the .Net client it will time out. That might be what you're describing here.

There's some debate about the exact cause of this issue.

For one side, problem is that SSMS and the .Net Client have different defaults. The most common offender is ARITHABORT, which SSMS set to ON but most SQL Server providers leave at the server default (OFF):

WARNING

The default ARITHABORT setting for SQL Server Management Studio is ON. Client applications setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application. When troubleshooting queries with Management Studio always match the client ARITHABORT setting.

This results in a cached query plan (a fairly complex topic) that works well in SSMS, but not so well with the.Net client.

For the other side, the problem is just parameter sniffing, meaning your stored procedure has a bad plan cached. This side argues that the ARITHABORT setting causes the server to select a different plan, skipping the bad one. But the core problem is the parameter sniffing, and the ARITHABORT setting is actually a workaround.

This SO question covers a lot of the possible solutions (setting ARITHABORT ON, using OPTION RECOMPILE, using OPTIMIZE FOR UNKNOWN, etc.). That question also links to the seminal work of Erland Sommarskog, Slow in the Application, Fast in SSMS?: Understanding Performance Mysteries, which is probably more than you'll ever, ever want to know.

Community
  • 1
  • 1
Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • Thanks for the response! I can see this is a rabbit hole of discussion but one worth having! I will bring this back to my team and can do further research that n the topics you recommend. Thanks again! – DancingZorba Dec 18 '16 at 13:31