Performance discrepancy in SQL Server Profiler between web query and the same query ran in SSMS

0
votes

I have a query that takes ~20x as long for SQL server to execute when it comes from a web request, as it does from when the exact same query is ran via SQL Server Management Studio

The following screenshot is from the SQL Server Profiler. The first two records relate to the receipt and execution of query that's come in via the web request, whilst the third record is the exact same query run from SSMS. Why would there be a such a huge difference between the two?

A point: The query is generated from LINQ. I took a copy of the generated SQL and ran it in SSMS to get these results.

enter image description here

1
sqlsql-server-2005linq-to-sqlsql-server-profiler
Duration includes the time it takes to transmit the data back to the client. What are you doing with the data in the web application? - Lasse V. Karlsen
Ah, i did not realise that. The web application does a chunk of aggregation on the data, but that only takes about a second or so. I guess i should do all this aggregation on SQL Server instead. - MrBliz
What do logical reads say? Is there a big discrepancy? If so may be parameter sniffing. - Martin Smith

1 Answers

0
votes

ARITHABORT is ON by default in SSMS and OFF by default for a SqlClient connection.

See if this solves your issue:

new SqlCommand("SET ARITHABORT ON", connection).ExecuteNonQuery();