sqlsql-serverdatabase-performancesqlperformancedatabase-tuning

SPROC Slow from .NET but 5 times faster from SSMS

I have followed answers to similar questions already, including setting ARITHABORTH ON/OFF, clearing out buffers using DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE. Also tried converting parameters to local variables. But still having performance issues.

enter image description here

As seen in the screenshot, the same procedure from SSMS takes about 625 ms, but from the .NET client, takes about 3.5 seconds.

What is interesting is that data reads are also different. The "Binary" column has some data for the .NET client, but not for SSMS, but I am not an expert and do not know why.

Any help would be much appreciated.

Technology: SQL SERVER 2016 Standard

The underlying table contains about 3 million rows The parameters taken in by the procedure are usually very similar.

Once again, thank you in advance for your help.

Solution

  • Because I'm using Entity Framework, the connection string in my application has MultipleActiveResultSets=True. When I remove this from the connection string, the queries have the same performance in .NET and SSMS.