SQL Azure very slow

Question

Hi Experts

We have SQL Azure database that is running very slow, so i took a backup of the database and restored on my on premise sql server express edition. 

I tried a update statement on a logs tables that have approximately about 1,062,367 records.

UPDATE logs set FileId = NULL
where LEN(FileId) < 36

The above update statement updates about 910,593 records on localhost host in 20 sec and if i run the same updates statement on SQL Azure it took 40mins and 16sec. The update statement was issues using SQL Server management studio installed on my local machine. With reference to internet connection, we have fiber connection with download speed of 70.62mbps and upload speed of 84.09mbps 

I am not sure whats going wrong with SQL Azure database i.e. could there be a specific fault on my database at Microsoft side etc.

Any advice or suggestion will be highly appreciated.

Kind Regards

Bhavesh

---

Bhavesh

Answer 1

hi Bhavesh,

I do not think it would be internet connection that may be causing it but to eliminate it completely, I would say run the script using portal, see if it makes a difference.

To further troubleshoot, could you let us know what service tier and performance level are your sql azure database? in any case i dont think it should take 40 min. lets see if we can figure it out.

-----------------------------------

---

Vishal Narayan Saxena http://twitter.com/vishalishere http://www.ogleogle.com/vishal/

Answer 2

Hi Vishal

Thanks for your reply.

Its a the basic service tier and performance level is set to Basic (5 DTUs)

Let me know if you require any further information.

---

Bhavesh

Answer 3

I have waited for 40 min for the update to complete and it has successfully and updated logs table so i can't re-run the update statement again because there will be no records to update.

I have managed to find another example that took about 10 mins to complete on the SSMS but when ran the same query on the azure management portal it took only 6 secs which puzzled me. 

Here is the query: 

select   
 l.Value3 as ClientReference,
 l.[Key] as ActivityUser,
 l.Value2 as FormDisplayName,
 l.Value6 as Status,
 l.DateStamp as LastUpdated,
 l.[Action] as FileDescription,
 l.CompanyId as ClientID,
 l.FileId as FileID,
 f.id
from Logs l
LEFT OUTER JOIN files f on f.Id = l.FileId 
where l.AccountId = 578
and type = 1

The above query returns about 3694 records only.

Here is the details of the query performance 

Azure Management Portal

• Duration(ms): 6487
• CPU(ms): 2443
• Logical Reads: 74729
• Physical Reads: 66147
• Logical Writes: 0

SSMS

• Duration(ms): 615259
• CPU(ms): 3666
• Logical Reads: 74729
• Physical Reads: 71370
• Logical Writes: 0

Any update will be appreciated.

---

Bhavesh

Answer 4

hi Bhavesh,

no clue why that might be happening, do you still see the issue? please let me know and if you still see it I would escalate it to SQL team to check it.

-----------------------

---

Vishal Narayan Saxena http://twitter.com/vishalishere http://www.ogleogle.com/vishal/

Answer 5

Hi,

Any updates on the above issue ?

Regards,

Mekh.

Answer 6

Hi Vishal

It is still happening so please escalate the issue to SQL Team and let us know.

Thanks

---

Bhavesh

Answer 7

thanks Bhavesh, I will reach out to them today, is there an email where you can be reached to provide any details if needed, you can email me at vishalishere [at] msn [dotcom]

Mekh, Kindly escalate this to the right team, I will also reach out to SQL Azure team and give them heads up.

---

Vishal Narayan Saxena http://twitter.com/vishalishere http://www.ogleogle.com/vishal/

Answer 8

It would be helpful if you could turn on "include client statistics" from SSMS so that we can see the timing details.  You can do this from the "Query" menu in SSMS (shift+Alt+S).

Tony

---

Tonyp

Answer 9

Hi Tony

Please find below client Statistics - this time the query took about 19 mins.

Let me know if you require any further information.

---

Bhavesh

Answer 10

Hi Bhavesh,

You are mentioning above that you are using a Basic database. You might be constraint by the performance the Basic Service Tier offers. You can query the DMV sys.dm_db_resource_stats in the database which gives you one hour of history on your resource consumption to see if you hit the limits of the Performance Level. If you reach the limits, you should try to run the operation while the DB is in one of the higher performance levels, for example S1 or S2.

If generally the performance is fine for you database in Basic, but you need to run more expensive maintenance operations once in a while, you should consider to upgrade the DB for the duration of these operations and then downgrade it back to basic afterwards.

Hope that helps,

Jan

Answer 11

based in the stats it looks like you are spending most of the time waiting for the server to return the 1MB of data.  I would recommend that you upgrade to an S2 and rerun the query and see how much better the results are.  This would cost you $0.10 for 1-hour of testing.

---

Tonyp

Answer 12

Thanks for your reply

I have updated the database to S2 and the query took about 1min and 26s.

Why the query is faster when running on SQL Azure portal directly?

---

Bhavesh

Answer 13

We are experiencing extremely low response time on East US SQL server. The latency has increased 10 times compared to normal.

The database is in 'Web' mode.

Is there any fix?

Answer 14

We have the same problems started with our SQL Database East US on 12/10/14. MS support cannot help, and it seems they even do not want to admit that the problem is on MS side, as they advise to rebuild and create missing indexes. I do not object against these actions in general, but this is irrelevant in this particular case, when during 1 hour the response times increased 10 times. Since that situation became even worse.

Does anyone know is there any solution?

Answer 15

Hi nick_gonpav,

Please have a look at this discussion about Azure SQL Database performance issue.

https://social.msdn.microsoft.com/Forums/en-US/a99c7ed9-d7aa-4ab2-802d-dd6f7360ea4e/east-us-database-very-slow?forum=ssdsgetstarted

Regards,

Mekh.

Answer 16

This is interesting, we also sudently started to experience performance issues with our production database around this day.

It is true there are a lot of improvements we can do, but the fact is that this performance issue happened from one day to another. In our case, the workload is stable, so I still believe is not an optimization issue.

It seems something in the infra structure has suddenly changed.

Any news about this?

Igor

---

.NET Software developer for industrial internet and automation system.