.NET to remote SQL Server slow on one machine, not

问题:

I'm developing a .NET 4.0 application on a newly setup Windows 7 desktop. Queries to the remote database are extremely slow in that application. The same application is running pretty fast on my laptop which I've used up until now. The desktop I'm using now is a lot faster than the laptop. I'm using a VPN connection to connect to the remote db server (same as on the laptop). It's slow in both IIS and Cassini.

I should add that the application is using Entity Framework for data access.

Does anyone have an idea what the problem could be?

UPDATE 1: OK, this is really getting weird. I looked at the queries in SQL Server Profiler on laptop and desktop. They're both the same. But on my desktop, the queries take longer and longer to execute. So I fired up Management Studio on the desktop and executed a query that I grabbed from SQL Server Profiler. It ran really fast (under a second). But when I executed it again it took 1 second. Then, 2 seconds, then 4 (see the pattern?). I can't for the life of me figure out what would be causing this. Here's the query:

    select top (2) [Extent1].[RequestID] AS [RequestID], 
[Extent1].[RequestDate] AS [RequestDate], 
[Extent1].[RequestTitle] AS [RequestTitle], 
[Extent1].[RequestTypeID1] AS [RequestTypeID1], 
[Extent1].[RequestTypeID2] AS [RequestTypeID2], 
[Extent1].[SubjectMatterID1] AS [SubjectMatterID1], 
[Extent1].[SubjectMatterID2] AS [SubjectMatterID2], 
[Extent1].[ProviderID] AS [ProviderID], 
[Extent1].[Description] AS [Description], 
[Extent1].[Notes] AS [Notes], 
[Extent1].[RequestedBy] AS [RequestedBy], 
[Extent1].[OrganisationID] AS [OrganisationID], 
[Extent1].[ProgramID] AS [ProgramID], 
[Extent1].[StatusID] AS [StatusID], 
[Extent1].[MethodID] AS [MethodID], 
[Extent1].[StaffID] AS [StaffID], 
[Extent1].[TopicID] AS [TopicID], 
[Extent1].[RequestCreatedBy] AS [RequestCreatedBy], 
[Extent1].[RequestCreatedOn] AS [RequestCreatedOn], 
[Extent1].[RequestModifiedBy] AS [RequestModifiedBy], 
[Extent1].[RequestModifiedOn] AS [RequestModifiedOn], 
[Extent1].[RequestedByExtraID] AS [RequestedByExtraID]
FROM [dbo].[Request] AS [Extent1]

But wait! It gets weirder! The following query always takes the same amount of time (less than a second), no matter how often I execute it:

select top (2) * From request

I have wasted an insane amount of time on this. If anyone has any pointers, you'd be in my book of cool people forever.

UPDATE 2 If I "Include Actual Execution plan" in management studio, the query is always very fast, no matter how often I execute it.

回答1:

Finally, after days of trying all kinds of different settings, and even reinstalling everything (including Windows) from scratch, I somehow figured out the problem. It was - and I still can't believe this - the network card settings. I disabled "IPv4 Checksum Offload", "Large Send Offload (IPv4)", "TCP Checksum Offload (IPv4)", and "UDP Checksum Offload (IPv4)". Now it's nice and fast...

回答2:

Well, i've experienced something similar in our app. Beware that ManagementStudio results may led you to incorrect conclusions. There is a parameter ARITHABORT on connection which is by default set to ON in MS, but from your app it is always OFF by default.

Links:

SQL Server ARITHABORT

https://stackoverflow.com/questions/2465887/why-would-set-arithabort-on-dramatically-speed-up-a-query

回答3:

Run a trace on the DB server and see what actual SQL statements are actually being run against it. I have a feeling your EF generates diff SQL than what you have in management studio. You may need to optimize it. VPN connection could also be part of the problem.

回答4:

Same thing happed to me and I spent days trying to figure it out. Queries from Entity Framework and ADO.NET to a remote SQL Server from my workstation would run extremely slow, but fast on all the other developer's stations. Disabling the Large Send Offload V2 (IPv4) immediately solved the problem.