CODEWITHSUNDEEP
sql-serverazure
Don Chambers
Don Chambers

Reputation: 4251

Azure SQL query slow

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.

Upvotes: 2

Views: 1075

Answers (1)

Brian MacKay
Brian MacKay

Reputation: 32019

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.

Upvotes: 1

Related Questions