tag:support.appharbor.com,2010-11-23:/discussions/problems/399-sql-server-is-slowAppHarbor: Discussion 2012-01-04T05:30:16Ztag:support.appharbor.com,2010-11-23:Comment/65513282011-04-10T19:37:00Z2012-01-04T04:14:58ZSQL server is slow<div><p>Query was executed in 11 minutes :(</p></div>Bohdantag:support.appharbor.com,2010-11-23:Comment/65513282011-04-10T19:40:07Z2011-04-10T19:40:07ZSQL server is slow<div><p>Which server are you running on?</p></div>friismtag:support.appharbor.com,2010-11-23:Comment/65513282011-04-10T19:41:45Z2012-01-04T04:14:58ZSQL server is slow<div><p>db002.appharbour.net</p>
<p>db1698</p>
<p>query:</p>
<p>declare @p1 xml<br>
set @p1=convert(xml,N'113100pa desc100true')<br>
exec [dbo].[GetShapeStats_v2] @ShapeCriteriaXML=@p1</p></div>Bohdantag:support.appharbor.com,2010-11-23:Comment/65513282011-04-10T19:43:17Z2012-01-04T04:14:58ZSQL server is slow<div><p>query was cut, I am sending attached file</p></div>Bohdantag:support.appharbor.com,2010-11-23:Comment/65513282011-04-10T20:43:34Z2011-04-10T20:43:34ZSQL server is slow<div><p>We're in the process of implementing additional monitoring and
management of our SQL Server instances. The instance your database
is located on is heavily loaded right now. One possible resolution
would be for you to delete your current database and create a new
one. The new database would be assigned to a more lightly loaded
instance. I realise this is not very satisfactory, and I would like
to stress that we are working on ways to ensure more consistent SQL
Server performance.</p></div>friismtag:support.appharbor.com,2010-11-23:Comment/65513282011-04-11T11:54:36Z2011-04-11T16:32:58ZSQL server is slow<div><p>Hi,</p>
<p>I'm having the same problem. Getting sql timeouts from a simple
query to select a user by email address (only one record in table).
I'm on db003. Re-creating the db didn't create it on another server
either.</p>
<p>Hope you can help</p></div>tom.philiptag:support.appharbor.com,2010-11-23:Comment/65513282011-04-11T16:43:47Z2011-04-11T16:43:47ZSQL server is slow<div><p>@Bohdan the server your database is on is no longer heavily
loaded, are you still experiencing problems?</p></div>friismtag:support.appharbor.com,2010-11-23:Comment/65513282011-04-11T17:18:01Z2012-01-04T04:14:59ZSQL server is slow<div><p>I will check tonight (currently website is pointed to SQL
Azure)</p>
<p>Sending you some numbers from yesterday<br>
Locally - 2 sec<br>
SQL Azure - 14 sec<br>
appharbor - 26 sec</p></div>Bohdantag:support.appharbor.com,2010-11-23:Comment/65513282011-04-12T00:40:36Z2012-01-04T04:14:59ZSQL server is slow<div><p>appharbor is 24 sec today it is still not acceptable :(</p>
<p>What should I do to improve performance?</p></div>Bohdantag:support.appharbor.com,2010-11-23:Comment/65513282011-04-12T04:04:26Z2011-04-12T04:04:26ZSQL server is slow<div><p>Would you mind if I poked around your database to see what the
stored<br>
process does?</p>
<p>On 11/04/2011, at 17.40, Bohdan<br>
<a>tender+d9014efc09abc3ab24c8fe3aa1633c12817392d89@tenderapp.com</a><br>
wrote:</p></div>friismtag:support.appharbor.com,2010-11-23:Comment/65513282011-04-12T05:48:00Z2011-04-12T05:48:00ZSQL server is slow<div><p>Same problem: db1179 on db000.appharbor.net</p>
<p>Select * From [parts-Common] returns in 30 seconds for 1501
records. Usually this returns in less than 1 second (from my past
use on AppHarbor). Queries are executing so slowly now that my site
is breaking with SQL Server timeouts...</p>
<p>Please feel free to poke around in my database, repo, etc...
(whatever you need)</p>
<p>Cheers,<br>
-Jonathan</p></div>kg4lodtag:support.appharbor.com,2010-11-23:Comment/65513282011-04-12T17:03:47Z2012-01-04T04:14:59ZSQL server is slow<div><p>Michael Friis, you can play with db</p></div>Bohdantag:support.appharbor.com,2010-11-23:Comment/65513282011-04-19T01:40:28Z2011-04-19T01:40:28ZSQL server is slow<div><p>@Bohdan I've just tried running the query on my local machine
(fast Intel SSD, Core2 Duo) and it completed in around 12s. I've
also taken a glimpse at the stored procedure, and it looks rather
complex and has many joins. Is the 2 second number you are quoting
using the same ammount of data as on AppHarbor/Azure?</p>
<p>At any rate, I don't think the performance you are seeing on
AppHarbor is out of line within our expectations. If you need
higher performance, I recommend optimizing the stored procedure. We
can also discuss setting up a dedicated AppHarbor database server
on a faster instance for you.</p></div>friismtag:support.appharbor.com,2010-11-23:Comment/65513282011-04-19T01:55:48Z2011-04-19T01:55:48ZSQL server is slow<div><p>@jf I've just tried running that query, and it returns resonably
fast. The slow query time may have been an anomaly, please let us
know if it happens again.</p>
<p>It seems the induvidual rows in the [part-common] table are very
large. Unless you need all the columns, you can probably optimize
the query by only selecting the columns you need.</p></div>friism