2

I have setup an Azure SQL database. My goal to to evaluate it for use on a real project.

I have a simple query that is very slow.

select *
from table1 T1 join table2 T2
        on T1 .T2_id = T2.id

On my laptop SQL Server this is sub-second but in azure it's 11 seconds.

Table1 has 6,643 rows and table2 has 12 rows.

The execution plan is identical on both SQL Servers.

If I just return a count(*) it's sub-second in both places.

The execution plan contains a clustered index scan of Table1's primary key which is 90% of the cost (in both environments). With the count(*), this scan is not in the plan.

I am using the basic 'service tier' but there is very little data I am the only user. This is a test, but I can't put anything into production with this performance.

Edit: I upgraded to Standard tier, with "S2 50 DTUS's" - the highest in standard tier. I am still having the problem.

Don Chambers
  • 3,798
  • 9
  • 33
  • 74
  • You can read [this link](http://stackoverflow.com/questions/31086778/why-is-running-a-query-on-sql-azure-so-much-slower) – Nguyễn Hải Triều Oct 26 '15 at 02:16
  • This must be a *very* weak instance. That query is *nothing* even without indices. That's like a dozen pages read and a few ms of CPU. – usr Oct 31 '15 at 18:17

1 Answers1

1

SQL Azure IO is throttled using Database Throughput Units (DTUs). I suspect you are hitting your limit, although on the surface this certainly doesn't sound like an expensive query.

Here's a similiar question that goes into some in-depth analysis of a similiar problem: Simple select count(id) uses 100% of Azure SQL DTUs

Ultimately, the quick way to find out is this: try a higher-end plan for a few minutes and see what happens.

Community
  • 1
  • 1
Brian MacKay
  • 31,133
  • 17
  • 86
  • 125
  • I upgraded to Standard tier, with "S2 50 DTUS's" - the highest in standard tier. I am still having the problem. I updated the original question. – Don Chambers Oct 26 '15 at 03:06
  • On the monitor screen I see that the DTU percentage is 1.2%. I assume this means I have not reached the DTU limit. Why would this be so slow? – Don Chambers Oct 27 '15 at 02:21