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
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.
When we make multiple
SQL calls from code (
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 (
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.
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.
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.
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.
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.