SQL Query is getting progressively slower

Question

We are experiencing a very weird problem when making consecutive query calls. Not sure what info is important, so I will give as much info as I can.

We have a webserver and database server through Rackspace. They are running Windows Server and Sql Server 2008 r2 express version for the database. The problem only began to happen when we moved our database to a remote server. There is no problem when both are on the same box.

Symptoms

When we make multiple SQL calls from code (PHP, ASP) to database server, the results will get returned at a slower, but consistent rate. The SQL call generally has to be the same call, and after the first two or three calls we see a slow down to 1 second, 3 seconds, 8 seconds, 25 seconds, timeout. When it slows down, the times are very consistent(1,3,8,25,timeout). And we can almost always cause the slow down, but it isn't always consistent.

It seems that a single selected result set is the hardest to reproduce the slowdown, but we can reproduce it. Just not as consistently as a more complex call with multiple result sets.

What we know.

• It's not a code problem, because we can reproduce using bare bones calls and from different languages.
• It's not the sql, because we can reproduce with extremely simple calls on tables with 10 rows. And the rows are only small integers. But it also happens on big tables.
• If we run procedures in SQL Management studio, there is never a problem. Only across the network.
• It also doesn't matter if we call raw sql from code (PHP, ASP), or call stored procedures to get result sets.
• We don't see any event log errors on the server, or at least we don't know where to look.

We're not sure what would cause this, or even where to continue our search. Any info would be great.

Edit

We added SSMS to another server, but there was no slow down to any of the queries.

The Sql Server version Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (Intel X86) Mar 19 2015 13:34:46 Copyright (c) Microsoft Corporation Express Edition with Advanced Services on Windows NT 6.0 <X86> (Build 6002: Service Pack 2) (Hypervisor)

Just to be clear, this is not a single query. We are seeing this happen on a very wide variety of queries. From selecting from a very simple table, with like 3 columns, 30 rows, and indexing. To a select with many joins across tables with 300,000 records. But when the slow down starts to happen, it happens consistently no matter how complex the sql.

Edit 2

The ARITHABORT ON did not solve the problem.

I've linked to one of the EXECUTION PLANS. This procedure probably causes the most consistent slow down. I just added it to a jsfiddle, html section. I'm assuming that would be ok.

Edit 3

Just some more information. The server is a dedicated server.

Also, we spun up a server on Amazon and made some calls to the database server(on Rackspace), and we had the same problem. Same slow down.

Update

I believe the problem is/was with Rackspace servers. We upgraded to a General Purpose server from a Standard. With 4x more CPU's, 2x more RAM, and 4x more bandwidth. But when we point to our old Standard server the slowdown issue goes away. Thanks for everyone's help.

Answer 1

Try SET ARITHABORT ON in your client application...

Why would SET ARITHABORT ON dramatically speed up a query?

I have almost always seen this to be differences between the ADO(usually OFF) and SSMS(ON) for the ARITHABORT setting. This setting can dramatically impact the Query Plan that SQL Server uses for certain queries. Here is a very detailed article (slow queries but SSMS works fine) and how to perform parameter sniffing troubleshooting to get to the bottom of it: http://www.sommarskog.se/query-plan-mysteries.html

One way to test this would be to set it OFF in SSMS (it is on by default in SSMS and off in client apps generally) and run the queries to see if they slow down:

From the MSDN article:

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.

Answer 2

I've seen this before when running parameterised stored procedures from an ASP/C# application. The fix I found for it was to save the parameter to a variable and then use the variable in the SQL queries.

CREATE PROC get_some_data (@id INT)
AS
DECLARE @local_id INT = @id

SELECT data FROM table where id = @local_id

I can't remember the reason why this worked but it reduced query times from a couple of minutes when run via our web app to match what I saw via SSMS where it ran under a second.