2

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.

  • 3
    You say Management Studio never demonstrates the problem because it's not over the network - but is that really true? You're RDPing into the box and running SSMS on the same server where the database is running? What happens if you use SSMS remotely? – Aaron Bertrand Jan 4 '16 at 19:55
  • 4
    Can you post the actual execution plan xml (not screenshot) of the slow sql ? Also, did you check the power options for the database server - balanced or high performance mode - since you moved your database to a different host ? Its worth running a fiddler trace to see whats going on intransit. – Kin Shah Jan 4 '16 at 19:55
  • Have you looked at blocking and wait stats on the SQL Server? Can you provide the actual T-SQL you're running? – Max Vernon Jan 4 '16 at 20:10
  • Thanks for the responses. We're going to put SSMS on another server and run some queries to see if we can reproduce the problem. I'll edit the question with our results. – James Jan 4 '16 at 20:12
  • 1
    I'd try to investigate the entire server setup (servers, network, disk etc). I'd also try to deploy the solution on another setup if possible with a different layout, just to see if the issue will exists there as well. You likely need to incorporate tracing tools at the various steps. – Allan S. Hansen Jan 7 '16 at 7:01
2

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.

  • You can turn it on at the database, I would advise testing this first (of course) or setting it off in SSMS and getting it to reproduce. Hope this works!! 1. Right click your database 2. Click Properties 3. Click Options on the Left pane 4. Turn "Arithmetic Abort Enabled" to "True" – IanG Jan 5 '16 at 16:59
  • Sorry, I spoke too soon. The procedure was working better, but after running some more tests we realized that it is still running slow. It was just temporarily working better, which is part of the problem we are having, can't find an exact pattern. – James Jan 5 '16 at 19:48
1

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.

  • 2
    That's an old workaround for parameter sniffing. It's the same as optimize for unknown in the newer versions of SQL Server. – Allan S. Hansen Jan 6 '16 at 16:50
  • @Michael Smith Thanks for the response, but didn't help with our current problem. – James Jan 6 '16 at 19:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.